In: Accounting
Home Loan Amortization; Goal Seeking, Creating Macros, and Creating Summary Information for Management.
Goals of this Project:
1. Freeze Panes to consistently display titles of Loan Amortization Table 2. Learn how to perform Goal Seeking. You will put in three different monthly payment amounts and see the effect on the number of periods. For example, for an 8 percent, 2 point loan, 360 periods, do not pay the points up front, and borrow $400,000, answer the following questions: How many years will it take to pay off the loan if you pay an additional $750 on the Principal? How many years will it take to pay off the loan if you pay an additional $1500 on the Principal? How many years will it take to pay off the loan if you double the monthly payment?
In case of no down payment :
Loan Amount : $400,000
Interest : 8%
Period : 360 months or 30 yrs
Hence the payment of EMI = Interest + Principal
EMI = In excel use function pmt(rate,no. of yrs,loan amt *-1) or P = (Pv*R) / [1 - (1 + R)^(-n)] = $2935
Interest = ($400,000 * 8%) / 12 = $2667
Principal = EMI - Interest = $2667 - $2935 = $268
How many years will it take to pay off the loan if you pay an additional $750 on the Principal?
If $750 is added to EMI then it will take 17yrs 2 months to pay off the the loan.
How many years will it take to pay off the loan if you pay an additional $1500 on the Principal?
If $1500 is added to EMI then it will take 11 yrs 5 months to pay off the the loan.
How many years will it take to pay off the loan if you pay double the monthly payment
If $2935 is added to EMI then it will take 7 yrs 6 months to pay off the the loan.
YEAR | DATE | PAYMENT | INTEREST | PRINCIPAL | ENDING BALANCE |
1 | 2019 |
ORIGINAL $750 $1500 $2935 |
$31,602 $31,542 $31,204 $30,559 |
$3,619 $12,679 $22,016 $39,882 |
$393,040 $387,321 $377,984 $360,118 |
2 | 2020 |
ORIGINAL $750 $1500 $2935 |
$31,302 $30,489 $29,377 $27,249 |
$3,919 $13,731 $23,844 $43,192 |
$389,121 $373,590 $354,140 $316,926 |
3 |
2021 |
ORIGINAL $750 $1500 $2935 |
$30,976 $29,350 $27,398 $23,664 |
$4,244 $14,871 $25,823 $46,777 |
$384,876 $358,719 $328,317 $46,777 |
4 | 2022 |
ORIGINAL $750 $1500 $2935 |
$30,624 $28,115 $25,255 $19,781 |
$4,597 $16,105 $27,966 $50,660 |
$380,279 $342,614 $300,351 $219,489 |
5 | 2023 |
ORIGINAL $750 $1500 $2935 |
$30,242 $26,779 $22,934 $15,576 |
$4,978 $17,442 $30,287 $54,864 |
$375,301 $325,172 $270,064 $164,625 |
6 | 2024 |
ORIGINAL $750 $1500 $2935 |
$29,829 $25,331 $20,420 $11,023 |
$5,391 $18,890 $32,801 $59,418 |
$369,910 $306,282 $237,263 $105,207 |
7 | 2025 |
ORIGINAL $750 $1500 $2935 |
$29,382 $23,763 $17,697 $6,091 |
$5,839 $20,457 $35,523 $64,350 |
$364,071 $285,825 $201,740 $40,857 |
8 | 2026 |
ORIGINAL $750 $1500 $2935 |
$28,897 $22,065 $14,749 $1,120 |
$6,324 $22,155 $38,472 $40,857 |
$357,747 $263,669 $163,268 $0 |
9 | 2027 |
ORIGINAL $750 $1500 $2935 |
$28,372 $20,226 $11,556 - |
$6,848 $23,994 $41,665 - |
$350,899 $239,675 $121,603 - |
10 | 2028 |
ORIGINAL $750 $1500 |
$27,804 $18,235 $8,098 |
$7,417 $25,986 $45,123 |
$343,482 $213,689 $76,480 |
11 | 2029 |
ORIGINAL $750 $1500 |
$27,188 $16,078 $4,352 |
$8,032 $28,143 $48,868 |
$335,450 $185,547 $27,611 |
12 | 2030 |
ORIGINAL $750 $1500 |
$26,522 $13,742 $687 |
$8,699 $30,478 $27,611 |
$326,750 $155,068 $0 |
13 | 2031 |
ORIGINAL $750 $1500 |
$25,800 $11,213 |
$9,421 $33,008 |
$317,329 $122,060 |
14 | 2032 |
ORIGINAL $750 $1500 |
$25,018 $8,473 |
$10,203 $35,748 |
$307,126 $86,312 |
15 | 2033 |
ORIGINAL $750 $1500 |
$24,171 $5,506 |
$11,050 $38,715 |
$296,076 $47,598 |
16 | 2034 |
ORIGINAL $750 $1500 |
$23,254 $2,293 |
$11,967 $41,928 |
$284,109 $5,669 |
17 | 2035 |
ORIGINAL $750 $1500 |
$22,260 $51 |
$12,960 $5,669 |
$271,149 $0 |
18 | 2036 |
ORIGINAL $750 $1500 |
$21,185 | $14,036 | $257,113 |
19 | 2037 |
ORIGINAL $750 $1500 |
$20,020 | $15,201 | $241,912 |
20 | 2038 |
ORIGINAL $750 $1500 |
$18,758 | $16,463 | $225,449 |
21 | 2039 |
ORIGINAL $750 $1500 |
$17,392 | $17,829 | $207,620 |
22 | 2040 |
ORIGINAL $750 $1500 |
$15,912 | $19,309 | $188,311 |
23 | 2041 |
ORIGINAL $750 $1500 |
$14,309 | $20,912 | $167,400 |
24 | 2042 |
ORIGINAL $750 $1500 |
$12,574 | $22,647 | $144,752 |
25 | 2043 |
ORIGINAL $750 $1500 |
$10,694 | $24,527 | $120,226 |
26 | 2044 |
ORIGINAL $750 $1500 |
$8,658 | $26,563 | $93,663 |
27 | 2045 |
ORIGINAL $750 $1500 |
$6,453 | $28,767 | $64,896 |
28 | 2046 |
ORIGINAL $750 $1500 |
$4,066 | $31,155 | $33,741 |
29 | 2047 |
ORIGINAL $750 $1500 |
$1,480 | $33,741 | $0 |
30 | 2048 |
ORIGINAL $750 $1500 |