Question

1. You are considering taking out an adjustable rate mortgage (ARM) to finance your new home and have been given the following information by the bank:

• $250,000 house price
• 80% Loan to Value
• 200 bps margin
• 2.5% LIBOR – Index for Year 1
• Teaser Rate in Year 1 – 2%
• Periodic Cap – 1.5%
• Life Cap – 5%

You also went on Bloomberg and found the following LIBOR Index projections for the next few years:

• 3% - Index for Year 2
• 5% - Index for Year 3
• 6% - Index for Year 4
• 6.5% - Index for Year 5

Build out an amortization schedule for the first 5 years (60 months). Show all work/calculations in your Excel file to receive full credit.

Answer 1

Given: House Price - $ 250,000, Loan Value - 80% of house value = $ 200,000, ARM = 2%, Teaser rate = 1% - 2%, Periodic cap = 1.5%, Life Cap = 5%.

Teaser rate is the introductory rate, which can be given for first year only. Let's say, here it is 1%.

Adjustable rate margin (ARM) is the extra interest rate on the indexed value of reference index (LIBOR), which is 2% here.

Periodic cap is the interest rate limit between periods, which is 1.5%, here. It means, your interest rate can not change for more than 1.5% between two consecutive periods.

Life cap is the max limit of interest rate, which you need to pay. Here, if we take LIBOR index rates and add 2% in that from our ARM, then it will always cross our periodic limit of 1.5%. Hence, we are only increasing 1.5% interest every year, upto 5% cap limit. Let's assume, that the person is paying an EMI of 6000 per month, then below is the amortization schedule for his loan and he will be paying his loan in 49th month.

Period	Principal Amount	Interest rate	Interest Component	Principal Component	EMI	Outstanding Principal
1	200,000	1%	2000	4000	6000	196,000
2	196,000	1%	1960	4040	6000	191,960
3	191,960	1%	1919.6	4080.4	6000	187,880
4	187,880	1%	1878.796	4121.204	6000	183,758
5	183,758	1%	1837.58396	4162.41604	6000	179,596
6	179,596	1%	1795.9598	4204.0402	6000	175,392
7	175,392	1%	1753.919398	4246.080602	6000	171,146
8	171,146	1%	1711.458592	4288.541408	6000	166,857
9	166,857	1%	1668.573177	4331.426823	6000	162,526
10	162,526	1%	1625.258909	4374.741091	6000	158,151
11	158,151	1%	1581.511498	4418.488502	6000	153,733
12	153,733	1%	1537.326613	4462.673387	6000	149,270
13	149,270	1.50%	2239.049819	3760.950181	6000	145,509
14	145,509	1.50%	2182.635566	3817.364434	6000	141,692
15	141,692	1.50%	2125.3751	3874.6249	6000	137,817
16	137,817	1.50%	2067.255726	3932.744274	6000	133,884
17	133,884	1.50%	2008.264562	3991.735438	6000	129,893
18	129,893	1.50%	1948.388531	4051.611469	6000	125,841
19	125,841	1.50%	1887.614359	4112.385641	6000	121,729
20	121,729	1.50%	1825.928574	4174.071426	6000	117,555
21	117,555	1.50%	1763.317503	4236.682497	6000	113,318
22	113,318	1.50%	1699.767265	4300.232735	6000	109,018
23	109,018	1.50%	1635.263774	4364.736226	6000	104,653
24	104,653	1.50%	1569.792731	4430.207269	6000	100,223
25	100,223	3%	3006.679244	2993.320756	6000	97,229
26	97,229	3%	2916.879621	3083.120379	6000	94,146
27	94,146	3%	2824.38601	3175.61399	6000	90,971
28	90,971	3%	2729.11759	3270.88241	6000	87,700
29	87,700	3%	2630.991118	3369.008882	6000	84,331
30	84,331	3%	2529.920851	3470.079149	6000	80,861
31	80,861	3%	2425.818477	3574.181523	6000	77,286
32	77,286	3%	2318.593031	3681.406969	6000	73,605
33	73,605	3%	2208.150822	3791.849178	6000	69,813
34	69,813	3%	2094.395347	3905.604653	6000	65,908
35	65,908	3%	1977.227207	4022.772793	6000	61,885
36	61,885	3%	1856.544023	4143.455977	6000	57,741
37	57,741	4.50%	2598.360516	3401.639484	6000	54,340
38	54,340	4.50%	2445.286739	3554.713261	6000	50,785
39	50,785	4.50%	2285.324642	3714.675358	6000	47,070
40	47,070	4.50%	2118.164251	3881.835749	6000	43,188
41	43,188	4.50%	1943.481643	4056.518357	6000	39,132
42	39,132	4.50%	1760.938317	4239.061683	6000	34,893
43	34,893	4.50%	1570.180541	4429.819459	6000	30,463
44	30,463	4.50%	1370.838665	4629.161335	6000	25,834
45	25,834	4.50%	1162.526405	4837.473595	6000	20,996
46	20,996	4.50%	944.8400933	5055.159907	6000	15,941
47	15,941	4.50%	717.3578975	5282.642102	6000	10,659
48	10,659	4.50%	479.6390029	5520.360997	6000	5,138
49	5,138	5%	256.9141756	5743.085824	6000	-605
50	-605	5%	-30.2401156	6030.240116	6000	-6,635
51	-6,635	5%	-331.7521214	6331.752121	6000	-12,967
52	-12,967	5%	-648.3397274	6648.339727	6000	-19,615
53	-19,615	5%	-980.7567138	6980.756714	6000	-26,596
54	-26,596	5%	-1329.79455	7329.79455	6000	-33,926
55	-33,926	5%	-1696.284277	7696.284277	6000	-41,622
56	-41,622	5%	-2081.098491	8081.098491	6000	-49,703
57	-49,703	5%	-2485.153415	8485.153415	6000	-58,188
58	-58,188	5%	-2909.411086	8909.411086	6000	-67,098
59	-67,098	5%	-3354.88164	9354.88164	6000	-76,453
60	-76,453	5%	-3822.625722	9822.625722	6000	-86,275