In: Finance
You have just bought an apartment for ¥62 million, using a deposit amounting to ¥8 million, and you borrowed the rest. The loan interest rate is 3.2% p.a compounded monthly and you have taken the loan with a twenty year life.
Calculate the monthly repayments needed to fully repay the loan within twenty years (assuming repayments occur at the end of the month).
After 30 months the interest rate increases to 4.2% p.a. what is the new monthly payment needed now to ensure the loan is still paid of within the twenty years?
1. Amount borrowed = 62-8 = 54 million yen
Effective annual interest rate = [1+(3.2/1200)]^12 - 1
= 3.247353% per annum
Now PV = 54 million (or 54,000,000), r = 3.247353%/12 (monthly rate) and n = 20*12 = 240
Thus monthly payment can be computed in excel, using the "pmt" function.
Using the pmt function we get a value of 306,213.21 yen per month.
2. Total principal payment made after 30 months is shown in the table below:
n | PMT | beg bal | int | closing bal |
1 | 306,213.21 | 54,000,000.00 | 146,130.89 | 53,839,917.67 |
2 | 306,213.21 | 53,839,917.67 | 145,697.68 | 53,679,402.14 |
3 | 306,213.21 | 53,679,402.14 | 145,263.31 | 53,518,452.24 |
4 | 306,213.21 | 53,518,452.24 | 144,827.76 | 53,357,066.78 |
5 | 306,213.21 | 53,357,066.78 | 144,391.03 | 53,195,244.60 |
6 | 306,213.21 | 53,195,244.60 | 143,953.11 | 53,032,984.50 |
7 | 306,213.21 | 53,032,984.50 | 143,514.02 | 52,870,285.31 |
8 | 306,213.21 | 52,870,285.31 | 143,073.73 | 52,707,145.83 |
9 | 306,213.21 | 52,707,145.83 | 142,632.26 | 52,543,564.88 |
10 | 306,213.21 | 52,543,564.88 | 142,189.59 | 52,379,541.25 |
11 | 306,213.21 | 52,379,541.25 | 141,745.72 | 52,215,073.76 |
12 | 306,213.21 | 52,215,073.76 | 141,300.65 | 52,050,161.19 |
13 | 306,213.21 | 52,050,161.19 | 140,854.37 | 51,884,802.36 |
14 | 306,213.21 | 51,884,802.36 | 140,406.89 | 51,718,996.04 |
15 | 306,213.21 | 51,718,996.04 | 139,958.20 | 51,552,741.02 |
16 | 306,213.21 | 51,552,741.02 | 139,508.29 | 51,386,036.10 |
17 | 306,213.21 | 51,386,036.10 | 139,057.17 | 51,218,880.06 |
18 | 306,213.21 | 51,218,880.06 | 138,604.82 | 51,051,271.66 |
19 | 306,213.21 | 51,051,271.66 | 138,151.25 | 50,883,209.70 |
20 | 306,213.21 | 50,883,209.70 | 137,696.45 | 50,714,692.95 |
21 | 306,213.21 | 50,714,692.95 | 137,240.43 | 50,545,720.16 |
22 | 306,213.21 | 50,545,720.16 | 136,783.16 | 50,376,290.11 |
23 | 306,213.21 | 50,376,290.11 | 136,324.66 | 50,206,401.57 |
24 | 306,213.21 | 50,206,401.57 | 135,864.92 | 50,036,053.28 |
25 | 306,213.21 | 50,036,053.28 | 135,403.94 | 49,865,244.01 |
26 | 306,213.21 | 49,865,244.01 | 134,941.71 | 49,693,972.50 |
27 | 306,213.21 | 49,693,972.50 | 134,478.23 | 49,522,237.52 |
28 | 306,213.21 | 49,522,237.52 | 134,013.49 | 49,350,037.80 |
29 | 306,213.21 | 49,350,037.80 | 133,547.49 | 49,177,372.08 |
30 | 306,213.21 | 49,177,372.08 | 133,080.24 | 49,004,239.11 |
Thus principal amount due after 30 months = 49,004,239.11 yen or 49.0042 million yen
Now new interest rate = 4.2% and as this is also compunded monthly then effective rate =
[1+(4.2/1200)]^12 - 1 = 4.28180072%
Thus balance time left = 240 months - 30 months = 210 months
Again we will use the pmt function with PV = 49,004,239.11, r = 4.28180072%/12 and n = 210
We get a monthly payment of 331,996.01 yen in this case.