Question

Problem 5 - Varying Payments and Equal Principal Repaid

McKenna has a loan to be repaid by 17 annual payments at an effective annual interest rate of 3%. Payments 1-11 are $600 each, payments 12-15 are $340 each, and the last 2 payments are $570 each. Calculate the interest portion in McKenna's 14 th payment.

I14=

Answer 1

B2 present present value year payment value factor of payments @3% | to be made opening year balance interest principal closing part balance part 1 $600.00 0.9709 582.52 2 $600.00 0.9426 565.56 1 $7,164.64 $600.00 $214.94 $ 385.06 $6,779.58 2 6,779.58 $600.00 $203.39$396.61 $6,382.97 Interest portion in McKenna's 14th payment (114) $50.36 3 $600.00 0.9151 549.08 3 $6,382.97 $600.00 $191.49$408.51 $5,974.46 4 $600.00 0.8885533.09 5 $600.00 0.8626$517.57 6 $600.00 0.8375 502.49 7 $600.00 0.8131 487.85 8 $600.00 0.7894473.65 9 $600.00 0.7664 459.85 10 $600.00 0.7441 S 446.46 11 $600.00 0.7224S 433.45 12 $340.00 0.7014 238.47 13 $340.00 0.6810 231.52 14 $340.00 0.6611224.78 15 $340.00 0.6419 218.23 $ 355.21 17 $570.00 0.6050 344.86 $ 7,164.64 4 $5,974.46 $600.00$179.23 $420.77 $5,553.69 5 5,553.69 $600.00 $5,120.31 6 $5,120.31 $600.00 $153.61 $446.39 7 $4,673.91 $600.00 $140.22 $459.78 $4,214.13 8 $4,214.13 $600.00 $126.42 $473.58 $3,740.56 9 $3,740.56 $600.00 $112.22 $487.78 $3,252.77 10 $3,252.77 $600.00 $ 97.58 $502.42 $2,750.36 11 $2,750.36 $600.00 82.51 $517.49 $2,232.87 12 $2.232.87 $340.00 S66.99 $273 13 $1,959.85 $340.00 58.80 $281 14 $1,678.65 $340.00 $ 50.36 $289.64 $1,389.01 15 $1,389.01 $340.00 41.67 $ 298.33 $1,090.68 16 $1,090.68 $570.00 $ 32.72 $537.28 553.40 $166.61 $433.39 673.91 12 13 14 15 16 17 570.000.6232 19 20 21 loan amount-

for formulas and calculations, refer to the image below -

present value present value of payments to be made opening balance payment interest part principal part closing balance year payment 1/1.03 C3 D3 D3/1.03 C4 D4 13.3% J3-K3 13-L3 2 600 -C4-K4 14-L4 Interest portion in McKenna's 14th payment (114)- D4/1.03 C5 D5 D5/1.03 C6 D6 D6/1.03 C7 D7 C5-K5 C6-K6 C7-K7 C8-K8 -C9-K9 -C10-K10 -C11-K11 -C12-K12 C13-K13 C14-K14 C15-K15 -C16-K16 -C17-K17 C18-K18 -C19-K19 =15*3% 16-L6 17*3% -18*3% =19*3% -1 10*396 -|11*396 112*396 5 600 6 600 -18-L8 D8/1.03C9*D9 D9/1.03 C10 D10 D10/1.03-C11*D11 D11/1.03C12 D12 D12/1.03C13*D13 D13/1.03C14 D14 D14/1.03C15* D15 D15/1.03C16 D16 -D16/1.03-C17*D17 D17/1.03C18 D18 -D18/1.03C19*D19 8 600 9 600 10 600 11 600 12 340 13 340 14 340 15 340 16 570 17 570 -C10 -C11 -C12 -C13 -C14 110-L10 111-L11 112-L12 113-L13 114-L14 115-L15 116-L16 117-L17 -118-L18 119-L19 9M10 10 M11 11 M12 12 M13 13 M14 14 M15 15 M16 16 M17 17 M18 12 15 =115*396 C16 -C17 弍18 -C19 16 1-117*3% 118*396 =119"3% loan amount- -SUM(E3:E19

Kindly ask in comments if you have any query.

kindly give a thumbs up if satisfied with the solution. It will be highly appreciated.