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.
You neglect to provide the discount rate. I will use 1% monthly arbitrarily.
Fortunately, your data is monthly. The actual date should not matter.
So, we can use Excel NPV, which is more forgiving than XNPV. In particular, Excel NPV ignores empty cells in the list of cash flows.
(Caveat: If you had a zero cash flow in any interstitial months, you must enter the zero instead of leaving the cell empty. You can use cell formatting to make zero values appear to be blank, if you wish.)
Consequently, the same formula can be used for all properties, without special cases for the starting and ending months. To demonstrate:
Formulas:
B3: =NPV(B$2, C3:I3)
B3: =NPV(B$2, C3:I3)*(1+B$2) (ERRATA; image not corrected)
Copy B3 into B4:B5
Other formulas (not required):
K3: =NPV(B$2, D3:F3)
K4: =NPV(B$2, C4:E4)
K5: =NPV(B$2, C5:I5)
Aside.... B2 contains the value 1% formatted as Custom "NPV @ "0.00%
Note that NPV formulas specify all possible columns, even if some columns before and after the cash flow range might be empty for the particular property.
- Thank you for replying. I should have clarified one more thing in my question and example: in the actual spreadsheet, in most cases there will be future cash flows that show up beyond the "end date" that I want to use (when the lease moves into an option period), but I only want the NPV calculation to look at the dates between the start and ending time period. The start isn't as important because I have blank cells until the start of the lease begins, but the ending date will vary. I hope that makes more sense and sorry, I should have clarified that earlier.
JonAust wrote: ``in most cases there will be future cash flows that show up beyond the "end date"``
I think you are saying that in any row, there might be numbers beyond the end date that should not be included in the NPV calculation.
But I do not see any explanation of how to determine which column represents the end date. So, I cannot help you.
I suggest that you provide an Excel file that demonstrates all conditions and input data. Ideally, attach it to a response by clicking "browse files to attach" near the Post button.
If you cannot do that, then upload an example Excel file that demonstrates the problem to a file-sharing website, and post a download URL that does not require that we log in. I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.
If the forum does not allow you to enter URLs (yet), edit the URL manually. For example, the URL for this thread is techcommunity dot microsoft dot com /t5/excel/how-to-calculate-npv-for-regular-intervals-but-varying-dates/td-p/3750164 .
- Thank you again for your assistance here. I'm including an example Excel file in the following dropbox link: https://www.dropbox.com/scl/fi/12sp0y8jp86kjlobhcz8i/NPV-Example-Calculation.xlsx?dl=0&rlkey=ru36pjwdjwp7z857d9k6b3jds
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
