4. Calculating Present Values in Excel
Excel is a great tool for calculating present values (PV) quickly. In this lesson, we'll build a simple PV model to help us value a windfarm investment project.
Calculating present values in Excel
- Excel is a great tool for calculating the present value of future cashflows
- In this example of a windfarm, we have 10 separate future cashflows
- To value the windfarm, we simply discount each cashflow back to its PV and sum these values
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
In the previous lesson we derived the following formula for discounting future cash flows back to their present day value.
In this lesson, we'll apply this formula to value a wind farm which has just been put on the market.
The wind farm has been operating for 15 years and has 10 years' operating life remaining.
The local utility has signed an agreement to buy the electricity from the wind farm at a fixed price.
After operating costs and taxes have been deducted, the wind farm provides an annual cash flow of $750,000.
The owner of the wind farm has put it on sale for $5,000,000.
And the question is, do we buy this asset? In my Excel sheet, I’ve included the main assumptions in this blue panel, and for sale price, annual cash flows and discount rates, I've named these cells.
Our first task will be to enter the annual cash flows for each year.
And to do this, I'll go to my first cell and simply let it equal annual cash flow.
And I'll copy this formula for the remaining years with Alt + E S F.
Next I'll discount each of these cash flows.
And to do this, I'll return to my previous formula.
And from the formula, I'll simply divide the cash flow by one plus the discount rate all to the power of the number of years into the future that we received that cash flow.
So in my formula in Excel, I'll write "=", select the annual cash flow, and then divide by one plus the discount rate, all to the power of the number of years, which is simply cell C10.
I'll then close the bracket and press Tab, and this tells me in the first year that my annual cash flow of $750,000 has a present day value of $681,818.
I'll simply copy this formula for the remaining cells.
And as we’d expect, the value of our annual cash flows decreases over time.
Obeying the time value of money concept, the cash is more valuable the sooner it is received.
To calculate the value of the wind farm, we simply add these discounted cash flows together.
So I'll write "=sum", select the cash flows and press Enter.
And this tells me that our wind farm is worth $4,600,000.
So for a discounted rate of 10% this wind farm is currently overvalued.
What happens if I change the discount rate, to say 8%? Well, now the present value of the cash flows is just above $5,000,000.
And perhaps we should buy the wind farm.
So which discount rate is correct? I'll help you answer this question in the next lesson.
Before then, if you'd like some practice, I've left another wind farm investment on the next sheet as an exercise.
I'll leave my answer to this exercise in the after file below the video.