Loan amortization, explained with real numbers

What is loan amortization?

Amortization is paying off a loan in equal, regular payments that cover the interest and, bit by bit, the amount you borrowed. The amount borrowed is the principal. When the last payment is made, the balance is exactly zero.

Most mortgages, car loans, student loans and personal loans work this way. The payment never changes on a fixed-rate loan, but what it pays for does. Every month, interest is worked out on the balance you still owe and taken first. Whatever is left of the payment reduces the balance. A smaller balance means less interest next month, so a little more goes to principal, and so on until the loan is gone.

An amortization schedule is that process written out: a table with one row per payment showing the interest, the principal and the balance left. The calculator below builds one for any loan.

Loan amortization calculator

Enter the loan amount, the yearly rate, the term and any extra you could add each month. It shows the payment, the total interest, and where every year’s payments go, with the formula and your numbers underneath.

The rest of this guide uses the same example: $300,000 borrowed at 6% for 30 years. The payment is $1,798.65 a month. Over 360 payments you pay back $647,515, so the interest comes to about $347,515, more than the loan itself.

How is loan interest calculated each month?

Monthly interest is the balance you owe × the yearly rate ÷ 12. The payment covers that interest first, and the rest comes off the balance.

Notice the interest is not added to the balance. Each month’s interest is paid in full by that month’s payment, so on a normal amortized loan interest never earns interest. The cost comes from the size of the balance and how long it stays large.

Why is most of my payment interest at first?

Because interest is charged on the balance, and at the start the balance is as large as it will ever be. On the $300,000 loan, $1,500 of the first $1,798.65 payment is interest. Here is the same payment at points along the 30 years:

• Year 1 is 83% interest. Of $21,584 paid in the first year, $17,900 is interest and $3,684 is principal.
• Five years in, you have barely dented it. You have paid about $107,919, of which only $20,837 reduced the balance. You still owe $279,163.
• The crossover comes late. Principal first beats interest at payment 223, in year 19, and half the loan is not paid off until payment 252, in year 21.

There is a neat pattern underneath. The principal part grows by exactly the monthly rate each month: $298.65 × 1.005¹¹⁹ = $540.66, the principal in payment 120. It is compound growth, working on your side of the payment.

What is the amortization formula?

Two formulas do all the work: one for the fixed payment and one for the balance after any number of payments. Both use the monthly rate and the number of months.

This is the annuity formula from the time value of money, solved for the payment: the payment is the amount whose 360 monthly values, brought back to today at 0.5% a month, add up to exactly $300,000.

How much does the rate and term change the interest?

A lot. A shorter term means a bigger payment but far less interest, and each point of rate adds tens of thousands of dollars over a long loan. Here is $300,000 at different rates over 15 and 30 years (principal and interest only):

Payments and interest scale with the amount borrowed, so for $150,000 halve every figure, and for $600,000 double them.

15-year vs 30-year mortgage interest

At 6%, the 15-year loan costs $732.92 more a month ($2,531.57 against $1,798.65) and saves $191,832 in interest. In its first year, $12,725 goes to principal, more than three times the 30-year loan’s $3,684.

You can also get the 15-year result from a 30-year loan: pay the 30-year loan plus $732.92 extra every month and, at the same 6%, it is paid off in exactly 180 months with the same $155,683 of interest. The difference is that the extra is optional, so a tight month does not become a missed payment. If the 15-year loan comes with a lower rate than the 30-year, the 15-year saves more than this; compare the actual offers.

How much do extra payments save on a loan?

Every extra dollar goes straight to principal (as long as your lender applies it that way), which stops interest on that dollar for the rest of the loan. On the $300,000, 6%, 30-year loan:

A one-time payment works too. $10,000 extra in the first month, and nothing more, ends the loan 2 years 6 months early and saves about $45,566. Before paying extra, check your loan papers for a prepayment penalty, and tell the lender the money is for principal.

How do biweekly payments work?

Paying half the monthly payment every two weeks means 26 half payments a year, which is 13 full payments instead of 12. The extra payment is where all the savings come from.

You can get the same effect without signing up for anything: add $149.89 to each monthly payment, or pay one extra payment a year. If a biweekly plan charges a fee, the free version is better.

How do you make an amortization schedule in Excel or Google Sheets?

Both have built-in loan functions with the same arguments: the monthly rate, the payment number, the number of months and the amount borrowed. Results come out negative because they are money going out.

NPER’s 278.36 months means 278 full payments and a small 279th, which is the 23 years 3 months in the extra-payments table. The last argument of CUMIPMT and CUMPRINC is 0 for payments at the end of each month, which is how loans normally work.

Building the full schedule

1. Put the loan amount in B1 (300000), the yearly rate in B2 (6%), the years in B3 (30), and the payment in B4: =PMT(B2/12, B3*12, -B1), which gives 1,798.65 as a positive number.
2. Make headings in row 6: Month, Starting balance, Interest, Principal, Ending balance.
3. Row 7: A7 = 1, B7 = $B$1, C7 = B7*$B$2/12, D7 = $B$4-C7, E7 = B7-D7.
4. Row 8: A8 = A7+1, B8 = E7, then copy C7:E7 down into row 8.
5. Copy row 8 down to row 366. The ending balance in the last row is 0.

To test extra payments, add an Extra column and subtract it in the ending balance. Row 7 of this sheet shows the $1,500.00 and $298.65 worked out by hand above.

Car loans, personal loans and credit cards

Any fixed-rate loan with equal monthly payments follows the same math; only the size and length change. Credit cards are the exception, because they have no fixed schedule.

Why credit cards work differently

A card has no set number of payments and no payment that is built to reach zero. You can add to the balance at any time, and the minimum payment is set by the card company, not by an amortization formula. Pay only the minimum and the balance can take years to clear.

In the US, card statements must show how long paying only the minimum would take and what it would cost in total. When that would take more than three years, the statement usually also shows the monthly payment that would clear the balance in three. Those lines are worth reading.

You can give a card balance your own schedule. Take $5,000 at 24% a year, which is roughly $100 of interest in the first month ($5,000 × 0.24 ÷ 12). With no new charges and 2% a month of interest, a fixed $150 a month takes 56 months and costs about $3,322 in interest. Treating it as a 3-year loan means $196.16 a month and about $2,062 in interest.

Interest you pay on any debt is money leaving every month for nothing new. The leak check works out what a card balance costs you in interest each month, puts it next to fees and unused subscriptions, and shows how long the balance would take to clear.