Question

Assume that you are planning on purchasing a new car. You are considering financing the $40,000 purchase price using a car loan arranged through the car dealership. The terms of the loan are: 8 years of fixed monthly payments, and 2.4% quoted annual periodic rate of interest (this will need to be converted to a monthly rate by dividing the annual rate by 12). Assuming the loan will be completely paid off by the end of the 8 years, determine the monthly payment associated with loan, and then prepare an amortization table in a similar way as shown in the class Excel example. Include all of the months in your table, and make sure you show not only the beginning and ending balance for each month, but also the monthly payment and the breakdown between the part used to pay interest and the part used to pay down the principal. (Hint: use functions and formulas in the first row, and the beginning balance in the second row, in a way that will allow you to copy and paste to the remaining cells of your table)

Answer 1

The monthly payment / instalment associated with the loan is $ 458.36.

The solution is provided below on left side along with formula help to construct the Table on the right side:

G K K L L M N O P 4 А в 1 Solution 2 1 Purchase Price 3 2 Loan Term 4 3 Number of months 5 4 Interest Rate 6 5 Monthly Int. rate 7) 6 Monthly payment $ 40,000 8 years 96 months 2.40% p.a 0.2% p.m $-458.36 H I J Formula Help 1 Purchase Price $ 40,000 2 Loan Term 3 Number of months =13*12 Interest Rate 2.40% 5 Monthly Int. rate =25/12 6 Monthly instalment =PMT(J6,14,12) Month Opening Loan balance Interest Monthly | Loan Payment Amortization Closing Loan Balance Month Opening Loan balance Interest Monthly Loan Payment Amortization closing Loan Balance 9 10 11 1 =H10+1 =H11+1 = 2 =M10 --110*$J$6 --111*$J$6 =J7 = 10 = 10-J10 =K11-111 =110+L10 =111+L11 copy 2nd row to below rows 13 15 16 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 96 months 40,000.00 $ -80.00 $ -458.36 $ 39,621.64 $ -79.24 $ -458.36 $ 39,242.52 $ -78.49 $ -458.36 $ 38,862.64 $ -77.73 $ -458.36 $ 38,482.01 $ -76.96 $ -458.36 $ 38,100.61 $ -76.20 $ -458.36 $ 37,718.45 $ -75.44 $ -458.36 $ 37,335.53 $ -74.67 $ -458.36 $ 36,951.84 $ -73.90 $ -458.36 $ 36,567.38 $ -73.13 $ -458.36 $ 36,182.15 $ -72.36 $ -458.36 $ 35,796.16 $ -71.59 $ -458.36 $ 35,409.39 $ -70.82 $ -458.36 $ 35,021.85 $ -70.04 $ -458.36 $ 34,633.53 $ -69.27 $ -458.36 $ 34,244.43 $ -68.49 $ -458.36 $ 33,854.56 $ -67.71 $ -458.36 $ Sheet1 + -378.36 $ 39,621.64 -379.12$ 39,242.52 379.88 $ 38,862.64 -380.64 $ 38,482.01 -381.40 $ 38,100.61 -382.16 $ 37,718.45 -382.92 $ 37,335.53 -383.69 $ 36,951.84 -384.46 $ 36,567.38 385.23 $ 36,182.15 -386.00 $ 35,796.16 -386.77 $ 35,409.39 -387.54 $ 35,021.85 -388.32 $ 34,633.53 -389.09 $ 34,244.43 -389.87 $ 33,854.56 -390.65 $ 33,463.91 21 22 23 24 25 26

