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.

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?

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, and please show how to construct the one way data table.

Answer 1

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

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