Amortization Formula: How to Calculate Loan Payments and Schedules

The Standard Amortization Formula

The standard amortization formula calculates the periodic payment amount for a fixed-rate loan. It is expressed as M = P * [r(1+r)^n] / [(1+r)^n - 1], where M is the periodic payment, P is the principal, r is the periodic interest rate, and n is the total number of payments. This formula ensures that the loan is fully paid off by the end of its term with a constant payment amount.

The formula is valid for any positive periodic interest rate. If the interest rate is zero, the payment simplifies to the principal divided by the total number of payments (P / n). For most loans, the periodic interest rate is the annual rate divided by the number of periods per year, and the total number of payments is the loan term in years multiplied by the number of periods per year.

Sources: TransUnion, Investopedia, Wikipedia

Variables Explained: Principal, Rate, Term

The principal (P) is the initial amount borrowed. The periodic interest rate (r) is the interest rate applied each period. For monthly payments, divide the annual interest rate by 12. The total number of payments (n) is the number of compounding periods over the loan's life. For a 30-year mortgage with monthly payments, n equals 360.

These three variables are the core inputs to the amortization formula. Ensuring they are correctly converted to the same time period is critical for accurate results.

• Principal (P): The original loan amount, not including interest or fees.
• Periodic interest rate (r): Annual interest rate divided by the number of payments per year (e.g., 12 for monthly).
• Total number of payments (n): Loan term in years multiplied by the number of payments per year.

Sources: TransUnion, Investopedia

Step-by-Step Manual Calculation Example

The monthly periodic rate is r = 0.06 / 12 = 0.005, and the total number of payments is n = 30 × 12 = 360. Using the formula: M = 200,000 * [0.005(1.005)^360] / [(1.005)^360 - 1].

1. Determine the total number of payments: 30 × 12 = 360.
2. Compute (1 + r)^n: (1.005)^360 ≈ 6.0226.
3. Calculate the numerator: 0.005 × 6.0226 = 0.030113.
4. Divide by the denominator: (6.0226 - 1) = 5.0226, so 0.030113 / 5.0226 ≈ 0.005995.

Sources: TransUnion, Investopedia, Wikipedia

Using Spreadsheets for Amortization Schedules

Spreadsheets provide built-in functions to simplify amortization calculations. The PMT function calculates the periodic payment amount. The PPMT function returns the principal portion of a payment for a given period, and the IPMT function returns the interest portion. These functions are widely available in spreadsheet software and are commonly used in financial modeling.

To build an amortization schedule, you can start with the loan amount, rate, and term, then use these functions to compute payment components for each period, updating the remaining balance each row.

• PMT(rate, nper, pv): returns the periodic payment.
• PPMT(rate, period, nper, pv): returns the principal portion for a specified period.
• IPMT(rate, period, nper, pv): returns the interest portion for a specified period.

Sources: Wall Street Prep

Deriving the Formula (For Advanced Users)

The amortization formula can be derived by modeling the principal balance after each payment. Let p_t be the balance after t payments, and let r represent 1 + i, where i is the periodic interest rate. The balance evolves as p_t = p_t-1r - A, with initial condition p_0 = P, where A is the periodic payment. Solving this recurrence gives p_n = P r^n - A[(r^n - 1)/(r - 1)]. Setting p_n = 0 and solving for A yields the standard formula.

This derivation shows that the formula ensures the final balance is zero, meaning the loan is fully repaid.

Sources: Wikipedia

Common Mistakes in Manual Calculation

Manual calculations often stumble on a few points: using the annual rate directly instead of the periodic rate, forgetting to convert the term to the number of periods, and misordering arithmetic operations. Additionally, when calculating the interest portion of a payment, it is calculated as the outstanding balance multiplied by the periodic interest rate, not the original principal. The principal payment is the total payment minus the interest payment, and the remaining balance is the previous balance minus the principal payment.

Another common error is assuming the interest portion declines linearly; in reality, it declines as the balance decreases. Keeping each step organized helps avoid these pitfalls.

Sources: Investopedia, Amortization-calc.com, Consumer Financial Protection Bureau