Question

1. Assume you purchase a home for $395,000. You find a bank that offers a 30-year mortgage with an APR of 4.65% but requires 20% down. You decide to finance your home through this bank. Based on that repare a 30-year amortization schedule showing your monthly payments, showing how much interest you will pay for this home over the 30 years of payments to the bank, describing what change in your budget you and your spouse might make to find an additional $100 to make additional principle payments on your home. After having that ready, prepare a second amortization schedule showing the new payment, showing how much faster you will pay off your mortgage, and how much interest you will have saved by making the $100 additional payment.

You can create your own amortization schedule, or find an excel template online to submit.

Answer 1

It isn't possible to post the amortization schedule of 360 months, hence I'm providing you the first and ending images of the schedule. I'm also attaching the formuala view so that you can look at the formulas used.

total loan value = K L M normal case total loan value = downpayment = present value of all monthly payments = monthly interest rate = monthly periods = monthly payment = $ 395,000 $ 79,000 $ 316,000 0.388% 360 $1,629.41 if $100 more is paid monthly total loan value = $ 395,000 downpayment = $ 79,000 present value of all monthly payments = $ 316,000 monthly interest rate = 0.388% monthly payment = $ 1,729.41 monthly periods = 318.33 hence, loan shall be repaid 360 - 318.33 = 41.67 months earlier loan amortization schedule period monthly payment interest expense principal repayment carrying value of loan $ 316,000 $ 1,629.41 $ 1,224.50 $ 404.91 $ 315,595.09 $ 1,629.41 $ 1,222.93 $ 406.48 $ 315,188.61 $ 1,629.41 $ 1,221.36 $ 408.06 $ 314,780.55 $ 1,629.41 $ 1,219.77 $ 409.64 $ 314,370.91 $ 1,629.41 $ 1,218.19 $ 411.23 $ 313,959.69 $1,629.41 $ 1,216.59 $ 412.82 $ 313,546.87 $ 1,629.41 $ 1,214.99 $414.42 $ 313,132.45 $ 1,629.41 $ 1,213.39 $ 416.02 $ 312,716.43 $ 1,629.41 $ 1,211.78 $ 417.64 $ 312,298.79 $1,629.41 $ 1,210.16 $ 419.25 $ 311,879.54 $ 1,629.41 $ 1,208.53 $ 420.88 $ 311,458.66 $ 1,629.41 $ 1,206.90 $422.51 $ 311,036.15 $ 1,629.41 $ 1,205.27 $ 424.15 $ 310,612.00 $1,629.41 $ 1,203.62 $ 425.79 $ 310,186.21 $ 1.629.41 $1.201.97 $427.44 $ 309.758.77 loan amortization schedule period monthly payment interest expense principal repayment carrying value of loan $ 316,000 $ 1,729.41 $ 1,224.50 $ 504.91 $ 315,495.09 $ 1,729.41 $ 1,222.54 $ 506.87 $ 314,988.22 $ 1,729.41 $ 1,220.58 $ 508.83 $ 314,479.39 $ 1,729.41 $ 1,218.61 $ 510.80 $ 313,968.58 $ 1,729.41 $1,216.63 $512.78 $ 313,455.80 $ 1,729.41 $ 1,214.64 $ 514.77 $312,941.03 $ 1,729.41 $ 1,212.65 $ 516.77 $312,424.26 $ 1,729.41 $1,210.64 $ 518.77 $ 311,905.49 $ 1,729.41 $ 1,208.63 $ 520.78 $ 311,384.71 $ 1,729.41 $ 1,206.62 $ 522.80 $ 310,861.92 $ 1,729.41 $ 1,204.59 $ 524.82 $310,337.09 $ 1,729.41 $ 1,202.56 $ 526.86 $ 309,810.24 $ 1,729.41 $ 1,200.51 $ 528.90 $ 309,281.34 $ 1,729.41 $ 1,198.47 $530.95 $ 308,750.39 $ 1.729.41 $ 1.196.41 $ 533.00 $ 308.217.39

