Need help with NPV formula

Hi Everyone,

I was applying discount percentage of 4% to a cash value of -193 , I feel that my value is incorrect please see attached screenshot.

My formula is NPV(Cash flow input %, PCON). Could you please help me what I am doing wrong here.

Regards,

Manisha

Accepted answers

All comments

mpippara

I was expecting NPV to be -185.65

jnoone

Hey - Can you add a line item which is boolean formatted and then check the cash flow period from which npv calculation has to happen and then make it part of formula and see if that corrects.

JaredDolich

@mpippara

Is your PCON always a negative value for each period (cash out?). NPV function needs at least one negative value (cash out) and at least one positive value (cash in).

Your NPV will correct itself once you enter the correct cash flows by period.

I think by reading your use case you're expecting .04/12 cash in each month or .646 after your initial investment of -193.8. To get that result you'll need to set up your cash flows like this. We get a number much closer to what you were expecting.

If you have a more complex time series, you can use the 3rd and 4th variable in the function. You can read about it in this article.

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/NPV.html

mpippara

Hi Jared,

Thank you for the response! The time scale i use is year, should i convert it to monthly values and assign the numbers in such way?

Thanks,

Manisha

JaredDolich

@mpippara

Before we go there, can you describe the use case?

What is the initial investment? 193.8?
What is the annual return? 4%?
What is the cash flow frequency? Monthly? Yearly?

From there, you can set up your formula.

The NPV function does not need a time dimension. You would only use the time dimension as a convenience to you so you can plan your cash flows.

Here's an Annual Example:

I invest 100 at 4% annual return and at the end of 3 years I get 112.5.

The NPV is, as expected, 4.

Try breaking up your use case like this:

mpippara

I have different PCON's for each of the 3 years and I am applying 4% cashflow% for all 3 years.

My NPV is same as my PCON, could you let me know what am I doing wrong here.

Regards,

Manisha

Akhtar.shahbaz

Hi @mpippara

In order to get the exact value, you have to use "POWER(1+ Cashflow rate %,N)" formula in order to match Excel's NPV formula, where N is the no of year.

I have tried to replicate your problem below:

I have used the above logic to get the correct NPV.

Excel calculation:

Hope this helps!

Thanks

Akhtar

mpippara

Hi Akhtar,

I believe you're right! Will try and let you know. Thanks a ton!

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…