Rolling NPV with timescale workaround

Iain_Briggs
Iain_Briggs
edited November 2023 in Blog

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.

Screenshot1.png

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:

2. NPV time range error screenshot.png

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

3. Rolling NPV.JPG

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!

Tagged:

Comments

Categories