In the DCF exercise we calculated that the sum of the project’s discounted cash flows was 37.3. To get from DCF (discounted cash flows) to NPV (net present value) 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. To calculate the NPV of the project, we would subtract the initial investment cost, say 20m. You can see that happening below. NPV equals 17.3
NPV’s use in investment appraisal
NPV is a measure of expected return and a key tool in investment appraisal. Someone sitting in the M&A department of a large 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 generates a return greater than the cost of funds.
Problems when using the NPV formula in Excel financial modelling
Excel’s NPV formula is designed to help us with all our previous hard work. Key inputs for the formula are the cost of funds (10% in our example) and cash flows (20m in, 60m out in our example).
You can find the NPV formula if you click on the “fx” button on the Excel task bar from a blank spreadsheet cell. Type in “NPV” into the “Insert function” box, click on “go”, and NPV will come up on the list. Click on “NPV” and you’ll get some guidance as to how the formula is constructed (see the picture below, where it has been done for you). You may notice that the NPV formula is not hard to start working with. All it needs as an input is the rate (10% in our example) and the cash flows (20m in, 60m out).
You can download the Excel spreadsheet “NPV formula” to look at problems that can occur if you use this formula in financial modelling.
The problem: the NPV formula discounts the first cash flow
You will notice from the NPV formula spreadsheet that the bottom calculation gives us the answer we were expecting (17.3) but the prior calculation gives us the wrong answer. This occurs because the NPV formula in Excel discounts the very first cash flow the formula is looking at. Because the 20m is invested right at the start of the project, with no delay, it shouldn’t be discounted. The bottom calculation shows you how to use the NPV formula correctly: pointing it to the future cash flows and then subtracting the initial cost of the investment separately.
Excel’s NPV formula: financial modelling trap no. 1
We have identified a big trap in using Excel’s investment appraisal formulas in financial modelling! The NPV formula in Excel discounts the first cash flow you point it to, whether you want it to or not. If you are to use Excel’s NPV formula in financial modelling, you have to set out your workings carefully – or maybe it’s best not to use it at all?!
Continue with the Excel financial modelling course extract