In: Finance
You just took out a 10 year commercial real estate loan for your business. The loan amount is $250,000 and the interest rate is 3.5%. How many periods (months) will it take you to pay off $100,000 of the principal loan? Round your answer to the closest whole number. PLEASE answer in excel with appropriate excel formulas
At months 54 Principal of 100,000 is paid off. as per following table.
A | B | C | D | E | |
Beginning Balance | PMT=PMT(3.5%/12,120,A1) | Interest= =A1*3.5%/12 | Principal Part= PMT - Interest | Ending balance = Beginning Balance - Principal Part of PMT | |
1 | 250000 | $2,472.15 | 729.1667 | $1,742.98 | $248,257.02 |
2 | $248,257.02 | $2,472.15 | 724.083 | $1,748.06 | $246,508.96 |
3 | $246,508.96 | $2,472.15 | 718.9845 | $1,753.16 | $244,755.79 |
4 | $244,755.79 | $2,472.15 | 713.8711 | $1,758.28 | $242,997.52 |
5 | $242,997.52 | $2,472.15 | 708.7428 | $1,763.40 | $241,234.11 |
6 | $241,234.11 | $2,472.15 | 703.5995 | $1,768.55 | $239,465.57 |
7 | $239,465.57 | $2,472.15 | 698.4412 | $1,773.71 | $237,691.86 |
8 | $237,691.86 | $2,472.15 | 693.2679 | $1,778.88 | $235,912.98 |
9 | $235,912.98 | $2,472.15 | 688.0795 | $1,784.07 | $234,128.92 |
10 | $234,128.92 | $2,472.15 | 682.876 | $1,789.27 | $232,339.65 |
11 | $232,339.65 | $2,472.15 | 677.6573 | $1,794.49 | $230,545.16 |
12 | $230,545.16 | $2,472.15 | 672.4234 | $1,799.72 | $228,745.43 |
13 | $228,745.43 | $2,472.15 | 667.1742 | $1,804.97 | $226,940.46 |
14 | $226,940.46 | $2,472.15 | 661.9097 | $1,810.24 | $225,130.22 |
15 | $225,130.22 | $2,472.15 | 656.6298 | $1,815.52 | $223,314.71 |
16 | $223,314.71 | $2,472.15 | 651.3346 | $1,820.81 | $221,493.89 |
17 | $221,493.89 | $2,472.15 | 646.0239 | $1,826.12 | $219,667.77 |
18 | $219,667.77 | $2,472.15 | 640.6977 | $1,831.45 | $217,836.32 |
19 | $217,836.32 | $2,472.15 | 635.3559 | $1,836.79 | $215,999.53 |
20 | $215,999.53 | $2,472.15 | 629.9986 | $1,842.15 | $214,157.38 |
21 | $214,157.38 | $2,472.15 | 624.6257 | $1,847.52 | $212,309.86 |
22 | $212,309.86 | $2,472.15 | 619.2371 | $1,852.91 | $210,456.95 |
23 | $210,456.95 | $2,472.15 | 613.8328 | $1,858.31 | $208,598.64 |
24 | $208,598.64 | $2,472.15 | 608.4127 | $1,863.73 | $206,734.90 |
25 | $206,734.90 | $2,472.15 | 602.9768 | $1,869.17 | $204,865.74 |
26 | $204,865.74 | $2,472.15 | 597.5251 | $1,874.62 | $202,991.11 |
27 | $202,991.11 | $2,472.15 | 592.0574 | $1,880.09 | $201,111.02 |
28 | $201,111.02 | $2,472.15 | 586.5738 | $1,885.57 | $199,225.45 |
29 | $199,225.45 | $2,472.15 | 581.0742 | $1,891.07 | $197,334.38 |
30 | $197,334.38 | $2,472.15 | 575.5586 | $1,896.59 | $195,437.79 |
31 | $195,437.79 | $2,472.15 | 570.0269 | $1,902.12 | $193,535.67 |
32 | $193,535.67 | $2,472.15 | 564.479 | $1,907.67 | $191,628.00 |
33 | $191,628.00 | $2,472.15 | 558.915 | $1,913.23 | $189,714.77 |
34 | $189,714.77 | $2,472.15 | 553.3348 | $1,918.81 | $187,795.96 |
35 | $187,795.96 | $2,472.15 | 547.7382 | $1,924.41 | $185,871.55 |
36 | $185,871.55 | $2,472.15 | 542.1254 | $1,930.02 | $183,941.53 |
37 | $183,941.53 | $2,472.15 | 536.4961 | $1,935.65 | $182,005.88 |
38 | $182,005.88 | $2,472.15 | 530.8505 | $1,941.30 | $180,064.58 |
39 | $180,064.58 | $2,472.15 | 525.1884 | $1,946.96 | $178,117.62 |
40 | $178,117.62 | $2,472.15 | 519.5097 | $1,952.64 | $176,164.99 |
41 | $176,164.99 | $2,472.15 | 513.8145 | $1,958.33 | $174,206.66 |
42 | $174,206.66 | $2,472.15 | 508.1027 | $1,964.04 | $172,242.61 |
43 | $172,242.61 | $2,472.15 | 502.3743 | $1,969.77 | $170,272.84 |
44 | $170,272.84 | $2,472.15 | 496.6291 | $1,975.52 | $168,297.32 |
45 | $168,297.32 | $2,472.15 | 490.8672 | $1,981.28 | $166,316.04 |
46 | $166,316.04 | $2,472.15 | 485.0885 | $1,987.06 | $164,328.98 |
47 | $164,328.98 | $2,472.15 | 479.2929 | $1,992.85 | $162,336.13 |
48 | $162,336.13 | $2,472.15 | 473.4804 | $1,998.67 | $160,337.46 |
49 | $160,337.46 | $2,472.15 | 467.6509 | $2,004.50 | $158,332.97 |
50 | $158,332.97 | $2,472.15 | 461.8045 | $2,010.34 | $156,322.63 |
51 | $156,322.63 | $2,472.15 | 455.941 | $2,016.21 | $154,306.42 |
52 | $154,306.42 | $2,472.15 | 450.0604 | $2,022.09 | $152,284.33 |
53 | $152,284.33 | $2,472.15 | 444.1626 | $2,027.98 | $150,256.35 |
54 | $150,256.35 | $2,472.15 | 438.2477 | $2,033.90 | $148,222.45 |
55 | $148,222.45 | $2,472.15 | 432.3155 | $2,039.83 | $146,182.62 |
56 | $146,182.62 | $2,472.15 | 426.366 | $2,045.78 | $144,136.84 |
57 | $144,136.84 | $2,472.15 | 420.3991 | $2,051.75 | $142,085.09 |
58 | $142,085.09 | $2,472.15 | 414.4149 | $2,057.73 | $140,027.36 |
59 | $140,027.36 | $2,472.15 | 408.4131 | $2,063.73 | $137,963.63 |
60 | $137,963.63 | $2,472.15 | 402.3939 | $2,069.75 | $135,893.87 |
61 | $135,893.87 | $2,472.15 | 396.3571 | $2,075.79 | $133,818.08 |
62 | $133,818.08 | $2,472.15 | 390.3027 | $2,081.84 | $131,736.24 |
63 | $131,736.24 | $2,472.15 | 384.2307 | $2,087.92 | $129,648.32 |
64 | $129,648.32 | $2,472.15 | 378.1409 | $2,094.01 | $127,554.32 |
65 | $127,554.32 | $2,472.15 | 372.0334 | $2,100.11 | $125,454.20 |
66 | $125,454.20 | $2,472.15 | 365.9081 | $2,106.24 | $123,347.97 |
67 | $123,347.97 | $2,472.15 | 359.7649 | $2,112.38 | $121,235.58 |
68 | $121,235.58 | $2,472.15 | 353.6038 | $2,118.54 | $119,117.04 |
69 | $119,117.04 | $2,472.15 | 347.4247 | $2,124.72 | $116,992.32 |
70 | $116,992.32 | $2,472.15 | 341.2276 | $2,130.92 | $114,861.40 |
71 | $114,861.40 | $2,472.15 | 335.0124 | $2,137.13 | $112,724.27 |
72 | $112,724.27 | $2,472.15 | 328.7791 | $2,143.37 | $110,580.90 |
73 | $110,580.90 | $2,472.15 | 322.5276 | $2,149.62 | $108,431.28 |
74 | $108,431.28 | $2,472.15 | 316.2579 | $2,155.89 | $106,275.39 |
75 | $106,275.39 | $2,472.15 | 309.9699 | $2,162.18 | $104,113.21 |
76 | $104,113.21 | $2,472.15 | 303.6635 | $2,168.48 | $101,944.73 |
77 | $101,944.73 | $2,472.15 | 297.3388 | $2,174.81 | $99,769.92 |
78 | $99,769.92 | $2,472.15 | 290.9956 | $2,181.15 | $97,588.77 |
79 | $97,588.77 | $2,472.15 | 284.6339 | $2,187.51 | $95,401.26 |
80 | $95,401.26 | $2,472.15 | 278.2537 | $2,193.89 | $93,207.37 |
81 | $93,207.37 | $2,472.15 | 271.8548 | $2,200.29 | $91,007.07 |
82 | $91,007.07 | $2,472.15 | 265.4373 | $2,206.71 | $88,800.36 |
83 | $88,800.36 | $2,472.15 | 259.0011 | $2,213.15 | $86,587.22 |
84 | $86,587.22 | $2,472.15 | 252.5461 | $2,219.60 | $84,367.62 |
85 | $84,367.62 | $2,472.15 | 246.0722 | $2,226.07 | $82,141.54 |
86 | $82,141.54 | $2,472.15 | 239.5795 | $2,232.57 | $79,908.98 |
87 | $79,908.98 | $2,472.15 | 233.0678 | $2,239.08 | $77,669.90 |
88 | $77,669.90 | $2,472.15 | 226.5372 | $2,245.61 | $75,424.29 |
89 | $75,424.29 | $2,472.15 | 219.9875 | $2,252.16 | $73,172.13 |
90 | $73,172.13 | $2,472.15 | 213.4187 | $2,258.73 | $70,913.40 |
91 | $70,913.40 | $2,472.15 | 206.8308 | $2,265.32 | $68,648.09 |
92 | $68,648.09 | $2,472.15 | 200.2236 | $2,271.92 | $66,376.16 |
93 | $66,376.16 | $2,472.15 | 193.5971 | $2,278.55 | $64,097.61 |
94 | $64,097.61 | $2,472.15 | 186.9514 | $2,285.20 | $61,812.42 |
95 | $61,812.42 | $2,472.15 | 180.2862 | $2,291.86 | $59,520.56 |
96 | $59,520.56 | $2,472.15 | 173.6016 | $2,298.55 | $57,222.01 |
97 | $57,222.01 | $2,472.15 | 166.8975 | $2,305.25 | $54,916.76 |
98 | $54,916.76 | $2,472.15 | 160.1739 | $2,311.97 | $52,604.79 |
99 | $52,604.79 | $2,472.15 | 153.4306 | $2,318.72 | $50,286.07 |
100 | $50,286.07 | $2,472.15 | 146.6677 | $2,325.48 | $47,960.60 |
101 | $47,960.60 | $2,472.15 | 139.8851 | $2,332.26 | $45,628.33 |
102 | $45,628.33 | $2,472.15 | 133.0826 | $2,339.06 | $43,289.27 |
103 | $43,289.27 | $2,472.15 | 126.2604 | $2,345.89 | $40,943.38 |
104 | $40,943.38 | $2,472.15 | 119.4182 | $2,352.73 | $38,590.65 |
105 | $38,590.65 | $2,472.15 | 112.5561 | $2,359.59 | $36,231.06 |
106 | $36,231.06 | $2,472.15 | 105.6739 | $2,366.47 | $33,864.59 |
107 | $33,864.59 | $2,472.15 | 98.77172 | $2,373.37 | $31,491.22 |
108 | $31,491.22 | $2,472.15 | 91.84938 | $2,380.30 | $29,110.92 |
109 | $29,110.92 | $2,472.15 | 84.90685 | $2,387.24 | $26,723.68 |
110 | $26,723.68 | $2,472.15 | 77.94406 | $2,394.20 | $24,329.48 |
111 | $24,329.48 | $2,472.15 | 70.96097 | $2,401.19 | $21,928.29 |
112 | $21,928.29 | $2,472.15 | 63.95751 | $2,408.19 | $19,520.10 |
113 | $19,520.10 | $2,472.15 | 56.93363 | $2,415.21 | $17,104.89 |
114 | $17,104.89 | $2,472.15 | 49.88926 | $2,422.26 | $14,682.63 |
115 | $14,682.63 | $2,472.15 | 42.82434 | $2,429.32 | $12,253.31 |
116 | $12,253.31 | $2,472.15 | 35.73882 | $2,436.41 | $9,816.90 |
117 | $9,816.90 | $2,472.15 | 28.63263 | $2,443.51 | $7,373.39 |
118 | $7,373.39 | $2,472.15 | 21.50571 | $2,450.64 | $4,922.75 |
119 | $4,922.75 | $2,472.15 | 14.35801 | $2,457.79 | $2,464.96 |
120 | $2,464.96 | $2,472.15 | 7.189459 | $2,464.96 | ($0.00) |
Best of Luck. God Bless