Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

How to do in Excel with PMT formula and what if analysis. How to do on Excel with PMT formula? Determining Terms of Automobile Leases

How to do in Excel with PMT formula and what if analysis.

How to do on Excel with PMT formula?

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 $26,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 $1,000, monthly

payments of no more than $470, and lease terms of no more than three 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:

Age Resale Price

1 year . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $21,000

2 years . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19,500

3 years . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17,000

4 years . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14,500

5 years . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13,500

Avant-Garde's cost of capital is 18 percent per year, or 1.5 percent per month.

Required

a. With the aid of spreadsheet software, develop a competitive and profitable lease payment program.

Assuming a $1,000 down payment, calculate the program's monthly payments for 2, 3, 4 and 5 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 5 the time value of money;

nper 5 the number of periods; pv 5 the present value; fv 5 the future value; and type 5 0 (when the

payment is at the end of the period) or 1 (when the payment is at the beginning 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. Consider the residual value as a future

value and enter it as a negative number, indicating the lessor has not paid the full cost of the car.]

b. Reevaluate the lease program assuming a down payment of $2,000.

c. Reevaluate the lease program assuming a down payment of $1,000 and a $2,000 increase in re-

sidual values.

d. Reevaluate the lease program assuming a down payment of $2,000 and a $2,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?

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

College Accounting A Contemporary Approach

Authors: David Haddock, John Price, Michael Farina

5th Edition

126078035X, 978-1260780352

More Books

Students also viewed these Accounting questions

Question

5. How can I help others in the network achieve their goals?

Answered: 1 week ago