Interested in how to do it, what to take into account and/or an example in
How to calculate NPV in Excel
Answers
You are aware of the NPV function?
No, I'm not. If there is a function, am interested in an understanding of just how Excel calculates NPV. I don't really get the whole thing.
NPV function works as follows:
1. In XL worksheet insert cash flows in cells vertically
2. Initial investment in yr zero is negative
3. Pull up XL, NPV function
4. Copy from XL spreadsheet cash flow range into NPV box
5. Insert discount rate in box
6. Result is NPV
Unfortunately, the NPV function in Excel does not correctly calculate NPV. See below:
WACC 8% Rate From Cash Flow
ROIC 10% PV Table Times Rate
Investment $(50,000) 1.00 $(50,000)
Year 1 $16,000 0.92593 $14,815
Year 2 $16,000 0.85734 $13,717
Year 3 $16,000 0.79383 $12,701
Year 4 $16,000 0.73503 $11,760
Year 5 $16,000 0.68058 $10,889
PV of Inflows $63,882 Sum of Years 1 - 5
NPV of Investment $13,882 PV of Inflows less Year Zero
IRR 18%
MIRR 14%
NPV using "NPV" Function
on ALL Cash Flows $12,855 Should = NPV of Investment, above
PV of Inflows using "NPV"
Function on Cash Inflows $63,883 See PV of Inflows, calculated above
The correct NPV is $13,882 not $12,855 as calculated using the Excel NPV function on all cash flows. To calculate the correct NPV using the Excel function, you need to apply the NPV function to all cash flows except year zero. Then, in an adjacent cell, net the NPV calculated by Excel (which is really the PV of Future Cash Flows) with the Cash outflow in year zero.
We teach this in our Forecasting and Modeling
1
Kate,
Try this...
1. Type this text into your formula bar:
=npv(
2. Press Ctrl+a to launch the Function Arguments dialog.
3. In the bottom-left corner of the dialog, you'll see the hyperlinked text, "Help on this function". Click on it.
In Excel 2010, the first paragraph under Remarks in the help topic says, "The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments. For more information, see the examples below."
This remark isn't very clear, but their formula is clear, and their Example 2 illustrates the circumstance you describe.
By the way, an alternative way to get the correct answer would be to include the year-zero value in the NPV's values arguments, then divide the result by 1 plus the interest rate. Doing so in the classroom could help to explain the issues involved.
Charley
Special thanks to Charley for posting, as follow-up to this conversation, this Excel template that you can use for your own calculations:
"Example of using Excel's NPV Function"
https://www.proformative.com/resources/example-using-excels-npv-function
Best... Sarah
Filed Under:
FP&A