MA25-37. Determining Terms of Automobile Leases (Requires Spreadsheet) Avant-Garde Motor Company has asked you to develop lease terms for the firm's popular Avant- Garde Challenger, which has an average selling price (new) of $25,000. You know that leasing is attractive because it assists consumers in obtaining new vehicles with a small down payment and "rea- sonable" monthly payments. Market analysts have told you that to attract the widest number of young professionals, the Challenger must have an initial down payment of no more than $500, monthly payments of no more than $400, and lease terms of no more than five years. When the lease expires, Avant-Garde will sell the used Challengers at the automobile's resale market price at that time. It is difficult to predict the future price of the increasingly popular Challenger, but you have obtained the following information on the average resale prices of used Challengers: 1 year. 2 years 3 years 4 years 5 years Age Resale Price $21,000 18,000 17,000 15,500 13,000 Avant-Garde's cost of capital is 12% per year, or 1% per month. Required a. With the aid of spreadsheet software, develop a competitive and profitable lease payment program. Assuming a $500 down payment, calculate the program's monthly payments for two, three, four-, and five-year leases. Assume the down payment and the first lease payment are made immediately and that all subsequent lease payments are made at the start of the month. (Hint: Most software packages include a function such as the following: PMT (rate,nper.pv.fv.type), where rate = the time value of money; nper = the number of periods: pv = the present value; fv = the future value; and type = 0 (when the payment is at the end of the period) or 1 (when the payment is at the begin- ning of the period). For monthly payments, rate should be set at the annual rate divided by 12, and npr should be set at the number of months in the lease. Here, fv is the residual value.) b. Reevaluate the lease program assuming a down payment of $1,000. c. Reevaluate the lease program assuming a down payment of $500 and a $1,000 increase in residual values. d. Reevaluate the lease program assuming a down payment of $1,000 and a $1,000 increase in re- sidual values. e. What is your final recommendation? What risks are associated with your recommendation? Are there any other actions to consider