NPV and Amortisation

Options
Roelofg
Roelofg Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor
in Planning & Modeling

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

Tagged:

Comments

  • Roelofg
    Roelofg Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor
    See above, Thank you
  • Roelofg
    Roelofg Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor

    Great stuff.. Thank you.

     

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

  • Roelofg
    Roelofg Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor

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

     

     

  • Roelofg
    Roelofg Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor

    @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.

     

     

Categories