Question

Create an amortization schedule for a 20-year, $200,000 loan at 7.5% interest with monthly payments.

1. During which payment number does the amount applied to the principal exceed the amount paid to interest?
2. True or False? After half of the time has passed in making payments, we also note that exactly half of the overall balance has been paid off. Explain.
3. After completing the schedule, show two different ways for finding the total amount of interest paid over the 20 years.

Answer 1

Answers based on Schedule given at last :

(a) During payment no 131 , principal applied exceed interest applied to that payment.

(A) Opening Principal

(B) Interest

(C) Amount

(D) EMI

(E) Princ. Applied

(F) Remaining Principal

131

$127,887

$799

$128,687

$1,611

$812

$127,075

(b) False , when half of the time i.e. 120 months have passed, the opening principal of 121st payment is 135,734 which is more than half i.e. 100,000 of the original balance.

(A) Opening Principal

(B) Interest

(C) Amount

(D) EMI

(E) Princ. Applied

(F) Remaining Principal

121

$135,734

$848

$136,582

$1,611

$763

$134,971

(c)

One way of finding the total interest paid is adding up the interest column ($186,685) in Amortization schedule and second way is adding up the EMS column ($386,685) and then deducting principal amount there from ($200,000).

(A) Opening Principal

(B) Interest

(C) Amount

(D) EMI

(E) Princ. Applied

(F) Remaining Principal

Total

$29,869,557

$186,685

$30,056,242

$386,685

$200,000

$29,669,557

Amortization schedule for reference : some rows deleted as answer limit is 65000 characters

Total

$29,869,557

$186,685

$30,056,242

$386,685

$200,000

$29,669,557

Amortization Schedule

Loan Amount No of years Rate of Interest (R) Monthly Payment/EMI $200,000 201 7.50% pa $1,611 pm (A) Opening Principal Princ. Remaining (B) Interest (C) Amount (D) EMI Applied Principal PMT EMI- (A) R (A+B) formula interest (A+B-D) Formulas Month 1 2 4. 51 61 71 8 91 10 11 $200,000 $199,639 $199,275 $198,910 $198,542 $198,171 $197.799 $197,424 $197,047 $196,667 $196,285 $195,900 $195,514 $195,124 $194,733 $194,339 $193,942 $1,250 $1,248 $1,245 $1,243 $1,241 $1,239 $1,236 $1,234 $1,232 $1,229 $1,227 $1,224 $1,222 $1,220 $1,217 $1,215 $1,212 $201,250 $200,887 $200,521 $200,153 $199,783 $199,410 $199,035 $198,658 $198,278 $197,896 $197,512 $197,125 $196,736 $196,344 $195,950 $195,553 $195,154 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 | $361 $363 $366 $368 $370 $373 $375 $377 $380 $382 $384 $387 $389 $392 $394 $397 $399 $199,639 $199,275 $198,910 $198,542 $198,171 $197,799 $197,424 $197,047 $196,667 $196,285 $195,900 $195,514 $195, 124 $194,733 $194,339 $193,942 $193,543 GWA

54-| $193.543 | $193.141 5192.137) $192 331| 5191922 ) 5191.510 | 191.096 $190.679 || $190.260 | $189,837| $189,413 | $188.985 | $188.555 | $188.123 | 5187,687 | $187.249|| $186,808 | $186.365 | 5185.918 | $185.469」 $185.017 $184.562] $184.104 | $183.644 | $183.181 5182.714 | $182.245 | | $181.773| 1.210 | 198.75 | 51.611 | 51.2071519-349 」 $1.611 | 51.205 | 193,942] $1.611 | 12021 193.58] 51.611 | 12005193.121 | 51.611 | 51.17] 192,707 」 $1,611 | 5 1.194 | 192290 | 51.611 51.1921 191.871 | 51.611 1.189 」 $191.449 」 $1,611 | 51.186 | 5191,024 | 51.611 1,184 | 5190,5971 $1,611 51.181| $190.167 」 $1,611 51.178) $189,734 | $1,611 | 51.176) $189,298 | $1,611 51.173 | $188,860 | $1,611 51.170 | $188.419 」 $1,611 51.168 | 5187.976 | $1,511 51.165 | 5187529 」 $1.611 51.162 | 5187.080 | S1.611 51.159 | 5186.528 ) 51.611 1.156 | 5186.13] S1.611 51.154 | S135.15] 1.511 51.151| $185.255 | $1.511 | 51.148 | S122.192| 51.511 | 51.145 | 5184325 | S1.611 51.142] S123.856 | S1.611 51.139| S12338 | S1.611 | 1.136 S122. 29] S1.611 | 54021 5193.141 5192.737 SA07) 5192.331 | S409 」 $191.922 | 5412) 5191.5101 5414] 5191.096 5417| 5190.679 5419 | $190.260 | $422 | $189,837 $425 | 5189,413 $4271 5188,985 $430 | $188,555 5433] $188.123 $435 | $187,687 5187.249 SA41 | 5186,808 SAAA | $186.365 SA46 」 5185.918 $449] 185,469 5452) 5185.017 5455 | 184.562 5458 | $184.104 5461 | S123.54 5463) $183.181 5465) $182.714 5869 | 5182.245 472) 5181.73 $875|5181 298 | $438 |

