Question

Part 1. Create a loan amortization table (50 points) You are considering purchasing a SUV with a sticker price of $42,270 (nonnegotiable, with a 20% down payment required). You have cash to cover the down payment and you want to make monthly payments over five years to cover the remainder of the cost. The highest monthly payment that you can afford is $700. The credit union has agreed to loan you the money at a 4.23% annual interest rate la: Create an amortization table in Excel and label the tab "Bank PMT". Answer the following question a) What is your monthly payment, and can you afford to purchase the car? b) How much interest are you paying over the life of the loan?

Answer 1

SUV Price 42270 20% Down pay 8454 Loan 33816 interst rate 0.35% Rate 60 Years npvr Loan amount 33816 pv Payment per 1 per BANK PPMT & IPMT Montly Principal $ Montly Interst $ Loan interst rate Periods Per Years amount $ 12 -£507.09 1 0.35% 33816 -£119.20 -£508.88 -£117.41 2 0.35% 5 33816 12 12 33816 -£510.67 0.35% 5 -£115.62 12 0.35% 5 33816 -£512.47 -£113.82 5 0.35% 5 33816 12 -£514.28 -£112.01 12 0.35% 6 5 33816 -£516.09 £110.20 33816 -£108.38 7 0.35% 5 12 -£517.91 12 0.35% 5 33816 -£519.73 -£106.56 -£521.57 0.35% 5 33816 12 -£104.72 10 12 33816 0.35% 5 -£523.40 -£102.89 -£525.25 11 0.35% 5 33816 12 -£101.04 -£527.10 12 0.35% 5 33816 12 -£99.19 12 13 0.35% 5 33816 -£528.96 -£97.33 14 0.35% 5 33816 12 -£530.82 -£95.47 12 15 0.35% 5 33816 -£532.69 -£93.60 16 0.35% 5 33816 12 -£534.57 -£91.72 12 33816 17 0.35% 5 -£536.46 -£89.83 18 0.35% 5 33816 12 -£538.35 -£87.94 0.35% 19 5 33816 12 -£540.24 -£86.04 12 -£84.14 20 0.35% 5 33816 -£542.15 Ln Ln Ln un 4

0.35% 21 33816 12 -£544.06 -£82.23 12 0.35% 33816 -£545.98 -£80.31 23 0.35% 5 33816 12 -£547.90 £78.39 33816 24 0.35% 12 -£549.83 -£76.46 -£74.52 25 0.35% 5 33816 12 -£551.77 12 -£72.57 26 0.35% 33816 -£553.72 27 0.35% 5 33816 12 -£555.67 -£70.62 28 -£68.66 0.35% 33816 12 -£557.63 29 0.35% 33816 12 -£559.59 -£66.70 0.35% 30 5 33816 12 -£561.57 -£64.72 31 0.35% 33816 12 -£563.55 -£62.74 -£60.76 32 0.35% 5 33816 12 -£565.53 12 33 0.35% 5 33816 -£567.53 -£58.76 0.35% 34 5 33816 12 £569.53 -£56.76 33816 -£571.53 35 0.35% 5 12 -£54.76 -£573.55 36 0.35% 5 33816 12 -£52.74 12 -£50.72 37 0.35% 33816 -£575.57 33816 38 0.35% 5 12 -£577.60 -£48.69 33816 39 0.35% 5 12 -£579.63 -£46.65 12 40 0.35% 5 33816 -£581.68 -£44.61 41 0.35% 5 33816 12 -£583.73 £42.56 33816 42 0.35% 5 12 -£585.79 -£40.50 43 33816 0.35% 5 12 -£587.85 -£38.44 12 44 -£36.37 0.35% 5 33816 -£589.92 -£592.00 45 0.35% 5 33816 12 -£34.29

0.35% 12 46 5 33816 -£594.09 -£32.20 47 0.35% 33816 12 -£596.18 £30.11 48 0.35% 5 33816 12 -£598.29 -£28.00 -£25.89 49 0.35% 5 33816 12 -£600.39 12 -£23.78 50 0.35% 33816 £602.51 -£21.65 51 0.35% 5 33816 12 -£604.63 33816 -£19.52 52 0.35% 12 -£606.77 33816 -£17.38 53 0.35% 5 12 -£608.90 -£15.24 54 0.35% 33816 12 -£611.05 55 0.35% 5 33816 12 -£613.21 -£13.08 56 0.35% 5 33816 12 £615.37 £10.92 12 33816 -£8.75 57 0.35% -£617.54 58 0.35% 5 33816 12 -£619.71 -£6.58 -£621.90 59 0.35% 5 33816 12 -£4.39 -£624.09 -£2.20 60 0.35% 5 33816 12 ТОTAL -£33,816.00 -3,761.35 Ln Ln Ln Ln LO Ln LO LO LO Ln Ln Ln LO