Reducing the number of calcs for a 30 Year NPV

[NMG]

Hi All,

This is my first post!

As a financial modeller I frequently calculate NPV over 30/40/60 Year periods. This involves setting out the cashflows for each period and applying the relevant real inflation % for each year. The NPV formula is then applied to the 30/40/60 Yr period to calculate.

In one instance the Real Inflation rate is only variable in Yrs 1 & 2 and therefore constant for Years 3 to 30. My question is how can I write the formula for the NPV over just 3 columns that will give me the same result as when done conventionally over the full 30 Years? I have uploaded a file that sets this out more clearly.

Thanks in advance

 

[Luke M]

It's a little bit tricky, in the layout you have. For cell G19, we can calculate the SUM of all the payments with a SUMPRODUCT formula. However, the NPV function needs to actually have all the payments listed out, individually. So, we embed the calculation from the SUMPRODUCT into the NPV function, and if we confirm as an array (using Ctrl+Shift+Enter), then our formula works quite nicely.

At the end of the day, I'd probably opt for listing out all the years individually as you had it originally, as it's easier to understand and audit. But, just to show that it can be done, I did it.

 

[NMG]

Thanks Luke - that was really quick and much appreciated.

I had already got almost the same value in the G19 cell using the FV formula, but I would have never worked out the NPV in D21....so many thanks.

The rationale for this is that I have a model that is performing 000s of these calculations and the file/model has grown to 70MB and has become unwieldy and this is the area that is consuming so much overhead, so your solution is most timely. Thanks.

 

[NMG]

Hi Luke

I am today trying to follow the logic of the code you provided, in particular the section:
ROW(A1:INDEX(A:A,G16))
I have tried to just evaluate this part to folllow it, but I am stumped; could you explain what I am missing please? Thanks

 

[Luke M]

Sure thing. The goal here is to artificially create an array with the numbers 1:X, where X is the variable length. You started with a time of 30 years, but we already have the first 2, so we just need 28 years/numbers, correct? (Hence the 28 in cell G16).

Okay, that's great, but how do we build that array? Glad you asked! The ROW function, in an array formula, will take a range of cells and return an array with the row numbers. So, if you gave it:
ROW(A1:A10)
it would produce an array of:
{1,2,...,9,10}

With the thinking that you probably wanted your formula to be flexible in case you wanted to do 30, 40, or 50 years, I wanted to plan ahead. The INDEX function says to look at col A, and return the Nth cell. In our case, G16 = 28, so it says to get the 28th cell from col A (which is A28).
Then, our formula changes from being:
ROW(A1:INDEX(...))
to
ROW(A1:A28)
which produces the array
{1,2,3,...,27,28}
and voila! We have our array of 28 numbers. If we change G16 to be 38, then INDEX grabs the 38th cell, which is A38, and we get an array of:
{1,2,3,...,37,38}

Does that make sense?

 

[NMG]

Thanks Luke - that makes perfect sense now...

My problem was that ROW(A1:INDEX(A:A,G16)) was evaluating to "1" even though I could see the array as (A1:A28).

It is always good to find someone brighter than yourself!

This will help me a lot - many thanks.