Understanding the Mathematics of Personal Finance

MY WEBSITE SPREADSHEET

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.