1

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

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 after that manipulation for the relevant years, it doesn't equal.

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

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

=F9*RATE*(B10-B9)/365

should be

=F9*((1+RATE)^((B10-B9)/365)-1)

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
| improve this answer | |

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.