Loan Amortization Table: Calculate Each Payment

A loan amortization table shows how each scheduled payment is divided between interest and principal, and how the balance changes over time. Enter the loan amount, APR, and term to calculate a level monthly payment and total interest; use the method below to build the full schedule.

Formula

For a fixed-rate loan with equal monthly payments, calculate the payment with:

M = round[P × r ÷ (1 − (1 + r)⁻ⁿ), 2]

• P is the starting loan principal, in dollars.
• APR is the annual percentage rate entered as a percentage.
• r is the monthly rate as a decimal: APR ÷ 1,200.
• n is the number of monthly payments: years × 12.
• M is the monthly payment, rounded to the nearest cent.

For each row in the table, calculate interest as opening balance × r. Calculate principal paid as M − interest, then calculate the closing balance as opening balance − principal paid. The next row’s opening balance is the previous row’s closing balance. Total interest in this calculator is the rounded monthly payment multiplied by the number of payments, minus the original principal.

Worked Example

Using the calculator’s default values—$20,000 principal, 8% APR, and 5 years—first find the number of payments and monthly rate:

• n = 5 × 12 = 60 monthly payments.
• r = 8 ÷ 1,200 = 0.0066667 per month.
• M = round[20,000 × 0.0066667 ÷ (1 − (1.0066667)⁻⁶⁰), 2] = $405.53.

Build the first row from the starting balance:

• Opening balance: $20,000.00.
• Interest: $20,000 × 0.0066667 = $133.33, rounded to cents.
• Principal paid: $405.53 − $133.33 = $272.20.
• Closing balance: $20,000.00 − $272.20 = $19,727.80.

For the second row, start with $19,727.80. Interest is $19,727.80 × 0.0066667 = $131.52; principal paid is $405.53 − $131.52 = $274.01; the closing balance is $19,453.79. Repeat those calculations for each month. The calculator’s total-interest result is $405.53 × 60 − $20,000 = $4,331.80. A lender’s final payment or interest total can differ slightly if it applies a different rounding or payment-date method.

Assumptions

• The interest rate is fixed for the full term.
• Payments are equal, made monthly, and treated as occurring at the end of each monthly period.
• Interest compounds monthly using APR ÷ 12.
• Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.

What You’ll Need

• The amount borrowed, excluding any costs that are not financed.
• The loan’s annual percentage rate as provided in the lender’s disclosure.
• The scheduled term in years, or the payment count in months.
• A calculator or spreadsheet that can calculate percentages and round to cents.

Use the figures from the loan documents when available. The calculator is a general estimate; a lender may use specific dates, daily interest, fees, or other contract details that change the schedule.

Step-by-Step

1. Set up the columns. Create columns for payment number, opening balance, payment, interest, principal, and closing balance. You may also add the payment date if you know the schedule.
2. Enter the starting details. Put the original principal in the first opening-balance cell. Record the APR and term separately so you can check your inputs.
3. Convert the APR to a monthly decimal. Divide the annual percentage by 100 to express it as a decimal, then divide by 12. For example, 8% becomes 0.08 ÷ 12 = 0.0066667. Equivalently, divide the APR number by 1,200.
4. Find the number of payments. Multiply the number of years by 12. A five-year monthly schedule has 60 payment rows.
5. Calculate the regular payment. Use the formula above or the calculator. Keep the result rounded to cents for the schedule. Do not substitute a yearly rate directly for the monthly rate.
6. Calculate the first month’s interest. Multiply the opening balance by the monthly rate. Round the interest to cents if you are making a standard monthly spreadsheet schedule.
7. Split the payment. Subtract that month’s interest from the scheduled payment. The remainder is principal paid. If the result is zero or negative, the payment does not reduce the balance under these assumptions.
8. Calculate the new balance. Subtract principal paid from the opening balance. Carry this closing balance into the next row as its opening balance.
9. Fill the schedule down. Repeat the interest, principal, and balance calculations for every payment. The interest portion generally falls as the balance falls, while the principal portion rises.
10. Check the last row. The balance should reach zero or be close to it. If rounding leaves a small residual, adjust only the final payment to clear the remaining amount, and label it as an adjusted final payment. Do not silently change every scheduled payment.
11. Check totals. Add the interest column to get scheduled interest using your row-by-row rounding method. Compare it with the calculator’s estimate, which uses rounded payment × payment count − principal. Small differences can occur because the two methods round at different stages.

Common Mistakes

• Mixing percentage and decimal formats: 8% is 0.08 as a decimal, or 8 in a formula that divides by 1,200. Entering 8 as a decimal makes the rate far too large.
• Using the annual rate each month: monthly interest uses APR ÷ 12, expressed as a decimal—not the full annual percentage.
• Rounding too early: Keep sufficient precision for the rate and formula, then round the payment and monthly money values to cents as appropriate. Different rounding conventions can change the final row.
• Forgetting the final adjustment: A tiny remaining balance may result from cent rounding. Confirm the lender’s actual payoff amount rather than treating a spreadsheet residual as an exact bill.
• Including costs inconsistently: A financed fee changes the amount being repaid. Do not add costs to the principal unless they are included in the actual loan balance.

Tips

• Keep the original inputs visible beside the table so you can audit the calculation.
• Use formulas that reference the previous row’s closing balance, rather than typing balances by hand.
• Format money cells to two decimal places, but retain adequate underlying precision for calculations.
• If payments are not monthly, or the loan has a variable rate, balloon payment, interest-only period, or extra principal payments, model those terms separately rather than relying on this level-payment schedule.

Troubleshooting

The payment looks unusually high or low: Check that the principal is in dollars, APR is a percentage, and term is in years. Confirm that the term was multiplied by 12 and the rate divided by 1,200.

The balance increases instead of falling: Check that the payment exceeds that month’s interest. A payment that only covers interest does not reduce principal; a smaller payment can cause the balance to grow.

The balance does not reach zero: Review formulas for references to the correct prior balance, inspect rounding, and check the payment count. Apply a clearly marked final-payment adjustment for a small rounding remainder. A large remainder may mean the payment, term, or rate is wrong.

Your result differs from a lender’s schedule: Compare the starting balance, APR, number and timing of payments, payment rounding, and treatment of fees. Ask the lender for the contractual amortization schedule if you need the exact figures.

Frequently Asked Questions

What does an amortization table show?
It lists each scheduled payment’s interest and principal portions, along with the remaining balance after payment.

Does the interest portion stay the same?
No. With a fixed rate and level payments, interest is generally higher earlier because it is calculated on a larger balance. The principal portion generally grows over time.

Can I use this method for a variable-rate loan?
Only for a period when the rate is known and treated as fixed. Recalculate the payment and later rows when the rate or contract terms change.

How do extra payments affect the table?
Extra principal payments reduce the balance sooner and can change later interest and payoff timing. This basic calculator does not model them; use the loan’s payment rules and update the balance for each extra payment.

Why is my total interest slightly different from the calculator?
The schedule may round interest each month, while the calculator estimates total interest from the rounded payment and payment count. Dates and lender-specific methods can also affect totals.

This result is an estimate; the lender’s disclosure gives the exact figures.

Related

• 3 Year Amortization Table Calculator and Loan Schedule
• WIC Qualifying Income: Limits and Eligibility
• Total Qualifying Income Calculator and Guide

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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2