Question

Ann would like to buy a house.

It costs $2,500,000.

Her down payment will be $50,000.

She will take out a mortgage for the remainder.

It will be a 30 year, fully amortizing, FRM, with constant monthly payments and monthly compounding.

The annual interest rate is 4.50%.

She will pay $5,000 in closing costs at origination.

She will also pay 1.75% of the balance in buy-down points at origination.

Note: the home is bought and the loan is taken in month 0, the first payment is due in month 1.

In the spreadsheet where it says “cash inflow”, “outflow” and “net cash flow” you should only take into account cash flow related to the mortgage.

She forecasts four possible scenarios for house price appreciation (HPA).

Optimistic Case: 4.5% annual HPA, hence 4.5/12% monthly HPA

Base Case: 2.5% annual HPA, hence 2.5/12% monthly HPA

Pessimistic Case: 0% annual HPA, hence 0/12% monthly HPA

Very Bad Case: -6% annual HPA, hence -6/12% monthly HPA

Answer 1

There will be 4 scenarios of cash flow statements that ANN will have to analyse as per 4 HPA given:

cost of the house	$2,500,000
down payment	$50,000
House loan	$2,450,000
term (years)	30
monthly payment of principal	$6,806
annual interest rate	4.50%
Monthly interest rate	0.38%
	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec	total
Monthly interest	$9,187.50	$9,162	$9,136	$9,111	$9,085	$9,060	$9,034	$9,009	$8,983	$8,958	$8,932	$8,907	$108,566
principal left	$2,443,194.44	$2,436,389	$2,429,583	$2,422,778	$2,415,972	$2,409,167	$2,402,361	$2,395,556	$2,388,750	$2,381,944	$2,375,139	$2,368,333
Annual HPA	4.50%
Cash flow statement
Gross Scheduled Income	annual HPA	4.50%	$112,500
Gross Operating Income (GOI)			112,500
Operating Expenses
Repairs and Maintenance	0.00%	$0
Water and Sewer	0.00%	$0
Total Operating Expenses	0.00%		0
Net Operating Income			112,500
- Debt Service (Principal and Interest)
closing cost		$5,000
buy-down points	1.75%	42,875
Principal repayment			81,667
interest payment			108,566
Net cash flow			($77,732)
Cash flow statement
Gross Scheduled Income	annual HPA	2.50%	$62,500
Gross Operating Income (GOI)			62,500
Operating Expenses
Repairs and Maintenance	0.00%	$0
Water and Sewer	0.00%	$0
Total Operating Expenses	0.00%		0
Net Operating Income			62,500
- Debt Service (Principal and Interest)
closing cost		$5,000
buy-down points	1.75%	42,875
Principal repayment			81,667
interest payment			108,566
Net cash flow			($127,732)
Cash flow statement
Gross Scheduled Income	annual HPA	0.00%	$0
Gross Operating Income (GOI)			0
Operating Expenses
Repairs and Maintenance	0.00%	$0
Water and Sewer	0.00%	$0
Total Operating Expenses	0.00%		0
Net Operating Income			0
- Debt Service (Principal and Interest)
closing cost		$5,000
buy-down points	1.75%	42,875
Principal repayment			81,667
interest payment			108,566
Net cash flow			($190,232)
Cash flow statement
Gross Scheduled Income	annual HPA	-6.00%	($150,000)
Gross Operating Income (GOI)			$    (150,000.00)
Operating Expenses
Repairs and Maintenance	0.00%	$0
Water and Sewer	0.00%	$0
Total Operating Expenses	0.00%		0
Net Operating Income			$    (150,000.00)
- Debt Service (Principal and Interest)
closing cost		$5,000
buy-down points	1.75%	42,875
Principal repayment			81,667
interest payment			108,566
Net cash flow			($340,232)