Calculate Cashflow on the basis of IRR set by the user

mohitlamba0101
mohitlamba0101
in Modeling

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)

Tagged:

Answers

  • ashubham
    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
    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.

Categories