B338 - X Fox 325 G 313 H 299 300 301 302 303 314 304 317 318 305 306 307 308 309 310 2 AB 312 299 300 301 315 302 316 303 304 305 319 306 320 307 321 308 322 309 310 324 311 325 312 313 327 314 315 316 330 317 331 318 332 319 333 320 321 322 323 337 324 338 325 $1,629.41 $ 1,629.41 $1,629.41 $1,629.41 $ 1,629.41 $ 1,629.41 $1,629.41 $1,629.41 $ 1,629.41 $1,629.41 $1,629.41 $1,629.41 $ 1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $ 1,629.41 $ 1,629.41 $ 1,629.41 $1,629.41 $ 1,629.41 $1,629.41 $1,629.41 $ 1,629.41 $1,629.41 $ 1,629.41 $347.40 $ 342.43 $ 337.44 $ 332.44 $ 327.41 $ 322.36 $317.30 $312.21 $ 307.11 $ 301.99 $ 296.84 $ 291.68 $286.50 $281.29 $ 276.07 $ 270.82 $265.56 $ 260.27 $ 254.97 $ 249.64 $244.30 $ 238.93 $ 233.54 $ 228.13 $ 222.70 $ 217.25 $ 211.78 323 I $1,729.41 $ 1,729.41 $ 1,729.41 $ 1,729.41 $ 1,729.41 $ 1,729.41 $1,729.41 $ 1,729.41 $ 1,729.41 $1,729.41 $1,729.41 $ 1,729.41 $ 1,729.41 $ 1,729.41 $ 1,729.41 $ 1,729.41 $1,729.41 $ 1,729.41 $ 1,729.41 $ 1,729.41 $574.67 $ 550,527.78 $ 1,282.02 $ 1,286.98 $ 1,291.97 $ 1,296.98 $ 1,302.00 $ 1,307.05 $ 1,312.11 $ 1,317.20 $ 1,322.30 $ 1,327.43 $ 1,332.57 $ 1,337.73 $ 1,342.92 $ 1,348.12 $ 1,353.34 $ 1,358.59 $ 1,363.85 $ 1,369.14 $ 1,374.44 $ 1,379.77 $ 1,385.12 $ 1,390.48 $ 1,395.87 $ 1,401.28 $ 1,406.71 $ 1,412.16 $ 1,417.63 311 $ 88,368.69 $ 87,081.71 $ 85,789.74 $ 84,492.76 $ 83,190.76 $ 81,883.71 $ 80,571.60 $ 79,254.40 $ 77,932.10 $ 76,604.68 $ 75,272.11 $ 73,934.37 $ 72,591.46 $ 71,243.34 $ 69,889.99 $ 68,531.40 $ 67,167.55 $ 65,798.41 $ 64,423.97 $ 63,044.20 $ 61,659.08 $ 60,268.60 $58,872.73 $57,471.45 $ 56,064.74 $ 54,652.58 $53,234.94 $ 130.78 $ 124.59 $ 118.37 $ 112.12 $ 105.86 $99.57 $93.25 $ 86.91 $ 80.55 $ 74.16 $67.74 $ 61.30 $ 54.84 $ 48.35 $ 41.84 $ 35.30 $ 28.73 $ 22.14 $ 15.53 $ 8.89 $ 2.22 $ 234,527.78 $ 1,598.63 $ 1,604.83 $1,611.04 $ 1,617.29 $ 1,623.55 $1,629.85 $ 1,636.16 $ 1,642.50 $ 1,648.87 $ 1,655.26 $ 1,661.67 $ 1,668.11 $ 1,674.57 $ 1,681.06 $ 1,687.58 $ 1,694.12 $ 1,700.68 $ 1,707.27 $ 1,713.89 $ 1,720.53 $572.45 $ 316,000.00 $ 32,151.26 $ 30,546.44 $ 28,935.39 $ 27,318.10 $ 25,694.55 $ 24,064.70 $ 22,428.54 $ 20,786.04 $ 19,137.17 $ 17,481.92 $ 15,820.25 $ 14,152.14 $ 12,477.57 $ 10,796.50 $9,108.93 $ 7,414.81 $5,714.13 $ 4,006.86 $ 2,292.98 $ 572.45 $ 0.00 312 326 313 314 315 328 329 316 317 318 319 total 334 335 savings in interest payment = $270,588.43 - $234.527.78 savings in interest payment = $ 36,060.65

「 明 B357 : X V & | 344 | G | H| I | J | K | 351 352 353 355 356 351 B | 1338 339 340 341 342 343 34 | 345 346 347 348 349 350 351 352 353 354 355 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 S1.629.41 $1,629.41 $1,629.41 $1,629.41 S1.629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $1,629.41 $586,588.43 $138.68 $132.90 $127.11 $121.28 $115.4 $109.57 $103.68 $ 7.7 591.84 $85.88 $79.90 s73.89 $67.87 S61.81 $55.74 59.4 $43.52 $37.38 $31.21 $25.01 $18.80 $12.55 $6.29 | 5270,588.43 $1,490.73 $1,496.51 $1,502.31 $1,508.13 $1,513.97 $1,519.84 $1,525.73 $1,531.64 $1,537.58 $1,543.53 $1,549.51 $1,555.52 $1,561.55 $1,567.60 $1,573.67 $1,579.7 $1,585.89 $1,592.04 $1,598.21 $1,604.40 $1,610.62 $1,616.86 $1,623.12 5316,000.00 F $34,297.98 $32,801.47 $31,299.17 $29,791.04 $28,27.07 $26, 757.23 $25,231.50 $23,699.86 $22,162.28 $20,618.75 $19,069.24 $17,513.72 $15,952.17 $14,384.57 $12,810.90 $11, 231.13 $9,645.24 $8,053.20 $6,455.00 $4,850.60 $3,239.98 $1,623.12 50.00 365 367 368 369 356 357 358 359 360 | 374 total |

