Pease solve using Excel, thank you.

You have the opportunity to invest in an office building in downtown Jacksonville, in which you would purchase as a 5-year holding investment with a 7.5% required rate of return (discount rate). The building offers a total rentable area of 20,000 square feet, and 100 garage-parking spaces. You are provided the following information on the property from the current owner:

1. The owner is asking $3,500,000 for the building.

2. The owner has annual contracts from tenants on the building for 18,000 square feet of the total space at $12.00 psf, gross. Additionally, 90 parking spaces are leased by annual contract for $120.00 per month, per space. You expect this same office vacancy to occur into perpetuity and expect parking vacancy to equally correlate with the office vacancy. These lease terms are found to be in line with the downtown office market.

3. Current annual operating expenses for the building follow:
   1. Management Fees: 5% of EGI
   2. Annual Real Estate Taxes: $9,000
   3. Hazard Insurance: $3,000
   4. Maintenance/Repairs: $11,000
   5. Supplies $4,000
   6. Capital Replacement Allowance: $8,000
   7. Administrative Costs: $4,500
   8. Operating Costs of the Garage: $6,500

Your mortgage lender has committed to you a loan to purchase the office building (should you decide to partake in the investment), which would offer the following terms:

Loan amount: $1,500,000

Interest Rate: 4% Fixed-rate mortgage

Loan Duration: 10 years, interest-only (no amortization)

A study of the office building market in downtown Jacksonville indicates the following trended increases in incomes and expenses, which can safely be assumed in analysis forecasts:

a. Office rents: 3% per annum

b. Parking rents: 3% per annum

c. Real Estate Taxes: 4% per annum

d. Other Operating Expenses: 4% per annum

You have also determined that you expect the capitalization rate at the end of year 5 to be 75 bps higher than the going-in cap rate.

Assume the selling expenses associated with selling the property at the end of year 5 are 3% of the selling price. With respect to depreciation, the office improvements will be depreciated on a straight-line basis over 39 years. Also assume the office improvements are 90% of the value of the Jacksonville office building.

Assume the owners tax rate is 35% for ordinary income and 20% for capital gains. Assume the tax rate on the recapture of depreciation is 25%.

Use the attached template for your answers.

Unlevered Analysis

1. Based on the above information, prepare a reconstructed year-one income and expense statement, and a reconstructed income and expense statement forecasting returns for the 5-year holding period (six years analysis required).
2. Calculate the before-tax, unlevered cash flows on the property. Calculate the going-in or purchase cap rate.
3. Calculate the unlevered cash-on-cash return.
4. Calculate the exit price and exit cash flow

Mortgage Analysis

1. Create the monthly and annual payments
2. Create the mortgage loan schedule
3. Calculate the DSCR

Before-tax Levered Analysis

1. Calculate the before-tax cash flows for the 5 years
2. Calculate the cash-on-cash return.
3. Calculate the exit cash flow

After-tax Levered Analysis

1. Calculate taxable income for the 5 years
2. Calculate the after-tax cash flows for the 5 years
3. Calculate the after-tax cash flow from selling the property

Lease analysis

The buyer is thinking about the structure of the leases in the building. The tenants currently pay $12 psf, full service gross with 3% annual rent escalation. Another alternative is for the tenants to accept pay $11.5 with a 2% annual rent escalation and a $1.70 base stop. What is better for the owner of the building? Recall that the owners required rate of return is 7.5%.

