Building an Amortization Schedule in a Spreadsheet

From Loan Model to Spreadsheet

An amortization schedule turns the loan’s payment rule into a row-by-row record. For each payment period, the schedule shows the balance before payment, interest charged, payment made, principal repaid, and balance after payment. A spreadsheet is useful because one correctly written row can be copied to generate the remaining periods.

The setup here assumes a fixed interest rate per payment period, equal payments at the end of each period, and a fully amortizing loan. The rate must be expressed for the same period as the payments. Let $L$ be the original loan principal, $i$ the effective rate per payment period, $n$ the number of payments, and $R$ the level payment. Balances and payments are calculated without intermediate rounding unless the problem explicitly requires rounding.

Definition: In period $k$, the opening balance is the amount owed immediately before interest accrues for that period. The interest charge is the opening balance multiplied by the period rate. The payment is divided into interest and principal: principal repaid equals payment minus interest. The closing balance is the opening balance minus principal repaid, and becomes the next period’s opening balance.

A useful spreadsheet layout separates the loan inputs from the schedule. For example, place the principal in cell D2, the period rate in D3, the number of payments in D4, and the level payment in D5. Use columns A through F for period number, opening balance, interest, payment, principal, and closing balance. Row 10 can hold the first payment period. Keeping inputs in fixed cells makes formulas easier to inspect and copy.

Payment Formula and Spreadsheet Recurrences

For a loan paid at the end of each period, the present value of the $n$ payments must equal the original principal. Thus $L=R a_{\overline{n}|}$, where $a_{\overline{n}|}$ is evaluated at the period rate $i$. For $i\ne0$, the payment is $R=L/a_{\overline{n}|}$. In a spreadsheet, D5 can therefore contain `=D2*(D3/(1-(1+D3)^(-D4)))`. An equivalent spreadsheet payment function may use a negative present value to return a positive payment, depending on the program’s cash-flow sign convention.

The schedule formulas in the first data row are:

A10: `=1`
B10: `=$D$2`
C10: `=B10*$D$3`
D10: `=$D$5`
E10: `=D10-C10`
F10: `=B10-E10`

For the second row, enter A11 as `=A10+1` and B11 as `=F10`. Use `=B11*$D$3` for C11, `=$D$5` for D11, `=D11-C11` for E11, and `=B11-E11` for F11. Copy the second row down through the final payment period. The dollar signs in references such as `$D$3` keep the input cell fixed when formulas are copied; references such as B11 and F10 change with the row.

Why the recurrence works: If $B_k$ is the opening balance in period $k$, then interest is $iB_k$. After the payment $R$, the next opening balance is $B_{k+1}=B_k+iB_k-R=B_k(1+i)-R$. Iterating this recurrence gives $B_{n+1}=L(1+i)^n-Rs_{\overline{n}|}$. A fully amortizing loan has $B_{n+1}=0$, so $R=L(1+i)^n/s_{\overline{n}|}=L/a_{\overline{n}|}$. This is the same payment obtained by equating the present value of the payments to $L$.

If the periodic rate is zero, the displayed payment formula has a zero denominator and should not be used in that form. Under the same level-payment assumptions, the payment at zero interest is $R=L/n$. For a positive rate, a schedule row should satisfy three linked relationships: interest equals opening balance times the period rate; principal equals payment minus interest; and closing balance equals opening balance minus principal.

Worked Schedules

Worked Example: Three Payments at a Fixed Period Rate

A hypothetical equipment loan has principal $1,000, a rate of 10% per half-year, and three end-of-half-year payments. Find the level payment and the spreadsheet values for all three rows. Use full precision in the calculations and report schedule amounts to four decimal places.

Method. Enter $L=1{,}000$, $i=0.10$, and $n=3$. The payment is $R=L/a_{\overline{3}|}$, so $a_{\overline{3}|}=(1-(1.10)^{-3})/0.10=2.486851991$. Therefore $R=1{,}000/2.486851991=402.1148$, rounded to four decimal places.

The first row starts with an opening balance of $1,000.0000. Interest is $1{,}000(0.10)=100.0000$; principal is $402.1148-100.0000=302.1148$; and the closing balance is $1{,}000.0000-302.1148=697.8852$.

In period 2, opening balance is the previous closing balance, $697.8852. Interest is $697.8852(0.10)=69.7885$; principal is $402.1148-69.7885=332.3263$; and closing balance is $697.8852-332.3263=365.5589$.

In period 3, interest is $365.5589(0.10)=36.5559$; principal is $402.1148-36.5559=365.5589$; and the closing balance is approximately $0.0000. The schedule is consistent: each row uses the preceding closing balance as its opening balance, and the final balance is zero apart from displayed rounding.

Common mistake avoided: the 10% rate is a rate per half-year, not an annual rate to be divided again. The spreadsheet’s rate input must match the payment period.

Worked Example: Copying Formulas for a Quarterly Loan

A hypothetical borrower receives a loan that will be repaid with four end-of-quarter payments of $300. The interest rate is 5% per quarter. Find the original principal and construct the schedule. Report amounts to four decimal places.

Method. Here $i=0.05$, $n=4$, and $R=300$. Since the payments are level, the original principal is their present value: $a_{\overline{4}|}=(1-(1.05)^{-4})/0.05=3.545950504$. Thus $L=300(3.545950504)=1{,}063.7852$, rounded to four decimal places. Put this amount in D2, 0.05 in D3, 4 in D4, and 300 in D5; fill the schedule formulas down four rows.

