NPV with rolling start date
Hi  I am trying to build a module with an NPV calculation which in each month calculates the NPV of cashflows starting from the next month until the last time period in the module.
So in Jan24 it would calculate the NPV with cashflows from Feb24 to the end of the module. Then in Feb24 it would calculate the NPV from Mar24 and so on.
I guess this needs a way of formulaically setting up the reference of the cells passed to the NPV function, so that the reference moves as you go through time in the model.
I can't see how this is done in an efficient way. Any ideas would be greatly appreciated!
Best Answer

Hi @MattHarvey  we have something similar going in one of our models. Essentially it's the need to have two time dimensions. So have created a fake time dimension which is the same as the model time dimension and then used the proper time dimension to run the calculations as can use previous, next, etc
Does require some time mapping to move the data back and forth but broadly does what we want it to do.
0
Answers

0

Thanks  I understand how to get the NPV calc working ok. What I'm trying to do now is to get the calculation working on a monthly rolling basis, calculating the NPV of the cashflows of the remaining months in the model. That's to say, NPV for month m is calculated as the NPV of cashflows m+1 to T, where T is the last period in the model. Any ideas on that?
0 
To dynamically calculate NPV in Excel for each month:
=NPV(0.1, OFFSET(B2,1,0,COUNT(B:B)ROW(B2)+1,1))
Copy this formula down for each row where you want the NPV calculation. Adjust the cell references based on your data layout.
0 
Thanks  have it working fine in Excel  was after an Anaplan implementation.
0 
Ok thanks  I think I understand your approach  appreciate the response.
1