How to make a loan amortization schedule in Excel

Step 1: inputs and the monthly payment

Put every assumption in its own cell at the top: the amount borrowed, the annual rate from your loan agreement, the number of monthly payments and, later, any extra you plan to pay. The example is a 12,000 personal loan at 8.4% over 12 months, kept short so the whole table fits on screen. The same formulas fill down to 60 rows for a car loan or 360 for a mortgage.

PMT needs the rate per period, so divide the annual rate by 12, and the number of payments, not years. The minus in front of B1 makes the payment positive. ROUND to cents, because a lender bills a payment in cents and every row below uses it.

Step 2: interest, principal and the running balance

Row 8 is payment 1. B8 is =$B$1, the full amount owed. Interest for the month is the opening balance times the monthly rate: =ROUND(B8*$B$2/12,2). Principal is what is left of the payment: =C8-D8. The closing balance is =B8-E8.

Row 9 then starts with =F8, the previous closing balance, and fills down. That one link chains the whole table together. Lock the input cells with $ ($B$2, $B$4) so they do not shift as you fill; leave the row references relative.

Watch the split move. Payment 1 is 84.00 interest and 962.08 principal; by payment 12 interest has fallen to 7.27, because each month it is charged on a smaller balance.

Step 3: a last payment that lands on exactly 0

Because the payment is rounded and so is each month's interest, a straight fill leaves a few cents owing (or overpaid) after the last row. Fix it in the payment column: =IF(A8=$B$3,B8+D8,$B$4). On every row except the last it pays the normal amount; on the last it pays whatever is still owed plus that month's interest.

Check the table with two sums. Principal must add up to the amount borrowed, and the payments minus the loan must equal the interest. Here the 12 payments total 12,552.97. Payment times term, B4*B3-B1, says 552.96 of interest: the cent of difference is the last payment absorbing the rounding.

Step 4: extra payments and when the loan ends

Columns I to O repeat the schedule with an Extra column fed by B5 (300 a month here, an example amount). The difference is in how the payment is capped, so the balance can never go below zero: payment =MIN($B$4,J8+K8), extra =MIN($B$5,J8+K8-L8), principal =L8+M8-K8, closing =J8-N8.

With those MIN caps, the month the loan is paid off gets a smaller payment (320.22 in month 10 here) and every row after it shows 0, so you can fill the table well past the expected end without negative balances. A plain "payment + extra" column keeps paying after the balance is gone.

Check with your lender before you rely on the result. Some apply an extra amount to principal straight away, some hold it as an early payment of next month's bill, and some loans charge for paying early. The table assumes extra goes straight to principal.

Interest for part of the loan, and the IPMT/PPMT shortcuts

To see interest for a year or any run of payments, sum that block of the interest column. Payments 1 to 6 cost 402.03 of interest and payments 7 to 12 only 150.94. For a long loan with a payment-date column (=EDATE(first_date,A8-1)), sum by year with =SUMPRODUCT((YEAR(dates)=2027)*interest) instead of counting rows.

Excel also has functions that skip the table. IPMT(rate,per,nper,pv) returns the interest in payment number per, PPMT the principal, and CUMIPMT(rate,nper,pv,start_period,end_period,type) the interest between two payment numbers. CUMIPMT needs a positive pv, has no optional arguments (type is 0 for end-of-period payments) and returns a negative number. These functions are not available in this site's practice grid, so the table here is built with plain arithmetic.

Expect them to differ from your table by a cent or so in later rows. They work from the exact payment of 1,046.081814 and never round interest, while your schedule pays 1,046.08 and rounds each month. Neither is wrong; the table matches what a lender bills.

Which approach to use

Just need the payment or total interest: PMT alone, as in the loan payment guide. Want to see every month's split, test extra payments, or match a lender's statement: build the table with balance-times-rate interest, rounded to cents, with the IF on the last row. Need one figure such as interest paid in year three of a mortgage: CUMIPMT in Excel, or a SUM over the table. Prefer to start from a ready-made file: the free loan amortization schedule template has the same formulas laid out for 24 payments.

Your lender's figures may still differ by small amounts. Many personal and car loans charge interest daily on the actual days between payments, not one twelfth of the annual rate. The monthly method here is the standard calculation for a fixed-rate installment loan.