# When I capitalize amortization table, the present value doesn't equal the sum of PV or the XNPV

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. Commented Nov 8, 2019 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

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


should be

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


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