Introduction.  By the time you reach college it is likely that you have obtained at least one loan.  This may have occurred either to contribute to paying college expenses or buying a car among other things.  This sort of situation is very appropriate for reviewing many of the important fundamental features of a spreadsheet program such as Excel.

While I am not able to be completely authoritative about the history of spreadsheets, it seems that VisiCalc was the first spreadsheet program for a microcomputer.  It was developed by Dan Bricklin and Bob Frankston in 1978 for Apple II computers.  It used a row and column based layout and formula syntax that are stilled used today.  This evolved through many forms for a variety of platforms.

The appearance of Lotus 1-2-3 seems to have helped convince businesses that the IBM-PC was truly a productivity enhancing tool.  When Microsoft released Windows 3.0 in 1990, Lotus was slow to get on the bandwagon and didn't quickly develop for the windows user interface.  This gave a large opening for Microsoft and they developed Excel.  Eventually all the spreadsheet tools migrated to work in a Windows environment along with the Mac-OS.  Excel remained one of the major movers.

Essentially, a spreadsheet is based on a matrix or array of cells which can hold numbers, text or formulas.  One of the most important features of a spreadsheet is the ability to recalculate results as you change the contents of other cells.  We will see this feature, along with many others, put to extensive use this semester.

Determining Mortgage Payments.  You should open your version of Microsoft Excel and you should see a screen that looks a lot like the following.

Notice the cells and the coordinates of each cell using letters for columns and numbers for rows.

Now you want to start by typing some text into the cells.  We will modify the formats of most of these cells later.

1. Type A Comparison of Mortgage Payments in cell A1

2. Type Interest Rate vs. Mortgage Amount in cell A2

3. Now you want to type the following table of interest rates in the column A4 ... A20.  Interest amounts are usually given in increments of 1/8.

.095

4. Now you want to enter the following mortgage amounts across the row B3 ... J3.

Before proceeding we will format many of the items in the spreadsheet.

Some Basic Cell Formatting.  It is always worthwhile to make your spreadsheets more readable.  You never know who else will see them or whether you will put them down for a while and have to come back to them.  You don't want them or you to get lost!

The first thing we want to do is put in some sort of legends for the interest rates and the mortgage amounts.  This will involve inserting a row and a column and then typing some information into particular cells.

1. Click on any cell in column A.

2. Click on the Insert menu.

3. Click on the Columns item.

This will insert a new column A.

4. Now you want to right click on the column header A to get the pop-up menu.
5. Select the Column Width item in the menu.
6. In the little dialog window that appears select the pixel width as 25 and click OK.

This should give us sufficient column width for later use.

7. Now you want to type Interest Rates in cell A12 and right justify the entry.
8. Change the font size to 12 and make the letters bold.

Now we will put in a legend on the mortgage amounts.

9. Click on any cell in row 3.

10. Click on the Insert menu.

11. Click on the Rows item.

12. In the cell B3 type Mortgage Amount in Dollars.

13. Select the cells B1 ... K3.

14. Click on the Format menu.

15. Select the Cells item.

16. Click on the Alignment tab.

17. In the Horizontal combo box select Center Across Selection.

18. While you have them selected you should also make all of these text entries bold.

19. Select cells B1 ... K1 and change the font size to 16

20. Select cells B2 ... K2 and change the font size to 14

21. Select cells B3 ... K3 and change the font size to 12

Now we want to format the percentages and dollar amounts so that they are more readable.  First the percentages,

21. Select the column of interest rates, B5 ... B21
22. Click on the Format menu.
23. Select the Cells item.
24. Make sure the Number tab is selected.
25. Select the Percentage entry and set the number of decimal places to 3.

Now for the dollar amounts,

26. Select the row of mortgage amounts, C4 ... K4.
27. Click on the Format menu
28. Select the Cells item
29. Make sure the Number tab is selected
30. Select the Number entry and set the number of decimal places to 0.
31. Click on the Use the 1000 Separator (,) checkbox

I don't think there is any need to include a dollar sign unless you really would like it.  Now your spreadsheet should look like the following.

Before you go any further it is definitely time to save your spreadsheet.  Make sure you have a directory/folder for this course.  You may want to name it OOPSSVB.  Then within this folder you want to have a subdirectory/folder called Mortgages.  Click on the save button and save this spreadsheet as MortgageBasics in this Mortgages subfolder.  It is extremely important that you organize your examples and homework effectively during this course.  If you scatter things around it is difficult to find them and to know what is your most current version.

Using Functions.  Now we need to fill in the cells within the payments table.  Since mortgages are almost always paid using monthly payments this is how we will set this comparison up.

