Create an Excel model (show formulas too) to answer the following questions:

To finance the purchase of the Model 3, you have available a bank loan with an APR of 3% with monthly compounding. What would be your monthly payment if you use the bank loan, over a 36-month period, to purchase the car?

Assume you make no down payment under the purchase option and the first loan payment is due in one month. Assuming you have decided on the Model 3 as your next vehicle, should you purchase or lease the Model 3? Why? Write an If function to have the model display your choice.

The residual value is often difficult to estimate. Oftentimes, it is set as a fraction of MSRP. In the base case above, the residual value is 44% of the MSRP. Conduct a sensitivity analysis, using Excels Data Table function, which calculates the effective annual interest rate of the lease option based on residual values ranging from 40% to 50% of MSRP.

Use 1% increments in your residual value range. At what residual value are you indifferent between the purchase and lease options?

Purchase Option:

MSRP: $41,000

Federal Tax Credit: $7,500 (Available at time of purchase)

Net Selling Price: $33,500 (=MSRP - Federal Tax Credit)

Residual (Resale) Value in 3 Years: 44% of MSRP (should equal $18,040)