How to calculate NPV for regular intervals but varying dates

[JonAust]

Hi everyone, I am trying to create a formula to calculate NPV for multiple properties each with a different start and end date. The properties are combined into one spreadsheet, each column represents a different month and each row represents a different property. The cash flow for each property is reflected under the corresponding month.

For example:

Column A   Column B   Column C   Column D   Column E   Column F   Column G

Property     Jan 2023     Feb 2023    Mar 2023    Apr 2023   May 2023   Jun 2023

Property 1                     $10,000      $10,000       $10,000     $12,000      $13,000

Property 2  $7,500        $8,000        $6,500

Property 3                                                           $4,500       $8,500        $15,000

For this example I don't really care what is used for the discount rate... assume 1% (monthly)

I'm trying to create a formula to calculate the NPV for each property using the different start / end dates.

Property 1: start is Feb 2023, end is Apr 2023

Property 2: start is Jan 2023, end is Mar 2023

Property 3: start is Apr 2023, end is Jun 2023

The big spreadsheet contains a lot more properties and a lot more months / years, but this should get me started.

Also, I should clarify one more thing about 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 don't want to include in the NPV calculation (i.e. 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.

Does anyone know how to create a formula for this?

[#2]

@JonAust

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.

[#3]

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.

[#4]

@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 .

[#5]

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=ru36pjw...

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...

[#6 · Accepted solution]

@JonAust 

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.

[#7]

Thank you so much!