Question

Quiz: Chapter 4 Quiz (Copy) Time Remaining: 02:33:54 Submit Quiz This Question: 1 pt 1 of 9 This Quiz: 9 pts possible When you purchased your car, you took out a five-year annual-payment loan with an interest rate of 6.1 % per year. The annual payment on the car is $5,000. You have just made a payment and have now decided to pay off the loan by repaying the outstanding balance. What is the payoff amount for the following scenarios? a. You havé owned the car for one year (so there are four years left on the loan)? b. You have owned the car for four years (so there is one year left on the loan)? a. You have owned the car for one year (so there are four years left on the loan)? The payoff if you have owned the car for one year (so there are four years left on the loan) is S (Round to the nearest cent.) b. You have owned the car for four years (so there is one year left on the loan)? The payoff if you have owned the car for four years (so there is one year left on the loan) is $ (Round to the nearest cent.)

Answer 1

To answer both the questions, we prepare a loan repayment schedule

The PMT function in Excel gives us the payment to be made in each period to repay the loan, if loan amount, loan tenure and interest rate are known.

In our case, we have the payment in each period, loan tenure and interest rate. Hence we assume the loan amount to be any arbitrary number (don't worry, we'll use another Excel function to back-calculate exact loan amount so that the payment in each period is $5,000)

Using this data we build a loan repayment schedule for the assumed loan amount.

Now we use the "Goal Seek" function of Excel from the Data tab on the ribbon, We set the sell value in payment to $5,000, by changing the value of the loan amount and we get the exact loan amount in the output.

We get the following loan repayment schedule

Loan period	5
Interest rate	6.10%
Loan amount	$21,004.65
Year	Payment	Interest	Principal	Outstanding
1	$5,000.00	$1,281.28	$3,718.72	$17,285.93
2	$5,000.00	$1,054.44	$3,945.56	$13,340.37
3	$5,000.00	$813.76	$4,186.24	$9,154.13
4	$5,000.00	$558.40	$4,441.60	$4,712.54
5	$5,000.00	$287.46	$4,712.54	$0.00

Now we can answer both the questions

a.

If we have used the car for 1 year, the payoff is $17,285.93

b.

If we have used the car for 4 years, the payoff is $4,712.54