Simple Loan Calculator Loan amount Annual interest rate Loan period in years Start date of loan Enter values $ 5,000.00 6.50% 3 1/1/21 $ Monthly payment Number of payments Total interest Total cost of loan 153.25 36 516.82 5,516.82 $ $ No. 1 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Payment Date 2/1/21 $ 3/1/21 $ 4/1/21 5/1/21 $ 6/1/21 $ 7/1/21 $ 8/1/21 $ 9/1/21 $ 10/1/21 $ 11/1/21 $ 12/1/21 $ 1/1/22 $ 2/1/22 $ 3/1/22 $ 4/1/22 $ 5/1/22 $ 6/1/22 $ 7/1/22 $ 8/1/22 $ 9/1/22 $ 10/1/22 $ 11/1/22 $ 12/1/22 $ 1/1/23 $ 2/1/23 $ 3/1/23 $ 4/1/23 $ 5/1/23 $ 6/1/23 $ 7/1/23 $ 8/1/23 $ 9/1/23 10/1/23 11/1/23 12/1/23 1/1/24 Beginning Balance 5,000.00 4,873.84 4,746.99 4,619.46 4,491.24 4,362.32 4,232.70 4,102.39 3,971.36 3,839.63 3,707.18 3,574.02 3,440.13 3,305.52 3,170.18 3,034.11 2,897.30 2,759.75 2,621.45 2,482.40 2,342.61 2,202.05 2,060.73 1,918.65 1,775.80 1,632.17 1,487.77 1,342.58 1,196.61 1,049.85 902.29 753.93 604.77 454.80 304.02 152.42 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Payment 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 153.25 Principal $ 126.16 126.85 $ 127.53 128.22 $ 128.92 $ 129.62 130.32 131.02 131.73 132.45 $ 133.16 133.89 $ 134.61 $ 135.34 136.07 136.81 137.55 138.30 $ 139.05 139.80 $ 140.56 $ 141.32 $ 142.08 $ 142.85 143.63 $ 144.40 145.19 145.97 $ 146.76 147.56 $ 148.36 $ 149.16 149.97 $ 150.78 151.60 $ 152.42 Ending Balance 4,873.84 4,746.99 ,619.46 4,491.24 4,362.32 4,232.70 4,102.39 3,971.36 3,839.63 3,707.18 3,574.02 3,440.13 3,305.52 3,170.18 3,034.11 2,897.30 2,759.75 2,621.45 2,482.40 2,342.61 2,202.05 2,060.73 1,918.65 1,775.80 1,632.17 1,487.77 1,342.58 1,196.61 1,049.85 902.29 753.93 604.77 454.80 304.02 152.42 0.00 Interest 27.08 $ 26.40 $ 25.71 $ 25.02 $ 24.33 $ 23.63 $ 22.93 $ 22.22 $ 21.51 $ 20.80 $ 20.08 $ 19.36 $ 18.63 $ 17.90 $ 17.17 16.43 $ 15.69 $ 14.95 14.20 $ 13.45 $ 12.69 $ 11.93 $ 11.16 $ 10.39 $ 9.62 8.84 $ 8.06 $ 7.27 $ 6.48 5.69 $ 4.89 $ 4.08 $ 3.28 2.46 $ 1.65 $ 0.83 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Assignment 13-1 Loan Amortization Activity Instructions: Use the Loan Amortization Excel spreadsheet to answer the following questions in the five scenarios, Scenario 1. Imagine that you are a first-time home buyer. Rather than buy a new home, you decide to purchase an existing one. You find a nice home in a good location for $163,125. You have a down payment of 20% (which is $32,625) and you decide on a 30-year fixed rate loan. This means that you need to borrow a loan amount of $130,500. You shop around and learn that you qualify for an interest rate of 4.8%. Your monthly (12) mortgage payments will begin next year on January 1st. Use the loan amortization template excel sheet to answer the questions by entering the above amounts indicated in each question in cells E3-E6. 1. What are your scheduled monthly payments on a $130,500 loan at 4.8% over 30 years? See cell E8 in the Excel spreadsheet. > 4112 $ 191.25 SOL 900. 4 D E T H Simple Loan Calculator LANTER $ 5.000.0 Amal irresirate 6.504 Loan pertad in years ---- Start Weinan Month Denment S Number of ements 15 Tulat 516.83 Tosal of loan $ 5.516.82 CE Beginning Ending Kelere PERTI PTING SANT 1 2/1/21 5.000.00 151.25$ 126.16 27.03 $ 4.173.84 4 2 421, 151.25 26,85 $ 26.00 $ 4.746. 1 4,741.WS 191.255 T27,115 25.71 $ 1.419.45 4 5:102: s 4.478.46 $ 153.5 25.00 $ 4,411.24 3 4451,24 151.25 S 120.97 24.333 4.362.32 6 7 1342.32 $ 19125 29.62 21.03 $ 1.212. 7 1625 4212.11 191.35 $ 22.03 $4,100.8 3 0:12 4.102.39 152.25 . 121.00 22.22 $ 3.974.3 10/12 3.971.36 $ 131.73 3.839.63 10 191.25S 112.0 20.71 $ 3.207.18 12-1/2: 3.77.18 151.25 13.16 20.63 $3,574.02 42 12 1417 3.574.00 153.25 133.89 19.36 $ 3.440.13 3.10.133 193.25 $ 134.61 16.63 $ 3.16657 14 3:1:22 37:5.52 151.35 $ 135.34 $ 17.91 $ 3,170.18 7 15 13 41/22 3.170.18 153.25$ 136.07 $ 17.17 $ 3.014.11 16 19 511.22 3.03.11 153.25 136.81 16.0 $ 2.187.0 19.0 18 7:1/22 $ ? 2.759.75$ 153.25 14.965 2.621.6 19 8.1.225 2.621.95 $ 153.255 139.05 14.20 21 2,112,40$ 131.25 S 101/22 $ 2.442.61 $ 151.25 $ 140.59 $ 12.05 2,200.1 11/1/22 2.202.06 $ 153.25 $ 141. 11.93 $ 2.063.73 12/1/72 2,090.73 153.25 S 112.00 24 2 11.16 $ 1.91605 191.1 1.93 10.9 $ 1,175.211 1.775.80 $ 153.25$ 9.62 $ 1.612.17 26 153.25 144.00 $ 3.84 1.487.77 41203 1,487,77 $ 1.33 $ 149.19 KIX 1.142.1 23 5:1/23 $ 1.342.58 $ 151.25 $ 145.97 $ 7.27 $ 1,176.01 6/1/23 $ 1.196.61 5 151.25 146.76 $ 6.43 7/1/21 133.25$ 151.35 14. 751.93 9/1/23 $ 753.93 $ 153.25$ 149.16 $ 4.08 $ 604.77 10/1/23 $ 153.25 $ 149.97 $ 1.22 $ 454.80 11:1121 154.IS 191.25 $ 190, 2.45 104. 12:1/23 $ 384.00 $ 153.25 $ 151.60 $ 1.66 $ 152.0 1:1/24 $ 153.25 $ 152.4 S 0.83 $ 0.00 10. 21.51 $ $ 2. Look at the first payment you will make (row 13 in the excel sheet). How much of the payment goes toward interest and how much toward principal? 311523 5 1,412.17 106.15 3. How much total interest will you pay over the course of the loan on $130,500 at 4.8% over 30 years? See cell E10. Loan Calculator 4. Considering total interest paid, what is the total cost of the home? To answer, add the total price of the home to the total interest paid. + EI