In: Finance
Suppose you apply a 5-year bank loan of $500000 at 12% APR, repayable in equal installments at the end of each month.
a. How much do you need to pay each month?
(Round up your answer to the nearest two decimal points)
b.How much is owed (to the bank) at the end of year 3?
(Round up your answer to the nearest two decimal points)
c. How much interest is paid at the first month of year 4 of the loan?
(Round up your answer to the nearest two decimal points)
d. How much interest is paid over the last two years of the loan? (Round up your answer to the nearest two decimal points)
a) EMI = Loan/PVIFA(r%,n)
r = rate of interest = 12%/12 = 1%
n = no. of months = 5 x 12 = 60
PVIFA(r%,n) = [1-(1/(1+r)^n / r ]
PVIFA(1%,60) = [1-(1/(1+1%)^60 / 1%]
=[1-(1/(1+0.01)^60 / 0.01]
=[1-(1/(1.01)^60 / 0.01]
=[1-0.55044 / 0.01]
=0.44955/0.01
=44.96
Thus EMI = 500000/44.96
=11122.22 $
Amortization schedule
Towards | |||||
Installment No. | Opening balance | Installments | Interest @ 1% | Principal | Closing balance |
A | B | C = A x 1% | D = B-C | E = A-D | |
1 | 500000.00 | 11122.22 | 5000.00 | 6122.22 | 493877.78 |
2 | 493877.78 | 11122.22 | 4938.78 | 6183.44 | 487694.34 |
3 | 487694.34 | 11122.22 | 4876.94 | 6245.28 | 481449.06 |
4 | 481449.06 | 11122.22 | 4814.49 | 6307.73 | 475141.33 |
5 | 475141.33 | 11122.22 | 4751.41 | 6370.81 | 468770.53 |
6 | 468770.53 | 11122.22 | 4687.71 | 6434.51 | 462336.01 |
7 | 462336.01 | 11122.22 | 4623.36 | 6498.86 | 455837.15 |
8 | 455837.15 | 11122.22 | 4558.37 | 6563.85 | 449273.30 |
9 | 449273.30 | 11122.22 | 4492.73 | 6629.49 | 442643.81 |
10 | 442643.81 | 11122.22 | 4426.44 | 6695.78 | 435948.03 |
11 | 435948.03 | 11122.22 | 4359.48 | 6762.74 | 429185.29 |
12 | 429185.29 | 11122.22 | 4291.85 | 6830.37 | 422354.93 |
13 | 422354.93 | 11122.22 | 4223.55 | 6898.67 | 415456.26 |
14 | 415456.26 | 11122.22 | 4154.56 | 6967.66 | 408488.60 |
15 | 408488.60 | 11122.22 | 4084.89 | 7037.33 | 401451.26 |
16 | 401451.26 | 11122.22 | 4014.51 | 7107.71 | 394343.56 |
17 | 394343.56 | 11122.22 | 3943.44 | 7178.78 | 387164.77 |
18 | 387164.77 | 11122.22 | 3871.65 | 7250.57 | 379914.20 |
19 | 379914.20 | 11122.22 | 3799.14 | 7323.08 | 372591.12 |
20 | 372591.12 | 11122.22 | 3725.91 | 7396.31 | 365194.81 |
21 | 365194.81 | 11122.22 | 3651.95 | 7470.27 | 357724.54 |
22 | 357724.54 | 11122.22 | 3577.25 | 7544.97 | 350179.57 |
23 | 350179.57 | 11122.22 | 3501.80 | 7620.42 | 342559.14 |
24 | 342559.14 | 11122.22 | 3425.59 | 7696.63 | 334862.51 |
25 | 334862.51 | 11122.22 | 3348.63 | 7773.59 | 327088.92 |
26 | 327088.92 | 11122.22 | 3270.89 | 7851.33 | 319237.59 |
27 | 319237.59 | 11122.22 | 3192.38 | 7929.84 | 311307.74 |
28 | 311307.74 | 11122.22 | 3113.08 | 8009.14 | 303298.60 |
29 | 303298.60 | 11122.22 | 3032.99 | 8089.23 | 295209.37 |
30 | 295209.37 | 11122.22 | 2952.09 | 8170.13 | 287039.24 |
31 | 287039.24 | 11122.22 | 2870.39 | 8251.83 | 278787.41 |
32 | 278787.41 | 11122.22 | 2787.87 | 8334.35 | 270453.07 |
33 | 270453.07 | 11122.22 | 2704.53 | 8417.69 | 262035.38 |
34 | 262035.38 | 11122.22 | 2620.35 | 8501.87 | 253533.51 |
35 | 253533.51 | 11122.22 | 2535.34 | 8586.88 | 244946.63 |
36 | 244946.63 | 11122.22 | 2449.47 | 8672.75 | 236273.87 |
37 | 236273.87 | 11122.22 | 2362.74 | 8759.48 | 227514.39 |
38 | 227514.39 | 11122.22 | 2275.14 | 8847.08 | 218667.32 |
39 | 218667.32 | 11122.22 | 2186.67 | 8935.55 | 209731.77 |
40 | 209731.77 | 11122.22 | 2097.32 | 9024.90 | 200706.87 |
41 | 200706.87 | 11122.22 | 2007.07 | 9115.15 | 191591.72 |
42 | 191591.72 | 11122.22 | 1915.92 | 9206.30 | 182385.41 |
43 | 182385.41 | 11122.22 | 1823.85 | 9298.37 | 173087.05 |
44 | 173087.05 | 11122.22 | 1730.87 | 9391.35 | 163695.70 |
45 | 163695.70 | 11122.22 | 1636.96 | 9485.26 | 154210.43 |
46 | 154210.43 | 11122.22 | 1542.10 | 9580.12 | 144630.32 |
47 | 144630.32 | 11122.22 | 1446.30 | 9675.92 | 134954.40 |
48 | 134954.40 | 11122.22 | 1349.54 | 9772.68 | 125181.73 |
49 | 125181.73 | 11122.22 | 1251.82 | 9870.40 | 115311.32 |
50 | 115311.32 | 11122.22 | 1153.11 | 9969.11 | 105342.22 |
51 | 105342.22 | 11122.22 | 1053.42 | 10068.80 | 95273.42 |
52 | 95273.42 | 11122.22 | 952.73 | 10169.49 | 85103.93 |
53 | 85103.93 | 11122.22 | 851.04 | 10271.18 | 74832.75 |
54 | 74832.75 | 11122.22 | 748.33 | 10373.89 | 64458.86 |
55 | 64458.86 | 11122.22 | 644.59 | 10477.63 | 53981.23 |
56 | 53981.23 | 11122.22 | 539.81 | 10582.41 | 43398.82 |
57 | 43398.82 | 11122.22 | 433.99 | 10688.23 | 32710.59 |
58 | 32710.59 | 11122.22 | 327.11 | 10795.11 | 21915.47 |
59 | 21915.47 | 11122.22 | 219.15 | 10903.07 | 11012.41 |
60 | 11012.41 | 11122.22 | 109.81 | 11012.41 | 0.00 |
Thus a) Monthly payment = $11122.22
b) Amount owed (to the bank) at the end of year 3 = closing balance of installment no 36 = $236273.87
c) Interest paid at the first month of year 4 of the loan = Interest of installment no 37 = $2362.74
d) Interest paid over the last two years of the loan = $30659.41 ( Total of interest paid from installment no.37 to installment no 60)