Question

Purchase Price	$33,500.00			Finance Rate Table
less				Term	No Rebate	Rebate
Down Payment	$3,500.00			1	12.50%	19.70%
Trade-in Value	$4,500.00			2	14.90%	20.90%
Rebate	$1,000.00			3	17.30%	22.10%
				4	18.50%	23.30%
Loan Amount	$24,500.00			5	19.70%	24.50%
	--------------------Term of Loan-----------------------
	1	2	3	4	5
Finance Rate	19.70%	20.90%	22.10%	23.30%	24.50%
Monthly Payment	$2,266.03	$1,257.75	$936.93	$789.29	$711.94
Total Payment	$27,192.35	$30,185.89	$33,729.63	$37,886.09	$42,716.61
Finance Charge	$2,692.35	$5,685.89	$9,229.63	$13,386.09	$18,216.61

Please answer the following below using your Automobile Loan Calculator workbook along with the following information:

• Purchase Price is $33,500
• Down Payment is $3,500
• Trade-in Value is $4,500
• Customers get $1,000 if they elect to take the Rebate
• Use the Finance Rate Table provided below:

• Term	No Rebate	Rebate
  1	12.5%	19.7%
  2	14.9%	20.9%
  3	17.3%	22.1%
  4	18.5%	23.3%
  5	19.7%	24.5%

A) If a customer elects to take the Rebate, what is the Loan Amount?

B) If a customer does not elect to take the rebate, what would the monthly payment be for a 3-year loan?

C) If a customer elects to take the $1,000 Rebate, what would be the Total Payments for a 5-year loan?

D) What is the difference in Monthly Payment amounts for customers who do and do not elect to take the Rebate for a 1-year loan?

E) What is the difference in Finance Charges for customers who do and do not elect to take the Rebate for a 1-year loan?

Answer 1

A]

Loan amount with rebate = purchase price - down payment - trade in value - rebate = 33,500 - 3,500 - 4,500 - 1,000 = $24,500

B]

Loan amount without rebate = Loan amount with rebate + rebate = 24,500 + 1,000 = $25,500

Loan rate for 3-year term without rebate = 17.30%

Monthly payment is calculated using PMT function in Excel :

rate = 17.30% / 12 (converting annual rate into monthly rate)

nper = 3 * 12 (3 year loan with 12 monthly payments each year)

pv = 25,500 (loan amount)

PMT is calculated to be $912.96

C]

Loan amount with rebate = $24,500

Loan rate for 5-year term with rebate = 24.50%

Monthly payment is calculated using PMT function in Excel :

rate = 24.50% / 12 (converting annual rate into monthly rate)

nper = 5 * 12 (5 year loan with 12 monthly payments each year)

pv = 24,500 (loan amount)

PMT is calculated to be $711.94

Total payments = monthly payment * number of months = $711.94 * (5 * 12) = $42,716.61

D]

Loan amount with rebate = $24,500

Loan rate for 1-year term with rebate = 19.70%

Monthly payment is calculated using PMT function in Excel :

rate = 19.70% / 12 (converting annual rate into monthly rate)

nper = 1 * 12 (1 year loan with 12 monthly payments each year)

pv = 24,500 (loan amount)

PMT is calculated to be $2,266.03

Loan amount with rebate = $25,500

Loan rate for 1-year term with rebate = 12.50%

Monthly payment is calculated using PMT function in Excel :

rate = 12.50% / 12 (converting annual rate into monthly rate)

nper = 1 * 12 (1 year loan with 12 monthly payments each year)

pv = 25,500 (loan amount)

PMT is calculated to be $2,271.61

Difference in monthly payments = $2,271.61 - $2,266.03 = $5.58