$181,298 $180,820 $180,339 $179,855 $179,367 $178,877 $178,384 $177,888 $177,388 $176,886 $176,380 $175,871 $175,359 $174,844 $174,326 $173,804 $173,279 $172,751 $172,220 $171,685 $171,147 $170,605 $170,060 $169,512 $168,960 405) $167,846 $167,284 $1,133 $1,130 $1,127 $1,124 $1,121 $1,118 $1,115 $1,112 $1,109 $1,106 $1,102 $1,099 $1,096 $1,093 $1,090 $1,086 $1,083 $1,080 $1,076 $1,073 $1,070 $1,066 $1,063 $1,059 $1,056 $1,053 $1,049 $1,046 $182,431 $181,950 $181,466 $180,979 $180,488 $179,995 $179,499 $179,000 $178,497 $177,991 $177,483 $176,971 $176,455 $175,937 $175,415 $174,890 $174,362 $173,831 $173,296 $172,758 $172,216 $171,671 $171,123 $170,571 $170,016 $169,458 $168,895 $168,330 $1,611 $1,611 $1,611 $1,611 $1,611 | $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $478 9481 $484 $487 $490 $493 $496 $499 $503 $506 $509 $512 $515 $518 $522 $525 $528 $531 $535 $538 $542 $545 $548 $552 $555 $559 $562 $566 $180,820 $180,339 $179,855 $179,367 $178,877 $178,384 $177,888 $177,388 $176,886 $176,380 $175,871 $175,359 $174,844 $174,326 $173,804 $173,279 $172,751 $172,220 $171,685 $171,147 $170,605 $170,060 $169,512 $168,960 $168,405 $167,846 $167,284 $166,719 72 73!

| 73| 175 176 177 179 5167,288 | $166.719 $166.149 $165.577 $165,000 | $164.420 | $163,837 | $163.250 | $162.659 $162,064 | $161,466 | $160,864 | $160,258 $159,648 $159,035 $158,418 $157,797 $157.172 $156.543 $155.910 $155.273 $154.633 $153.988 $153.339 $152.686 $152.029 $151.368 $150.703 51.0-5 | 5168 330 | 51.611| 51.0-21 S167,761] $1,611| $1,038 | 167,188 | 51.611 | 51.05 | 5166.611] $1,611 1.31] 5166.031 | S1.611 51.028 | S165,448 | 51.611 51.02 | 5164.861 | $1,611 $1,020 | $164,270 | $1,611 $1,017」 $163.675 | $1,611 $1,013 | $163,077】 $1,611 $1,00g | S162,475 | $1,611 $1,005 | $161,869 | $1,611] $1,002 | 5161,260 | $1,611 | 5998 | S160,646 | $1,611] | 5994 | $160,029 | $1,611 | $990 | $159,408 | $1,611] 5986 | $158,783| $1,611 5982 | $158.154 | $1,611 5978 | $157,521 | S1.611 | 5974 | $156, 885 | S1.611 | 5970 | $156.244 | S1.611 | 5966 | $155.599 | S1.611 | 5962 | S154950 51.511 5958 | 152.298 | 51.511 5954 | 13.5-11 1.511 5950 152280 1 51.511 5946| 15231.151511 39.21151.55] 51.511 5566 | 5166.719 ] 5569 | 5166.149 | 5573) 5165.577 557615165,000 ) $580 ) 5164.420 | 5584 | 5163.837 | 5587) 5163.250 | 5591 | 5162.659 | 5595 | $162,064 | 5598 | 5161.466 | 5602 | 5160.864 | $606 | $160.258 | 5610 | 5159,648 | 5613) $159,035 | 5617) $158.418 | 5621] 5157.797 | $625 | 157.172 | 5629 | $156.543 | $633 | 5155.910 | $637 | 5155.273 | 56411 5154.533 | $6451 5153.98 | 5549 5153.339 | 5653 5152.686 | 5657 $152.029 | 5661 5151368 | 5665 | 5150,703 | S669 | 5150.034 |

