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 Jan-24 it would calculate the NPV with cashflows from Feb-24 to the end of the module. Then in Feb-24 it would calculate the NPV from Mar-24 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 -
Hi @MattHarvey,
Hope You are well. Do you able to find solution in Anaplan?
If Yes, Can u share the solution pls?
Thank You In Advance
Ashutosh
0
