Amortization Table Setup: Formula and Example

An amortization table setup uses a loan’s starting balance, annual interest rate, term, and payment schedule to show how each payment is divided between interest and principal. Enter the loan amount, APR, and term in the calculator below to estimate the monthly payment and total interest, then use the setup steps to build a period-by-period table.

Formula

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

Payment = P × r ÷ [1 − (1 + r)⁻ⁿ]

• P is the starting loan principal, in dollars.
• r is the monthly interest rate as a decimal: annual APR ÷ 1,200.
• n is the number of monthly payments: years × 12.

The table’s recurring calculations are: interest for a period = starting balance × r; principal repaid = payment − interest; ending balance = starting balance − principal repaid. The ending balance becomes the next period’s starting balance. The calculator reports total interest as the rounded monthly payment multiplied by the number of payments, minus the original principal.

Worked Example

Using the calculator’s default inputs—$20,000 principal, 8% APR, and a five-year term:

1. Convert APR to a monthly decimal rate: 8 ÷ 1,200 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Apply the payment formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = approximately $405.53 per month, rounded to cents.
4. Calculate total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

To start the table, use a balance of $20,000. The first month’s interest is $20,000 × 0.0066667, or about $133.33. Subtract that from the $405.53 payment to get about $272.20 of principal repaid. The resulting balance is about $19,727.80. Repeat the calculation for each month, using the new balance. Rounding each row to cents can create a small difference in the last row; lenders may adjust the final payment to bring the balance to zero.

Assumptions

• The interest rate stays fixed throughout the term.
• Payments are equal, made monthly, and applied at the end of each period.
• Interest compounds monthly at APR divided by 12.
• The estimate excludes fees, taxes, insurance, PMI, and escrow.

How to Set Up the Full Table

Use a spreadsheet or paper ledger with one row per payment period. The calculator gives the payment and total-interest estimate; the table adds the changing balance and the principal-versus-interest split. Set up columns for payment number, beginning balance, payment, interest, principal, and ending balance.

1. Enter the agreed starting principal, annual APR, and term. Use the lender’s figures rather than an amount that includes costs unless those costs are actually part of the loan principal.
2. Convert the annual rate to a periodic rate that matches the payment interval. For monthly payments under the assumptions above, divide the percentage APR by 1,200 to get a decimal.
3. Calculate the fixed payment with the formula above. Keep more precision in the spreadsheet calculation and display currency to two decimal places.
4. For each row, multiply that row’s beginning balance by the periodic rate to find interest. Subtract interest from the payment to find principal paid.
5. Subtract principal paid from the beginning balance. Carry the ending balance into the next row as its beginning balance.
6. Check that the final balance is approximately zero and that total principal repaid is approximately the original principal. Investigate any material discrepancy.

A basic spreadsheet can use formulas with cell references so changing the APR, term, or principal updates every row. Keep the first balance linked to the principal input; link later beginning balances to the preceding ending balances. This reduces transcription errors and makes the schedule easier to audit.

How to Read an Amortization Table

At the beginning of a level-payment loan, the balance is relatively high, so the interest portion usually takes up more of the payment. As principal is repaid, interest is calculated on a smaller balance; consequently, a larger portion of later payments reduces principal. The scheduled payment can remain level even though its components change.

For example, in the first row of the worked example, roughly $133.33 goes to interest and $272.20 to principal. The next month’s interest is computed on roughly $19,727.80, not on the original $20,000. This is why an amortization table is more informative than multiplying the first month’s interest by the number of months.

Common Setup Errors and Limits

• Mixing percentage and decimal rates: 8% is 0.08 annually; for this monthly formula, it becomes 0.08 ÷ 12. Entering 8 as the decimal rate makes interest far too large.
• Using the wrong period count: a five-year monthly schedule has 60 payments, not 5 or 12.
• Rounding too early: rounding the periodic rate or intermediate balances at every step can cause drift. Calculate with full precision and round displayed money values to cents.
• Assuming every loan is level-payment: variable rates, irregular payment dates, interest-only periods, deferred payments, and fees can change the schedule. This basic setup does not model them.
• Expecting the final row to match exactly: if each payment is rounded to cents, a small residual can remain. A real repayment schedule may use a slightly different final payment.

An amortization table is a mathematical estimate, not a statement of the account balance. The loan contract and lender’s payment allocation and rounding practices determine actual figures. Extra payments also need separate treatment: if applied to principal, they can change future balances and the payoff date, but the table above assumes only the scheduled payment.

Frequently Asked Questions

What information do I need to set up an amortization table?
For a basic fixed-rate monthly schedule, you need the starting principal, APR, loan term, and payment frequency. Check the lender’s documents for the applicable rate and payment details.

How do I calculate the interest in each row?
Multiply the row’s beginning balance by the periodic interest rate as a decimal. With monthly payments and the assumptions here, the periodic rate is APR ÷ 1,200.

Why does the principal portion increase over time?
The payment is level, but interest is calculated on the outstanding balance. As that balance falls, less of the payment covers interest and more reduces principal.

Does total interest equal the first month’s interest multiplied by the term?
No. Interest generally falls as the balance is repaid. Add the interest amounts across all rows, or use the calculator’s estimate based on its rounded fixed payment.

Why is my ending balance not exactly zero?
Rounding each payment or row to cents can leave a small residual. Also check the rate conversion, number of periods, payment timing, and whether the actual loan includes features this basic formula does not represent.

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

Related

• Qualifying Income Exception: What It Means
• Qualifying Income Calculator: Estimate Loan Income
• Qualifying Income Calculator for Loan Applications

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