Finance & Investment
Build a Loan Amortization Schedule and Check Every Row
Create a fixed-rate loan schedule from payment, interest and principal, with a worked first-payment example and independent balance checks.
An amortization schedule explains the payment
A loan amortization schedule separates each payment into interest and principal and shows the remaining balance after that payment. A free calculator can help construct the schedule, but its assumptions must match the loan being modeled. The standard formula here assumes a fixed interest rate, equal payment intervals and payments at the end of each interval. It excludes fees and irregular payment dates unless they are modeled separately. A smaller balance after every payment is expected only when the payment covers the period's interest and also reduces principal.
Calculate an equal monthly payment
Let P be principal, i the monthly interest rate and n the total number of monthly payments. The fully amortizing payment is M=P i/[1-(1+i)^(-n)]. If the nominal annual percentage rate is 12% with monthly periods, use i=0.12/12=0.01. For P=10,000 and n=12, M is approximately 888.4879, or 888.49 when displayed in currency units. If the interest rate is zero, use M=P/n. A zero-interest branch avoids division by zero and represents simple repayment of the principal over the selected number of installments.
Build the first two rows
With an opening balance of 10,000 and monthly rate 1%, first-month interest is 100. Using the unrounded payment 888.4879, principal reduction is approximately 788.4879 and the closing balance is 9,211.5121. In month two, interest is about 92.1151 and principal reduction is about 796.3728, leaving 8,415.1393. The payment remains constant while interest declines and principal reduction rises. If your spreadsheet gives the same interest amount every month under this model, it is probably applying the rate to the original principal instead of the remaining balance.
Write spreadsheet columns that can be audited
Use columns for period number, opening balance, interest, payment, principal and closing balance. Interest equals opening balance times the periodic rate. Principal equals payment minus interest, and closing balance equals opening balance minus principal. The next row's opening balance must equal the previous row's closing balance. Put the rate and payment in fixed input cells so that copied formulas reference the intended values. Retain the actual contract's rounding convention rather than mixing rounded payments with unrounded interest without documenting the difference.
Check totals and the final payment
Across a fully repaid schedule, the sum of principal reductions should equal the original principal, and total payments should equal principal plus total interest when no other charges are included. The final balance should be zero within the chosen rounding convention. Rounding each monthly payment can leave a small residual, so the last payment may need adjustment. Do not simply hide a negative balance by formatting it as zero. Record why the final installment differs and compare the schedule's total interest with a direct sum of its interest column.
Model extra payments explicitly
An extra amount applied to principal reduces later interest under a declining-balance model, but it may shorten the term, reduce future payments after a recast, or be handled differently by the contract. Add a separate extra-principal column instead of silently replacing the scheduled payment. Subtract it when computing the closing balance and stop when the debt is repaid. Verify any fees or restrictions with the actual lender. A spreadsheet can quantify an assumed treatment but cannot determine whether the lender will apply a submitted payment in that way.
Read the site's output as a model
Enter principal, annual rate and term in the Loan Amortization Calculator, then compare its payment and balance progression with the first two rows above. If a lender's statement differs, check daily versus monthly interest, payment dates, financed fees and rounding before concluding that either calculation is wrong. A variable-rate loan needs a schedule that changes rates at the appropriate dates. Keep the original inputs and any extra-payment assumptions with the result so a later revision can be traced. The schedule estimates contractual cash flows; it does not create a loan approval or binding payoff quote.
Use the Loan Amortization Calculator
Source and scope
Related guides
Calculate a balloon payment from principal, rate and regular payments, then distinguish the maturity date from the amortization schedule.
Build an amortization schedule row by row, check the first two months and twelve-month balance, and distinguish full-term totals from the local first-year display.
Derive the fixed-payment formula from present value, check a twelve-month loan and trace how each payment reduces principal.