LSP 120

Installment Loans - Activity 14

  

All group activities must include a statement signed by all members of your group that each group member fully participated in the activity. Save it to the desktop and save frequently during the hour.

Learning Goals for this Activity

  1. You will be able to calculate loan payments using the PMT function in Excel.
  2. You will be able to calculate the total amount paid, the total interest paid and the total principal paid on the loan.

To use the PMT Function...
Choose the Function Wizard button then Financial in the left column and PMT in the right column. A screen will appear and you will be prompted to enter values.

Rate is the loan interest rate. (Monthly amount)
Nper is the number of payments. (Total number of payments to be made)
Pv is the amount of the loan. (No change - just the amount of the loan)
Fv is the future value of the loan and is not filled in.(Leave blank)
Type is indicating whether the payment is made at the beginning or the end of the month. Use 0 for this problem.(Just enter 0 or leave blank)

Amortization Table

Month

Beg Balance

Payment

Interest

Principal

End Balance

0

          

1

          

2

          

 

1. You have decided to buy a 2003 Chevy Cavalier. The total cost with tax, title, license and optional equipment is $20,000. You will make a downpayment equal to 20% of the total cost of the car. The remaining amount will be financed.

You have two financing options.

Option 1 - Get $3,000 cash back. This amount will reduce the amount you need to finance. You will be able to get a 5 year car loan on the remaining amount with a 5.70% interest rate from your bank.

Option 2 - Get no cash back and finance the entire amount. The loan will be for 3 years with a 0.0% interest rate from GMAC financing.

a. What is the amount of money that you will need to borrow under Option 1? 

b. Make an amortization table for Option 1 and verify that your ending balance after 60 months is zero. Paste the first five lines (months 0 - 4) in your Word document. What is your monthly payment? What is the total amount paid over the term on the loan?

c. How much will you need to borrow if you choose option 2? What is your monthly payment?

d. Which financing option should you choose? Explain.

 wpe2.jpg (2647 bytes)
2. You have decided to buy a 2003 Chevy Blazer. The total cost with tax, title, license and optional equipment is $23,000. You will make a down payment equal to 20% of the total cost of the Blazer. The remaining amount will be financed.

You have two financing options.

Option 1 - Get $2,500 cash back. This amount will reduce the amount you need to finance. You will be able to get a 5 year car loan on the remaining amount with a 6.50% interest rate from your bank.

Option 2 - Get no cash back and finance the remaining amount. The loan will be for 3 years with a 0.0% interest rate from GMAC financing.

a. What is the amount of money that you will need to borrow under option 1? 

b. Make an amortization table for Option 1 and verify that your ending balance after 60 months is zero. Paste the first five lines (months 0 - 4) in your Word document. What is your monthly payment?  What is the total amount paid over the entire term on the loan?

c. How much will you need to borrow if you choose option 2. What is your monthly payment?

d. Which financing option should you choose? Explain.

 wpe2.jpg (2647 bytes)