Lab 4: Due Tuesday, March 7, 2000

You may not have enough time during the organized lab time to finish this exercise. You will need to go to the U.C. user room at another time to finish it.

Goal:

What to do before the lab:

The exercise (what to do during the lab):

Part 1: Hands-on Exercise 1 on pages 85-89 of the text. Add "Prepared by: your name in cell A9. Print the resulting spreadsheet and formulas.

Part 2: Hands-on Exercise 2 on pages 94-101

Part 3: Improvements to Hands-on Exercise 2.

Step 1: A design problem: Lack of generality. Replace "30 years" in B5 by "20 years". Replace "15 years" in C5 by "10 years". Print a copy of the resulting spreadsheet. On the output write an explanation of why the worksheet is invalid. The following steps will fix the problem.

Step 2: Insert a new row above row 6. Change B5 to "30", C5 to "15" and in B6 and C6 put "Years".

Step 3: Modify the formulas in B7 and C7 by replacing "30" and "15" by references to B5 and C5.

Step 4: Copy your new formulas to the rest of the columns.

Step 5: Print a copy of the spreadsheet and also a copy of the formulas.

Step 6: Replace "30" and "15" by "20" and "10". Change the mortgage amount to $175,000. Print a copy of the spreadsheet.

Part 4: Add new information the spreadsheet from part 3

As shown in the spreadsheet shown at the end of this assignment, add new columns to show the total amount a person would pay if they make every monthly payment for the specified number of years for the loans described in columns B and C. Also calculate the difference between those totals. Insert new rows at the top of the spreadsheet and add the heading in about an 18 point font. Print a landscape copy of the spreadsheet comparing 30 and 15 year terms for a loan of $180,000. Also print a copy of the formulas.

Optional for extra credit (2 points):

Starting with the worksheet from part 4, add two additional columns calculating the total interest paid over the life of the loan in each of the situations. Arrange for the resulting sheet to be printed on one page and print the spreadsheet with loans of 20 and 10 years on a amount of $175,000. Also print a copy of the formulas.

What to hand in:

Amount Borrowed       100000 
Starting Interest      7.50%


               Monthly Payment                Total of Payments
Interest         30         25 Difference         30         25  Difference  
              years      years                 years      years
   7.50%    $699.21    $738.99   $39.78     $251,717   $221,697   $30,020  
   8.50%    $768.91    $805.23   $36.31     $276,809   $241,568   $35,241  
   9.50%    $840.85    $873.70   $32.84     $302,708   $262,109   $40,599  
  10.50%    $914.74    $944.18   $29.44     $329,306   $283,255   $46,052  
  11.50%    $990.29  $1,016.47   $26.18     $356,505   $304,941   $51,564  
  12.50%  $1,067.26  $1,090.35   $23.10     $384,213   $327,106   $57,107  

Financial consultant: Your name