Question
Develop a real estate investment analysis model to calculate the Net Present Value (NPV) and the Internal Rate of Return (IRR) from owning and operating
Develop a real estate investment analysis model to calculate the Net Present Value (NPV) and the Internal Rate of Return (IRR) from owning and operating an office building. Calculate the NPV and IRR on cash flows before and after federal income tax.
This building has 250 thousand square foot of gross area of which 213 thousand square feet can be considered net rentable space. The property includes enough land to provide adequate parking. The price to acquire the fee simple interest is $50 million and for depreciation purposes, you will allocate $8 million to the land and $42 million to the depreciable improvements. The Lernalott Building is five years old; however, you have not yet acquired it.
Fill in the charts, with equations/math and solutions. Work has to be shown.
INPUT VARIABLE SECTION:
| Current Rent Schedule | |||||||
| Rent Role | Gross SF | Rentable SF | Leased SF | Rent/SF | Revenue | Fully Leased | |
| 1st Floor | 25,000 | 15,000 | 15,000 | $32.00 | 480,000 | 480,000 | |
| 2nd Floor | 25,000 | 22,000 | 18,000 | $25.00 | 450,000 | 550,000 | |
| 3rd Floor | 25,000 | 22,000 | 22,000 | $27.00 | 594,000 | 594,000 | |
| 4th Floor | 25,000 | 22,000 | 22,000 | $27.00 | 594,000 | 594,000 | |
| 5th Floor | 25,000 | 22,000 | 22,000 | $27.00 | 594,000 | 594,000 | |
| 6th Floor | 25,000 | 22,000 | 12,000 | $28.00 | 336,000 | 616,000 | |
| 7th Floor | 25,000 | 22,000 | 22,000 | $28.00 | 616,000 | 616,000 | |
| 8th Floor | 25,000 | 22,000 | 22,000 | $29.00 | 638,000 | 638,000 | |
| 9th Floor | 25,000 | 22,000 | 15,000 | $30.00 | 450,000 | 660,000 | |
| 10th Floor | 25,000 | 22,000 | 22,000 | $32.00 | 704,000 | 704,000 | |
| Total | $250,000 | $213,000 | $192,000 | $24 | $5,456,000 | $6,046,000 | |
| Weighted Average Rent/ Rentable SF | $28.385 | Fully Leased/Rentable SF | |||||
| Percent | 100% | ||||||
| Percent of Gross SF | 85.20% | Rentable SF/ Gross SF | |||||
| Occupancy Level | 90.2415% | Revenue/Fully Leased | |||||
| Esimated Vacancy Rate | |||||||
| Year | 1 | 2 | 3 | 4 | 5 | 6 | |
| Estimated Vacancy | 9.75852% | 8% | 8% | 6% | 6% | 6% | |
| Estimated Expenses | Tax Rates | ||||||
| Variable Expenses | Cost/SF | Income Tax | 34% | ||||
| Utilities | $1.85 | Capital Gain | 15% | ||||
| Maintenance | $0.50 | Recapture | 25% | ||||
| Total Variable Expenses | $2.35 | ||||||
| Depreciation Information | |||||||
| Fixed Expenses | Acquisition Cost of the Building | $50,000,000.00 | |||||
| Insurance | $0.75 | Cost allocatable to the land | $8,000,000.00 | ||||
| Ad Valorem Taxes | $5.00 | Cost allocated to the building | $42,000,000.00 | ||||
| Total Fixed Expenses | $5.75 | Depreciation term in Years | 39 | ||||
| Total Operating Expenses | $8.10 | Annual Depreciation Expense | $1,076,923.08 | ||||
| Financing Terms | |||||||
| Property Resale Information | LTV | 80% | |||||
| Going Out Capitalization F | 7% | Term in years, monthly payments | 25yr./300mo. | ||||
| Selling Expense | 4% | Interest Rate | 6% | ||||
| Discount Rates | |||||||
| Rate on Total Asset Before Tax | 7.00% | ||||||
| Rate on Equity Before Tax | 12.00% | ||||||
| Rate on Equity After Tax | 7.92% | ||||||
| CALCULATION OF OPERATING INCOME AND CASH FLOWS | ||||||
| Net Operating Income | ||||||
| Year | 1 | 2 | 3 | 4 | 5 | 6 |
| Gross Potential Income | ||||||
| Allowance for Vacancy | ||||||
| Effective Gross Rent | ||||||
| Less Operation Expenses | ||||||
| Variable Expenses | ||||||
| Fixed Expenses | ||||||
| Total Expenses | ||||||
| Net Operation Income | ||||||
| Mortgage Amoritization Schedule | ||||||
| Loan Amount | ||||||
| Year | 1 | 2 | 3 | 4 | 5 | |
| EOY Loan Balance | ||||||
| Payment | ||||||
| Principal Reduction | ||||||
| Interest | ||||||
| Calculation of Tax on Operating Income | ||||||
| Year | 1 | 2 | 3 | 4 | 5 | |
| Net Operation Income | ||||||
| Less Interest Expense | ||||||
| Less Depreciation | ||||||
| Taxable Income | ||||||
| Income Tax | ||||||
| Calculation of Operating Cash Flows | ||||||
| Year | 1 | 2 | 3 | 4 | 5 | |
| Net Operation Income | ||||||
| Less Debt Service | ||||||
| Before Tax Cash Flow | ||||||
| Less Income Tax | ||||||
| After Tax Cash Flow | ||||||
| ESTIMATES OF THE PROCEEDS FROM THE EVENTUAL SALE OF THE PROPERTY | |||||
| Market Value Estimate = NOI / R | |||||
| Market Value | |||||
| Adjusted Basis Caluclation | |||||
| Acquisition Cost | |||||
| Less Accumulated Depreciation | |||||
| Adjusted Basis | |||||
| Taxable Gain on Sale | |||||
| Sale Price (Market Value) | |||||
| Less Selling Expense | |||||
| Net Sale Price | |||||
| Less Adjusted Basis | |||||
| Gain on Sale | |||||
| Tax on the Gain | |||||
| Recapture the Depreciation | |||||
| Tax on Recapture | |||||
| LT Capital Gain | |||||
| Tax on LT Captial Gain | |||||
| Total Tax on Sale | |||||
| Cash Flow from Sale | |||||
| Net Sale Price | |||||
| Loan Balance Payoff | |||||
| Before Tax Cash From Sale | |||||
| Less Texes on the Sale | |||||
| After Tax Cash from Sale | |||||
| RATES OF RETURN | ||||||
| Before-Tax Return on Investment without Mortgage Financing | ||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 |
| Acquisition Cost | ||||||
| Annual Operation CF | ||||||
| Cash Proceeds from Sale | ||||||
| Summary of Cash Flow | ||||||
| Internal Rate of Return | ||||||
| Net Present Value | ||||||
| Before-Tax Return on Equity with Mortgage Financing | ||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 |
| Acquisition Cost | ||||||
| Annual Operation CF | ||||||
| Less Debt Service | ||||||
| Cash Proceeds from Sale | ||||||
| Summary of Cash Flow | ||||||
| Internal Rate of Return | ||||||
| Net Present Value | ||||||
| After-Tax Return on Equity with Mortgage Financing | ||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 |
| Acquisition Cost | ||||||
| After-Tax Annual Cash Flow CF | ||||||
| After-Tax Proceeds from Sale | ||||||
| Summary of Cash Flow | ||||||
| Internal Rate of Return | ||||||
| Net Present Value | ||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started