Net Present Value – how to get it wrong in an Excel model
In the spreadsheet at exercise 4 we calculated that the sum of the project’s discounted cash flows was 43.26. To get from DCF to NPV we subtract the cost of the initial investment. If it’s a project we’re investing in, perhaps those costs consist of all the new plant and equipment we are going to have to buy. If it’s a company we are buying, perhaps it’s what we are having to pay for our investment.
Imagine we were deciding to invest in a power station. Revenues consisted of electricity sales to the local power company. Expenses consisted of fuel and maintenance costs. We had built a model which projected the power station’s cash flows and had discounted those cash flows as per exercise 4. The value of the project’s discounted cash flows is 43.26.
To calculate the NPV of the project, we would subtract the cost of investing in the power station – say 30m. You can see that happening in exercise 5. NPV equals 13.26.
NPV is a measure of return and a key tool in investment appraisal. Someone sitting in an M&A department of a large power generation company will be inclined to invest in projects that generate a positive NPV. The cash flows from a positive NPV project, discounted at the cost of funds, are higher than the initial cost of investment. A positive NPV project has a return greater than the cost of funds.
Have a look at cell B65:
- What happens to the NPV if you change the cost of funds in cell B65? Does NPV increase or decrease?
Financial modelling: how to get it wrong in Excel
Look again at exercise 5 in the Excel spreadsheet. Notice the use of the Excel formulae at cells B76, B74 and B78. Click into the cells to see how the formulae are constructed.
- Which do you think is the wrong answer? Which do you think is the right answer? Why?
Click here if you want to check your answers.
Pitfalls when using the NPV formula in Excel financial modelling
The NPV formula in Excel discounts the very first cash flow the formula is looking at. If you click on cell B78, you will see that formula applies the 9% discount rate at cell B65, and then discounts the first cash flow at cell B68. Because the 30m is invested right at the start of the project, with no delay, it shouldn’t be discounted.
Cell B74 shows you how to construct the NPV formula correctly: pointing it to the future cash flows and then subtracting the initial cost of the investment.
Financial modelling trap no. 1
We have identified our first big trap in Excel! The NPV formula in Excel discounts the first cash flow you point it to, whether you want it to or not. Maybe the tables are safer than using the Excel formula as a shortcut!
Return to the advanced financial modelling course page
Please click to return to the advanced financial modelling course page.
Back to top