Computer Science 120

March 20, 2001

Background

    Many of the exercises in CS 120 are of a (largely) mechanical nature.  In the case of Excel, for example, you acquire the ability to perform many manipulations of a spreadsheet, but you don't necessarily ever create a realistic document from scratch.  This exercise is a combination of review, new material, and a chance to see a more realistic use of spreadsheets.  Your completed version of this exercise will be judged upon your ability to perform the "mechanical" manipulations, the sense of judgment you show in laying out the various components, and the quality of the report as measured by the common sense advice and the coherence of the writing..

   Note that not all of the required tasks are spelled out in great detail for you.  To complete some of them you may have to use the Help features from within Excel.

    THE MATERIALS FOR THIS LAB ARE DUE WEDNESDAY, MARCH 28 AT NOON.  Late submissions will be penalized.  This lab is equivalent to four "regular" labs.  There will be time to work on it after the exam (if you finish early) on Thursday, but we will be moving on to Access starting on Tuesday.

Narrative

    Chris and Pat Devereux are in the process of buying a new house.  Like most people who buy houses these days, they will take out a mortgage.  Their mortgage will be a fixed-rate mortgage, i.e. the interest rate will not change over the course of the mortgage.  They have come to you with various questions (given at the end of this page) and you are to prepare them a report that includes the answers to these questions.  Your will also be giving them a spreadsheet so that they can experiment with the various aspects of the loan.  Your report should make reference to, but not include this spreadsheet.  It should, however, contain a repayment graph, as defined below.  Your report should be professional in appearance, though not long.  (You do not have to use any of the Word wizards to prepare it, but you may if you so desire.)  

 

Building the Model

 

  1. Begin by launching Excel and creating an empty spreadsheet.


  2. Chris and Pat's first question concerns whether or not they can afford certain houses.  Create a small spreadsheet that includes the data below:
    3. Principle $     100,000
    Interest Rate6.5%
    Number of years15
    Monthly Payment$871.11

    The monthly payment should be computed automatically.  For instance, if the interest rate changes to 8%, the payment should be $955.65.
  3. At this point, Chris and Pat have decided upon a house that will require a $90,000 mortgage at 7.25%.  They will pay this mortgage back over a 20-year period.  They wish to see a payment schedule. 


  4. On another sheet of the workbook, create a schedule with four columns.  

    1. Label the columns: Base Payment, Interest, Principle, Balance.  In the first row of data, leave the first three columns blank.  Link the fourth column back to the cell on the previous sheet that contains the principle.
    2. In the second row, link the first cell (Base Payment) back to the Payment cell on the original sheet.
    3. The interest for a given month is the monthly interest rate times the outstanding balance.  In the interest column use a formula to compute this month's interest.
    4. The principle for the month is simply the difference between the payment this month and the interest this month.  Use a formula to put that in. 
    5. The balance this month is the difference between the balance last month and the principle this month.  Use a formula to put that in.
    6. Your second sheet should now look like this
      Base PaymentInterestPrincipleOutstanding Balance
       $  90,000.00
      $711.34 $       543.75$167.59 $  89,832.41

       

  5. Next, edit the formulae in the interest and base payment cells so that they use absolute references.  (This will involve adding $'s to the references that are already there.)  There will be no visible change in the sheet as a result of this operation.  If you are not sure if you have done this correctly, ask.


  6. Next select the four cells in the bottom row.  Copy them to the clipboard.  Then, click in the cell below $711.34 and choose Paste.  If all has gone well, the balance in this row should now be $89,663.81.  (Note:  The meaning of this is that after two months, Chris and Pat still owe $89,663.81 even though they have paid out $1422.68.  Great deal, eh?)


  7. Next, determine how many payments Chris and Pat will have to make.  Clearly you do not wish to Paste that many times.  Instead, select a rectangle that starts at the row you just created and goes down the requisite number of rows.  (It is a significant number.)  Then, under the Edit menu, choose Fill, and then Down.  If you have gone the correct number of rows, the balance should be 0 in the last row.  (This is the complete payment schedule.)  Chris and Pat are concerned about how long until the loan is "half" paid back.  Be sure to address this in your report.  They are also concerned about the total amount that they will pay over the course of the loan.


  8. Many financial advice columns suggest paying back an additional amount each month, if you can afford it.  (This has some tax consequences that you should ignore for this assignment.)  Add a new column between the Principle and the Outstanding balance column.  Label it "Extra Payment" and enter $50 in the second row. 


  9. Now the Outstanding Balance formulae are incorrect.  First adjust the one in the first row.  The new outstanding balance should be the old balance LESS the principle paid LESS the extra payment.  If it is done correctly, the number should be $89,782.41.


  10. Unfortunately, you just fixed. the formula in the one cell.  Copy this formula throughout the outstanding balance column.


  11. Chris and Pat have determined that they can pay an extra $75 each month.  Adjust the sheet so that they do so.  In the report, be sure to include two additional pieces of data: when the loan will be paid back under this schedule and the total amount of all payments under this schedule.


  12. Chris and Pat, like most people, can interpret graphs more easily than they can interpret numbers.  Build a graph that shows the outstanding balance over time.  (It should start at $90,000 and drop to zero, but should go no further.) 


The Report

     Chris and Pat need a report outlining their situation and options.  Your report should include a graph of the repayment, answers to the questions posed above, and general advice.  In addition, Chris and Pat are considering whether they should just get at 15 year mortgage instead of taking the 20-year option and then putting in extra money.  Discuss the pluses and minuses of this option.

    You have just heard that interest rates may drop by as much as full percentage point.  How will this affect Chris and Pat?

 

To Turn In

    You are to turn in a paper copy (yes, you have to print it out!) of the report and an electronic copy (via email to dlevine@cs.sbu.edu) of the Excel workbook.