I'm quite confident that no one in this room knows the formula for computing these payments off the top of their head.  Some of you might not even be able to find it in a book.  Computing such values is one of the many advantages of using a spreadsheet since there is a built in function to do the computation.  The function is the following.

=PMT(rate,nper,pv,fv,type)

The meaning of the entries in the function are in the following table.

These entries imply a few things that must be done.  Assuming monthly payments

• we need to divide the interest rate by 12 to get rate.

• we need to multiply the number of years that it will take to pay off the loan by 12 to get nper.  This also implies we need to have the user input the duration of the loan in a particular cell on the spreadsheet.  While this is usually 30, it is best to design for greater flexibility and clarity of assumptions.

So we need a cell to contain the duration of the loan.

32. Type Loan Duration (YRS) in cell A23.

33. Bold, and right justify the entry.  Change the font size to 12.

34. You should type 30 in cell B23.

Now we are ready to fill in the payment cells.  Fortunately, Excel has some additional help features to make this easier.

35. Click on cell C5.

36. Type in an = sign.  As soon as you do a new button should appear at the left end of the formula bar.  It will look like the following.

37. You should click on the downward pointed triangle to open the menu and then select More Functions. . . .  You will get a dialogue window/form like the following.

38. Select Financial in the Function category: scrolling text box.

39. Select PMT in the Function name: scrolling text box and click OK.  You should get a new dialogue form like the following.

40. Now you want to fill in the text boxes after moving the dialogue to make sure you can see the needed cells.

41. When the Rate text box has the cursor, click on the B5 cell to get the interest rate and type /12 to get B5/12 in the entry..

42. Move the cursor to the Nper text box and type 12* then click on the B23 cell.

43. Move the cursor to the Pv text box and click on the C4 cell.

44. You can leave the Fv and Type text boxes blank since they will default to the desired values.  When done your dialogue should look like the following.

45. Now you can click on the OK command button.   Now you should see -839.

46. Now you want to format the entries C5 ... K21 to be currency with two digits after the decimal and parentheses for negative amounts.  I'll leave the steps to you.

47. Now you should see ($839.06) in C5

This is telling you that if you get a 30 year mortgage at 7.5% on $120,000 your monthly payments will be $839.06.  It is red and in parentheses because it is outgoing money.

Copying Cell Formulas.  Now that we have gone through all of these steps to develop this cell formula it would be nice if there was some shortcut for filling in the other cells.  Fortunately, the people that developed spreadsheets had similar motivation.

If you click on the C5 cell you will notice the following formula in the formula bar.

=PMT(B5/12,12*B23,C4)

Make sure to keep your eye on the cell values as we continue to work.  Just for the hyuks, let's copy this formula into the D5 cell and then into the C6 cell.  

The D5 cell formula becomes

=PMT(C5/12,12*C23,D4)

The C6 cell formula becomes

=PMT(B6/12,12*B24,C5)

Notice how when we copy the formula from C5 one cell to the right into D5 

• the B5 changes into C5,

• the B23 changes into C23 and 

• the C4 changes into D4.

In each case the letter also "shifts one to the right" and appropriately becomes C or D.  This is due to what is called relative addressing.  This is often very convenient.

On the other hand notice that when we copy from C5 one cell down to C6 the letters don't change because  we stay in the same column, but 

• B5 becomes B6,

• B23 becomes B24 and

• the C4 becomes C5.

In this case the numbers shift down one.

The other thing you should notice on your spreadsheet is that you get an error message #DIV/0!.  This is because the function is trying to use some argument value that needs to be non-zero.

If you change both row and column, both the letters and numbers will change concomitantly.

But how can we prevent these adjustments when we want and make desired coordinates not change as we copy?  For example, the duration of the loan is always contained in cell B23.  We don't want that to change when we copy to other locations.  To do this we need to use absolute addressing and put a $ sign in front of each coordinate we don't want to change when we copy.

Thus if we are going to copy C5 across the row we want to change the formula to

=PMT($B5/12,12*$B$23,C4)

so that the percentage rate B5 and the loan duration B15 won't change as we copy across.  We want C4 to change to D4 ... K4 as we move across so we don't lock the number and since we are staying in the row we don't have to lock the 4 with a $.

If we also want to copy down the column we need to modify the formula to

=PMT($B5/12,12*$B$23,C$4)

So that we can copy either across or down.  After having done this you should get a spreadsheet like the following.

Just for the sake of seeing how little the payments increase if you halve the loan duration to 15 YRS you should enter 15 in cell B23 and hit enter to get the sheet to recalculate.

Finally, you should right click the tab at the bottom of the sheet that says Sheet1.  On the menu that pops up select Rename.  Change the name to Payments.

Now  that you have completed this little exercise we will move on to bigger and better things!