101] 102] 103|| 104|| 105| 106 | 107 106 | 109|| 110| 111| 112| 113| | 114| 115| 116| 117|| 118| 119|| 120 121] 122| 123|| $150.034 | $149 361 5142.683) $148.001 | 147.315 5146.624 1 $145.930 | $145.230 ) 5144.527] $143,819 | 5143.107」 5142.390 | $141.669 」 $140.943」 $140.213 $139,478 | $138.738 | $137.994 | $137.245 | $136.492) $135.734 | $134.971) 5134.204 5133.431 5132.654 5131.872 $131.025 $130.293 5938 | $150.372| 51.611| 9 32 | S150.294 | 1.511| 5929 | 149.612| 51.611] 9251 5148.926 」 $1.6111 9 21] 5148.235 」 $1.6111 5816 ) 147 41 | 51,511] 81215146.242 | 51.611] 20E | 146.138 」 $1,611 | 5903 | $145.430 | $1,511| 5899 」 $144.718 | $1,511| 5894 | 5144.001 | $1,611| 5890 」 $143,280 | $1.511| 5885 」 $142.554 | $1.511| 5881| $141,824 | $1.511| 5 876 | $141,089 」 $1,511| 5872 | $140.349 | $1,511| 5867) $139.505 | 51.511| 5862| 5138.857」 $1.611| 5858 | $138.103 | 51.511] 5853 | 5137.345 | $1.511) 5848 | $136.582| 51.511] 58AM) $135.315] 51.611) 5839 | 5135.042 | $1.511 | 5834 | 13.265 | 51.511| 5829 | S133483 | 51.511 | 5824 | 5132.656 | 51.511 | 5819 | 131204 | S1.611] S814 | S131.107 | 51.611] $673 | $149,361 5678 | 5148.683 56821 $148.001 5686 | 5147315 $690 | 5146.624 $695 | $145.930 5699 」 $145.230 ) 5703 | 5144.527 5708 | $143,819 | 5712 | $143.107 | $717」 $142,390 | 5721| $141.669 | 5726 | $140,943 | 5730 | $140,213 | 5735 | $139,478 5739 」 $138,738 | 5744 | $137.994 | $749 」 $137.245| 5753 | $136.492 | 5758 | $135.734 | 5763| $134.971| 5758 | 5134.204 | 5772) S133.431 | 5 1 5132.654 5782| 5131.372 | 578715131.085 | $7921 130.293 | 5797] 5129.496] 124 125| 126 127] 128

