Objective:

Suppose Sally and Dave are considering buying an investment apartment, renting it for five years and then selling it. They are planning to buy the property with a mortgage loan with 5-year Fixed Interest rates.

They ask your advice to evaluate if this is a profitable investment.

Assumptions/Input Data:

• The condo costs $600,000 and depreciates over 30 years.
• Investors are planning to buy it with a mortgage loan with a 10% down payment, monthly payment frequency, and 25 years horizon. 5-year Fixed Interest rates are currently 5.25% APR. [assume it is repaid at the beginning of each month].
• The current rent for the condo is $2,500 per month [received at the beginning of the month]
• There are additional miscellaneous expenses:
  • condo fees of $200 per month [paid at the beginning of each month]
  • insurance on mortgage + maintenance $50 per month [paid at the beginning of each month]
  • tenant management fee $2000 [paid at the beginning of each year]
  • property taxes of $1,500 annually [paid at the end of each year]
• All expenses and revenues will grow at the long-term inflation rate (i.e. 2%), except the property tax, which will grow with the property value (i.e., 5%)
• Currently, Sally and Dave have an effective tax rate of 30%. But their expenses related to their rental income are tax-deductible. [i.e. these expenses reduce their taxable income (or EBIT), therefore they pay less taxes. In general, the non-taxable expenses need to be subtracted from the after-tax income]
• Investors predict that during the next 5 years, the price of this condo will appreciate by 5% per year.
• The transaction costs for buying and selling the condo are
  • 3% of condo price at the time of purchase, for city tax/ legal fees/
  • 5% of condo price at the time of selling, for the realtors/ legal fees/

Model Output:

In MS-Excel, create a model, where you input the above data points and the model gives the following information as an output:

• What is the Cash Flow for each month? Assume taxes are paid in December of each year.
• What is the IRR of this investment?
• What is the IRR if inflation rises to 3%? Or drops to 1%?

If you find working with monthly and annual frequencies together in a spreadsheet too complicated, for this assignment it is acceptable to work with only the annual frequency.

***Mention the excel formulas used