Understanding the Mathematics of Personal Finance
SOME PROGRAMMING NOTES
When you enter a number of payments in one of these spreadsheets, the sheet shows entries from Pmt Nr 0 or 1, as appropriate for the topic at hand, up to the number of payments entered. The actual spreadsheet, however, has been programmed down to row 1,000. If you click on a blank cell in the Payment column of the spreadsheets I used above, you’ll see the same formula as in the cells with visible data. If you need to recover the formula in the payment column (or any other column), you can easily do it by copying one of these “blank” cells up into the required cells above it.
The sheets’ ability to stop showing anything after the specified number of payments has been reached is handled by the “If’ statements that are cluttering up all the cell formulas. The Pmt Nr column formula is simple; add 1 to the previous cell (the cell above) and check to see if the result is larger than the Nr Mnthly Pmts. If it isn’t larger, show the result. If it is larger, don’t show anything.
Every other cell to the right of the Pmt Nr column looks left to the Pmt Nr cell. If the Pmt Nr cell is showing a number, perform the specified calculation and show it. If the Pmt Nr cell is not showing anything, don’t show anything.
If you want to print your results using these spreadsheets, you have to do a little extra work. Spreadsheet programs automatically check to see which cells have entries and then designate a print area to include these cells. What you need to do
is to reset the print area specifically to show only the region of the sheet where you have useful information. This is very easy to do—but you will have to find out how to do it with your particular spreadsheet program. If you don’t do it, you’ll print not only the material you want but also the many blank pages representing all the rows down to row 1,000.
I’ve set the formatting of the cells in these spreadsheets so that percentages are shown to two decimal places. Amounts of money are usually shown to the penny except where this level of resolution probably isn’t necessary. For example, in Figures 15.1 and 15.2, you’ ll see balances, payments, and interest shown to the penny but the principal is only shown to the nearest dollar. This can all be changed with the formatting available in the spreadsheet program. Remember that the spreadsheet is internally carrying all numbers to a high level of resolution and just rounding this internal resolution to the presentation resolution you’ve specified. The accuracy of your results does not depend on your format. Suppose you bought three items that each cost $33.33. Your total cost is exactly $99.99. If, however, you only display numbers to the nearest dollar, on the spreadsheet it would look as if you were adding up three $33 dollar items and getting $100.
Lastly, I plan to upgrade the online spreadsheets and add capabilities regularly. If I find a bug or one is reported to me, I’ll list the bug on the Errata page and correct the spreadsheet. All corrections and upgrades will be documented on my website.
