How is it that when I capitalize this amortization table, the present value doesn't equal the sum of PV or the XNPV formula of all the future payments?

It's not about this amortization specifically, but any amortization table for long periods.

I tried it on some other amortization tables and it never equal the present value of the loan. If I capitalize the future payments with the same rate as the loan rate, it must always be equal to the present value of the loan. It's must be so mathematically. So I don't see what's wrong. I thought it was because there are some years with 366 days, but even if I use it in the calculations for the relevant years, it does come close- but not enough.

Link for the amortization table: https://drive.google.com/file/d/1tocPRNMOqQM52wpEC_hjV9AGak-M8cHy/view?usp=sharing

Does anyone have an answer for it? Thanks

  • This sounds more like a question about accounting in general. – Dilip Sarwate Nov 8 at 15:03

You need to change your interest calculation as shown below.

I.e. the formula for cell C10 (and likewise for the rest of column C)

was originally


should be


enter image description here

That is the correct way of applying compounding using a daily rate calculated from an effective annual rate.

daily rate, d = (1 + 0.0295)^(1/365) - 1 = 0.0000796559

compounded over 148 days, r = (1 + d)^148 - 1 = 0.0118584

interest on principal = 21250000 r = 251990

or, in one go as in cell C10

interest on principal = 21250000 ((1 + 0.0295)^(148/365) - 1) = 251990

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.