A real estate investor would like to buy a Haunted Mansion residential rental property in Pomona, and needs to figure out how much it's worth considering debt financing and a 3-year planned holding horizon. The loan would be a fixed-rate amortizing loan for 15 years requiring monthly payments, with an annual rate of 7%. The planned debt service would equal $70,000 each year. The investor's required return is 13% before taxes on equity invested into the Haunted Mansion. In the table below there is additional information on the investor's estimated annual net operating income (NOI) for each year and the estimate resale price.

If you can, do the math in Excel with correct referencing to cells with intermediate results. This will allow you to avoid rounding errors. Otherwise, increase decimal places - the more the better! Say, 6 or even higher.

First, fill out the missing values in the table below.

	Year 1	Year 2	Year 3	Year 3 ***	***
NOI	$80,000	$86,000	$92,000	$852,174	Resale price
Debt service	$70,000	$70,000	$70,000	$ [ Select ] ["1", "2", "3", "4", "5"] 1: 537,992 2: 567,235 3: 631,282 4: 792,983 5: 1,169,219	Loan balance
Before-tax cash flow on equity	$ [ Select ] ["1", "2", "3", "4", "5"] 1: 7,000 2: 10,000 3: 25,000 4: 28,000 5: 40,000	$ [ Select ] ["1", "2", "3", "4", "5"] 1: 16,000 2: 22,000 3: 30,000 4: 32,000 5: 49,000	$ [ Select ] ["1", "2", "3", "4", "5"] 1: 22,000 2: 35,000 3: 36,000 4: 37,000 5: 58,000	$ [ Select ] ["1", "2", "3", "4", "5"] 1: 109,042 2: 120,060 3: 284,939 4: 429,588 5: 462,008	Before-tax equity cash flow from sale

Next, calculate other financing information relevant to the investor:

The implied loan amount is $ [ Select ] ["1", "2", "3", "4", "5"] .	The implied debt coverage ratio is [ Select ] ["1", "2", "3", "4", "5"] .
1: 622,143 2: 648,993 3: 715,044 4: 840,704 5: 1,263,017	1: 1.11 2: 1.14 3: 1.34 4: 1.38 5: 1.50

Finally, calculate additional investment information:

Maximum downpayment (or equity investment) is $ [ Select ] ["1", "2", "3", "4", "5"] .	Maximum acceptable asking price is $ [ Select ] ["1", "2", "3", "4", "5"] .	Implied going-in cap rate is [ Select ] ["1", "2", "3", "4", "5"] %.
1: 156,606 2: 202,360 3: 234,104 4: 381,906 5: 399,457	1: 883,097 2: 997,309 3: 1,021,601 4: 1,096,950 5: 1,465,377	1: 6.85 2: 8.19 3: 9.02 4: 9.06 5: 10.03

Considering the investor's estimated cash flows, and based on our class discussions on this topic, would the lender be willing to offer the loan to this investor? [ Select ] ["1", "2"] .

1	Yes
2	No