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.