Question

Make a comparison between two investment choices, based on the information given below.

Investment opportunity one: investing in stock market. Five years ago, SP500 stock index was 1312. Currently, it is 2618.97. What is the average annual rate of return of SP500 stock index in last five years?

Investment opportunity two: Five years ago, someone used her $40,000 saving to make a down payment for a townhouse in RTP. The house is a three-bedroom townhouse and sold for $200,000 when she bought it. After paying down payment, she financed the house by borrowing a 30-year mortgage. Mortgage interest rate is 3.625%. Right after closing, she rent out the house for $1,650 per month. In addition to mortgage payment and rent revenue, she listed the following information so as to figure out investment return:

1. HOA fee is $75 per month and due at end of each year

2. Property tax and insurance together are 3% of house value

3. She has to pay 10% of rent revenue for an agent who manages her renting regularly

4. Her personal income tax rate is 20%. While rent revenue is taxable, the mortgage interest is tax deductiable. She has to make the mortgage amortization table to figure out how much interest she paid each year

5. In last five years, the market value of the house has increased by 3.87% per year

6. If she wants to sell the house today, the total transaction cost will be 5% of selling price Given the above information, please calculate the internal rate of return (IRR) of this investment in house.

SHOW CALCULATIONS AND FORMULA USED IN EXCEL ONLY

Answer 1

Case 1	Index	1312
	Current index	2618.97
	Annual return on property	14.8%

case 2

Cash Flow
Invested amount	40,000
2014	13,404
2015	19,090
2016	13,111
2017	12,955
2018	12,794
selling out of property	77342
NPV of cash inflow @3.625%	157128.5
Annual return on property	31.5%

Case 2	Investment in House
Suppose current year is 2018
	Cash flow	House value	200,000
	2014	Invested in house, down payment	-40,000
		Taken Loan
	2014	EMI payment every year @3.625%	-8756.16
	2014	Rent out house @ 1650/month	19800
		HOA fees@75/month	-900
		Property tax @3% of house value	-6000
		Payment to agent for managing rent@ 10%	-1980
	2014	Profit for tax	14,050
		Tax@20%	2809.926
		Profit after tax	11,240
	2014	Total Profit	13,404
		House value	207740
	2015	EMI payment every year @3.625%	-8756.16
		Rent out house @ 1650/month	19800
		HOA fees@75/month	-900
		Property tax @3% of house value	-402.10632
		Payment to agent for managing rent@ 10%	-1980
		Profit for tax	14,160
		Tax@20%	2832.084
		Profit after tax	11,328
		0
	2015	Total Profit	19,090
		House value	215779.538
	2016	EMI payment every year @3.625%	-8756.16
		Rent out house @ 1650/month	19800
		HOA fees@75/month	-900
		Property tax @3% of house value	-6473.38614
		Payment to agent for managing rent@ 10%	-1980
		0	0
		Profit for tax	14,275
		Tax@20%	2855.056
		Profit after tax	11,420
	2016	Total Profit	13,111
		House value	224130.2061
	2017	EMI payment every year @3.625%	-8756.16
		Rent out house @ 1650/month	19800
		HOA fees@75/month	-900
		Property tax @3% of house value	-6723.906184
		Payment to agent for managing rent@ 10%	-1980
		Profit for tax	14,394
		Tax@20%	2878.876
		Profit after tax	11,516
	2017	Total Profit	12,955
		House value	232804.0451
	2018	EMI payment every year @3.625%	-8756.16
		Rent out house @ 1650/month	19800
		HOA fees@75/month	-900
		Property tax @3% of house value	-6984.121353
		Payment to agent for managing rent@ 10%	-1980
		Profit for tax	14,518
		Tax@20%	2903.572
		Profit after tax	11,614
	2018	Total Profit	12,794
	Selling out the property
		Value	232804
		transaction cost	-11640
		Total cash inflow	221164
		Outstanding loan	-143822
		Total cash inflow after clearing loan	77342

Cash Flow
Invested amount	40,000
2014	13,404
2015	19,090
2016	13,111
2017	12,955
2018	12,794
selling out of property	77342
NPV of cash inflow @3.625%	157128.5
Annual return on property	31.5%