1.On Sheet 2: Make a spreadsheet model in Excel following the format used in class, i.e., have sections for inputs, decision variables, calculated quantities, outputs, and use range names.

a.Assume a purchase price of $23,000 and an annual interest rate of 5%.

b.The down payment should equal to the last four digits of your student ID number. For example, if your ID # is 987654321, then the down payment should be $4,321.

2.Also on Sheet 2: Make a 1-way data table where the loan length varies from 12 to 60 months in increments of 12 months. Output columns should be monthly payment size and TIP.

3.Also on Sheet 2: Make a 2-way data table for TIP where the loan length varies from 12 to 60 months in increments of 12 months and the annual interest rate varies from 3% to 7% in increments of 0.5%. Highlight all cells in the data table with a TIP below $1,600.

4.On Sheet 3: briefly answer to the following questions:

a.Make an XY plot of TIP vs. loan length (i.e., put loan length on the x-axis). What kind of relationship is there between loan length and TIP (positive/negative, linear/nonlinear)?

b.Make an XY plot of PMT vs. loan length (i.e., put loan length on the x-axis). What kind of relationship is there between loan length and PMT (positive/negative, linear/nonlinear)?

c.What kind of relationship is there between the interest rate and TIP (positive/negative, linear/nonlinear)?

d.If you can only afford to make monthly payments of $450 or less, and the annual interest rate is 5%, what options do you have regarding the loan length?

Last 4 digits 1234