# 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. – Dilip Sarwate Nov 8 '19 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
``````