Calculate Cashflow on the basis of IRR set by the user

In Excel we have Goal Seek function to set Target value of a calculated cell and based on that target value, excel performs reverse calculations and gives source value needed to achieve the target. Now, in Anaplan I want to achieve this similar functionality.

IRR is an Anaplan function resulting in value calculated from cashflows with respect to time. This result is stored in a line item without any dimensions.

Is there any way where end user can set target value for IRR and cashflow is calculated accordingly performing reverse calculations? (For a single cell)

Accepted answers

All comments

ashubham

@mohitlamba0101

Try reading about 'Breakback'. Seems like Breakback may solve the problem for you.

Link: https://help.anaplan.com/1b7aa87d-aa13-49f6-8f7d-d893fb8bccbe-Breakback

JatanR

I believe that your problem can be solved through the use of financial functions, specifically the PMT function on Anaplan: https://help.anaplan.com/07d126f7-dd4d-4510-b15d-add22fc527fd-PMT

Considering financial theory, the IRR simply represents the rate of return on an investment for which the Net Present Value (NPV) is zero. For this, we can force the Present Value (PV) = 0. Consider a simple example of making periodic payments specified time frame (say, a yearly payment over 5 years) and receiving a lump sum amount at the end of 5 years (FV)

In financial terms, the example above can be formally represented as:

Present Value (PV): Forced to be 0 for calculation of IRR

Payment (PMT): The 5 periodic payments made annually

Number of Periods (N): 5, since for 5 years you make a payment annually

Future Value (FV): The lump sum promised to you at the end of the period for your investment

Interest Rate (R): The rate of return

Out of the 5 parameters above, if you have any 4 already known to you (or are going to input them), calculating the 5th one is straightforward.

My understanding of your problem statement is that you would like to input the IRR, hence you have the R. Since you will be calculating IRR, the PV will be set to 0. The number of periods (N), it appears, is known. I am assuming there is either a FV or a PMT also available; and the left over 5th parameter is the missing value to be calculated.

Also, please note that a sign-change between FV and PMT is important here. Imagine receiving a $10,000 investment later (FV): since it is "cash in", we will represent it as 10000. Meanwhile, the periodic payments you make (PMT) will hold a negative sign, since it is "cash out". Or vice versa.

By formulating your problem in terms fitting the general financial functions available, I believe a solution can be arrived at. Note, there are multiple ways to formulate a problem (for instance, instead of PV = 0, we could go with FV = 0 and flip the direction of calculation, which would be more relevant for a different kind of use case) and the specifics would depend entirely on your use case.

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…