Question

Jason is interested in buying a new Maserati Gibli. The sales price of the car is $77,000. Jason intends to put a down payment of $15,000 and take up the balance with a 5 year loan. Since Jason has excellent credit, he anticipates the annual interest rate (APR) for his future loan would be 2.84%. Round your answers to the nearest dollar.

1. Using a spreadsheet model, what will be the monthly payment for his car if he were to take up the loan for the remaining balance of the car over 5 years with an APR at 2.84%?

2. The maximum down payment Jason can afford is $30,000. Construct a one-way data table with the down payment amount as the column input and monthly payment as the output. Vary the down payment amount between $0 and $30,000 in increments of $5,000. What is the range of payments that Jason is expecting?

3. After thinking about the future loan, Jason decides he only wants to pay $900 per month. Using the appropriate Excel tool, find the exact down payment amount that Jason needs.

Please show how to solve all problems using Excel.

Answer 1

SOLUTION;-

GIVEN DATA;-

The sales price of the car is 77,000.

Jonson intends to put a down payment of 15,000

the balance with a 5 years loan

the annual interest rate (APR) for his future loan would be 2.84%.

1) Calculation of monthly payment using spreadsheet model is following:

Formula used for calculation of monthly payment in cell B9 =-PMT(B5/12,B6*12,B4,0)

2) For constructing one-way data table. Write the down payment amount in column (range F4:F10)

and APR 2.84% in cell G3

Write formula =B9 in cell F3

Then Select range F3:G10 and on Excel ribbon, click Data > What-if Analysis > Data Table

Enter $B$5 in Row Input Cell and $B$3 in Column Input Cell > Click OK

3) We can find the exact down payment by using Goal Seek in Excel

Go to Data > What-If analysis > Goal Seek

Set Cell: $B$9

To value: 900

By changing cell: B3

Required advance payment = $ 26,714