The spreadsheet Ch3Amortization. xls, Loan tab, produces an amortization table for a loan with regular monthly payments.

Table 3.7 is a snapshot of a part of this spreadsheet. This table distorts the spreadsheet slightly in that because the spreadsheet is too wide to fit on a printed page, I’ve taken the input data—the data to the left of the green line in the spread – sheet—and put it on top in the table.

The input variables for these calculations are

Start Month the month when you take the loan; a number between 1 and 12;

Start Year the year when you take the loan;

Nr Mnthly Pmts if the loan is described in years, this is 12 (number of years);

Principal the amount you’re borrowing;

Rate the annual percentage rate (APR).

The actual date of the loan is not shown. The output columns are

a running count of the payments. Zero is the start date; the month and year of each payment; this is the balance after the current payment; the monthly payment;

the interest accrued between last month and this month; a running total of the interest payments in each calendar year.

Table 3.7 Snapshot of the Loan Tab of Spreadsheet Ch3Amortization. xls

 Start Month: 7 Start Year: 2005 Nr Mnthly Pmts 180 Principal \$350,000 Rate 6.94%

 Pmt Nr Mnth Year Balance (\$) Payment (\$) Interest (\$) Tot Int/Year (\$) 0 7 2005 350,000.00 0.00 0.00 0.00 1 8 2005 348,890.00 3,134.17 2,024.17 2,024.17 2 9 2005 347,773.57 3,134.17 2,017.75 4,041.91 3 10 2005 346,650.69 3,134.17 2,011.29 6,053.20 4 11 2005 345,521.32 3,134.17 2,004.80 8,058.00 5 12 2005 344,385.42 3,134.17 1,998.26 10,056.27 6 1 2006 343,242.94 3,134.17 1,991.70 1,991.70 7 2 2006 342,093.86 3,134.17 1,985.09 3,976.78 8 3 2006 340,938.13 3,134.17 1,978.44 5,955.23 9 4 2006 339,775.72 3,134.17 1,971.76 7,926.99 10 5 2006 338,606.59 3,134.17 1,965.04 9,892.02 11 6 2006 337,430.69 3,134.17 1,958.27 11,850.30 12 7 2006 336,248.00 3,134.17 1,951.47 13,801.77 13 8 2006 335,058.46 3,134.17 1,944.63 15,746.41 14 9 2006 333,862.04 3,134.17 1,937.75 17,684.16 15 10 2006 332,658.71 3,134.17 1,930.84 19,615.00 16 11 2006 331,448.42 3,134.17 1,923.88 21,538.87 17 12 2006 330,231.12 3,134.17 1,916.88 23,455.75 18 1 2007 329,006.79 3,134.17 1,909.84 1,909.84 19 2 2007 327,775.38 3,134.17 1,902.76 3,812.59 20 3 2007 326,536.84 3,134.17 1,895.63 5,708.23 178 5 2020 6,214.38 3,134.17 53.75 444.89 179 6 2020 3,116.15 3,134.17 35.94 480.83 180 7 2020 0.00 3,134.17 18.02 498.85