Please use Excel financial functions or algebraic time value of money equations
ID: 2819719 • Letter: P
Question
Please use Excel financial functions or algebraic time value of money equations to answer these questions in your spreadsheet
Part 3. Let’s buy a car?
You’re looking to buy a fully loaded 2018 Kia Optima SX Turbo fully loaded with tech, surround sunroof and leather seats at a price of $30,000. Being a poor college student, you have cash to pay taxes, title, license & fees but your parents offer to give you 10% of the price, $3000, as a down payment and you need to finance the remainder of $27,000. You smartly researched your finance options and got preapproval at a 3.24% APR for 60 months on your own so you’re not at the mercy at depending on financing from the car dealership. Kia is offering 0% APR financing with $1500 cash back or $4000 cash back. They are practically giving these cars away! The cash back amounts under both options would be used as an additional down payment reducing the amount that you need to finance (borrow). You would use your 3.24% APR pre-approved financing if you elect the $4000 cash back option. Answer the following questions.
1. What would be your monthly car loan payment under the Kia’s 0% APR financing offer with $1500 (assume a 60-month loan term)?
2. What would be your monthly car loan payment under the Kia’s $4000 cash back offer and your 3.24% APR pre-approved financing (assume a 60-month loan term)?
3. 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 $4000 cash back offer as you would with the 0% APR financing offer with $1500 cash back?
4. Let’s assume you go with the offer in question #2. Construct an amortization schedule for the loan for all 60 monthly payments. What is your loan balance after 36 months?
5. Now let’s assume you were willing to make a monthly car loan payment of $475. Re-do your amortization schedule from question#4 with this monthly payment. How long will it take to pay off the loan with the $475 monthly payment?
Explanation / Answer
5 Due to word limitation i am not able to post the last table, but if you prepare another amortization schedule with $475 instead of $416 you will get that loan will be paid in 52months.
We know interest rate is 0% so we just need to make principal payments of $25,500 as we know we will get 1,500 cashback and 3,000 from parents So effective we need to pay 25,500 in 60months. So, our monthly installment will be :- 25,500/60 $425 per month 2. Kia is giving us 4,000 cashback offer so that means we need to pay only 26,000 to Kia and we are also getting 3,000 from parents to make initial downpayment. So, our loan amount will be 23,000. We need pay 3.24% APR for this loan so interest per month = 3.24/12 i.e.0.27% Now, using finance calculator :- I/Y = 0.27% N = 60 PV = 23,000 FV = 0 Compute PMT We are getting $416 3. We know for question 1 we need to make payment of 425 per month and now we need to calculate APR Now, using finance calculator :- N = 60 PV = 23,000 FV = 0 PMT = 425 Compute I/Y 0.345% So, APR will be 0.345%*12 4.14% 4. Amortization Schedule Month Opening Principal Interest @0.27% Payment per month Closing Balance 1 23,000 62 416 22,646 2 22,646 61 416 22,291 3 22,291 60 416 21,935 4 21,935 59 416 21,579 5 21,579 58 416 21,221 6 21,221 57 416 20,862 7 20,862 56 416 20,503 8 20,503 55 416 20,142 9 20,142 54 416 19,780 10 19,780 53 416 19,418 11 19,418 52 416 19,054 12 19,054 51 416 18,690 13 18,690 50 416 18,324 14 18,324 49 416 17,958 15 17,958 48 416 17,590 16 17,590 47 416 17,221 17 17,221 46 416 16,852 18 16,852 46 416 16,481 19 16,481 44 416 16,110 20 16,110 43 416 15,737 21 15,737 42 416 15,364 22 15,364 41 416 14,989 23 14,989 40 416 14,614 24 14,614 39 416 14,237 25 14,237 38 416 13,860 26 13,860 37 416 13,481 27 13,481 36 416 13,102 28 13,102 35 416 12,721 29 12,721 34 416 12,339 30 12,339 33 416 11,957 31 11,957 32 416 11,573 32 11,573 31 416 11,188 33 11,188 30 416 10,802 34 10,802 29 416 10,416 35 10,416 28 416 10,028 36 10,028 27 416 9,639 37 9,639 26 416 9,249 38 9,249 25 416 8,858 39 8,858 24 416 8,466 40 8,466 23 416 8,073 41 8,073 22 416 7,678 42 7,678 21 416 7,283 43 7,283 20 416 6,887 44 6,887 19 416 6,489 45 6,489 18 416 6,091 46 6,091 16 416 5,691 47 5,691 15 416 5,291 48 5,291 14 416 4,889 49 4,889 13 416 4,486 50 4,486 12 416 4,082 51 4,082 11 416 3,677 52 3,677 10 416 3,271 53 3,271 9 416 2,864 54 2,864 8 416 2,456 55 2,456 7 416 2,046 56 2,046 6 416 1,636 57 1,636 4 416 1,224 58 1,224 3 416 812 59 812 2 416 398 60 398 1 416 (17) Loan balance after 36 months will be $9,639