An NPV formula that calculates as Excel's does

AnaplanIdeas

Description:

To have an NPV formula function that calculates NPV exactly as Excel's period-based NPV formula does.

Benefit/impact:
Current NPV formula returns results as Excel's XNPV formula does (using elapsed days instead of periods). We are in a position of having prospective customers and existing customers use workarounds in order to feel that they are capable of using Anaplan to reproduce their existing results.

This is not an issue of accuracy, rather it is an opportunity to expand our capacity in this area to accommodate a prevalent method of calculating NPV that is entrenched in current business practices. Our current "fix" appears to be spending time in building one-off NPV modules to calculate NPV in the Excel style which adds inefficiency to the sales cycle and to workspace size.

AnaplanIdeas

Status changed to: On Roadmap

bill.liao

Hi,

 

The IRR function seems to be in a similar situation, will there be any plans to add a new formula that will completely match Excels?

 

In addition, both of these functions in Excel have the capability of selecting a subset of a cashflow by choosing a range, will Anaplan add similar capability in the future? Current workarounds seems to be inefficient in both model construction and workspace size.

 

Regards,

Bill

RobertPhilcox

Hi, 

 

Is there any update on the roadmap for this function? I see this post was last updated 3 years ago.  

david.savarin

Same question : this has been  showing on roadmap for quite a time and the financial function (npv and irr mainly for us) working on time periods with time subsets would be a great addition.

Tiffany.Rice

The function documentation absolutely needs to call out the lack of parity with Excel based calculation and provide workarounds in a more face-up way. The current help documentation would lead one to believe this function should calculate in the same way as Excel's NPV which is not the case:

NPV | Anapedia

. Ideally there would be a native function that allows for parity with Excel but if not currently feasible a documented workaround would likely save people a lot of head scratching/searching.

The following post was very helpful to understand the difference between the functions in Excel vs. Anaplan. Thanks @Amaya for the examples you provided, your help was invaluable!!!

NPV and Amortisation

Roelofg

Jun 23, 2021

Hi Guys

 

I am running into a problem using the NPV calculation and a subsequent amortization calculation.

 

Please see attached:

I used the NPV formula to calculate the NPV of a 12 month  variable cash flows. This is equal to the opening balance of a liability. 213,473)

 

When i develop an amortization schedule including interest cost uisng the cash flow as the payment schedule, the end balance is not zero.

I attached the results as well as the formula used..

 

I am not sure where i made the mistake?

 

Thank you in advance

---

View Post