This guide shows you how to build a complete loan schedule (also called an amortization table) by hand or with a spreadsheet. You enter the loan amount, annual interest rate, and term, and you get a row-by-row breakdown of each payment: how much goes to interest, how much pays down principal, and the remaining balance. You’ll also see the total interest paid over the life of the loan. Before starting, gather the loan amount, the annual percentage rate (APR), and the term in years or months.
Formula
The heart of a loan schedule is the fixed monthly payment formula:
M = P × [r(1 + r)^n] / [(1 + r)^n – 1]
Where:
- M = monthly payment (in dollars)
- P = principal loan amount (in dollars)
- r = monthly interest rate = annual rate / 12 (as a decimal, so 8% becomes 0.08/12 = 0.0066667)
- n = total number of payments = term in years × 12
This formula assumes a fixed interest rate and equal monthly payments over the entire term. Each payment is the same, but the split between interest and principal changes every month.
Worked Example
Let’s use the default values from the calculator: a $20,000 loan at 8% APR for 5 years.
- Convert APR to monthly rate: r = 8 / 1200 = 0.0066667 (rounded to 6 decimal places).
- Find total number of payments: n = 5 × 12 = 60.
- Plug into the payment formula:
M = 20000 × [0.0066667 × (1.0066667)^60] / [(1.0066667)^60 – 1] - Calculate (1 + r)^n: (1.0066667)^60 ≈ 1.4898457
- Numerator: 20000 × 0.0066667 × 1.4898457 ≈ 20000 × 0.0099323 ≈ 198.646
- Denominator: 1.4898457 – 1 = 0.4898457
- Monthly payment: 198.646 / 0.4898457 ≈ 405.53 (rounded to the cent).
So the monthly payment is $405.53. Over 60 payments, you pay 60 × 405.53 = $24,331.80 total. The total interest is $24,331.80 – $20,000 = $4,331.80. These match the calculator’s outputs.
Assumptions
- Fixed annual interest rate (APR) that does not change.
- Payments are made monthly at the end of each period.
- Interest compounds monthly at APR / 12.
- No fees, taxes, insurance, PMI, or escrow are included.
- All payments are made on time; no late fees or prepayment penalties.
Step-by-Step: Building the Full Schedule
- Set up your columns: Payment number, Payment amount, Interest paid, Principal paid, Remaining balance.
- Fill in the starting balance: In row 0, the balance is the full loan amount (e.g., $20,000).
- For each payment period (1 to n):
Interest for this period = previous balance × monthly rate (r).
Principal paid = monthly payment (M) – interest for this period.
New balance = previous balance – principal paid. - Repeat for every payment: Continue until you reach payment n. The final balance should be $0.00 (or within a few cents due to rounding).
- Verify totals: Sum the interest column to get total interest. Sum the principal column – it should equal the original loan amount. The sum of all interest plus principal equals total payments.
Example first few rows for $20,000 at 8% for 5 years, payment $405.53, monthly rate 0.0066667:
- Payment 1: Interest = 20000 × 0.0066667 = $133.33; Principal = 405.53 – 133.33 = $272.20; Balance = 20000 – 272.20 = $19,727.80
- Payment 2: Interest = 19727.80 × 0.0066667 ≈ $131.52; Principal = 405.53 – 131.52 = $274.01; Balance = 19727.80 – 274.01 = $19,453.79
- Payment 3: Interest = 19453.79 × 0.0066667 ≈ $129.69; Principal = 405.53 – 129.69 = $275.84; Balance = 19453.79 – 275.84 = $19,177.95
Continue this pattern. On the final payment, the principal paid will be slightly adjusted to bring the balance to exactly zero.
Common Mistakes
- Using the annual rate instead of the monthly rate: Always divide APR by 12 before calculating monthly interest. Using 8% instead of 0.6667% will massively overstate interest.
- Mixing up years and months: If the term is given in years, multiply by 12 for the number of payments. If given in months, use that number directly.
- Rounding too early: Round the monthly payment to cents, but use the full unrounded value for interest calculations until the final display. Otherwise, rounding errors accumulate.
- Forgetting that the final payment may differ: Due to rounding, the last payment might be a few cents higher or lower. It’s normal to adjust the final principal to clear the balance.
- Ignoring the compounding assumption: This schedule assumes monthly compounding. If your loan compounds daily or uses simple interest, the schedule will be different.
Tips
- Use a spreadsheet: In Excel or Google Sheets, put the monthly rate in one cell and the payment in another, then use formulas like
=B2*$C$1for interest and=payment - interestfor principal. - Check your work: After building the schedule, sum the principal column – it must equal the original loan amount. Sum the interest column – it should match the total interest from the payment formula.
- Understand the early months: In the beginning, interest dominates; later, principal dominates. This is normal and not a mistake.
- If you plan to make extra payments: You can add an extra payment column and recalculate the schedule. Extra payments reduce the balance faster and shorten the term if you keep the same monthly payment.
- Use the calculator above for a quick check: Enter your actual numbers and compare the first few rows of your manual schedule with the payment amount and total interest shown.
Troubleshooting
- The final balance isn’t zero: This is almost always due to rounding. Adjust the last payment’s principal by the difference. For example, if the balance is $0.03, add $0.03 to the last principal payment.
- Your payment doesn’t match the calculator: Double-check that you converted APR to a monthly decimal (divide by 1200, not 100). Also ensure you’re using the correct number of payments (years × 12).
- Interest amounts seem too high: Verify you’re using the monthly rate (e.g., 0.0066667 for 8%), not the annual rate as a decimal (0.08).
- Negative balance appears: This happens if the payment is larger than needed (e.g., you used an overestimated payment). Reduce the payment or check your formula.
- Cannot compute error: If the payment is less than the monthly interest, the loan will never be paid off. The formula returns “Cannot compute” – this means your payment is too low.
Frequently Asked Questions
What is an amortization schedule?
It’s a table that shows each loan payment over time, breaking down how much goes to interest and how much reduces the principal, along with the remaining balance after each payment.
Why does the interest portion decrease over time?
Because interest is calculated on the remaining balance. As you pay down principal, the balance shrinks, so the interest charged on that smaller balance also shrinks.
Can I use this method for any loan type?
Yes, for any fixed-rate, fixed-term loan with equal monthly payments – such as auto loans, personal loans, or student loans. Adjustable-rate or interest-only loans require a different approach.
What if I make an extra payment?
An extra payment reduces the principal directly. You can either keep the same monthly payment and shorten the loan term, or recalculate a new lower payment if the lender allows it. The schedule must be updated accordingly.
Does the schedule include fees or insurance?
No. The standard schedule only covers principal and interest. Fees, taxes, insurance, or PMI are separate and would need to be added to your actual monthly cost.
How do I calculate the total interest paid?
Multiply the monthly payment by the number of payments, then subtract the original loan amount. For the example above: $405.53 × 60 – $20,000 = $4,331.80.
Remember, this is an estimate. Your lender’s official disclosure statement will give the exact figures for your specific loan.
Related
Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000 in apr | Interest rate (APR) | % | 8 | 0.01 | 100 in yrs | Loan term | years | 5 | 0.0833 | 50 out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2 out int | Total interest | $ | pmt * yrs * 12 - p | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- Jet Fuel Cost per Gallon? Prices and What Affects Them
- High Amount Personal Loan Interest Rate Calculator$405.53
- Fuel Costs and Home Loans: How They Connect$405.53
- Fuel Costs Perth: Prices, Factors and Savings
- Car Quote for a Used-Car Trade-In: What It Means
- Cost of Fuel: Calculate Your Trip’s Fuel Expense$42.00