Rolling NPV with timescale workaround
Author: Iain Briggs is a Certified Master Anaplanner and Associate Director at Lionpoint Group.
One known limitation of Anaplan’s NPV function is that it calculates across the full timescale. However, a relatively common requirement in investment analysis is to generate a rolling NPV — in other words, to calculate the NPV over time ignoring any cashflows that have already occurred.
In Excel, this could easily be achieved using the XNPV formula with suitable anchoring as shown in the screenshot below.
How can you achieve the same result in Anaplan?
If you use the NPV function in a module with time, you will encounter the following error:
(Error message says, "NPV function may not be used if the result line item has a timescale.")
There is however a simple workaround leveraging the NEXT() function.
The two key line items here are:
- Applicable discount factor: as the timescale in the above example is less than one year, we have to convert the annual discount rate into the equivalent per period rate.
POWER(1 + Discount Rate, DAYS() / 365) - Remaining NPV: this takes the next period’s result from the same line item, discounts this, and then adds the relevant cashflow for the current period.
NEXT(Remaining NPV) / Applicable Discount Factor + Cashflow
The resultant calculation is the same. Please note the above workaround needs the discount rate to be constant across the timescale.
Questions? Leave a comment!
Comments
-
Brilliant.
0 -
Amazing!
0 -
Excellent!!
0 -
Excellent; the simple use of the NEXT() function.
Not sure why the following applies: Please note the above workaround needs the discount rate to be constant across the timescale0 -
Yes that is an important caveat. The discount rate has to be constant over time for the NEXT() approach to work. Otherwise if you do have a discount rate that varies over time, then you can consider using a fake time range alongside real time to isolate the **** of future cashflows and discount each **** by the applicable discount rate for that period
0
