In the townhome investment model, do a sensitivity analysis by using a two-input data table. The two input variables are Rent and Resell Value: Rent ($16,000, $18,000,..., $30,000) Resell value ($250,000, $280,000, ..., $550,000) Show IRR as a function of these two variables. Use conditional formatting to highlight the unprofitable combinations.

Transcribed Image Text:

Yuting is considering buying a unit of townhome in Clearwater and renting it out for the income. The townhome will cost $220,000 and she is planning to buy it with all cash. Here are some additional facts: Yuting figures she can rent out the townhome for $24000 a year, and the rent will grow by approximately 3% per year. She'll have to pay property taxes of $3000 and property insurance of $2000 at the end of the year, and she assumes the property tax will grow at 3% per year as well, since real estate generally appreciates. And she is figuring on additional miscellaneous expenses of $1000 per year. All the income from the townhouse has to be reported on their annual tax return. Currently, Yuting has a tax rate of 24%, and she thinks her rate will continue for the foreseeable future. Her accountant has explained to her that she can depreciate rental property based on MACRS 27.5 year straight-line depreciation system. At the end of year 10 she plans to resell the townhome for $500,000. Since this is not her preminary residence, she will pay 15% capital gain on the income. -Calculate the cash flow for this investment each year. If she requires 12% annual return, what are the NPV and IRR for this investment? -Conduct a sensitivity analysis (a two-input data table) to show IRR as a function of resell value and rent. Highlight the combinations that are not profitable. Yuting is considering buying a unit of townhome in Clearwater and renting it out for the income. The townhome will cost $220,000 and she is planning to buy it with all cash. Here are some additional facts: Yuting figures she can rent out the townhome for $24000 a year, and the rent will grow by approximately 3% per year. She'll have to pay property taxes of $3000 and property insurance of $2000 at the end of the year, and she assumes the property tax will grow at 3% per year as well, since real estate generally appreciates. And she is figuring on additional miscellaneous expenses of $1000 per year. All the income from the townhouse has to be reported on their annual tax return. Currently, Yuting has a tax rate of 24%, and she thinks her rate will continue for the foreseeable future. Her accountant has explained to her that she can depreciate rental property based on MACRS 27.5 year straight-line depreciation system. At the end of year 10 she plans to resell the townhome for $500,000. Since this is not her preminary residence, she will pay 15% capital gain on the income. -Calculate the cash flow for this investment each year. If she requires 12% annual return, what are the NPV and IRR for this investment? -Conduct a sensitivity analysis (a two-input data table) to show IRR as a function of resell value and rent. Highlight the combinations that are not profitable.

Answer rating: 100% (QA)

Setup Excel model as follows EXCEL FORMULA To create TwoWay Data ... View the full answer