Review of Excel Basics Using Mortgages

 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. Type A Comparison of Mortgage Payments in cell A1 Type Interest Rate vs. Mortgage Amount in cell A2 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.

 0.075 0.07625 0.0775 0.07875 0.08 0.08125 0.0825 0.08375 0.085 0.08625 0.0875 0.08875 0.09 0.09125 0.0925 0.09375 0.095

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

 120000 130000 140000 150000 160000 170000 180000 190000 200000

 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.

 Entry Description PMT The function to compute the payments on a loan based on constant payments and a constant interest rate. rate The interest rate per period of the loan. nper The total number of payments for the loan. pv The total amount that a future amount of payments is worth now.  In our situation this is just the mortgage amount. fv The cash balance after all payments have been made.  This will default to 0 if omitted.  In our situation this is 0. type 0 or 1 depending on when the payment is made.  This defaults to 0 which corresponds to payment at the end of the period.

 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. Type Loan Duration (YRS) in cell A23. Bold, and right justify the entry.  Change the font size to 12. 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. Click on cell C5. 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.

 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.

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

 Now you want to fill in the text boxes after moving the dialogue to make sure you can see the needed cells. 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.. Move the cursor to the Nper text box and type 12* then click on the B23 cell. Move the cursor to the Pv text box and click on the C4 cell. 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.