Loans and mortgages Updated November 2026

Amortization schedule

An amortization schedule lists every payment of a loan with its interest part, principal part and remaining balance. Each period: interest = balance x r, principal = payment - interest, new balance = balance - principal. The payment stays fixed while the interest part falls and the principal part rises until the balance reaches zero.

Syntax

interest_k = balance_(k-1) x r;  principal_k = PMT - interest_k;  balance_k = balance_(k-1) - principal_k

Part	What it is
`PMT`	Fixed payment from the loan payment formula
`r`	Rate per period
`balance`	Outstanding principal before the payment

Examples

Loan 10,000 at 5% for 3 yearly payments
PMT = 10,000 x 0.05 / (1 - 1.05^-3) = 3,672.09
Year 1: interest 500.00, principal 3,172.09, balance 6,827.91
Year 2: interest 341.40, principal 3,330.69, balance 3,497.22
Year 3: interest 174.86, principal 3,497.22, balance 0.00

Result: Total interest = 1,016.26

Excel: =IPMT(5%, k, 3, -10000) and =PPMT(5%, k, 3, -10000) for period k

Loan 1,000 at 12% a year, 12 monthly payments
PMT = 88.85
Month 1: interest 10.00, principal 78.85, balance 921.15
Month 2: interest 9.21, principal 79.64, balance 841.51

Result: Interest falls and principal rises each month

Interest in payment 1 of a 200,000 mortgage at 6%, 30 years
200,000 x 0.005

Result: Interest = 1,000.00 of the 1,199.10 payment; principal = 199.10

Excel: =IPMT(6%/12, 1, 360, -200000)

Common mistakes

Computing interest on the original principal every period instead of the declining balance; that is simple interest, not amortization.
Rounding the payment to cents and then expecting the final balance to be exactly zero; the last payment is usually adjusted by a few cents.
In Excel, hard-coding the period number in IPMT and PPMT instead of referencing the row, so every row shows the same split.

Practice Amortization schedule

These drills check your answer on live data, free, no account needed.

Interest part of payment kA $105,000, 30-year mortgage at 4.25% APR has a monthly payment of $516.54. How much of payment number 85 is interest?
Principal part of payment kA $105,000, 30-year mortgage at 4.25% APR has a monthly payment of $516.54. How much of payment number 127 reduces the principal?
Balance after k paymentsA $105,000, 30-year mortgage at 4.25% APR has a monthly payment of $516.54. What is the balance right after payment 180?

Questions

How do I make an amortization schedule in Excel?

Put the payment in a cell with =PMT(rate, n, -loan). Then for row k: interest =IPMT(rate, k, n, -loan), principal =PPMT(rate, k, n, -loan), balance = previous balance - principal. Fill down n rows.

Why is the sum of principal equal to the loan amount?

Because each principal part reduces the balance, and the schedule ends at zero. The sum of the interest parts equals total interest paid, which is PMT x n minus the loan.

Mortgage payment formulaHow to calculate a monthly mortgage payment with M = P x r / (1 - (1 + r)^-n). Examples for 200,000 at 6% and 300,000 at 5%, Excel PMT and common mistakes.
Remaining loan balanceThe balance after k payments is the present value of the payments still to come: B_k = PMT x (1 - (1 + r)^-(n-k)) / r. Mortgage and car loan examples, Excel.
Total interest paid on a loanTotal interest on an amortizing loan is the payment times the number of payments minus the principal. Examples for 30-year and 15-year loans, Excel CUMIPMT.
Extra payments and payoff timePaying extra each month shortens the loan: n = -ln(1 - P x r / PMT) / ln(1 + r) with the higher payment. Example: 200 extra on 200,000 saves 9 years, 79,800.