for formulas and calculations, refer to the image below -

X fac total loan value = A B F G H I J K normal case total loan value = downpayment = present value of all monthly payments = monthly interest rate = monthly periods = monthly payment = 395000 =E3*20% =E3*80% -4.65%/12 =30*12 =PMT(E6, E7,-E5) if $100 more is paid monthly total loan value = 395000 downpayment = =K3*20% present value of all monthly payments = =K3*80% monthly interest rate = =4.65%/12 monthly payment = =E8+100 monthly periods = =NPER(K6,-K7,K5) hence, loan shall be repaid 360 - 318.33 = 41.67 months earlier =$E$8 loan amortization schedule period monthly payment interest expense principal repayment carrying value of loan =E5 =$E$8 =F13*$E$6 =C14-D14 =F13-E14 =$E$8 =F14*$E$6 =C15-D15 =F14-E15 =$E$8 =F15*$E$6 =C16-D16 =F15-E16 =$E$8 =F16*$E$6 =C17-D17 =F16-E17 =$E$8 =F17*$E$6 =C18-D18 =F17-E18 =F18*$E$6 =C19-D19 =F18-E19 =$E$8 =F19*$E$6 =C20-D20 =F19-E20 =$E$8 =F20*$E$6 =C21-D21 =F20-E21 =$E$8 =F21*$E$6 =C22-D22 =F21-E22 =F22*$E$6 =C23-D23 =F22-E23 =F23*$E$6 =C24-D24 =F23-E24 =$E$8 =F24*$E$6 =C25-D25 =F24-E25 =F25*$E$6 =C26-D26 =F25-E26 =F26*$E$6 =C27-D27 =F26-E27 ESES8 =F27*$E$6 =C28-128 =F27-F28 loan amortization schedule period monthly payment interest expense principal repayment carrying value of loan =K5 =$K$7 =L13*$K$6 =114-114 =L13-K14 =$K$7 =L14*$K$6 =115-J15 =L14-K15 =$K$7 =L15*$K$6 =116-116 =L15-K16 =$K$7 =L16*$K$6 =117-217 =L16-K17 =$K$7 =L17*$K$6 =118-118 =L17-K18 =$K$7 =L18*$K$6 =119-119 =L18-K19 =$K$7 =L19*$K$6 =120-J20 =L19-K20 =$K$7 =L20*$K$6 =121-J21 =L20-K21 =$K$7 =L21*$K$6 =122-122 =L21-K22 =$K$7 =L22*$K$6 =123-123 =L22-K23 =$K$7 =L23*$K$6 =124-J24 =L23-K24 =$K$7 =L24*$K$6 =125-125 =L24-K25 =$K$7 =L25*$K$6 =126-126 =L25-K26 =$K$7 =L26*$K$6 =127-127 =L26-K27 I=SKSZ 1=27*$K$6 1=128-128 I=127-K28 =$E$8 =$E$8 =$E$8 -$E$8