C D NO P. AB 18 S 33,463.91 $ -66.93 $ -458.36 $ 19 $ 33,072.48 $ -66.14 $ -458.36 $ 20 $ 32,680.26 $ -65.36 $ -458.36 S 30 21 $ 32,287.26 $ -64.57 $ -458.36 $ 31 22 $ 31,893.47 $ -63.79$ -458.36 $ 32 23 $ 31,498.90 $ -63.00 $ -458.36 $ 24 $ 31,103.54 $ -62.21 $ -458.36 $ 25 $ 30,707.38 $ -61.41 $ -458.36 $ 35 26 $ 30,310.44 $ -60.62 $ -458.36 $ 36 27 $ 29,912.70 $ -59.83 $ -458.36 28 $ 29,514.16 $ -59.03 $ -458.36 $ 38 29 $ 29,114.83 $ -58.23 $ -458.36 $ 39 30 $ 28,714.70 $ -57.43 $ -458.36 $ 40 31 $ 28,313.76 $ -56.63 $ -458.36 $ 32 $ 27,912.03 $ -55.82 $ -458.36 $ 33 $ 27,509.49 $ -55.02 $ -458.36 $ 43 34 $ 27,106.15 $ -54.21 $ -458.36 $ 44 35 $ 26,702.00 $ -53.40 $ -458.36 $ 45 36 $ 26,297.04 $ -52.59 $ -458.36 $ 46 37 $ 25,891.28 $ -51.78 $ -458.36 $ 38 $ 25,484.70 $ -50.97 $ -458.36 $ 39 S 25,077.31 $ -50.15 $ -458.36 $ 40 $ 24,669.10 $ -49.34 $ -458.36 $ 41 $ 24,260.08 $ -48.52 $ -458.36 $ 42 $ 23,850.24 $ -47.70 $ -458.36 $ 43 $ 23,439.58 $ -46.88 $ -458.36 $ 44 $ 23,028.09 $ -46.06 $ -458.36 $ 54 45 $ 22,615.79$ -45.23 $ -458.36 $ -391.43 $ 33,072.48 -392.22 $ 32,680.26 -393.00 $ 32,287.26 -393.79$ 31,893.47 -394.57 $ 31,498.90 -395.36 $ 31,103.54 -396.15 $ 30,707.38 -396.95 $30,310.44 -397.74 $ 29,912.70 -398.54 $ 29,514.16 -399.33 $ 29,114.83 400.13 $ 28,714.70 400.93 $ 28,313.76 -401.73 $ 27,912.03 -402.54 $ 27,509.49 -403.34 $ 27,106.15 404.15$ 26,702.00 -404.96 $ 26,297.04 -405.77 $ 25,891.28 406.58 $ 25,484.70 -407.39 $ 25,077.31 408.21 $ 24,669.10 - 409.02 $ 24,260.08 409.84 $ 23,850.24 410.66 $ 23,439.58 -411.48 $ 23,028.09 412.30 $ 22,615.79 413.13 $ 22,202.66 Sheet1

