In: Finance
Jake borrows $35,000 to finance the expansion of his business. He promises to repay with the entire amount plus interst in exactly 5 years. The annual interest rate is 7.5%. What is the amount of Jake's monthly payments? Please answer using excel formulas & show your work. Thanks.
P = Amount of loan = $35,000
n = 5*12 = 60 months
r = monthly interest rate = 7.5%/12 = 0.625%
Monthly payment = [r * PV] / [1 - (1+r)^-n]
= [0.625% * $35,000] / [1 - (!+0.625%)^-60]
= $218.75 / 0.311908176
= $701.328202
Monthly payment is $701.33
Amortization Schedule | |||||
Month | Beginning Balance | Payment | Interest Repayment of principal | Principal Portion | Ending Balance |
A | B | C | D = B*0.625% | E = C-D | F = B-E |
1 | 35000 | 701.33 | 218.75 | 482.58 | 34517.42 |
2 | 34517.42 | 701.33 | 215.733875 | 485.596125 | 34031.8239 |
3 | 34031.8239 | 701.33 | 212.6988992 | 488.6311008 | 33543.1928 |
4 | 33543.1928 | 701.33 | 209.6449548 | 491.6850452 | 33051.5077 |
5 | 33051.5077 | 701.33 | 206.5719233 | 494.7580767 | 32556.7497 |
6 | 32556.7497 | 701.33 | 203.4796853 | 497.8503147 | 32058.8993 |
7 | 32058.8993 | 701.33 | 200.3681209 | 500.9618791 | 31557.9375 |
8 | 31557.9375 | 701.33 | 197.2371091 | 504.0928909 | 31053.8446 |
9 | 31053.8446 | 701.33 | 194.0865285 | 507.2434715 | 30546.6011 |
10 | 30546.6011 | 701.33 | 190.9162569 | 510.4137431 | 30036.1874 |
11 | 30036.1874 | 701.33 | 187.726171 | 513.603829 | 29522.5835 |
12 | 29522.5835 | 701.33 | 184.516147 | 516.813853 | 29005.7697 |
13 | 29005.7697 | 701.33 | 181.2860604 | 520.0439396 | 28485.7257 |
14 | 28485.7257 | 701.33 | 178.0357858 | 523.2942142 | 27962.4315 |
15 | 27962.4315 | 701.33 | 174.765197 | 526.564803 | 27435.8667 |
16 | 27435.8667 | 701.33 | 171.474167 | 529.855833 | 26906.0109 |
17 | 26906.0109 | 701.33 | 168.162568 | 533.167432 | 26372.8434 |
18 | 26372.8434 | 701.33 | 164.8302716 | 536.4997284 | 25836.3437 |
19 | 25836.3437 | 701.33 | 161.4771483 | 539.8528517 | 25296.4909 |
20 | 25296.4909 | 701.33 | 158.1030679 | 543.2269321 | 24753.2639 |
21 | 24753.2639 | 701.33 | 154.7078996 | 546.6221004 | 24206.6418 |
22 | 24206.6418 | 701.33 | 151.2915115 | 550.0384885 | 23656.6033 |
23 | 23656.6033 | 701.33 | 147.8537709 | 553.4762291 | 23103.1271 |
24 | 23103.1271 | 701.33 | 144.3945445 | 556.9354555 | 22546.1917 |
25 | 22546.1917 | 701.33 | 140.9136979 | 560.4163021 | 21985.7754 |
26 | 21985.7754 | 701.33 | 137.411096 | 563.918904 | 21421.8565 |
27 | 21421.8565 | 701.33 | 133.8866029 | 567.4433971 | 20854.4131 |
28 | 20854.4131 | 701.33 | 130.3400816 | 570.9899184 | 20283.4231 |
29 | 20283.4231 | 701.33 | 126.7713946 | 574.5586054 | 19708.8645 |
30 | 19708.8645 | 701.33 | 123.1804034 | 578.1495966 | 19130.7149 |
31 | 19130.7149 | 701.33 | 119.5669684 | 581.7630316 | 18548.9519 |
32 | 18548.9519 | 701.33 | 115.9309494 | 585.3990506 | 17963.5529 |
33 | 17963.5529 | 701.33 | 112.2722054 | 589.0577946 | 17374.4951 |
34 | 17374.4951 | 701.33 | 108.5905941 | 592.7394059 | 16781.7557 |
35 | 16781.7557 | 701.33 | 104.8859729 | 596.4440271 | 16185.3116 |
36 | 16185.3116 | 701.33 | 101.1581977 | 600.1718023 | 15585.1398 |
37 | 15585.1398 | 701.33 | 97.40712392 | 603.9228761 | 14981.217 |
38 | 14981.217 | 701.33 | 93.63260595 | 607.6973941 | 14373.5196 |
39 | 14373.5196 | 701.33 | 89.83449724 | 611.4955028 | 13762.0241 |
40 | 13762.0241 | 701.33 | 86.01265034 | 615.3173497 | 13146.7067 |
41 | 13146.7067 | 701.33 | 82.16691691 | 619.1630831 | 12527.5436 |
42 | 12527.5436 | 701.33 | 78.29714764 | 623.0328524 | 11904.5108 |
43 | 11904.5108 | 701.33 | 74.40319231 | 626.9268077 | 11277.584 |
44 | 11277.584 | 701.33 | 70.48489976 | 630.8451002 | 10646.7389 |
45 | 10646.7389 | 701.33 | 66.54211789 | 634.7878821 | 10011.951 |
46 | 10011.951 | 701.33 | 62.57469362 | 638.7553064 | 9373.19567 |
47 | 9373.19567 | 701.33 | 58.58247296 | 642.747527 | 8730.44815 |
48 | 8730.44815 | 701.33 | 54.56530091 | 646.7646991 | 8083.68345 |
49 | 8083.68345 | 701.33 | 50.52302155 | 650.8069785 | 7432.87647 |
50 | 7432.87647 | 701.33 | 46.45547793 | 654.8745221 | 6778.00195 |
51 | 6778.00195 | 701.33 | 42.36251217 | 658.9674878 | 6119.03446 |
52 | 6119.03446 | 701.33 | 38.24396537 | 663.0860346 | 5455.94842 |
53 | 5455.94842 | 701.33 | 34.09967765 | 667.2303223 | 4788.7181 |
54 | 4788.7181 | 701.33 | 29.92948814 | 671.4005119 | 4117.31759 |
55 | 4117.31759 | 701.33 | 25.73323494 | 675.5967651 | 3441.72082 |
56 | 3441.72082 | 701.33 | 21.51075516 | 679.8192448 | 2761.90158 |
57 | 2761.90158 | 701.33 | 17.26188488 | 684.0681151 | 2077.83346 |
58 | 2077.83346 | 701.33 | 12.98645916 | 688.3435408 | 1389.48992 |
59 | 1389.48992 | 701.33 | 8.684312026 | 692.645688 | 696.844236 |
60 | 696.844236 | 701.33 | 4.355276476 | 696.9747235 | 0 |