Question

Excel work, please

Assume that a lender offers a 30-year, $150,000 adjustable rate mortgage (ARM) with the following terms: Initial interest rate = 7.5 percent Index = one-year Treasuries Payments reset each year Margin = 2 percent Interest rate cap = 1 percent annually; 3 percent lifetime Discount points = 2 percent Fully amortizing; however, negative amortization allowed if interest rate caps reached Based on estimated forward rates, the index to which the ARM is tied is forecasted as follows: Beginning of year (BOY) 2 = 7 percent; (BOY) 3 = 8.5 percent; (BOY) 4 = 9.5 percent; (EOY) 5 = 11 percent. Compute the payments, loan balances, and yield for the ARM for the five-year period.

Answer 1

Ans.

Calculation of  the payments, loan balances, and Yield for an ARM that having a 1% annual and 3% lifetime interest rate cap and does not accumulate negative amortization.

Information given in question as

Principal=$150,000 , Points=2.00% ,Term=30 years , Initial Rate=7.5%

Year	BOY Balance (1)	Uncapped Interest Rate (2)	capped Interest Rate (3)	Monthly Interest Rate (4) = (3)/12	Payment @ Capped Rate (5)	Monthly Interest (6) = {(1)*(3)}/12	Monthly Amortization (7) = (5)-(6)	Annual Amortization (8)	EOY Balance (9) = (1) - (8)
0
1	$150,000	7.50%	7.50%	0.63%	$1,048.82	$937.50	$111.32	$1,382.75	$148,617
2	$148,617	9.00%	8.50%	0.71%	$1,151.44	$1,052.71	$98.73	$1,232.11	$147,385
3	$147,385	10.50%	9.50%	0.79%	$1,255.55	$1,166.80	$88.75	$1,112.59	$146,273
4	$146,273	11.50%	10.50%	0.88%	$1,360.78	$1,279.88	$80.90	$1,018.54	$145,254
5	$145,254	13.00%	10.50%	0.88%	$1,360.78	$1,270.97	$89.81	$1,131.12	$144,123
	$144,123

Calculation of IRR

Cash Flows (CF)		Years (n)
-$147,000.00
$1,048.82		12
$1,151.44		12
$1,255.55		12
$1,360.78		12
$1,360.78		11
1360.78 + 144123		1

Solve for IRR

= 0.8% * 12 = 9.65% (annual rate , compounded monthly)