An important and common business transaction is getting a loan from a bank or financial services company. Suppose you want to buy a new car with a purchase price of $23,000, but you only have $6,000 available now to make as a down payment. Then you'd need to finance (i.e., get a loan for) the balance ($17,000) at a particular annual interest rate, such as 5%.

Typically, we pay back a loan 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. Its syntax is: PMT(Rate, Nper, PV, FV, Type), where

Rate= the interest rate per period;

Nper= the total number of payments, or the length of the loan (typically, in months);

PV= the present value, or loan amount;

FV= the future value, or 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;

= 1, if payments are due at the beginning of each period.

For example, the cell formula -PMT(.05/12, 36, 17000, 0, 0) yields a payment size of $509.51, which must be paid by the end of every month in order to repay the loan in 3 years. Note that PMT normally returns a negative number; putting a minus sign in front of the function causes the number returned to be positive. The total interest paid (TIP) over the course of a loan is the total amount of all payments minus the amount financed. Here, TIP = 36($509.51) - $17,000 = $1,342. A key decision to make prior to getting a loan is the length of the loan. This assignment explores the relationship between the loan length, payment size, and TIP.

To do:

1.Name your file "LastnameFirst-Asmt1.xslx" and submit on iLearn by 8:00 pm on Sept. 9, 2020.

2.On Sheet 1: Draw an influence chart for this situation, with TIP as the main output. Use Excel's Insert > Shapes to make rectangles, arrows, etc. This may take a little time and practice.

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

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

5.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.

6.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 of ID 5925