Understanding the Mathematics of Personal Finance

Spreadsheet Calculators

15.1 INTRODUCTION TO THE SPREADSHEETS

Two approaches to providing readers with computer-based calculators are employed in this book. One approach is to provide links to the many online calculators that provide slick interfaces and useful tutorials. A second approach is to create spread­sheets that, while not quite as elegant looking as the online calculators, allow the user to examine the calculations, modify and/or add features if desired, make avail­able some intermediate or extended results, generate reports, and so on. Also, by creating a set of custom spreadsheets tied to this book, I can provide a consistent interface among all of them.

An advantage of the online calculators for me is that I don’t have to create them; I just find them and then provide links at the appropriate places in the book. The disadvantage is that I cannot guarantee that these links will be supported and will be there when you want them. I’ve tried to play the odds by providing several links for every calculator. If they’ve all disappeared when you want them, you’ll have to go searching for new ones.

In order to give readers access to my spreadsheets, I am posting them on my website (www. lawrencedworsky. com). All you have to do is to click on the desired spreadsheet link and there it is. You will need a spreadsheet program on your com­puter that can read Microsoft Office® Excel® spreadsheets.1 The Excel format is as universal a format as you can find; almost every modern spreadsheet program will read it. When you click on one of these spreadsheets, you will be given the choice of opening it with your spreadsheet program or saving it to some location on your hard disk. If you choose to open it with your spreadsheet program, you can then save it to your hard disk at a later time. In either case, I recommend saving my original spreadsheet to your hard disk and then working with a copy of it. Also, you

1 I created these spreadsheets using the 1997-2003 Excel file format (.xls). This format will work on any version of Excel sold in the past 12 years or so, including the current version, as well as most other spreadsheet programs.

Understanding the Mathematics of Personal Finance: An Introduction to Financial Literacy, by

Lawrence N. Dworsky

Copyright © 2009 John Wiley & Sons, Inc.

should check back with the website occasionally to see if there have been any updates to any of these spreadsheets.

In order to use these spreadsheets, you must have a basic familiarity with how to use a modern spreadsheet. Your ability to perform basic operations such as scroll­ing, entering data, cutting or copying and pasting, printing a sheet or a part of a sheet, generating a simple formula in a cell, and so on is required. Since I don’t know which spreadsheet program you’ll be using, it’s not a good idea for me to try to provide a primer. There are very many good introductory books on spreadsheet use available.

I have deliberately not protected or locked any of the cells in these spreadsheets. This means that these spreadsheets can be modified to any extent, including to the extent of corrupting or deleting the calculations. In other words, make sure that you haven’t changed something and then forgotten about your change. You can always download the original spreadsheet again if you have to; you can’t modify the copy stored on my website. I recommend saving any modified versions under new names so you can reuse the original without losing your changes.

In many cases, I have taken somewhat circuitous approaches to writing these spreadsheets rather than using the many elegant and concise formulas available in the spreadsheet programs themselves. The results would be the same whichever way I had done it, but by actually working through the calculations rather than using the formulas, I can tie the spreadsheet calculations to the text materials and then you can follow what’s happening as closely as you want.

Most spreadsheets have excellent graphic capabilities. I recommend that you familiarize yourself with these capabilities as you use the spreadsheets. Get into the habit of graphing results as often as you can. When you’re doing comparisons, try to save intermediate results to a blank spreadsheet and then graph the comparisons. Once you develop the skill to create graphs quickly, you’ll really come to appreciate the power of “seeing things” that spreadsheets bring to you.

Lastly, since you are free to modify these spreadsheets as you wish and I of course have no idea just what modifications you might come up with, I don’t error- check input data. For example, when the spreadsheet asks for a month, you should enter a number between 1 and 12. Anything else will cause generally unpredictable and almost definitely useless results. On the other hand, you can’t damage either the hardware or the software by entering nonsense. Don’t be afraid to try things—it’s the best way to learn.

If you don’t have a spreadsheet program and would like a free one, I recommend the following packages. These packages are all full office suites, giving you a word processor, a presentation program, possibly some other programs, and of course a spreadsheet:

1. Open Office (www. openoffice. org for the download);

