Loan and mortgage amortization calculator

The level payment for a loan of P over n payments at a rate i per payment period is the annuity formula used by spreadsheet PMT. When interest compounds at a different frequency from the payments, the equivalent rate per payment is used.

payment = P × i / (1 − (1 + i)^−n)
i = (1 + r/m)^(m/f) − 1   (r = annual rate, m = compounding periods a year, f = payments a year)

Each row charges interest on the opening balance, applies the rest of the payment and any extra to principal, and carries the balance forward.

Assumptions and limits

• The payment and each period’s interest are rounded to cents; the final payment clears whatever remains.
• The rate is fixed for the whole term. Fees, insurance, taxes, escrow and rate resets are not included.
• Biweekly and weekly payments are treated as 26 or 52 equal periods a year, not as exact calendar days.
• An educational calculation, not financial, tax, legal or investment advice.

Worked example

A 250,000 loan at 6.5% for 30 years with monthly payments costs 1,580.17 a month and 318,861.58 in interest over 360 payments. A 2,000 loan at 10% over 24 months has a first-month principal portion of 75.62, matching spreadsheet PPMT(10%/12, 1, 24, 2000).

References

• Microsoft Excel: PMT function
• Microsoft Excel: PPMT function
• Microsoft Excel: IPMT function