NPV with Rolling Period

Ashutosh

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

Thanks

Ashutosh

pyrypeura

Hi,

@Iain_Briggs wrote a great article on rolling NPV calculation in November. Article can be found here

Rolling NPV with timescale workaround

Iain_Briggs

Nov 29, 2023

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.

https://us.v-cdn.net/6037036/uploads/J13YS0SEKUMQ/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:

https://us.v-cdn.net/6037036/uploads/0HWIVTN5EQL2/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.

https://us.v-cdn.net/6037036/uploads/KWXRCJFGE6ON/3-rolling-npv-jpg.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!

---

View Post

Ashutosh

Thank You for sharing the article @pyrypeura. It worked.

Regards

Ashutosh