Suppose you want to buy a new car with a purchase price of $22,000, but you only

Fantastic news! We've Found the answer you've been seeking!

Question:

Suppose you want to buy a new car with a purchase price of $22,000, but you only have saved up $5,000 to make as a down payment. Then you'd need to finance (i.e ., get a loan for) the balance of $17,000 at a particular annual interest rate, such as 5%.

Typically, we pay back loans by making a series of equal-sized payments due at the end of each month. Excel's PMT function can be used to find the amount of money you must pay every month in order to amortize the loan in some number of periods: PMT(Rate, Nper, PV, FV, Type), where

Rate = the interest rate per period (i.e ., per month)

N per = the total number of payments, or the length of the loan in months

PV = the present value (loan amount)

FV = the future value (cash balance) you want to attain after the last payment is made. If omitted, FV is assumed to be 0, i.e ., the future value of a loan is 0.

Type = 0, if payments are due at the end of each period; or = 1, if payments are due at the beginning of each period.

Requirements:

1) : Draw an influence chart for this situation, with TIP as the main output.

2) : Create a spreadsheet model in Excel

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

b. The down payment should equal the digits of this number. For example, if your 4321, then the down payment should = $4,321.

3) 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.

4) 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.

5. Do the following and briefly answer the 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)?