A B D E G H 1 Homework 5: Mortgage Calculation ($100,000 Loan Amount at 6% for 30 years) 2 3 =PMT(6%/2,360,-100000) 4 No. Mo Balance Interest Payment Pay-off 5 1 JAN 8 2 FEB 7 3 MAR 4 APR 9 5 MAY 10 6 JUN 11 7 JUL 12 8 AUG 13 9 SEP 14 10 OCT 15 11 NOV 16 12 DEC 17 Year 1 Total $0.00 $0.00 $0.00 18 19 (The following is for Year 12:) 20 No. Mo Balance Interest Payment Pay-off 21 133 JAN $81,451.48 $407 26 $599.55 $192.29 22 134 FEB $81,259.19 $406.30 $599.55 $193.25 23 135 MAR $81,065.94 $405.33 $599.55 $194.22 24 136 APR $80,871.72 $404.36 $599.55 $195.19 25 137 MAY $80,676.53 $403.38 $599.55 $196.17 26 138 JUN $80,480.36 $402.40 $599.55 $197.15 27 139 JUL $80,283.21 $401.42 $599.55 $198.13 28 140 AUG $80,085.08 $400.43 $599.55 $199.13 29 SEP $79,885.95 $399.43 $599.55 $200.12 30 142 OCT $79,685.83 $398.43 $599.55 $201.12 31 143 NOV $79.484.71 $397.42 $599.55 $202.13 32 144 DEC $79,282.58 $396.41 $599.55 $203.14 =PPMT(6%/12,144,360,100000) 33 Year 12 Total $4,822.57 $7,194.61 $2,372.04 32.97% 34 35 (The following is for the last year: Year 30) 36 No. Mo Balance Interest Payment Pay-off 37 349 JAN $6,966.14 $34.83 $599.55 $564.72 38 350 FEB $6,401.42 $32.01 $599.55 $567.54 39 351 MAR $5,833.88 $29.17 $599.55 $570.38 40 352 APR $5,263.50 $26.32 $599.55 $573.23 41 353 MAY $4,690.26 $23.45 $599.55 $576.10 Sheet1 Sheet2 Sheets + Ready 141 + Paste A BIU a.A IM Merge & Center $ % 9 G414 fx payoff 8 G K Pay-off $564.72 $56754 $570,38 $57323 $576.10 $578.98 $581.87 $584.78 $587.71 $590.65 $593.60 $596.57 $6.966.14 96.82% $100,000.00 A B C D E 35 (The following is for the last year: Year 30) 38 No. Balance Interest Payment 37 349 JAN $6,966.14 $34.83 $599 55 35 350 FEB $6.401.42 $32.01 $599.55 39 351 MAR $5,833.88 $29.17 $599.55 40 352 APR $5,263.50 $26.32 $599.55 353 MAY $4,690.26 $23.45 $599.55 42 354 JUN $4,114.16 $20.57 $599.55 43 355 JUL $3.535.18 $17.68 $599.55 44 356 AUG $2.953.31 $14.77 $599.55 45 357 SEP $2,368.53 $11.84 $599.55 46 358 OCT $1,780.82 $8.90 $599.55 47 359 NOV $1,190.17 $5.95 $599.55 48 360 DEC $596.57 $2.98 $599.55 49 Year 30 Total $228.47 $7,194.61 50 51 Total Amount fo 30 years $115,838.19 $215,838.19 52 53 Complete Amortization Schedule 54 No. Mo Balance Interest Payment 55 1 JAN $100,000.00 $500.00 $599.55 58 2 FEB $99,900.45 $499 50 $599.55 57 3 MAR $99,800.40 $499.00 $599.55 58 4 APR $99,699.85 $498.50 $599.55 59 5 MAY $99,598.80 $497.99 $599.55 60 6 JUN $99.497 24 $497 49 $599 55 61 7 JUL $99,395.18 $496.98 $599.55 62 8 AUG $99.292.61 $496.46 $599.55 63 9 SEP $99.189.52 $495.95 $599.55 54 10 OCT $99,085.92 $495.43 $599.55 65 11 NOV $98,981.79 $494.91 $599.55 66 12 DEC $98,877.15 $494 39 $599.55 67 13 JAN $98,771.99 $493.86 $599.55 69 14 FEB $98,666.30 $493,33 $599 55 69 15 MAR $98,580,08 $492.80 $599.55 70 16 APR $98.453.33 $492 27 $599.55 71 17 MAY $98,346.04 $491.73 $599.55 72 18 JUN $98.238 22 $491.19 $599.55 73 19 JUL $98,129.86 $490.65 $599.55 74 20 AUG $98,020.96 $490.10 $599.55 75 21 SEP $97.911.52 $489 56 $599.55 Sheet1 Sheet2 Sheet3 Ready Year 1 + Pay-off $99.55 $100.05 $100.55 $101.05 $101.56 $102 06 $102.57 $103.09 $103.60 $104.12 $104.64 $105.16 $105.69 $106.22 $106.75 $107 28 $107.82 $108.36 $108.90 $109.45 $109.99 N B C D G H L 2 3 85 11 NOV $98,981.79 56 12 DEC $98,877.15 67 13 JAN $98,771.99 68 14 FEB $98,666.30 69 15 MAR $98,560.08 70 16 APR $98,453.33 71 17 MAY $98,346.04 72 18 JUN $98,238.22 73 19 JUL $98,129.86 74 20 AUG $98,020.96 75 21 SEP $97,911.52 76 22 OCT $97,801,53 77 23 NOV $97,690.98 78 24 DEC $97,579.89 79 25 JAN $97,468.24 80 26 FEB $97,356.03 81 27 MAR $97,243.26 82 28 APR $97,129.92 83 29 MAY $97,016.02 84 30 JUN $96.901.55 85 31 JUL $96,786.51 88 32 AUG $96,670.89 87 33 SEP $96,554.69 88 34 OCT $96.437.92 89 35 NOV $96,320.56 90 36 DEC $96,202.61 91 37 JAN $96,084.07 92 38 FEB $95,964.94 93 39 MAR $95,845.21 94 40 APR $95,724.89 95 41 MAY $95,603.96 42 JUN $95.482.43 97 43 JUL $95,360.29 08 44 AUG $95.237.55 90 45 SEP $95,114.18 100 46 OCT $94.990.20 101 47 NOV $94.865.60 102 48 DEC $94,740.38 103 49 JAN $94,614.53 104 50 FEB $94.488.05 105 51 MAR $94 360.94 Sheet1 Sheet2 Ready $494.91 $494.39 $493.86 $493,33 $492.80 $492.27 $491.73 $491.19 $490.65 $490.10 $489.56 $489.01 $488.45 $487.90 $487.34 $486.78 $486.22 $485,65 $485.08 $484,51 $483.93 $483,35 $482.77 $482.19 $481.60 $481.01 $480.42 $479.82 $479.23 $478.62 $478.02 $477.41 $476.80 $476.19 $475,57 $474.95 $474.33 $473.70 $473.07 $472.44 $471.80 Sheet3 E www $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599,55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599.55 $599 55 F TITE $104.64 $105.16 $105.69 $106 22 $106.75 $107.28 $107.82 $108.36 $108.90 $109.45 $109.99 $110.54 $111.10 $111.65 $112.21 $112.77 $113.33 $113.90 $114.47 $115.04 $115.62 $116.20 $116.78 $117.36 $117.95 $118.54 $119.13 $119.73 $120.32 $120.93 $121.53 $122.14 $122.75 $123.36 $123.98 $124.60 $125.22 $125,85 $126.48 $127.11 $12775 4 + 5 +
