How Do You Calculate Loan Amortization Using a Financial Calculator or Spreadsheet?

Use a financial calculator to find the regular repayment, then use a spreadsheet to divide each payment into interest and principal and update the outstanding balance. For IB Mathematics: Applications and Interpretation, this is SL 1.7, Loan repayments and amortization.

Financial calculator method

Suppose a loan of \text{\}10,000$is repaid monthly over three years at an annual interest rate of$6%$. Enter the following TVM variables:

Solving for $PMT$ gives $PMT=-304.219\ldots$, so the monthly repayment is \text{\}304.22$. The negative sign represents money paid out; it does not mean the repayment amount is negative.

Spreadsheet method

The payment can be found using =PMT(6%/12,36,10000). The spreadsheet then applies the monthly rate $r=0.06/12=0.005$ to each opening balance:

$$\text{interest}=\text{opening balance}\times r$$ $$\text{principal repaid}=\text{payment}-\text{interest}$$ $$\text{closing balance}=\text{opening balance}-\text{principal repaid}$$

For the first month, the interest is 10\,000(0.005)=\text{\}50.00$. Therefore, the principal repaid is$304.22-50.00=\text{$}254.22$,l$\text{$}9745.78$. Copy these formulas down for all 36 payments.

In month two, calculate interest from the new balance, not the original loan: 9745.78(0.005)=\text{\}48.73$. The principal component is therefore$304.22-48.73=\text{$}255.49$$\text{$}9490.29$. This shows how the schedule changes over time.

A typical spreadsheet uses columns for payment number, opening balance, payment, interest, principal repaid, and closing balance. Link each row's opening balance to the previous row's closing balance. Do not type rounded balances repeatedly; retain the spreadsheet's full precision and format cells to display two decimal places. The final payment may differ slightly because of rounding.

A common misconception is that every payment reduces the principal by the same amount. In fact, the payment is constant, but the interest decreases and the principal component increases as the balance falls.

Exam technique

Show the TVM inputs or spreadsheet formulas before giving the answer. Keep full calculator precision until the end, give money to two decimal places, and check the sign convention, payment frequency, and END mode.