Using Microsoft Excel: Suppose you are in the market for a new
car worth $22,000. You are offered a deal to make a $2,000 down
payment now and to pay the balance in equal end-of-month payments
of $505.33 over a 48-month period. Consider the following
situations.
(a) Instead of going through the dealer’s financing, you want to
make a down payment of $1,800 and take out an auto loan from a bank
at 9.2% compounded monthly. What would be your monthly payment to
pay off the loan in four years?
(b) If you were to accept the dealer’s offer, what would be the
effective rate of interest per month the dealer charges on your
financing?
| Car value | 22000 | ||
| A | |||
| Down payment | 1800 | ||
| Loan Amount | 20200 | ||
| Loan tenure (yrs) | 4 | ||
| Loan tenure (months) | 48 | ||
| interest rate yearly | 9.20% | ||
| interest rate monthly | 0.7667% | ||
| Monthly payment | 504.60 | ||
| B | |||
| Down payment | 2000 | ||
| Loan Amount | 20000 | ||
| Loan tenure (yrs) | 4 | ||
| Loan tenure (months) | 48 | ||
| Monthly payment | 505.33 | ||
| Monthly interest rate | 0.81664% | ||
Showing formula in excel
| Car value | 22000 | ||
| A | |||
| Down payment | 1800 | ||
| Loan Amount | =D1-D3 | ||
| Loan tenure (yrs) | 4 | ||
| Loan tenure (months) | =D5*12 | ||
| interest rate yearly | 0.092 | ||
| interest rate monthly | =D7/12 | ||
| Monthly payment | =PMT(D8,D6,-D4) | ||
| B | |||
| Down payment | 2000 | ||
| Loan Amount | =D1-D13 | ||
| Loan tenure (yrs) | 4 | ||
| Loan tenure (months) | =D15*12 | ||
| Monthly payment | 505.33 | ||
| Monthly interest rate | =RATE(D16,D17,-D14) | ||
Get Answers For Free
Most questions answered within 1 hours.