You are the manager of a hotel. Your hotel needs a new company car. You have selected a replacement vehicle and now must select a procurement option. The dealer is offering several options. For options with rebates, the amount of the rebate is applied at the time of purchase effectively lowering the cash paid in the first option and the principal and ultimately the monthly payment in Options 3, 4, and 5. The monthly payments for Options 2 through 5 have been adjusted to account for the rebate. The only numbers you are concerned with are costs that would be different between the options. For example, the salvage value is the same for all purchase options so it is not included in the total cost. 80The gray columns in the template are the total cost in the present value columns.

Option 1: Cash with Rebate

Purchase Price: $46,000

Rebate: $2,000

Trading-in value of old vehicle = $10,000

Sales Tax: $2,520 paid in a lump sum on the purchase date

Option 2: Four-Year Loan no Rebate

Purchase Price: $46,000

Rebate: $0

Down Payment: $20,000 down (including trading-in value of $ 10,000 for the old vehicle)

The rest ($26,000) is financed at a nominal annual rate of 3.99% for four years. The resulting equal monthly payments of $587 are made at the end of the month. Total payment is $48,176.

Sales Tax: $2,520 paid in a lump sum on the purchase date

Option 3: Four-Year Loan with Rebate

Purchase Price: $46,000

Rebate: $2,000

Down Payment: $20,000 down (including trading-in value of $ 10,000 for the old vehicle)

The rest ($24,000) is financed at a nominal annual rate of 4.99% for four years. The resulting equal monthly payments of $553 are made at the end of the month. Total payment is $46,544.

Sales Tax: $2,520 paid in a lump sum on the purchase date

Option 4: Five-Year Loan with Rebate

Purchase Price: $46,000

Rebate: $2,000

Down Payment: $20,000 down (including trading-in value of $ 10,000 for the old vehicle)

The rest ($24,000) is financed at a nominal annual rate of 5.99% for four years. The resulting equal monthly payments of $464 are made at the end of the month. Total payment is $47,840.

Sales Tax: $2,520 paid in a lump sum on the purchase date

Option 5: Five-Year Loan with Rebate no Trade-in

Purchase Price: $46,000

Rebate: $2,000

Trading-in value of old vehicle = $10,000

Sold old vehicle for $12,000

The rest ($44,000) is financed at a nominal annual rate of 5.99% for five years. The resulting equal monthly payments of $850 are made at the end of the month. Total payment is $51,000.

Sales Tax: $3,220 paid in a lump sum on the purchase date

The present value equation for a one-time future payment/receipt is:

PV = FV * (1 + i)^-n

The present value equation for multiple payment/receipt is:

PV = A * (((1 + i_effective)ⁿ - 1) / (i_effective * (1 + i_effective)ⁿ))

The equation for the effective monthly interest rate for multiple payments is:

i_effective = ((1 + i_nominal) / (1 + Inflation)) 1

Use the above information to fill out the Excel template.

Option 1: Cash with Rebate Down Payment in Present Value Nominal Monthly Purchase Price Interest Rate in Present Value Rebate in Present Value Down Payment in Monthly Interest Sales Tax in Present Value Purchase Price in Present Value Rebate in Present Value Option 2: Four-Year Loan no Rebate Effective Monthly Loan Payment in Present Value Rate for Loan Present Value Down Payment in Monthly Interest Cash Sales Tax in Present Value Rebate in Present Value Four-Year Loan Purchase Price no Rebate in Present Value Option 3: Four-Year Loan with Rebate Effective Monthly Loan Payment in Present Value Rate for Loan Present Value Sales Tax in Present Value Four-Year Loan with Rebate 0.0500% % 0.0750% 0.1000% 0.1250% 0.1500% 0.1750% 0.2000% 0.2250% 0.2500% 0.27507 0.3000% 0.3250% 0.3500% 0.3750% 0.4000% 0.4250% 0.4500% 0.4750% 0.5000% 0.5250% 0.5500% 0.5750% 0.6000% 0.6250% 0.6500% 0.6750% 0.7000% 0.7250% 0.7500% 0.7750% 0.8000% 0.8250% 0.8500% 0.87507 0.9000% 0.9250% 0.9500% 0.9750% 1.00002 1.0250% 1.0500% % 1.0750% 1.1000% 1.1250% Purchase Price in Present Value Rebate in Present Value Down Payment in Monthly interest Option 4: Five Year Loan with Rebate Effective Monthly Loan Present Value Payment in Rate for Loan Present Value Sales Tax in Present Value Five-Year Loan Purchase Price with Rebate in Present Value Rebate in Present Value Option 5: Five-Year Loan with Rebate no Trade-in Trade in Value of Selling Price for Effective Monthly Loan Old Vehicle in Current Vehicle in Monthly Interest Payment in Present Value Present Value Rate for Loan Present Value Sales Tax in Present Value Five-Year Loan with Rebate no Trade-in Option 1: Cash with Rebate Down Payment in Present Value Nominal Monthly Purchase Price Interest Rate in Present Value Rebate in Present Value Down Payment in Monthly Interest Sales Tax in Present Value Purchase Price in Present Value Rebate in Present Value Option 2: Four-Year Loan no Rebate Effective Monthly Loan Payment in Present Value Rate for Loan Present Value Down Payment in Monthly Interest Cash Sales Tax in Present Value Rebate in Present Value Four-Year Loan Purchase Price no Rebate in Present Value Option 3: Four-Year Loan with Rebate Effective Monthly Loan Payment in Present Value Rate for Loan Present Value Sales Tax in Present Value Four-Year Loan with Rebate 0.0500% % 0.0750% 0.1000% 0.1250% 0.1500% 0.1750% 0.2000% 0.2250% 0.2500% 0.27507 0.3000% 0.3250% 0.3500% 0.3750% 0.4000% 0.4250% 0.4500% 0.4750% 0.5000% 0.5250% 0.5500% 0.5750% 0.6000% 0.6250% 0.6500% 0.6750% 0.7000% 0.7250% 0.7500% 0.7750% 0.8000% 0.8250% 0.8500% 0.87507 0.9000% 0.9250% 0.9500% 0.9750% 1.00002 1.0250% 1.0500% % 1.0750% 1.1000% 1.1250% Purchase Price in Present Value Rebate in Present Value Down Payment in Monthly interest Option 4: Five Year Loan with Rebate Effective Monthly Loan Present Value Payment in Rate for Loan Present Value Sales Tax in Present Value Five-Year Loan Purchase Price with Rebate in Present Value Rebate in Present Value Option 5: Five-Year Loan with Rebate no Trade-in Trade in Value of Selling Price for Effective Monthly Loan Old Vehicle in Current Vehicle in Monthly Interest Payment in Present Value Present Value Rate for Loan Present Value Sales Tax in Present Value Five-Year Loan with Rebate no Trade-in