B338 X Fox 325 A B G =$E$8 =$E$8 302 305 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =$E$8 =F311*$E$6 =F312*$E$6 =F313*$E$6 =F314*$E$6 =F315*$E$6 =F316*$E$6 =F317*$E$6 =F318*$E$6 =F319*$E$6 =F320*$E$6 =F321*$E$6 =F322*$E$6 =F323*$E$6 =F324*$E$6 =F325*$E$6 =F326*$E$6 =F327*$E$6 =F328*$E$6 =F329*$E$6 =F330*$E$6 =F331*$E$6 =F332*$E$6 =F333*$E$6 =F334*$E$6 =C312-D312 =C313-D313 =C314-D314 =C315-D315 =C316-D316 =C317-D317 =C318-D318 =C319-0319 =C320-D320 =C321-D321 =C322-D322 =C323-D323 =C324-D324 =C325-D325 =C326-D326 =C327-D327 =C328-D328 =C329-D329 =C330-D330 =C331-D331 =C332-D332 =C333-D333 =C334-D334 =C335-0335 =F311-E312 =F312-E313 =F313-E314 =F314-E315 =F315-E316 =F316-E317 =F317-E318 =F318-E319 =F319-E320 =F320-E321 =F321-E322 =F322-E323 =F323-E324 =F324-E325 =F325-E326 =F326-E327 =F327-E328 =F328-E329 =F329-E330 =F330-E331 =F331-E332 =F332-E333 =F333-E334 =F334-E335 H I 299 =$K$7 300 =$K$7 301 =$K$7 =$K$7 =$K$7 304 =$K$7 =$K$7 306 =$K$7 =$K$7 =$K$7 309 =$K$7 =$K$7 =$K$7 =$K$7 =$K$7 =$K$7 =$K$7 316 =$K$7 =$K$7 =$K$7 =K332+J332 total =SUM(114:1332) =L311*$K$6 =L312*$K$6 =L313*$K$6 =L314*$K$6 =L315*$K$6 =L316*$K$6 =L317*$K$6 =L318*$K$6 =L319*$K$6 =L320*$K$6 =L321*$K$6 =L322*$K$6 =L323*$K$6 =L324*$K$6 =L325*$K$6 =L326*$K$6 =L327*$K$6 =L328*$K$6 =L329*$K$6 =L330*$K$6 =L331*$K$6 =SUM(J14:1332) к =1312-1312 =1313-1313 =1314-2314 =1315-1315 =1316-1316 1=1317-J317 =1318-1318 =1319-J319 =1320-1320 1=1321-J321 =1322-1322 =1323-1323 =1324-1324 =1325-1325 =1326-1326 =1327-1327 =1328-J328 1=1329-J329 =1330-1330 =1331-1331 =L331 =SUM(K14:K332) =L311-K312 =L312-K313 =L313-K314 =L314-K315 =L315-K316 =L316-K317 =L317-K318 =L318-K319 =L319-K320 =L320-K321 =L321-K322 =L322-K323 =L323-K324 =L324-K325 =L325-K326 =L326-K327 =L327-K328 =L328-K329 =L329-K330 =L330-K331 =L331-K332 319 E$E$8 =$E$8 =$E$8 =$E$8 savings in interest payment = $270,588.43 - $234.527.78 savings in interest payment = =D374-1333 323 =$E$8 337 E$E$8 =F335*$E$6 =F336*$E$6 =F337*$E$6 =C336-D336 =C337-D337 =C338-D338 =F335-E336 =F336-E337 =F337-E338 338 2 =$E$8 Orda Latese

B357 for 344 AB G H I J K L M 351 353 354 341 355 =$E$8 356 357 358 359 360 361 362 363 364 338 =$E$8 339 =$E$8 340 =$E$8 =$E$8 342 =$E$8 343 344 =$E$8 345 =$E$8 346 =$E$8 347 =$E$8 =$E$8 349 =$E$8 350 =$E$8 351 352 =$E$8 =$E$8 =$E$8 355 =$E$8 =$E$8 =$E$8 =$E$8 359 =$E$8 360 total =SUM(C14:C373) =F350*$E$6 =C351-D351 =F351 * $E$6 =C352-D352 =F352*$E$6 =C353-D353 =F353*$E$6 =C354-D354 =F354*$E$6 =C355-D355 =F355*$E$6 =C356-D356 =F356*$E$6 =C357-D357 =F357* $E$6 =C358-D358 =F358*$E$6 =C359-D359 =F359*$E$6 =C360-D360 =F360*$E$6 =C361-D361 =F361 * $E$6 =C362-0362 =F362*$E$6 =C363-D363 =F363* $E$6 =C364-D364 =F364* $E$6 =C365-D365 =F365*$E$6 =C366-0366 =F366*$E$6 =C367-D367 =F367* $E$6 =C368-D368 =F368*$E$6 =C369-D369 =F369*$E$6 =C370-D370 =F370*$E$6 =C371-D371 =F371*$E$6 =C372-D372 =F372*$E$6 =C373-D373 =SUM(D14:D373) =SUM(E14:E373) =F350-E351 =F351-E352 =F352-E353 =F353-E354 =F354-E355 =F355-E356 =F356-6357 =F357-E358 =F358-E359 =F359-E360 =F360-E361 =F361-E362 =F362-E363 =F363-E364 =F364-E365 =F365-E366 =F366-E367 =F367-6368 =F368-E369 =F369-E370 =F370-E371 =F371-E372 =F372-E373 =$E$8 365 366 353 354 368 369 370 357 371 358 372 =$E$8 373 374 375

In case you have any query, kindly ask in comments.