What is an amortization schedule?

An amortization schedule is the complete repayment plan for a loan with equal payments. Each row is one payment. The columns are usually the payment number or date, the payment, the interest portion, the principal portion and the balance remaining. In accounting, amortization also means spreading the cost of an intangible asset over its useful life. This article covers loans only.

How is each row of an amortization schedule calculated?

For a fixed-rate loan paid monthly:

1. Interest = previous balance × annual rate / 12.
2. Principal = payment − interest.
3. New balance = previous balance − principal.

The payment itself comes from PMT, as described in how to calculate a loan payment with PMT. Because interest is charged on the remaining balance, interest falls each month and the principal share rises, while the payment stays the same.

What do the first payments look like on a 20,000 loan at 6 percent?

A loan of 20,000 at 6 percent annual interest, repaid monthly over 5 years (60 payments). The payment is =PMT(6%/12, 60, -20000), which gives 386.66.

Payment 1 interest is 20,000 × 0.06 / 12 = 100.00. Over the whole loan, the interest adds up to 3,199.35. The last payment is slightly smaller, 386.41, because the payments are rounded to cents and the final row absorbs the difference.

How do I build an amortization schedule in a spreadsheet?

Put the loan amount in H1, the annual rate in H2 and the years in H3. Use columns A to E for payment number, payment, interest, principal and balance, with headers in row 1 and the starting balance (=H1) in E1. Then, in row 2:

• Payment number (A2): 1, then =A2+1 below.
• Payment (B2): =PMT($H$2/12, $H$3*12, -$H$1). The absolute references let it be copied down.
• Interest (C2): =E1*$H$2/12. Or =IPMT($H$2/12, A2, $H$3*12, -$H$1).
• Principal (D2): =B2-C2. Or =PPMT($H$2/12, A2, $H$3*12, -$H$1).
• Balance (E2): =E1-D2.

Fill down for the total number of payments. A check: the balance on the last row should be zero, and the principal column should add up to the loan amount. The post on loan amortization with PMT, IPMT and PPMT explains each function, and the loan amortization schedule template has the whole table with an extra-payment option.

What can an amortization schedule tell me about my loan?

• The total interest over the life of the loan.
• How much equity you build by a given date.
• How an extra payment changes the end date. A payment applied to principal reduces every later interest charge, so extra payments early in the loan save the most.
• How long it takes before the principal share of the payment passes the interest share.

Why can an amortization schedule differ from a lender's statement?

• Lenders may calculate interest by day count (actual/365, 30/360) rather than a simple one-twelfth of the annual rate. Your table can differ by a few cents from the lender's statement. Use the lender's statement as the final authority.
• Escrow for taxes and insurance is not in the table.
• Adjustable-rate loans change the rate over time, so one PMT formula no longer applies.
• If you pay off several debts at once, an amortization schedule per loan is the base for a payoff plan. See avalanche vs snowball and the debt payoff planner.