EXERCISES: NPV, IRR and Debt Examples
Example 1- PE Multiple Calculator- Question
We want to compare the existing PE multiples on listed companies to what it implies about the required growth in earnings. So in C7 we have the Earnings per share and in C8 the current price (we have some sample EPS and share price data in H8 to I11).
- In row 15 we have created a growth row. For the first 3 years we have set up the growth to be the same (we can predict the next 3 years growth reasonably accurately) and then from year 4 onwards we have an input for terminal growth (the company reaches a steady state).
- Row 16 takes the EPS and grows it according to the assumptions in row 15.
- In C18 you need to determine the present value of these earnings based on the discount rate in cell C11.
- You then need to create a data table in E22 to K30 to see what the share price should be at different levels of terminal growth and initial growth.
- You need to add conditional formatting onto the data table so that you have:
- A colour scale where the suggested price in C18 is the middle (yellow) and anything below that turns green and anything above it turns red.
- Cells get shaded if the resultant value is between 90% and 110% of the actual current price in cell C19 (so if the actual current price is 100, cells will shade if they are a price of 90 to 110).
Example 2- Retirement Spreadsheet- Question
We need to determine how much in savings to we need to retire for a set life span.
In B1 to B12 we have the inputs which indicate:
- Current income and existing savings
- Anticipated inflation and investment returns above inflation
- The current age of the person, the planned retirement age and predicted age of death
- The % of current annual income you would need in retirement
- The % of current income that will be saved.
In column E, F and G we have the details of ages and when retirement happens
In columns H to J we have calculated what annual income will be based on inflation growth.
In column L to P we need to calculate how much our savings will be:
1. In column N, determine the growth of the saved assets based on the expected Net return (cell B5) based only on the opening balance
2. In column O, determine the growth on the savings during the year assuming that it flows in evenly through the year (so divide the current period savings by 2 to get an estimate)
In columns R to U we have shown the net position in retirement being the savings, what they will earn, what we need to spend and the closing balance. You need to
1. In column T, calculate our anticipated expenses based on the required level (per cell B10) and the income we would be earning if still working (column I)
You will note that we have set up the closing balance to show a -1 when we run out of money.
In column AC we want to determine how much in savings we should have in order to retire comfortably.
1. Create a formula that will look at the annual expenses we need to fund from date of retirement to date of death and present value them to date of retirement based on the net return expected in B5.
Example 3- Multiple Discount Rates- Question
We have a project that is very risky in the beginning (during construction) but then is low risk. We want to apply different discount rates per year to determine the Net Present Value.
- In E16 to I16, determine the discount factor to use given the required discount rates in cell E8 to I8.
Example 4- CAGR (Compound Annual Growth Rate)- Question
We need to determine the Cumulative Annual Growth Rate in Cash Generated over 5 years. The cash flows are shown in E10 to I10. In row 25 to 27 we have created a check bucket to see if your calculation correctly grows to the required number.
Calculate the CAGR in 3 different ways:
1. In B13 use the Rate function
2. In B15 use the IRR function (use E15 to I15 as helper cells if you want)
3. In B19 use the XIRR function (use E19 to F19 as helper cells- we have entered the dates for you).
Example 5- Loan Calculation based on required IRR- Question
We have a funder where we need to guarantee a set AFTER Tax IRR return to them.
At the end of the 12 months period we want to know how much we must pay to cover this.
- In cell C5 we have the required REAL return
- In C6 the current CPI and C7 gives the total return required
- In C8 we have the tax rate
You need to:
1. In C9 calculate the pre-tax effective rate required
2. In c10 convert this into a nominal interest rate to be used on a monthly basis
In row 14 we have shown some ad hoc payments that we will make during the 12 months.
In row 17 to 22 we have built a normal debt bucket
To complete this:
1. In row 19, calculate the ‘interest’ to be charged each month based on cell C10
2. In row 20, show the ad hoc repayments we make that reduce the ‘loan’ based on row 14
3. In cell P21, calculate what the final repayment needs to be to achieve the guaranteed return
4. In cells D25 to P25 show the cash flows that can be used in the IRR calculation in B27. This must result in the same return as required in C9.
To see the full course contents click here.