Office Parking Spaces Annually psf Per space per month Market office rent and parking rent 2 3 5 6 Year Market rent Office Rent Parking rent Growth Rati Growth Rati $ 120 Year 2 5 6 Gross Potential Rent-Office Gross Potential Rent- Parking Operating Expenses Projected Growth NA Mangement Fees Real Estate Taxes Insurance Maintenance Supplies Capital replacement Admin Operating Costs of Garage Amount 5% of EGI $ 9,000 $ 3,000 $ 11,000 $ 4,000 $ 8.000 $ 4,500 $ 6.500 Subtotal Expense projection Year 2 3 4 5 6 Growth rate Real Estate Taxes Insurance Maintenance Supplies Capital replacement Admin Operating Costs of Garage 2 3 5 6 1. Year-one and 5-Year Income Statement Year Rent Parking income Gross income Vacancy EGI Vacancy rate Real Estate Taxes Insurance Maintenance Supplies Capital replacement Admin Operating Costs of Garage Management fee Total Expenses of EGI 2. Calculate the before-tax, unlevered cash flows on the property. Calcualte the goingin cap rate on the property. NOI Initial purchase price Going -in caprate 3. Calculate the unlevered, cash-on-cash return. Cash-on-cash return 4. Calculate the exit price and the exit cash flow Exit cap rate Assumed sale price Sales expenses Net Sales Price Mortgage Analysis 1. Create the monthly and annual payments. Assumptions Mortgage Bal Mortgage rate Amortization (yrs) Monthly payment 2. Create the mortgage loan schedule 1 2 3 4 5 Year Annual payment Mtg balance Principal Interest 3. Calculate the DSCR DSCR Before-Tax Cash Flow with Debt 1. Calculate the Before-Tax Cash Flows fo the 5 years of the holding period 2 3 5 Year NOI Debt service BTCF 39 years 3 4 5 117 2. Calculate the cash-on-cash yield 118 119 Equity Invested 120 Cash-on-oash return 121 122 123 3. Calculate the exit cash flow 124 125 Before-Tax Cash Flow from Sale 126 Net Sales Price 127 Mtg Balance 128 BTCF sale 129 130 After-Tax Cash Flows 131 132 1.Calculate taxable income for the 5 years 133 134 Depreciation 135 Land value of total value 136 Improvements 137 Depreciationly 138 139 140 Year 1 2 141 NOI 142 Less Interest 143 Depreciation 144 145 Taxable income 146 Taxes 147 148 149 2. Calculate the after-tax cash flows for the 5 years. 150 151 After-tax CF 152 153 154 After-tax Cash Flow from the Sale 155 156 3. Calculate the after-tax cash flows from selling the property. 157 158 Price appreciation 159 Accumulated depreciation 160 161 Tax on appreciation 162 Tax on recapture 163 164 Total taxes of sale 165 166 167 Before-tax cash flow 168 Taxes 169 170 After-tax cash flow from sale Tax rate 20% Capital gains rate 25% Recapture rate Before-tax cash flow Taxes After-tax cash flow from sale Calculating Net Effective Annual Rents required rate of return Option 1 $12 psf with 3% annual escalation Year 2 3 4 5 6 Growth rate Growth rate Rent Less Operating Exp Add recoveries Net Rent 167 168 169 170 171 172 Lease Analysis 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 PV of rent Annual Eff Rer $11.50 psf with 2% annual escalations and a $1.70 base stop Year 1 2 3 4 5 6 Rent Less Operating Exp Add recoveries Net Rent Growth rate Growth rate Base Stop PV of rent Annual Eff Rer Office Parking Spaces Annually psf Per space per month Market office rent and parking rent 2 3 5 6 Year Market rent Office Rent Parking rent Growth Rati Growth Rati $ 120 Year 2 5 6 Gross Potential Rent-Office Gross Potential Rent- Parking Operating Expenses Projected Growth NA Mangement Fees Real Estate Taxes Insurance Maintenance Supplies Capital replacement Admin Operating Costs of Garage Amount 5% of EGI $ 9,000 $ 3,000 $ 11,000 $ 4,000 $ 8.000 $ 4,500 $ 6.500 Subtotal Expense projection Year 2 3 4 5 6 Growth rate Real Estate Taxes Insurance Maintenance Supplies Capital replacement Admin Operating Costs of Garage 2 3 5 6 1. Year-one and 5-Year Income Statement Year Rent Parking income Gross income Vacancy EGI Vacancy rate Real Estate Taxes Insurance Maintenance Supplies Capital replacement Admin Operating Costs of Garage Management fee Total Expenses of EGI 2. Calculate the before-tax, unlevered cash flows on the property. Calcualte the goingin cap rate on the property. NOI Initial purchase price Going -in caprate 3. Calculate the unlevered, cash-on-cash return. Cash-on-cash return 4. Calculate the exit price and the exit cash flow Exit cap rate Assumed sale price Sales expenses Net Sales Price Mortgage Analysis 1. Create the monthly and annual payments. Assumptions Mortgage Bal Mortgage rate Amortization (yrs) Monthly payment 2. Create the mortgage loan schedule 1 2 3 4 5 Year Annual payment Mtg balance Principal Interest 3. Calculate the DSCR DSCR Before-Tax Cash Flow with Debt 1. Calculate the Before-Tax Cash Flows fo the 5 years of the holding period 2 3 5 Year NOI Debt service BTCF 39 years 3 4 5 117 2. Calculate the cash-on-cash yield 118 119 Equity Invested 120 Cash-on-oash return 121 122 123 3. Calculate the exit cash flow 124 125 Before-Tax Cash Flow from Sale 126 Net Sales Price 127 Mtg Balance 128 BTCF sale 129 130 After-Tax Cash Flows 131 132 1.Calculate taxable income for the 5 years 133 134 Depreciation 135 Land value of total value 136 Improvements 137 Depreciationly 138 139 140 Year 1 2 141 NOI 142 Less Interest 143 Depreciation 144 145 Taxable income 146 Taxes 147 148 149 2. Calculate the after-tax cash flows for the 5 years. 150 151 After-tax CF 152 153 154 After-tax Cash Flow from the Sale 155 156 3. Calculate the after-tax cash flows from selling the property. 157 158 Price appreciation 159 Accumulated depreciation 160 161 Tax on appreciation 162 Tax on recapture 163 164 Total taxes of sale 165 166 167 Before-tax cash flow 168 Taxes 169 170 After-tax cash flow from sale Tax rate 20% Capital gains rate 25% Recapture rate Before-tax cash flow Taxes After-tax cash flow from sale Calculating Net Effective Annual Rents required rate of return Option 1 $12 psf with 3% annual escalation Year 2 3 4 5 6 Growth rate Growth rate Rent Less Operating Exp Add recoveries Net Rent 167 168 169 170 171 172 Lease Analysis 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 PV of rent Annual Eff Rer $11.50 psf with 2% annual escalations and a $1.70 base stop Year 1 2 3 4 5 6 Rent Less Operating Exp Add recoveries Net Rent Growth rate Growth rate Base Stop PV of rent Annual Eff Rer