Listen to this post if you prefer
There is a fundamental difference between solving for the NPV when cash flows are measured in annual increments vs. in monthly increments.
As the example spreadsheet embedded below shows, the NPV is by its nature an annual calculation, using an annual discount rate. Thus, when measuring monthly, to be sure that the result it returns to us is meaningful, we must adjust for the different increment in the following way:
=NPV((1+Rate)^(1/12)-1,range of projected values)+Time 0 investment amount
If we don’t do this, then the cash flows will be discounted far too aggressively because Excel will think that each column represents 12 months, not 1 month. To get the cells to light up in the example below, you’ll need to download the embedded file below by clicking on the download icon in the bottom of the embed border.
Just wanted to point out your equivalence formula above is wrong. I tried it out for a sample series:
Monthly values of 1 to 240 (increasing by 1, for 20 years). Yearly values of 78, 222, 366, …, 2814 in year 20. Rate 0.1
NPV(0.1, [yearly values]) = 8,642.65
NPV((1+0.1)^(1/12)-1, [monthly values]) = 9,021.86
NPV(0.1/12, [monthly values]) = 8,608.49
Did you even bother to test your formula?
Hi Stefan, thank you for your note. What about the equivalence formula do you feel is wrong?
Unfortunately, I do not have a correct version of an equivalence formula to point out what is wrong with the one above – this is what I was looking for in the first place when I found yours.
However, I tried using it in the example I gave above and it does not yield the expected results. Please feel free to reproduce my example (I don’t think I can upload my file), it should be simple, just a column with 240 rows and values 1 to 240 (the monthly values for 20 years) and next to it a column with 20 rows, each being the sum of the first 12 values, then 13-24, 25-36, etc.
Then you can run NPV(0.1) of the yearly values, and try to obtain the same result running NPV with your equivalence formula, of the monthly values. The results differ. Hence the equivalence is not correct (unfortunately, I do not know the correct one).
Hi Stefan, I may be misunderstanding you, but I think the reason that you think there is a problem with our formula is that you are expecting the Annual NPV and Monthly NPV results to match. They should not match due to the periodic differences in the discounting. I said that the formula needs to be adjusted, not that its adjustment would result in an equivalent result for the NPV. Does this make sense, or am I missing your point?