NPV and Amortisation

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

Answers

  • Hi @Roelofg ,

     

    I think the NPV of this would be 206,929.

     

    Please check the excel attached, and could I see the whole formula of your NPV formula.
    I am on the way to studying finance, and it would be useful for me.

     

    I hope it helps.

  • 213,473...

     

    I will investigate..

     

    Anaplan_NPV.png

  • Hi

     

    Just some observation/question from my end for your reference:

    1. Your 1st line item is "Annual Interest Rate" but your module is in monthly bucket. If your 5% is really Annual, then your monthly discount rate would be 12 square root of (1+5%) then minus 1

    2. Your payments in NPV line item is not discounted by the discount rate (your first line item)

     

    The rest I am not able to see the full formula, it would be great if you can share an export of your module in Excel.

     

    BR

    Tingting

  • See above, Thank you
  • Hi

    I have looked into your Excel and modified the monthly discount rate calculation to the one I mentioned previously. Now the End liability at the last month will be zero.

     

    Monthly Discount Rate Calculation:

    Screenshot 2021-06-24 at 2.12.24 PM.png

    Interest Calculation:

    Screenshot 2021-06-24 at 2.15.46 PM.png

     

    BR

    Tingting

     

  • Sorry attach my version of file for your reference.

     

    Tingting

  • Great stuff.. Thank you.

     

    But what should the formula within Anaplan then be for the NPV?

  • You can use POWER function in Anaplan which is similar to that in Excel: Power(1+5%, 1/12) - 1

    https://help.anaplan.com/7f308a58-fe32-400b-b549-519d08c1fad4-POWER

     

    Tingting

  • Thanks for the work around.

     

    To throw a rock in the bush.. - Are we then saying that the financial functions such as the NPV is useless and should not be used.......

     

     

  • NOPE.

    This is only to correct your first line item which is the discount rate only. Then you still need NPV function to calculate NPV just that with correct monthly discount rate.

     

    Tingting

     

  • Hi @Roelofg 

     

    I finally got to understand the Anaplan NPV function in the monthly calendar.

    Comparison with Excel NPV function:
    1) Month 1 cashflow
      (E) discounted
      (A) not discounted

    2) Discount Rate
      (E) ((1 + Annual Rate) / 12) ^ (Number of Months)
      (A) (1 + Annual Rate) ^ (Number of Month / 12)

    So in your situation, the NPV function of Excel and Anaplan differs.
    (E) 206,929 (A) 207,926

     

    See attached Blueprint, too.

     

    Anaplan_NPV_function.png

     

    Despite of the Anapedia NPV function page, you should set the NPV discount rate be the annual rate.

    I cannot fully understand this document.

     

    https://help.anaplan.com/c504a4f5-5513-42e3-91cf-b34d873d18f3-NPV

    Anapedia_NPV.png

    This topic tought me how it works!

    Thank you.

     

     

    Regards,
    Taichi

  • FYI

     

    Difference 2 is less necessary because we can use NPV((1+r)^(1/12), Cash flow) in Excel as @tingtingxia  did.
    In Excel, we can choose from two monthly discount rates.

    In the Anaplan, an exponential discount rate only, but it would be correcter Math.

  • @Amaya 

     

    Thank you for this....

     

    I used the suggested above and sorted out the problem.

     

    The issue around NPV is also to account for when the payments is made in the beginning of the (monthly) period or at the end of the (monthly) period. To be quite honest i do not trust the NPV formula on this stage.

     

    It is easy to check it and that is to build an amortization schedule on the input and results of the NPV formula. The theory is that the balance must be 0 at the end of the period. But is does not.

     

    Thanks for the input.