Search code examples
excelannuity

Why do annuity payments end earlier in Excel?

I am trying to calculate load payments with PMT function. I set total amout, rate per year, divided rate by 12, multiplied number of years by 12 and got:

enter image description here

Expected duration is 5 years, which should end at 06/01/2022 (we have DD.MM.YYYY format).

In each payment I subtracted payment from remaining sum

enter image description here

and sheet shows that payments will end in 2021 not in 2022.

Why?

Solution

  • You are subtracting the whole payment from the amount when a portion of each payment is to pay for the interest.

    You need to figure out how much of each payment is interest and how much goes to the balance and it is the second part that needs to be subtracted from the balance.

    To figure how much is Interest:

    =-D7*($E$1/12)

    Then to find how much goes tot the balance we simply subtract the interest from the payment:

    =E7-F7

    Then you add that to the balance and copy down.

    enter image description here