н L M N O P 55 56 57 58 59 60 61 62 63 64 65 66 67 68 46 $ 47 $ 48 $ 49 $ 50 $ 51 $ 52 $ 53 $ 54 $ 55 $ 56 $ 57 $ 58 $ 59 $ 60 $ 61 $ 62 $ 63 $ 64 $ 65 $ 22,202.66 $ -44.41 $ -458.36 $ 413.96 $ 21,788.70 21,788.70 $ -43.58 $ -458.36 $ 414.78 $ 21,373.92 21,373.92 $ -42.75 $ -458.36 $ 415.61 $ 20,958.31 20,958.31 $ -41.92 $ -458.36 $ 416.44 $ 20,541.86 20,541.86$ -41.08 $ -458.36 $ -417.28 $ 20,124.58 20,124.58 $ -40.25 $ -458.36 -418.11 $ 19,706.47 19,706.47 $ -39.41 $ -458.36 -418.95 $ 19,287.52 19,287.52 $ -38.58 $ -458.36S 419.79 $ 18,867.74 18,867.74 $ -37.74 $ -458.36 -420.63 $ 18,447.11 18,447.11 $ -36.89 $ -458.36 $ -421.47 $ 18,025.65 18,025.65 $ -36.05 $ -458.36 $ -422.31 $ 17,603.34 17,603.34 $ -35.21 $ -458.36 $ -423.15 $ 17,180.18 17,180.18 $ -34.36 $ -458.36 $ -424.00 $ 16,756.18 16,756.18 $ -33.51 $ -458.36 $ .424.85 $ 16,331.33 16,331.33 $ -32.66 $ -458.36 $ -425.70 $ 15,905.63 15,905.63 $ -31.81 $ -458.36 $ -426.55 $ 15,479.08 15,479.08 $ -30.96 $ -458.36 $ 427.40 $ 15,051.68 15,051.68 $ -30.10 $ -458.36 $-428.26 $ 14,623.42 14,623.42 $ -29.25 $ -458.36 $ 429.11 $ 14,194.31 14,194.31 $ -28.39 $ -458.36 $ .429.97 $ 13,764.34, 13,764.34 $ -27.53 $ -458.36 $ -430.83 $ 13,333.50 13,333.50 $ -26.67 $ -458.36 $ -431.69 $ 12,901.81 12,901.81 $ -25.80 $ - 458.36 $ 432.56 $ 12,469.25 12,469.25 $ -24.94 $ -458.36 $ -433.42 $ 12,035.83 12,035.83 $ -24.07 $ -458.36 $ -434.29 $ 11,601.54 11,601.54 $ -23.20 $ -458.36 $ -435.16 $ 11,166.38 11,166.38 $ -22.33 $ -458.36 $ 436.03 $ 10,730.35 10,730.35 $ -21.46 $ -458.36 S -436.90 $ 10,293.45 Sheet1 71 72 73 74 67 68 69 70 71 72 73 $ $ $ S $ $ $ 81 82

ор 83 $ $ $ $ $ $ 86 87 88 A 74 75 76 77 78 79 80S 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 C D 10,293.45 $ -20.59 $ -458.36 $ 437.77 $ 9,855.68 9,855.68 $ -19.71 $ -458.36 $ -438.65 $ 9,417.03 9,417.03 $ -18.83 $ -458.36 $ -439.53 $ 8,977.50 8,977.50 $ -17.96 $ -458.36 $ 440.41 $ 8,537.10 8,537.10 $ -17.07 $ -458.36 $ -441.29 $ 8,095.81 8,095.81 $ -16.19 $ -458.36 $ -442.17 $ 7,653.64 7,653.64 $ -15.31 $ -458.365 -443.05 $ 7,210.59 7,210.59 $ -14.42 $ -458.36 $ -443.94 $ 6,766.65 6,766.65 $ -13.53 $ -458.36 $ -444.83 $ 6,321.82 6,321.82 $ -12.64 $ -458.36 $_-445.72 $ 5,876.10 5,876.10 $ -11.75 $ -458.36 $ -446.61 $ 5,429.49 5,429.49 $ -10.86 $ -458.36 $ -447.50 $ 4,981.99 4,981.99 $ -9.96 $ -458.36 $ -448.40 $ 4,533.59 4,533.59 $ -9.07 $ -458.36 $ -449.29 $ 4,084.30 4,084.30 $ -8.17 $ -458.36 $ 450.19 $ 3,634.11 3,634.11 $ -7.27 $ -458.36 $ 451.09$ 3,183.01 3,183.01 $ -6.37 $ -458.36 $ 452.00 $ 2,731.02 2,731.02 $ -5.46 $ -458.36 $ .452.90 $2,278.12 2,278.12 $ -4.56 $ -458.36 $ -453.80 $ 1,824.31 1,824.31 $ -3.65 $ -458.36 $ 454.71 $ 1,369.60 1,369.60 $ -2.74 $ -458.36S 455.62 $_913.98 913.98 $ 1.83 $ -458.36 $ .456.53 $ 457.45 45745 $ -0.91 $ -458.36 $ 457,45 $ 0.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 94 95 96 98 99 100 101 102 103 104 105 106 107 108 109 110 Sheet1