TVM Assignment
Please answer the question in an excel spreadsheet with the formulas showing.
Part VI: Car Loan
You are looking to buy a 2018 Ford Focus Titanium Hatchback with sunroof and leather seats at a price of $26,000. Being a college student, you have cash to pay taxes, title, license and fees but your parents offer to give you 10% of the price, $2,600, as a down payment and you need to finance the remainder of $23,400. You smartly researched your finance options and got pre- approval at a 3.6% APR with monthly compounding for 5 years on your own so you are not at the mercy of depending on financing from the car dealership. Ford is offering 0% APR financing or $3,250 cash back which would be used as an additional down payment that reduces the amount that you would need to finance. You would use your 3.6% APR pre-approved financing if you elect the $3,250 cash back option. Answer the following questions.
I What would be your monthly car loan payment under the Ford’s 0% APR financing offer (assume a 60-month loan term)?
II What would be your monthly car loan payment under the Ford’s $3,250 cash back offer and your 3.6% APR pre-approved financing?
III At what APR would you be indifferent between the two offers? In other words, at what APR would you have the same monthly payment (assuming a 60-month loan term) for the $3,250 cash back offer as you would with the 0% APR financing offer?
IV Assume you go with the offer in question #II. Construct an amortization schedule for the loan for all 60 monthly payments. What is your loan balance after 36 months?
V Now assume you were willing to make a monthly car loan payment of $450. Re-do your amortization schedule from question #IV with this monthly payment. How long will it take to pay off the loan with the $450 monthly payment?
I) With the 0% APR,
total amount of loan= $ 26,000
down payment = $ 2,600
total amount of loan to be paid = 26,000 - 2,600 = 23,400
monthly car loan payment = 23,400 / 12 = $390
therefore, the monthly payment for 0% APR rate is $390.
II) The payment is calculated using excel spread sheet.
New down payment amount = $ 3,250
The loan payment amount will be = 26,000 - 2,600 - 3,250
=$20,150
loan amount = $ 20,150
number of payment =60
rate = 0.36/12
The function used for calculation for payment is PMT
= PMT(0.036/12,60,20150,0)
= $ 367.47
Therefore the monthly payment is $ 367.47
III) The APR rate is calculated by using the excel spreadsheet.
loan amount = $ 20,150
number of payment =60
payment = $367.47
rate = RATE ( 60,367.47,-20150,0)
= 0.30%
so the monthly APR rate will be 0.30%
IV) The amortization schedule is
| no of payment | payment | principal | interest | balance |
| 1 | ₹ 367.47 | ₹ 307.02 | ₹ 60.45 | ₹ 19,842.98 |
| 2 | ₹ 367.47 | ₹ 307.94 | ₹ 59.53 | ₹ 19,475.52 |
| 3 | ₹ 367.47 | ₹ 308.86 | ₹ 58.61 | ₹ 19,108.05 |
| 4 | ₹ 367.47 | ₹ 309.79 | ₹ 57.68 | ₹ 18,740.58 |
| 5 | ₹ 367.47 | ₹ 310.72 | ₹ 56.75 | ₹ 18,373.12 |
| 6 | ₹ 367.47 | ₹ 311.65 | ₹ 55.82 | ₹ 18,005.65 |
| 7 | ₹ 367.47 | ₹ 312.58 | ₹ 54.88 | ₹ 17,638.18 |
| 8 | ₹ 367.47 | ₹ 313.52 | ₹ 53.94 | ₹ 17,270.72 |
| 9 | ₹ 367.47 | ₹ 314.46 | ₹ 53.00 | ₹ 16,903.25 |
| 10 | ₹ 367.47 | ₹ 315.41 | ₹ 52.06 | ₹ 16,535.78 |
| 11 | ₹ 367.47 | ₹ 316.35 | ₹ 51.11 | ₹ 16,168.32 |
| 12 | ₹ 367.47 | ₹ 317.30 | ₹ 50.17 | ₹ 15,800.85 |
| 13 | ₹ 367.47 | ₹ 318.25 | ₹ 49.21 | ₹ 15,433.38 |
| 14 | ₹ 367.47 | ₹ 319.21 | ₹ 48.26 | ₹ 15,065.92 |
| 15 | ₹ 367.47 | ₹ 320.17 | ₹ 47.30 | ₹ 14,698.45 |
| 16 | ₹ 367.47 | ₹ 321.13 | ₹ 46.34 | ₹ 14,330.98 |
| 17 | ₹ 367.47 | ₹ 322.09 | ₹ 45.38 | ₹ 13,963.52 |
| 18 | ₹ 367.47 | ₹ 323.06 | ₹ 44.41 | ₹ 13,596.05 |
| 19 | ₹ 367.47 | ₹ 324.03 | ₹ 43.44 | ₹ 13,228.58 |
| 20 | ₹ 367.47 | ₹ 325.00 | ₹ 42.47 | ₹ 12,861.12 |
| 21 | ₹ 367.47 | ₹ 325.97 | ₹ 41.49 | ₹ 12,493.65 |
| 22 | ₹ 367.47 | ₹ 326.95 | ₹ 40.52 | ₹ 12,126.18 |
| 23 | ₹ 367.47 | ₹ 327.93 | ₹ 39.54 | ₹ 11,758.72 |
| 24 | ₹ 367.47 | ₹ 328.91 | ₹ 38.55 | ₹ 11,391.25 |
| 25 | ₹ 367.47 | ₹ 329.90 | ₹ 37.57 | ₹ 11,023.78 |
| 26 | ₹ 367.47 | ₹ 330.89 | ₹ 36.58 | ₹ 10,656.31 |
| 27 | ₹ 367.47 | ₹ 331.88 | ₹ 35.58 | ₹ 10,288.85 |
| 28 | ₹ 367.47 | ₹ 332.88 | ₹ 34.59 | ₹ 9,921.38 |
| 29 | ₹ 367.47 | ₹ 333.88 | ₹ 33.59 | ₹ 9,553.91 |
| 30 | ₹ 367.47 | ₹ 334.88 | ₹ 32.59 | ₹ 9,186.45 |
| 31 | ₹ 367.47 | ₹ 335.88 | ₹ 31.58 | ₹ 8,818.98 |
| 32 | ₹ 367.47 | ₹ 336.89 | ₹ 30.57 | ₹ 8,451.51 |
| 33 | ₹ 367.47 | ₹ 337.90 | ₹ 29.56 | ₹ 8,084.05 |
| 34 | ₹ 367.47 | ₹ 338.92 | ₹ 28.55 | ₹ 7,716.58 |
| 35 | ₹ 367.47 | ₹ 339.93 | ₹ 27.53 | ₹ 7,349.11 |
| 36 | ₹ 367.47 | ₹ 340.95 | ₹ 26.51 | ₹ 6,981.65 |
Pri
Loan balance after 36 months = 6981.65
Principal is caculated using PPMT function and interest is caculated using IPMT function in excel spread sheet.
V)
To prepare the amortization table with $450 monthly payment we need to calculate the monthly APR first.
amount =$ 20,150
paymnet = $450
number of payments = 60
rate = =RATE(5*12,450,-20150,0) ( calculated using excel spread sheet)
= 1.01%
We will use this rate for preparing amortization table.
The amortization table for $450 payment will be
| no of payment | payment | principal | interest | balance |
| 1 | ₹ 450.00 | ₹ 245.93 | ₹ 203.52 | ₹ 19,700.00 |
| 2 | ₹ 450.00 | ₹ 248.42 | ₹ 201.03 | ₹ 19,250.00 |
| 3 | ₹ 450.00 | ₹ 250.93 | ₹ 198.52 | ₹ 18,800.00 |
| 4 | ₹ 450.00 | ₹ 253.46 | ₹ 195.99 | ₹ 18,350.00 |
| 5 | ₹ 450.00 | ₹ 256.02 | ₹ 193.43 | ₹ 17,900.00 |
| 6 | ₹ 450.00 | ₹ 258.61 | ₹ 190.84 | ₹ 17,450.00 |
| 7 | ₹ 450.00 | ₹ 261.22 | ₹ 188.23 | ₹ 17,000.00 |
| 8 | ₹ 450.00 | ₹ 263.86 | ₹ 185.59 | ₹ 16,550.00 |
| 9 | ₹ 450.00 | ₹ 266.52 | ₹ 182.93 | ₹ 16,100.00 |
| 10 | ₹ 450.00 | ₹ 269.21 | ₹ 180.23 | ₹ 15,650.00 |
| 11 | ₹ 450.00 | ₹ 271.93 | ₹ 177.52 | ₹ 15,200.00 |
| 12 | ₹ 450.00 | ₹ 274.68 | ₹ 174.77 | ₹ 14,750.00 |
| 13 | ₹ 450.00 | ₹ 277.45 | ₹ 172.00 | ₹ 14,300.00 |
| 14 | ₹ 450.00 | ₹ 280.26 | ₹ 169.19 | ₹ 13,850.00 |
| 15 | ₹ 450.00 | ₹ 283.09 | ₹ 166.36 | ₹ 13,400.00 |
| 16 | ₹ 450.00 | ₹ 285.95 | ₹ 163.50 | ₹ 12,950.00 |
| 17 | ₹ 450.00 | ₹ 288.83 | ₹ 160.61 | ₹ 12,500.00 |
| 18 | ₹ 450.00 | ₹ 291.75 | ₹ 157.70 | ₹ 12,050.00 |
| 19 | ₹ 450.00 | ₹ 294.70 | ₹ 154.75 | ₹ 11,600.00 |
| 20 | ₹ 450.00 | ₹ 297.67 | ₹ 151.77 | ₹ 11,150.00 |
| 21 | ₹ 450.00 | ₹ 300.68 | ₹ 148.77 | ₹ 10,700.00 |
| 22 | ₹ 450.00 | ₹ 303.72 | ₹ 145.73 | ₹ 10,250.00 |
| 23 | ₹ 450.00 | ₹ 306.78 | ₹ 142.66 | ₹ 9,800.00 |
| 24 | ₹ 450.00 | ₹ 309.88 | ₹ 139.57 | ₹ 9,350.00 |
| 25 | ₹ 450.00 | ₹ 313.01 | ₹ 136.44 | ₹ 8,900.00 |
| 26 | ₹ 450.00 | ₹ 316.17 | ₹ 133.27 | ₹ 8,450.00 |
| 27 | ₹ 450.00 | ₹ 319.37 | ₹ 130.08 | ₹ 8,000.00 |
| 28 | ₹ 450.00 | ₹ 322.59 | ₹ 126.85 | ₹ 7,550.00 |
| 29 | ₹ 450.00 | ₹ 325.85 | ₹ 123.60 | ₹ 7,100.00 |
| 30 | ₹ 450.00 | ₹ 329.14 | ₹ 120.31 | ₹ 6,650.00 |
| 31 | ₹ 450.00 | ₹ 332.47 | ₹ 116.98 | ₹ 6,200.00 |
| 32 | ₹ 450.00 | ₹ 335.83 | ₹ 113.62 | ₹ 5,750.00 |
| 33 | ₹ 450.00 | ₹ 339.22 | ₹ 110.23 | ₹ 5,300.00 |
| 34 | ₹ 450.00 | ₹ 342.64 | ₹ 106.81 | ₹ 4,850.00 |
| 35 | ₹ 450.00 | ₹ 346.10 | ₹ 103.34 | ₹ 4,400.00 |
| 36 | ₹ 450.00 | ₹ 349.60 | ₹ 99.85 | ₹ 3,950.00 |
| 37 | ₹ 450.00 | ₹ 353.13 | ₹ 96.32 | ₹ 3,500.00 |
| 38 | ₹ 450.00 | ₹ 356.70 | ₹ 92.75 | ₹ 3,050.00 |
| 39 | ₹ 450.00 | ₹ 360.30 | ₹ 89.15 | ₹ 2,600.00 |
| 40 | ₹ 450.00 | ₹ 363.94 | ₹ 85.51 | ₹ 2,150.00 |
| 41 | ₹ 450.00 | ₹ 367.61 | ₹ 81.83 | ₹ 1,700.00 |
| 42 | ₹ 450.00 | ₹ 371.33 | ₹ 78.12 | ₹ 1,250.00 |
| 43 | ₹ 450.00 | ₹ 375.08 | ₹ 74.37 | ₹ 800.00 |
| 44 | ₹ 450.00 | ₹ 378.87 | ₹ 70.58 | ₹ 350.00 |
| 45 | ₹ 450.00 | ₹ 382.69 | ₹ 66.76 | ₹ -100.00 |
It will take 45 months to pay the loan
Get Answers For Free
Most questions answered within 1 hours.