2. IBM Lotus Symphony (http://symphony. lotus. com/software/lotus/ symphony/home. nsf/products for the download);

3. Google Docs (www. google. com); choose “documents” from the “more” tab.

The first two of these are conventional software packages in that you download the package and install it on your computer, pretty much the same as if you had bought the software on a disk. The last is an online software—everything is stored online. Software has recently become available to let you take your work offline.

All of these spreadsheets have a similar “look and feel.” Figure 15.1 is a picture of the worksheet portion of the spreadsheet that accompanies Chapter 3. Its file name is Ch3Amortization. xls. Don’t worry about what the calculations actually mean until you’re reading Chapter 3 . My purpose here is just to show you how to get around the spreadsheet. Different spreadsheet programs might look a bit different from this, particularly outside the actual work area.

If you can download this spreadsheet and open it in your spreadsheet program, I recommend doing so now. If not, look at the figures of this chapter but keep in mind that when you do open this spreadsheet with your software, it might look a little different.

Near the bottom of the spreadsheet, on the left, you’ll see the terms Loan, Save, and Loan V2. Each of these terms is located in a tab. These tabs are actually separate spreadsheets that are all relevant to the chapter and that I created and saved as a single file.

On the left side of the sheet is a vertical green line. If you scroll down, you’ll see that this line extends all the way down to row 1,000. I’ve set up all of the sheets to automatically handle situations with up to 1,000 line items. For example, if you are studying a loan with monthly payments, 1,000 line items is about 83 years. I think is a big enough capability. You can easily extend this if necessary.

To the left of the green line is a set of labels (first column) and numbers accom­panying these labels (second column). I refer to the area to the left of the green line as the input area. All, or in some cases that I’ll get into later, almost all, of the information needed to set up your problem is entered in the input area. The input area formats all information properly. For example, if you enter 0.0773 for the Rate, when you hit Enter, the number you just typed changes from 0.0773 to 7.73%.

Let me repeat that there is no error checking in these spreadsheets. If you enter 23 for the Start Month and -6 for the Number of Monthly Payments (Nr Mnthly Pmts), I can’t predict what will happen, but I’m pretty sure that it will be worthless.

As long as you’re only entering numbers in the input area, no permanent changes are being made to the structure of the spreadsheet. Because of this, you can change these numbers to your heart ’ i content. When you repeat a set of input numbers, you should expect the exact same output numbers to appear.

Try changing the Nr Mnthly Pmts to anything from 2 to 1,000. Not only will all the numbers on the sheet adjust, but the sheet will start at Payment Number (Pmt Nr) 0 (just to the right of the green line) and terminate at the number you just entered. You don’t have to worry about how I did this until you want to print your spread­sheet. I’ll discuss this below.

To the right of the green line is the area that’s used for output and also for capturing some inputs that can’t be easily summarized on the left of the green line.

The first three columns to the right of the green line, in this spreadsheet, are the Payment Number (Pmt Nr), the Month (Mnth), and the Year. This particular spread­sheet is used for studying loans such as mortgage loans. The first month and year shown are the month and year that you took the loan. Since the first payment is due 1 month later, I refer to the day you took the loan as Pmt Nr 0.

Every column to the right of the Pmt Nr column refers to the Pmt Nr column, as is standard for tables. For example, after Pmt Nr 12, which takes place in May of 2009, the balance is $22,535.97, the payment was $3,844.12, the interest paid was $174.40, and the total interest paid in 2009 was $1,114.92.

Note that the payment is the same for all 18 payments (this is the default situ­ation in a fixed interest, fixed payment mortgage) and that this payment amount was supplied by the spreadsheet.

The balance column entries get smaller each month, ending up at 0 after the eighteenth payment is made. This is exactly what the payment amount was calcu­lated to accomplish.

The total interest per year column is for tax reporting purposes, used when you have a loan tax deductible interest. This number starts low each January and grows through December. If what’s happening isn’l clear to you, change the Nr Mnthly Pmts to, say, 60 (for a 5-year loan) and look at the total interest per year column again.

If you put the cursor in one of the Payment cells other than the top one and look just above the actual work area columns, you’ll see a pretty messy-looking expres­sion, for example,

=IF(D14<>"",-PMT($B$8/12,nr-D14+1,H13+J14,0,1),"").

This expression is doing the work of calculating the monthly payment. Don’t worry about the IF part; I’ll get to that below. The actual calculating engine is the internal spreadsheet function:

PMT($B$8/12,nr-D14+1,H13+J14,0,1).

If you don’t understand what’s happening here, I recommend a book on spread­sheet writing or the internal Help system of your spreadsheet program. I use this same internal function every time I want to calculate the month’s payment, based on the interest rate, the number of payments to go, the current balance, and this month’s interest.

It would have been simpler to calculate the regular payment just once and then just copy this number down the column. The reason I did things the hard way is that this approach makes the spreadsheet itself dynamic in that if one of the payments is changed manually, the spreadsheet corrects itself automatically.

For example, suppose I make a $10,000 payment for Pmt Nr 9. If you have the spreadsheet running, try just entering $10,000 into the appropriate payment cell. Your spreadsheet should now look like Figure 15.2. If you don’t have the spreadsheet running, just look at Figure 15.2.

The spreadsheet has recalculated a new regular payment to bring your balance to 0 after 18 months, has recalculated the correct balance after your payment, and so on. My purpose here is not to repeat the explanations of Chapter 3 but to discuss

about the spreadsheet operation, so I won’t discuss the meanings of these changes to your loan.

What if I want to examine the results of some other changes? For example, what if I want to look at a loan with a different principal, or what if I want to move this big payment from Pmt Nr 9 to Pmt Nr 7? If I just put in these new changes, suddenly I have a mess on my hands. The Pmt Nr 9 cell no longer has its original formula; it’s frozen at $10,000 regardless of whether or not this number makes sense together with the new numbers. Also, every result on the sheet below the $10,000 entry is suspect because the rest of the spreadsheet doesn’t “know” that this number might be bogus.

Once something has been changed to the right of the green line, the spreadsheet has to be reinitialized before it is used again. This can be done in one of three ways:

1. Close the spreadsheet (save it to another name first if you want to keep it). Then reload the original spreadsheet either from my website or from a copy you’ve made of the original spreadsheet. This is the safest way of ensuring that you’ve got all the original formulas back. It can be annoying, however, because you have to reenter all of your information to the left of the green line.

2. Every time you press the key combination Ctrl-z (hold the control key down and press the z key), the last entry you made in the spreadsheet is undone. Equivalently, your spreadsheet program might have Undo and Redo icons on the page somewhere. Just keep track of how many times you Undo (and/ or Redo) and examine everything carefully to make sure you didn’t undo one change too many or too few.

3. Go to another cell on the spreadsheet that has the same formula (in the above example another of the Payment cells) and type Ctrl-C (copy the formula). Then go to the cell that has the $10,000 entry and type Ctrl-V (paste the formula). This works fine, but you have to keep careful track of what you’re copying from where and to where. This approach is most useful when you have to undo a range of changes, for example, if you changed almost every Payment in the spreadsheet one at a time and you want to undo all of this at once. This will even work if you’ve changed every Payment from number 1 to number 18—see the programming notes below.

The different tabs in a spreadsheet will bring up different sheets that are related by the chapter in the book that this spreadsheet “belongs to.” The tab system has the ability of tying information from one sheet to another in a given spreadsheet file, but I’m not using this capability. I’m only using the tab capability to help me orga­nize the spreadsheets to match the chapters in the book. Each tab represents (and could have been written as) a completely independent spreadsheet file. I did this in anticipation of user modifications of the spreadsheets; by keeping each spreadsheet independent of all other spreadsheets, there is no chance of inadvertent changes to a spreadsheet that you’re not looking at.

6

7

8

9

6 11 12

13

14

15

16

17

18

19

13 21 22

23

24

25

26

The Basic tab of the spreadsheet Ch4Mortgages. xls is identical to the Basic tab of the spreadsheet Ch3Amortization. xls. Paying off a fixed rate, fixed payment mortgage is identically a loan amortization.

Figure 15.3 shows part of the spreadsheet on the ARM tab of the Ch4Mortgages. xls. This spreadsheet is the same as the Basic tab spreadsheet except that the Rate variable has disappeared from the left side of the green line and has appeared as a column to the right of the green line.

ARM stands for adjustable rate mortgage. This spreadsheet has been prepared to accommodate interest rates that may change once or even many times over the course of the mortgage. To use this spreadsheet, you first enter all the required information to the left of the green line, then you enter the initial interest rate in the top cell of the Rate column. The number you enter automatically gets copied down to the bottom of the sheet (6.18% in Figure 15.4).

Suppose that the interest changes to 7.00% after a year (I don ’ t think this is a legal change for mortgages in most states, but I’m doing it just to keep my example contained in one page). “After 1 year” in this example is January of 2008, or equivalently at Pmt Nr 12. What you need to do is to enter 7% (0.07) into the Rate cell at Pmt Nr 12. The sheet automatically changes all the rates from Pmt Nr 12 down to the bottom of the sheet to 7%. At Pmt Nr 16, change the rate again, this time down to 6.50%. Enter 6.50% at the appropriate cell and all the rates from Pmt Nr 16 down to the bottom of the sheet change to this latest rate.

Figure 15.4 is part of the ARM spreadsheet showing these changes. The balance, payment, and so on columns are all automatically correctly updated. In Figure 15.4,

I have also changed Pmt Nr 14 to $10,000 just to show that this type of change will also be correctly handled by the spreadsheet.