excelavon

Loan Payment Calculator and Excel PMT Formula

Monthly payment, total interest and a full schedule, with the PMT, IPMT and PPMT formulas.

1,199.10 per payment

360payments
431,676.38total paid
231,676.38total interest
Principal 46.3% Interest 53.7%
Paid each year: principal (blue) and interest (yellow), year 1 to 30
Payment=PMT(B2/12,B3*12,-B1)
Interest in payment A7=IPMT(B2/12,A7,B3*12,-B1)
Principal in payment A7=PPMT(B2/12,A7,B3*12,-B1)

B1 holds the loan amount, B2 the annual rate (for example 6%), B3 the term in years. The minus sign makes the payment show as a positive number.

Repayment schedule, first 12 payments
#PaymentInterestPrincipalBalance
11,199.101,000.00199.10199,800.90
21,199.10999.00200.10199,600.80
31,199.10998.00201.10199,399.71
41,199.10997.00202.10199,197.60
51,199.10995.99203.11198,994.49
61,199.10994.97204.13198,790.36
71,199.10993.95205.15198,585.21
81,199.10992.93206.17198,379.04
91,199.10991.90207.21198,171.83
101,199.10990.86208.24197,963.59
111,199.10989.82209.28197,754.31
121,199.10988.77210.33197,543.98
Download the full schedule (CSV, 360 rows)

The PMT formula

PMT(rate, nper, pv) returns the payment for a loan with a fixed rate and equal payments.

  • rate is the interest rate per payment period. For monthly payments on an annual rate in B2, use B2/12.
  • nper is the total number of payments. For a term in years in B3, use B3*12.
  • pv is the loan amount. Excel treats money you receive as positive and payments as negative, so -B1 makes the payment show as a positive number.

Worked example: a 200,000 loan at 6% a year for 30 years, paid monthly: =PMT(6%/12,30*12,-200000) returns 1,199.10. Over 360 payments that is about 431,676 in total, of which about 231,676 is interest.

Building the schedule in Excel

Put the payment number (1, 2, 3 …) in column A from A7 down. IPMT gives the interest part of each payment and PPMT the principal part; the two always add up to the PMT result. Fill both down to the last payment. The CSV download above gives the full schedule with the same figures.

Why your lender’s figure can differ

Lenders may round each payment, charge fees, compound interest differently, or use a different day count. This tool assumes a fixed rate, equal payments at the end of each period and no fees.

Sources: Function behaviour: Microsoft Support function reference. Results checked against worked examples, and generated formulas evaluated in the open-source HyperFormula engine. Method & sources