At an MRSP of $41,000 the cutting-edge Telsa Model 3 comes in at a relatively affordable $33,500 after the $7500 federal tax credit is deducted. The lease option was created to make the car even more affordable, making the car available to a larger group of drivers. The terms of the lease were spelled out in the Summer as $350 per month with $4,150 down for a 36 month lease with 36,000 miles. For every mile over 36,000, you will owe 25 cents at the end of the lease. Model 3 marketing manager John Hughes notes that every deal will be a little different, and depends on the equipment and how the deal is written. Theres some variability in it, he said. Weve given dealers all the parameters. Rather than spell out those subtleties for us along with the myriad of disclaimers that accompany them, Hughes said it was best we talk to dealers to ask details. Gordon Lai, a sales manager at Alaska Sales and Service in Anchorage, Alaska offers us the following details which he said came directly from TESLA. 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) Lease Option: Term: 36 months / 36,000 miles Monthly Lease Payment: $350 Security Deposit: $0 Acquisition Fee: $695 (Due at lease signing) Down Payment: $4,150 (Due at lease signing) Disposition Fee: $395 (Due at end of lease) Mileage Fee: $0.25 per mile for every mile above 36,000 (Due at end of lease) Expected mileage: 40,000

create an Excel model to answer the following questions:

1. Assuming the first lease payment is due in one month, calculate the monthly interest rate of the lease option

2. Calculate the effective annual interest rate (EAIR) of the lease option.

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

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

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

6. At what residual value are you indifferent between the purchase and lease options?