Clarification of Answer by
04 Jun 2005 09:12 PDT
Here are the steps for calculating NPVs. I?ll show the fundamental
calculations but will leave the details to the spreadsheet, as there
are at least 60 calculations (and that?s if you guess the IRR
1. First set up your cash flows. The convention is to use ?year 0?
as the up-front investments because they happen today, then returns
are typically calculated at the end of year 1 for simplicity.
Year 0: -$20,000
Year 1: $4,000
Year 2: $4,000
Year 8: $4,000
2. Next we figure out the NPV factor. This is usually what confuses
early finance students ? but setting up the NPV factor is the easiest
way to check calculations when you?re doing a complex problem. (Note
that there are other ways to calculate NPV that are mathematically
equivalent, such as dividing your cash flows by the compound interest
rate directly, but they mask any errors that you might make in the
Year 0 has no discount, so the NPV factor is one.
It costs us 9% (or 14%) for year 1, so it?s 1/1.09 = 0.917431 or 1/1.14 = 0.877183.
Year 2 it?s 1/(1.09)^2 = 0.84168 or 1/(1.14)^2 = 0.769468
Year 3 it?s 1/(1.09)^3 = 0.772184 or 1/(1.14)^3 = 0.674972
3. Multiple each year?s cash flow by the NPV factor to get the
?discounted cash flow? for each year. This is the line that I?ve
termed ?NPV@9%? or ?NPV@14%? or even ?IRR test return? on the
Check the line for errors ? by making sure that our $4,000 decreases
each year as the impact of discounting increases.
4. Add the total of the discounted cash flows. At 9% our positive
cash flows overwhelm the -$20,000 investment and produce an discounted
cash flow or NPV of $2,139. That says the project is a ?Go.?
However, at 14% the cost of money is too high and our discounted cash
flows don?t produce a positive return. Instead it?s negative, at
-$1,445. It would take at least another 2 years of like returns to
turn the project positive at 14%.
We also know from the previous work that the point at which IRR is
zero is somewhere between 9% and 14%
I?ve set up a starting point of 12% for the calculations on the
spreadsheet. Cash flows don?t change ? we simply have to try new
?rates of return? that get us to zero for the total NPV.
This is actually the hard part ? even Microsoft Excel does this
iteratively, trying rates until it get close to zero for the total
At 12% you can see the NPV factors:
Year 0: 1.00
Year 1: 0.892857
Year 2: 0.797194
Year 8: 0.452349
At 12% the project still has a total of discounted cash flows of
-$129. We?re getting close to zero but aren?t there yet. You?ll want
to LOWER the rate of return until you get to the smallest possible
number that?s positive.
11.9% produces an NPV of -$63.
11.8% produces an NPV of $4 ? a positive number.
Can you get closer to zero? Yes, but only if you go to more decimal
places ? 11.805% will deliver an NPV of zero with rounding.
Please let me know if there?s any part of this portion of the Answer