Suppose that your client, a real estate investor, has asked you to evaluate an anchored retail shopping center that is for sale for $3,595,000 in Clearwater, Florida. You have been asked to perform an analysis of the property, including an estimate of cash flows and IRR, and to make a recommendation on whether or not your client should purchase the property. For this analysis, assume a five-year holding period. The shopping center has 33,250 square feet of rentable space. Since detailed information is not available on existing leases, assume that the property will lease at the average rent for the Tampa / St. Petersburg market. The average asking rent for Tampa is $12.90 and the average vacancy rate is 6.5%. Assume that rents will increase by 5% per year. Annual expenses are as follows: Insurance: $12,312 Utilities: $14,500 RE Taxes: $34,200 Cleaning: $4,500 Landscaping: $9,400 Repairs & Maintenance: $16,500 Miscellaneous: $6,000 These expenses will increase at the same rate as PGI. Financing is available up to 80% LTV with a 5-year interest-only mortgage at 7.5% with financing costs of 2% of the loan amount. The current overall cap rate (OAR) for this type of property is 8.7%. Apply it to year 6 NOI to calculate the sales price at the end of year 5. Assume sales costs of 5%. Assume that the building value equals 20% of the purchase price and can be depreciated over 39 years. The investor’s marginal tax rate is 33% and the capital gain tax rate is 15%. Depreciation recaptured is taxed at 25%. Essay Questions: Using Excel, construct a five-year discounted cash flow forecast for this investment (place this in tab 1). Note, you must include cash flows from operations and sale. Based on the cash flow statement, answer the following questions in tab 2: What is the initial equity at “time 0”? What is the net sales price and after-tax equity reversion in year 5? What is the 1st year’s after-tax cash flow? What is the after-tax IRR for this investment? Would you recommend purchase of this property to your client? In your responses, provide screenshots of your Excel document to support your answer. You will need to submit your essay responses as a single PDF document. There is no word limit or count. Investor Cash Flow Projection Year Initial Equity Investment Gross Income -Vacancy +Other Income Eff.Gross Income -Operation Expense Net Operating Income (NOI) -Capital Expenses -Debt Service(loan amount 2876000 ) Before Tax Operating Cash Flow Depreciation Taxable Income Operating Income Tax(@33%) After Tax Operating Cash Flow Calculating BTER Reversion Value -Sales Expenses Net Sales Price -Mortgage Balance Before Tax Equity Reversion Calculating ATER Depreciation Recaptured Taxable Gain Capital Gain Tax (@15%) Depreciation Recaptured Tax (@25%) After Tax Equity Reversion After Tax Cash Flow IRR
Suppose that your client, a real estate investor, has asked you to evaluate an anchored retail shopping center that is for sale for $3,595,000 in Clearwater, Florida. You have been asked to perform an analysis of the property, including an estimate of cash flows and
The shopping center has 33,250 square feet of rentable space. Since detailed information is not available on existing leases, assume that the property will lease at the average rent for the Tampa / St. Petersburg market. The average asking rent for Tampa is $12.90 and the average vacancy rate is 6.5%. Assume that rents will increase by 5% per year.
Annual expenses are as follows:
- Insurance: $12,312
- Utilities: $14,500
- RE Taxes: $34,200
- Cleaning: $4,500
- Landscaping: $9,400
- Repairs & Maintenance: $16,500
- Miscellaneous: $6,000
These expenses will increase at the same rate as PGI. Financing is available up to 80% LTV with a 5-year interest-only mortgage at 7.5% with financing costs of 2% of the loan amount. The current overall cap rate (OAR) for this type of property is 8.7%. Apply it to year 6 NOI to calculate the sales price at the end of year 5. Assume sales costs of 5%. Assume that the building value equals 20% of the purchase price and can be
Essay Questions:
Using Excel, construct a five-year discounted cash flow
- What is the initial equity at “time 0”?
- What is the net sales price and after-tax equity reversion in year 5?
- What is the 1st year’s after-tax cash flow?
- What is the after-tax IRR for this investment?
- Would you recommend purchase of this property to your client?
In your responses, provide screenshots of your Excel document to support your answer. You will need to submit your essay responses as a single PDF document. There is no word limit or count.
|
Investor Cash Flow Projection |
|
Year |
|
Initial Equity Investment |
|
Gross Income |
|
-Vacancy |
|
+Other Income |
|
Eff.Gross Income |
|
-Operation Expense |
|
Net Operating Income (NOI) |
|
-Capital Expenses |
|
-Debt Service(loan amount 2876000 ) |
|
Before Tax Operating Cash Flow |
|
Depreciation |
|
Taxable Income |
|
Operating Income Tax(@33%) |
|
After Tax Operating Cash Flow |
|
|
|
Calculating BTER |
|
Reversion Value |
|
-Sales Expenses |
|
Net Sales Price |
|
-Mortgage Balance |
|
Before Tax Equity Reversion |
|
|
|
Calculating ATER |
|
Depreciation Recaptured |
|
Taxable Gain |
|
Capital Gain Tax (@15%) |
|
Depreciation Recaptured Tax (@25%) |
|
After Tax Equity Reversion |
|
|
|
After Tax Cash Flow |
|
IRR |
Step by step
Solved in 2 steps with 19 images