$129,496 $128,694 $127,887 $127,075 $126,258 $125,436 $124,609 $123,777 $122.939 $122,096 $121,248 $120,395 $119,536 $118,672 $117,803 $116,928 $116,047 $115,161 $114,270 $113,373 $112,470 $111,562 $110,648 $109,729 $108,803 $107,872 $106,935 $105,992 $809 5804 $799 $794 $789 $784 $779 $774 $768 $763 $758 $752 $747 $742 $736 $731 $725 $720 $714 $709 $703 $697 $692 $686 $680 $674 $668 $662 $130,305 $129,499 $128,687 $127,870 $127,048 $126,220 $125,388 $124,550 $123,708 $122,860 $122,006 $121,147 $120,283 $119,414 $118,539 $117,659 $116,773 $115,881 $114,984 $114,082 $113,173 $112,259 $111,340 $110,414 $109,483 S108 545 $108.546 $107,603 $106,655 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $802 $807 $812) $817 $822 $827 $832 $838 $843 $848 $853 $859 $864 $869 $875 $880 $886 $891 $897 $903 $908 $914 $920 $925 $931 $937 $943 $949 $128,694 $127,887 $127,075 $126,258 $125,436 $124,609 $123,777 $122.939 $122,096 $121,248 $120,395 $119,536 $118,672 $117,803 $116,928 $116,047 $115,161 $114,270 $113,373 $112,470 $111,562 $110,648 $109,729 $108,803 $107,872 $106,935 $105,992 $105,044 1531 154 1551 156

$955 $961 $967 $973 $979 $985 $991 $997 $105,044 $104,089 $103,128 $102,162 $101,189 $100,210 $99,225 $98,234 $97,237 $96,234 $95,224 $94,208 $93,185 $92,157 $91,121 $90,080 $89,032 $87,977 $86,916 $85,848 $84,773 $83,692 $82,603 $81,509 $80,407 $79,298 $78,183 $77,060 $657 | $105,700 $651 $104,739 $645 $103,773 $639 $102,800 $632 $101,821 S626 $100,836 $620 $99,845 $614 $98,848 $608 $97,845 5601 $96,835 $595 $95,819 SSR9 $94,797 $582 $93,768 S576 $92,733 S570 $91,691 $563 $90,643 $556 $89,588 $550 $88,527 $543 $87,459 $537 $86,384 $530 $85,303 $523 $84,215 $516 $83,120 $509 $82,018 $503 $80,909 $496 $79,794 $489 $78,671 $482 $77,542 $1,611 $1. $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,003 $1,010 $1,016 $1,022 $1,029 $1,035 $1,042 $1,048 $1,055 $1,061 $1,068 $1,075 $1,081 $1,088 $1,095 $1,102 $1,109 $1,116 $1,123 $1,130 $104,089 $103,128 $102,162 $101,189 $100,210 $99,225 $98,234 $97,237 $96,234 $95,224 $94,208 $93,185 $92,157 $91,121 $90,080 $89,032 $87,977 $86,916 $85,848 $84,773 $83,692 $82,603 $81,509 $80,407 $79,298 $78,183 $77,060 $75,930 179 180 1811 1821 183) 184

213 $39,915 $38.553 $37,183 $35,804 $34,42 5180 214 215 2161 2171 2181 2191 2201 2211 2221 223 224 225 226 227 228 229 230 231 232 233 234 235 $41,268 $39,915 $38,553 $37,183 $35,804 $34,417 $33,021 $31,616 $30,203 $28,780 $27,349 $25,909 $24,459 $23,001 $21,534 $20,057 $18,571 $17,076 $15,572 $14,058 $12,534 $11,002 $9,459 $7,907 $6,345 $4,774 $3,192 $1,601 $41,526 $40,165 $38,794 $37,416 $36,028 $34,632 $33,227 $31,814 $30,391 $28,960 $27,520 $26,071 $24,612 $23,145 $21,668 $20,182 $18,687 $17,183 $15,669 $14,146 $12,613 $11,070 $9,518 $7,956 $6,385 $4,804 $3,212 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,611 $1,353 $1,362 $1,370 $1,379 $1,387 $1,396 $1,405 $1,414 $1,422 $1,431 $1,440 $1,449 $1,458 $1,467 $1,477 $1,486 $1,495 $1,504 $1,514 $1,523 $1,533 $1,542 $1,552 $1,562 $1,572 $1,581 $1,591 $1,601 $33,021 $31,616 $30,203 $28,780 $27,349 $25,909 $24,459 $23.001 $21,534 $20,057 $18,571 $17,076 $15,572 $14,058 $12,534 $11,002 $9,459 $7,907 $6,345 $4,774 $3,192 $1,601 $0 $107 597 $88 $78 $69 $59 $49 236 $40 237 238 239) 240 $30 $20 $10

Total $29,869,557 $186,685 $30,056,242 $386,685 $200,000 $29,669,557