C Sc 100 Applications Software

Excel Handout Assignment 2

Lab objectives:

1. Format a report

2. Learn and use the "What-if" method for simulating real world problems.

3. Learn to create presentation graphics.

 I. Objective
To create a spreadsheet that simulates and reports loan payments.

Loan Payment Problem

The simulation is of a loan where interest is charged at the end of each month when the payment is made. The interest rate is specified as an annual rate, and must be converted to a monthly rate by dividing by 12. The balance during the month is multiplied by the interest rate to find the interest charge for that month. Each payment is divided into two parts, the interest payment and the principle payment. The end balance is calculated as the begin balance minus the principle payment.

1. Enter the following items into a new spreadsheet:
 
A
B
C
D
E
F
1
 Loan Amount
10000.00
   Your Name
2
 Interest Rate
8.50%
      
3
 Payment
872.20
      
4
 Number of Periods
12
      
5
 Period Begin Bal Payment Principle Interest End Bal
6
1
 =$C$1 =C3 =C6-E6 =B6*($C$2/12) =B6-D6
7
 =A6+1 =F6 =IF(A7<$C$4,$C$3,(B7+E7))

Note that cell C7 has a long formula that spans three cells, but it's just entered into the one cell. The formula in C7 will compute the payment amount, which will be the same in all rows with the possible exception of the last payment. Paraphrasing C7, it says: "If the period number is less than the period number of the last payment, make a regular payment. Otherwise, pay the remainder owed (balance plus interest)."
 

2. Copy the last item in columns D through F to row 7 to complete the information for the second payment. We will use the second month’s formulas as the pattern for the rest of the months.

3. Fill row 7 columns A through F down to row 65 to make room for up to 60 payments (i.e., 60 periods). Note how the dollar signs (in the formulas--not those seen in the display of currency values) work after you copy the formulas. That is to say, observe the effect of absolute and relative cell addresses. If everything is correct, the top of your spreadsheet will have the same numbers as the following:

 
A
B
C
D
E
F
1
 Loan Amount
$10,000.00
   Smith, John J.
2
 Interest Rate
8.50%
      
3
 Payment
$872.20
      
4
 Number of Payments
12
      
5
Period
Begin Bal
Payment
Principle
Interest
End Bal
6
1
$10,000.00
$872.20
$801.37
$70.83
$9,198.63
7
2
$9,198.63
$872.20
$807.04
$65.16
$8,391.59
8
3
$8,391.59
$872.20
$812.76
$59.44
$7,578.83
9
4
$7,578.83
$872.20
$818.52
$53.68
$6,760.31
10
5
$6,760.31
$872.20
$824.31
$47.89
$5,936.00
11
6
$5,936.00
$872.20
$830.15
$42.05
$5,105.85
12
7
$5,105.85
$872.20
$836.03
$36.17
$4,269.81
13
8
$4,269.81
$872.20
$841.96
$30.24
$3,427.86
14
9
$3,427.86
$872.20
$847.92
$24.28
$2,579.94
15
10
$2,579.94
$872.20
$853.93
$18.27
$1,726.01
16
11
$1,726.01
$872.20
$859.97
$12.23
$866.04
17
12
$866.04
$872.17
$866.04
$6.13
$0.00
4. Use shading, formats, and borders to make your spreadsheet look similar the to the one above.

5. Save your notebook as loan in your home directory.

6. Print the above portion of your spreadsheet in two ways: with the values displayed as above, and with the cell formulas displayed. To print with the cell formulas displayed, from the Tools menu, select the Options… command, and the View sheet. In the Window Options area, check the Formulas check box on. Then select the range A1:F17, and, from the File menu, choose the Print command. Be sure to click the Selection radio button on, so that you will print only the selected range.

II. Objective
Now that you have the spreadsheet working, use it to answer the following "what-if" questions.
 
1. What if you can afford $355 a month for a car payment; the car you want costs $8,000, and the interest rate on your loan is 5%. How long will you be paying for the car, and how much will your last payment be (the last payment must be greater than zero and less than or equal to $355)? Hint: Adjust the number of payments to reach the desired outcome.
Once adjusted, print the spreadsheet (in only one way, with the values displayed as above). Include all rows starting with the Loan Amount row through the last row with a Begin Bal greater than zero.

2. What if your interest rate is 10% for a car loan; the car costs $8,000, and you wish to pay off the loan in one year. How much are your monthly payments? The payments should be equal except for possibly the last payment, which should be as close to the others as possible. Hint: Adjust the amount of payment to reach the desired outcome.

Once adjusted, print the spreadsheet. Include all rows starting with the Loan Amount row through the last row with a Begin Bal greater than zero.

3. What if you wish to pay off a car loan in three years; the car costs $8,000, and you want the monthly payment to be $250 with the last payment within $5 of $250. What must the loan interest rate be (to within one decimal place, for example 9.9%)? Hint: Adjust the interest rate to reach the known outcome.

Once adjusted, print the spreadsheet. Include all rows starting with the Loan Amount row through the last row with a Begin Bal greater than zero.

4. What if you can afford $250 a month for a car payment; the interest rate on the car loan is 7%, and you wish to pay off the loan in five years. The payments should be equal except for the last payment, which should be as close as possible to the others. What is the most you can borrow? Hint: Adjust the amount of the loan to reach the desired outcome.

Once adjusted, print the spreadsheet. Include all rows starting with the Loan Amount row through the last row with a Begin Bal greater than zero.

 III. Objective
To create and print presentation graphics from the spreadsheet of question 4.

Create and print the following types of charts from the data of question 4.

Balance after each payment chart:

Division of each payment into principle and interest chart:








Questions

  1. In step I.3, Why did we use the second payment as the pattern to fill in the remainder, instead of the first?
  2. Why do some cell references in row 7 use dollar signs, while others don’t? What would happen if all cell references in row 7 used dollar signs before using it to fill the remainder? What if all used dollar signs?
  3. Why does the Payment Division chart look the way it does? What does it show?
Items to turn in:
  1. A printout of the original spreadsheet.
  2. A printout of the original spreadsheet using the Tools/Options/View sheet/ Formulas check box to display the formulas.
  3. Printouts of the four adjusted spreadsheets (one from each question, displaying values only).
  4. A printout of the Loan Amortization chart.
  5. A printout of the Payment Division chart.
  6. Word processed answers to the 3 questions.