How to Calculate Amortization: Formulas, Spreadsheet Steps, and Examples

What an amortization calculator does and what it produces

An amortization calculator computes the monthly payment for an amortizing loan and produces an amortization schedule showing each payment's allocation to principal and interest. This schedule is a table that lists every payment over the loan's life, breaking down how much of each payment goes toward interest and how much reduces the principal balance.

For any fixed-rate, fully amortizing loan, the monthly payment remains the same throughout the term, but the portion of that payment applied to interest decreases over time while the portion applied to principal increases. The final payment is adjusted to bring the balance to exactly zero, so it may differ slightly from the regular payment due to rounding.

• Key outputs: monthly payment amount, total interest paid, principal balance after each payment.
• The schedule typically includes columns for payment number, payment amount, interest paid, principal paid, and remaining balance.
• Early payments are mostly interest; later payments are mostly principal.

Sources: Temple University, Fidelity, Investopedia, Calculator.net

The amortization formula

The standard formula for a fixed monthly payment on an amortizing loan is: M = P × [r(1+r)^n] / [(1+r)^n − 1], where M is the monthly payment, P is the loan principal (the amount borrowed), r is the monthly interest rate (annual rate divided by 12), and n is the total number of monthly payments (loan term in years multiplied by 12).

This formula assumes a fixed interest rate and a fully amortizing loan—one that pays off the principal over the term. It does not apply to interest-only, adjustable-rate, or balloon loans.

• Variables: P = principal, r = monthly interest rate, n = total number of payments.
• Monthly rate: annual interest rate ÷ 12.
• Number of payments: loan term in years × 12.
• The formula does not include taxes, insurance, or other fees.

Sources: Temple University, TransUnion, Investopedia, Calculator.net, Amortization US

Step-by-step manual calculation of a monthly payment

To calculate the monthly payment for a fixed-rate loan, follow these steps: determine the monthly interest rate by dividing the annual rate by 12, then determine the total number of payments by multiplying the loan term in years by 12. Plug these values into the formula above. Work through the exponents and arithmetic carefully, or use a scientific calculator or spreadsheet.

For example, consider a $250,000 loan at 5% annual interest over 30 years. The monthly rate is 0.05/12 = 0.004167, and n = 30 × 12 = 360. Using the formula gives a monthly payment of $1,342.05. Another example: a $30,000 car loan at 3% annual interest over 4 years yields a monthly payment of $664.03.

These examples are illustrative and use hypothetical rates; they are not current market rates.

• Example: $250,000, 5% annual rate, 30 years → $1,342.05/month.
• Example: $30,000, 3% annual rate, 4 years → $664.03/month.

1. Convert annual rate to monthly: r = annual rate / 12.
2. Compute total payments: n = years × 12.
3. Insert P, r, n into the formula: M = P × [r(1+r)^n] / [(1+r)^n − 1].
4. Calculate the result. Round to the nearest cent for payment amount.
5. Verify with multiple sources or a spreadsheet if possible.

Sources: Temple University, TransUnion, Investopedia

How to build a complete amortization schedule with a spreadsheet

A spreadsheet is the most practical way to build an amortization schedule. Start by entering the loan amount, annual interest rate, and loan term in years. Calculate the monthly payment using the spreadsheet's built-in PMT function, or by entering the formula directly. Then create a table with columns for payment number, payment amount, interest paid, principal paid, and remaining balance.

For each row, calculate the interest for that period by multiplying the current balance by the monthly interest rate. The principal paid is the total payment minus the interest paid. The new balance is the previous balance minus the principal paid. Repeat for each period until the balance reaches zero (or near zero, with the final payment adjusted for rounding).

Spreadsheet functions like PMT, PPMT, and IPMT can automate these calculations. Consult your spreadsheet's documentation for exact syntax, as the details may vary by program.

1. Enter loan principal (P), annual interest rate, and term in years.
2. Calculate monthly rate (r) and total payments (n).
3. Use PMT function or formula to compute monthly payment.
4. Create columns: Period, Payment, Interest Paid, Principal Paid, Balance.
5. For each period: Interest = Balance × r; Principal = Payment − Interest; Balance = Previous Balance − Principal.
6. Adjust the final payment to bring the balance to zero (rounding may cause a small difference).

Sources: Temple University, Fidelity, Investopedia

How to read and use an amortization schedule

An amortization schedule helps you see exactly how each payment is split between interest and principal. This can be useful for budgeting, planning prepayments, and understanding the long-term cost of a loan. The schedule shows that early payments go mostly toward interest, while later payments mostly reduce principal.

You can use the schedule to track your remaining balance at any point, compare loan offers, and see the impact of extra payments. By examining how much interest you would pay over the full term, you can make more informed decisions about refinancing or paying off the loan early.

An amortization schedule is not applicable to interest-only loans, because those loans have a period during which you pay only interest and the principal does not decrease.

• Identify the interest and principal components for each payment.
• Track remaining balance over time to know your payoff amount.
• Compare the total interest under different loan terms or rates.
• Clearer picture of how extra payments affect the remaining balance and total interest.

Sources: Temple University, TransUnion, Fidelity, Investopedia, Calculator.net

How to adjust for extra payments

Making extra payments toward the principal can shorten the loan term and reduce total interest paid. The standard amortization formula does not change when you make extra payments—your regularly scheduled payment remains fixed, but the outstanding balance decreases faster, which reduces the interest charged on subsequent payments.

To model extra payments in a spreadsheet, subtract the extra amount from the balance after the regular payment. The next period's interest will be calculated on the lower balance. Extra payments are typically applied to the principal, not to future payments, unless you specify otherwise. Before making extra payments, check your loan agreement for any prepayment penalties, as some loans may charge them.

• Extra payments reduce the principal balance, cutting future interest.
• They can shorten the loan term and lower total interest.
• The amortization formula itself remains unchanged; only the balance changes.
• Check for prepayment penalties before making extra payments.

Sources: TransUnion, Fidelity, Amortization US

Limitations of manual calculations and when to consult a financial advisor

Manual amortization calculations are subject to rounding errors, and the final payment may differ from the regular payment because it is adjusted to bring the balance to zero. The standard formula assumes a fixed interest rate and does not include taxes, insurance, or other fees, so your actual payment may be higher. Adjustable-rate loans, interest-only loans, and balloon loans do not follow the standard amortization formula.

Because amortization affects long-term financial commitments, consider consulting a financial advisor if you are planning a major loan or refinancing. They can provide personalized advice that accounts for your full financial situation, including tax implications and investment alternatives.

• Rounding can cause the final payment to differ from the regular payment.
• Taxes, insurance, and fees are not included in the formula.
• The formula applies only to fixed-rate, fully amortizing loans.
• A financial advisor can help you interpret how amortization fits into your broader financial plan.

Sources: Temple University, Investopedia, Calculator.net, Amortization US