How to build a loan amortization schedule in Excel

Step 1: the payment with PMT

=PMT(rate, nper, pv) returns the level payment that repays a loan of pv over nper periods. Two details matter:

• Match the periods. For monthly payments, use the monthly rate (annual rate ÷ 12) and the number of months. 9% a year is 0.75% a month.
• Mind the sign. Excel treats money you receive as positive and money you pay as negative. Enter the loan as -B1 and the payment comes out positive.

So =PMT(B2/12,B3,-B1) gives $1,049.42 a month.

Step 2: one row per payment

Each row of the schedule follows the same four steps:

1. Interest = opening balance × monthly rate: =B7*$B$2/12
2. Principal = payment − interest: =C7-D7
3. Closing balance = opening balance − principal: =B7-E7
4. The next row's opening balance = this row's closing balance: =F7

Build the first row, then fill it down. The dollar signs in $B$2 and $B$4 keep the rate and the payment fixed while the row references move.

Worked example: $12,000 over 12 months at 9%

Rows 7 to 18 all use the same formulas, filled down from row 7.

Worked example: $12,000 over 12 months at 9%

	A	B	C	D	E	F
1	Loan amount ($)	12,000.00
2	Annual interest rate	9.00%
3	Number of monthly payments	12
4	Monthly payment	1,049.42
5
6	Month	Opening balance	Payment	Interest	Principal	Closing balance
7	1	12,000.00	1,049.42	90.00	959.42	11,040.58
8	2	11,040.58	1,049.42	82.80	966.61	10,073.97
9	3	10,073.97	1,049.42	75.55	973.86	9,100.11
10	4	9,100.11	1,049.42	68.25	981.17	8,118.94
11	5	8,118.94	1,049.42	60.89	988.53	7,130.41
12	6	7,130.41	1,049.42	53.48	995.94	6,134.47
13	7	6,134.47	1,049.42	46.01	1,003.41	5,131.06
14	8	5,131.06	1,049.42	38.48	1,010.93	4,120.13
15	9	4,120.13	1,049.42	30.90	1,018.52	3,101.61
16	10	3,101.61	1,049.42	23.26	1,026.16	2,075.46
17	11	2,075.46	1,049.42	15.57	1,033.85	1,041.61
18	12	1,041.61	1,049.42	7.81	1,041.61	0.00
19	Total		12,593.01	593.01	12,000.00

Step 3: check the schedule

A correct schedule has three built-in checks. The closing balance after the last payment is 0. The principal column adds up to the loan amount ($12,000). And total payments minus the loan equals total interest: $12,593.01 − $12,000 = $593.01. If the last balance is not zero, the rate and the number of periods do not match, usually an annual rate used with monthly periods.

IPMT and PPMT: one payment without the whole table

If you only need the split of one payment, Excel has two shortcuts. =IPMT(B2/12,1,B3,-B1) returns the interest in month 1 ($90.00) and =PPMT(B2/12,1,B3,-B1) the principal ($959.42). Change the second argument to any month number. For the balance still owed after n payments, use =-FV(B2/12,n,-B4,B1). Note the sign here: FV takes the payment as negative and the loan as positive, the opposite of PMT above.

What changes the payment and the total interest

Three inputs drive everything: the amount, the rate and the term. Stretch the same $12,000 loan to 24 months and the payment drops to $548.22, but total interest rises from $593.01 to $1,157.21. Raise the rate to 12% over 12 months and the payment becomes $1,066.19, with $794.23 of interest. Because the inputs sit in their own cells, you can test each case by changing one number. The whole schedule updates, and the last closing balance stays at zero as long as the number of rows matches the number of payments.

If you want the schedule to adapt to any term automatically, wrap each row in an IF that returns an empty string once the month number is greater than the term, for example =IF(A7>$B$3,"",B7*$B$2/12).

Why early payments are mostly interest

Interest is charged on what you still owe, and at the start you owe the most. In this short loan, the interest share falls from $90.00 in month 1 to $7.81 in month 12. On a long mortgage the effect is much stronger: on a $300,000 loan at 6% over 25 years, the payment is $1,932.90 and the first month's interest alone is $1,500.00. That is why extra repayments early in a loan save so much interest.

Common mistakes

• Annual rate with monthly periods. =PMT(9%,12,-12000) calculates twelve annual payments at 9% a year. Divide the rate by 12.
• Negative payments everywhere. Forgetting the minus in front of the loan makes PMT negative and flips the sign of every column. Pick one convention and stick to it.
• Relative references to the inputs. Without $B$2, filling the formulas down shifts the rate reference into empty cells and the interest turns to zero.
• Rounding the payment in the schedule but not in PMT. If the lender rounds each payment to the cent, the last payment absorbs the difference. Expect a few cents of difference, not dollars.
• Ignoring fees. The schedule shows the interest rate, not the full cost of the loan. Fees raise the effective rate.
• Filling the schedule past the last payment. Extra rows keep subtracting the payment and show negative balances. Stop at the final month, or blank the extra rows with IF as shown above.
• Mixing up interest rate and APR. Lenders often quote an annual percentage rate that already includes fees. The schedule needs the nominal interest rate the lender charges on the balance.