Question

NEEDS TO BE DONE IN EXCEL WITH CALCULATIONS.

MakeNu mortgage company is offering a new mortgage instrument called the stable mortgage. This mortgage is composed of both a fixed rate and an adjustable rate component. Mrs. Maria Perez is interested in in financing a property, which costs 100,000 and is to be financed by Stable Home Mortgages( SHM) on the following terms:
a. The SHM requires a 5 percent down payment, costs the borrower 2 discount points, and allows 75 percent of the mortgage to be fixed and 25 percent to be adjustable. The fixed portion of the loan is for 30 years at an annual interest rate of 10.5 percent. Having neither an interest rate nor payment cap, the adjustable portion is also for 30 years with the following terms.

Initial interest rate = 9 percent

Index= one year treasuries

Payments reset each year

Margin= 2 percent

Interest rate cap= none

Payment cap= none

The projected one year U.S. Treasury bill index to which the ARM is tied, is as follows:
(BOY) 2= 10 percent; (BOY) 3= 11 percent; (BOY) 4= 8 percent; (BOY) 5 = 12 Percent
Calculate Mrs. Perez's total monthly payments and end of year loan balances for the first five years. Calculate the lenders yield, assuming Mrs. Perez repays the loan after five years.
b. Repeat part (a) under the assumption that the initial interest rate is 9.5 percent and there is an annual interest rate cap of 1 percent.

Answer 1

 

a.)

Based on the description the total loan amount would be:

Loan Amount = $95,000

Points = 2.00%

Fixed Rate Portion: 75.00% of the loan balance

10.50% annual interest rate

30 year term

.

	(1)	(2)	(3)	(4)	(5)	(6)	(7)	(8)
Year	BOY Balance	Annual Interest Rate	Monthly Interest Rate (2)/12	Payments	Monthly Interest    (3) x (1)	Monthly Amort    (4) -(5)	Annual Amort.	EOY Balance   (1) - (7)
0
1	$71,250	10.50%	0.88%	$651.75	$623.44	$28.31	$356.61	$70.893
2	70,893	10.50%	0.88%	651.75	620.32	31.43	395.91	70.497
3	70,497	10.50%	0.88%	651.75	616.85	34.90	439.54	70,058
4	70,058	10.50%	0.88%	651.75	613.01	38.74	487.98	69,570
5	69,570	10.50%	0.88%	651.75	608.74	43.01	541.75	69,028

.

Adjustable Rate Portion:	25.00% of the loan balance
	9.00% initial interest rate
	2.00% margin

..

	(1)	(2)	(3)	(4)	(5)	(6)	(7)	(8)
Year	BOY Balance	Annual Interest Rate	Monthly Interest Rate (2)/12	Payments	Monthly Interest    (3) x (1)	Monthly Amort     (4) -(5)	Annual Amort	EOY Balance   (1) - (7)
0
1	$23,750	9.00%	0.75%	$191.10	$178.13	$12.97	$162.26	$23,588
2	23,588	12.00%	1.00%	243.51	235.88	7.63	96.80	23,491
3	23,491	13.00%	1.08%	261.49	254.49	7.00	89.19	23,402
4	23,402	10.00%	0.83%	209.23	195.01	14.22	178.68	23,223
5	23,223	14.00%	1.17%	278.40	270.94	7.46	95.55	23,128

MORTGAGE SUMMARY:

 

.

YEAR	BOY Balance	Payments	EOY Balance
0
1	$95,000.00	$842.85	$94,481.13
2	94,481.13	895.26	93,988.42
3	93,988.42	913.24	93,459.69
4	93,459.69	860.99	92,793.03
5	82,793.03	930.15	92,155.73

Calculator: Calculator:IRR(CF1, CF2, ….CFn)

 

		CFj		nj
		-$93,100
		842.85		n = 12
		895.26		n = 12
		913.24		n = 12
		860.99		n = 12
		930.15		n = 11
		930.15 + 92,155.73		n = 1

Solve for the IRR:=0.94% x 12 = 11.26% (annual rate, compounded monthly)

.

b.) Same as the previous question (a). Only changing the term of Adjustable part.

Adjustable Rate Portion:	25.00% of the loan balance
	9.50% initial interest rate
	2.00% margin
	1.0% interest cap

.

	(1)	(2)	(3)	(4)	(5)	(6)	(7)	(8)
Year	BOY Balance	Capped Interest Rate	Monthly Interest Rate (2)/12	Payments	Monthly Interest    (3) x (1)	Monthly Amort     (4) -(5)	Annual Amort	EOY Balance   (1) - (7)
0
1	$23,750	9.50%	0.79%	$199.70	$188.02	$11.68	$146.42	$23,604
2	23,604	10.50%	0.88%	$217	$206.54	$10.47	$131.80	$23,472
3	23,472	11.50%	0.96%	$234.45	$224.94	$9.51	$120.33	$23,352
4	23,351	10.00%	0.83%	$208.78	$194.59	$14.19	$178.28	$23,173
5	23,173	11.00%	0.92%	$225.5	$212.42	$13.08	$165.13	$23,008

.MORTGAGE SUMMARY:

YEAR	BOY Balance	Payments	EOY Balance
0
1	$95,000.	$851.45	$94,497.
2	94,497.	868.75	93,969.
3	93,969.	886.20	93,409.
4	93,409.	860.53	92,743.
5	92,473.	877.25	92,036.

Yield: Using a financial calculator, the yield is now 11.01%.

.