You work for a large buy-to-rent company called Invitation Homes. This client is

purchasing thousands of single-family homes throughout the country and converting them

into rental properties, betting that there will be more demand for renting than homeowning

because housing prices are becoming so unaffordable. Please use the spreadsheet in the

Assignments section on Canvas to answer the following questions:

1. First, you need to calculate the PBTCFs using the following information:

In the first year of the investment, you plan to charge $3,500 in rent per month.

You expect rents to grow by 3% every year.

Due to the recession, you are willing to offer the tenant forbearance, meaning

you wont collect rent from them for the first three months of year 1. (You are

willingly conceding this temporary break.) You will require the tenant to pay back

the deferred rent in year 2.

You can run this building with operating expenses equal to 25% of EGI. The tenant

is responsible for paying the cost of water and power, which account for

approximately 50% of the operating expenses.

You can lease the unit immediately upon purchasing it. The tenant will sign an 18-

month lease, after which you will have to find a new tenant. Based on past

experience, you expect it will take you two months to find a new tenant, who will

then lease it for 18 months, followed by another two months to find a new tenant,

and so forth.

You purchase the building for $450,000. You expect it to appreciate 3.5% per year.

You set aside 5% of EGI per year in capital reserves.

When you sell the property, you pay 6% of the resale price in fees and expenses.

2. Second, using the discounting formula, you must discount each PBTCF and add them

together. This is your property value.

Your shareholders expect to earn 9.5% on their investments.

3. What is the NPV of investing in this property?

4. What is the IRR of this investment?

5. Finally, your boss wants you to do a sensitivity analysis to determine whether the

investment is still worthwhile if the future doesnt work out as expected. How do your

NPV and IRR change under the following scenarios?

Scenario A: Rents grow by 1.5% per year instead of 3% per year.

Scenario B: Operating expenses cost 40% of EGI instead of 25%.

Scenario C: It takes four months to find new tenants instead of two months.

Scenario D: The building requires major renovations in year 4, costing $150,000.

Please show work/formulas in these column/cells using excel

Font Formatting Table Alignment Number Styles B C D E F G H 1 J K L M 0 1 2 3 4 5 6 7 8 9 10 Potential Gross Income Vacancy & Collection Losses Other Income Rental Concessions Effective Gross Income Operating Expenses Expense Recoveries Net Operating Income Capital Expenditures Operating Cash Flow Resale Price Selling Expenses Reversion Cash Flow Property-Before-Tax Cash Flow 3 9 0 21 22 23 24 25 26 27 28 29 01 Ready CO