Period 1 interest is $1{,}063.7852(0.05)=53.1893$. Principal is $300-53.1893=246.8107$, leaving a closing balance of $1{,}063.7852-246.8107=816.9744$.

Period 2 opening balance is $816.9744. Interest is $816.9744(0.05)=40.8487$, principal is $300-40.8487=259.1513$, and closing balance is $816.9744-259.1513=557.8231$.

Period 3 opening balance is $557.8231. Interest is $557.8231(0.05)=27.8912$, principal is $300-27.8912=272.1088$, and closing balance is $557.8231-272.1088=285.7143$.

Period 4 interest is $285.7143(0.05)=14.2857$, principal is $300-14.2857=285.7143$, and closing balance is approximately $0.0000. The copied payment formula remains fixed at $300 because it refers to the absolute input cell `$D$5`; the balance and interest formulas change row by row.

Common mistake avoided: copying a formula such as `=D10` for the payment would cause the reference to move to a different row, rather than keep the scheduled payment fixed. Use an absolute input reference or an explicit fixed payment value.

Worked Example: Converting an Effective Annual Rate Before Building the Schedule

A hypothetical $1,000 loan has an effective annual rate of 21% and is repaid with two equal end-of-year-half payments, one every six months. Find the period rate, payment, and both schedule rows. Report amounts to four decimal places.

Method. The half-year rate $i$ must satisfy $(1+i)^2=1.21$, because two half-year accumulation periods make one year. Hence $i=\sqrt{1.21}-1=0.10$. Enter 0.10 as the spreadsheet period rate, not 0.21.

The payment is $R=1{,}000/a_{\overline{2}|}$, where $a_{\overline{2}|}=(1-(1.10)^{-2})/0.10=1.735537190$. Therefore $R=576.1905$ to four decimal places. In period 1, interest is $1{,}000(0.10)=100.0000$, principal is $576.1905-100.0000=476.1905$, and closing balance is $1{,}000.0000-476.1905=523.8095$.

In period 2, interest is $523.8095(0.10)=52.3810$. Principal is $576.1905-52.3810=523.8095$, leaving a closing balance of approximately $0.0000. The rate conversion and schedule agree with the original annual accumulation: $1.10^2=1.21$.

Common mistake avoided: using 21% directly as the half-year rate would apply the annual rate twice in one year and would not match the stated effective annual rate.

Checks and Common Spreadsheet Errors

A spreadsheet can produce plausible-looking numbers even when a reference or rate has been entered incorrectly. Check the model assumptions and the recurrence, not just whether a row contains numbers. The following checks are useful before relying on a copied schedule:

Period alignment: confirm that the rate is effective per payment period. Convert an annual rate when the payments are more frequent than annual.
Opening balance link: after the first row, each opening balance should equal the preceding closing balance.
Component identity: payment must equal interest plus principal in every period.
Balance identity: closing balance must equal opening balance minus principal repaid.
Final row: a fully amortizing schedule should finish at zero, subject only to displayed rounding or an explicitly specified final-payment adjustment.

A frequent copied-formula error is to calculate the next opening balance from the previous opening balance instead of the previous closing balance. For example, the incorrect formula `=B10` in B11 repeats period 1’s opening balance. The correct formula is `=F10`, which carries forward the amount remaining after principal has been repaid. Another common error is to use `=B10-D10` for the closing balance. That subtracts the entire payment, including the interest portion; the correct calculation is `=B10-E10`, subtracting principal only.

Spreadsheets may store more decimal places than they display. Unless the loan terms require payments rounded to cents each period, keep full precision in the formulas and format cells to show the requested number of decimals. Rounding every intermediate balance can accumulate error and leave a small nonzero final balance. In an examination calculation, distinguish a genuine formula error from a small residual caused by rounding.

Key takeaway: A copied amortization schedule is built from one payment calculation and two linked recurrences: interest is opening balance times the period rate, and next opening balance is current opening balance plus interest minus payment. Correct period units and spreadsheet references are essential to both.

Check Your Understanding

For each problem, assume a fixed rate per payment period, end-of-period level payments, and no intermediate rounding unless stated.

A spreadsheet has principal $5,000, a period rate of 1%, and a level payment of $1,200. What should the first row show for interest, principal repaid, and closing balance? (Answer: Interest $50.00; principal $1,150.00; closing balance $3,850.00.)
A $1,000 loan is repaid in three periods at 10% per period. The payment is $1{,}000/a_{\overline{3}|}$. What is the first period’s closing balance, rounded to four decimal places? (Answer: $697.8852.)
A loan has a nominal annual rate of 8% convertible quarterly. What period rate belongs in the spreadsheet, and what is the interest in the first quarter on an opening balance of $2,500? (Answer: Period rate 2%; first-quarter interest $50.00.)
In a schedule row, opening balance is $4,800, the period rate is 1.5%, and payment is $900. Calculate interest, principal repaid, and closing balance. (Answer: Interest $72.00; principal $828.00; closing balance $3,972.00.)
A loan’s effective annual rate is 9%, with semiannual payments. Write the equation for the semiannual rate $i$, then give its value to four decimal places. (Answer: $(1+i)^2=1.09$, so $i=\sqrt{1.09}-1=0.0440$, or 4.40% per half-year.)

What You'll Learn