Forum Discussion
How to calculate NPV for regular intervals but varying dates
Note a correction to the NPV formula in my previous posting. See the ERRATA explanation below.
The following demonstrates one implementation:
Key Formulas:
D2: =NPV($C$10, IF($E$1:$AC$1>=B2, IF($E$1:$AC$1<C2, E2:AC2)))*(1+$C$10)
Copy D2 into D3:D7
The formula must be array-entered (press ctrl+shift+Enter instead of just Enter) in some versions of Excel.
Ostensibly, the formula discounts cash flows to the first non-zero month on or after the start date.
Note that for Property 2 and 6, the first non-zero month is Jan'23, not the earlier start dates of Sep'20 and Jun'22.
-----
Since E1:AB1 contain first-of-the-month dates, we would like the second condition to be
EOMONTH($E$1:$AB$1, 0)<=C2
However, that does not work in my version of Excel. That is, not all Excel functions work as expected in array formulas -- a defect, IMHO. You might try it in your version.
The work-around is to add column AC, and to use the condition $E$1:$AC$1<C2 .
-----
ERRATA.... In a previous posting, the formula of the form NPV(B$2, C3:I3) is incorrect because Excel NPV discounts the first cash flow. Effectively, that discounts cash flows to the month before the first one. Multiplying by 1+B$2 corrects for that.
That correction has been incorporated in the implementation above by multiplying NPV(...) by 1+$C$10.
If that doesn't show up then here's another way for the link: https://www[dot]dropbox[dot]com/scl/fi/12sp0y8jp86kjlobhcz8i/NPV-Example-Calculation.xlsx?dl=0&rlkey=ru36pjwdjwp7z857d9k6b3jds
Note a correction to the NPV formula in my previous posting. See the ERRATA explanation below.
The following demonstrates one implementation:
Key Formulas:
D2: =NPV($C$10, IF($E$1:$AC$1>=B2, IF($E$1:$AC$1<C2, E2:AC2)))*(1+$C$10)
Copy D2 into D3:D7
The formula must be array-entered (press ctrl+shift+Enter instead of just Enter) in some versions of Excel.
Ostensibly, the formula discounts cash flows to the first non-zero month on or after the start date.
Note that for Property 2 and 6, the first non-zero month is Jan'23, not the earlier start dates of Sep'20 and Jun'22.
-----
Since E1:AB1 contain first-of-the-month dates, we would like the second condition to be
EOMONTH($E$1:$AB$1, 0)<=C2
However, that does not work in my version of Excel. That is, not all Excel functions work as expected in array formulas -- a defect, IMHO. You might try it in your version.
The work-around is to add column AC, and to use the condition $E$1:$AC$1<C2 .
-----
ERRATA.... In a previous posting, the formula of the form NPV(B$2, C3:I3) is incorrect because Excel NPV discounts the first cash flow. Effectively, that discounts cash flows to the month before the first one. Multiplying by 1+B$2 corrects for that.
That correction has been incorporated in the implementation above by multiplying NPV(...) by 1+$C$10.
- Thank you so much!