Real Estate Investment Analysis 3p6u1l
This document was ed by and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this report form. Report 2z6p3t
Overview 5o1f4z
& View Real Estate Investment Analysis as PDF for free.
More details 6z3438
- Words: 2,821
- Pages: 25
Forecasting Property Value
workbook example for Maegen's Magic Manor
This workbook has been designed to illustrate the process of forecasting the income and value of an investment property. is to accompany the example in the text (Maegen's Manor), the worksheet is flexible enough to accommodate almost any investment, including non-residential properties. Note that the individual worksheets are integrated and that they build upon be made in yellow boxes; the inserted formulas will make all necessary calculations. More in-depth explanations of the num in chapters 6, 9, 10, 11, 12, 13, and 16. Anticipated holding period:
(may be from 2 to 10 years)
6
Potential Gross Rent inputs: Type 1: Two-Bedroom Type 2: One-Bedroom Type 3: Studio Units
Units 50 150 80
Monthly Rent $858 $704 $528
Square Feet
Annual Rent / SF
Input in yellow cells only. (input for purchase price is o
Non-residential input: 0* Increase in rents: (to the following year)
2.5%
1 5.0%
3 3.5%
4 3.5%
5 3.5%
* fill in this box only if monthly rents above are current and need to be adjusted for the first year of o
1 Estimated vacancy rates:
2 5.0%
(for each year)
Other income:
4.7%
7.5%
2 4.0%
3 4.0%
4 6.0%
5 6.0%
(enter as a percentage of collectable rents)
Potential Gross Rent Estimate f
Source: Dr. Greg Smersh, Florida State University
lue of an investment property. Although its primary function h to accommodate almost any income-producing real estate egrated and that they build upon each other. Inputs need only n-depth explanations of the numbers used here can be found
Input in yellow cells only. (input for purchase price is on the Sale worksheet)
Annual gross rent estimate: 6
7
$2,288,900
8
9
8
9
eed to be adjusted for the first year of operations
6
7
10
6.0%
Potential Gross Rent Estimate for first year:
$2,346,100
Operating Expenses
(Chapter 6)
As discussed in the text, operating expenses for Maegen's Manor are expected to increase at an annual rate of 3.5% excep (which are 5% of EGI), and property taxes (which are expected to increase by 25% in year 4). Annual Operating Expense Increase:
Management Fee (% of EGI):
3.5%
0*
1
2
Increase in property taxes: (to the following and continuing years)
3
4
25.0%
* fill in this box only if property taxes below need to be adjusted for the first year of operatio
Potential Gross Rent Less : Allowance for Vacancies Plus : Other Income Effective Gross Income Operating Expenses Management Fee (% of EGI) Salary Expense Utilities Insurance Supplies Advertising Maintenance & Repairs Property Taxes
Current Expense Estimates: (from Table 5.6)
$ $ $ $ $ $ $
197,100 105,300 35,500 21,000 32,000 181,900 300,000
Net Operating Income (NOI)
Note: all numbers are ann
se at an annual rate of 3.5% except for management expenses Input in yellow cells only.
nt Fee (% of EGI):
5.0%
5
6
7
8
9
d to be adjusted for the first year of operations
Table 6.1 First-year Operating Forecast $ $ $
2,346,100 176,000 2,170,100 102,000 2,272,100
s:
113,600 204,000 109,000 36,700 21,700 33,100 188,300 300,000
1,006,400 $
1,265,700
Note: all numbers are annual and rounded to the nearest $100
Operating Statement
(Chapter 6)
The annual operating statement for all years of the anticipated holding period are shown below as well as the operating ex of the cells below contain formulas and changes should ONLY be made on the previous two worksheets. Table 6.2 Operating Forecast 1 2,346,100 176,000 2,170,100 102,000 2,272,100
2 2,463,400 98,500 2,364,900 111,200 2,476,100
3 2,586,600 103,500 2,483,100 116,700 2,599,800
4 2,677,100 160,600 2,516,500 118,300 2,634,800
5 2,770,800 166,200 2,604,600 122,400 2,727,000
113,600 204,000 109,000 36,700 21,700 33,100 188,300 300,000
123,800 211,100 112,800 38,000 22,500 34,300 194,900 300,000
130,000 218,500 116,700 39,300 23,300 35,500 201,700 300,000
131,700 226,100 120,800 40,700 24,100 36,700 208,800 375,000
136,400 234,000 125,000 42,100 24,900 38,000 216,100 375,000
15. Total Expenses
1,006,400
1,037,400
1,065,000
1,163,900
1,191,500
16. Net Operating Income
1,265,700
1,438,700
1,534,800
1,470,900
1,535,500
3 41.0%
4 44.2%
5 43.7%
1. 2. 3. 4. 5.
Potential Gross Rent Vacancy Allowance Other Income Effective Gross Income
6. Operating Expenses 7. Management Fee 8. Salary Expense 9. Utilities 10. Insurance 11. Supplies 12. Advertising 13. Maintenance & repairs 14. Property Taxes
Table 6.3 Forecasted Operating Expense Ratios 1 44.3%
2 41.9%
wn below as well as the operating expense ratio for each year. Not that all ous two worksheets. No inputs on this worksheet.
6 2,867,800 172,100 2,695,700 126,700 2,822,400
7 0 0 0 0 0
8 0 0 0 0 0
9 0 0 0 0 0
10 0 0 0 0 0
141,100 242,200 129,400 43,600 25,800 39,300 223,700 375,000
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1,220,100
0
0
0
0
1,602,300
0
0
0
0
6 43.2%
7 0.0%
8 0.0%
9 0.0%
10 0.0%
Mortgage Borrowing
(Chapter 9)
For Maegen's Manor, a mortgage for $8 million is expected (based on roughly 70% LTV). are 20 years with monthl interest rate of 8%. Inputs below calculate the annual debt service, portions due to interest and principal, and the mortgag anticipated holding period. Input in yellow cells only.
Mortgage Amount: $ Mortgage Term: Interest Rate: Amortization:
8,000,000 20 8.00% 12
(enter in years) (enter annual %) (annual = 1, monthly = 12)
Mortgage calculator: Monthly Debt Service: Annual Debt Service:
Annual Debt Service (A
Table 9.6 Amortization Schedule 1
2
3
4
5
6
Interest paid Principal paid Total Debt Service
633,889 169,093 802,982
619,855 183,128 802,982
604,655 198,327 802,982
588,194 214,789 802,982
570,367 232,616 802,982
551,060 251,923 802,982
Mortgage Balance
7,830,907
7,647,779
7,449,451
7,234,663
7,002,047
6,750,124
erms are 20 years with monthly amortization and an annual and principal, and the mortgage balance for each year of the
Monthly Debt Service: $ Annual Debt Service: $
66,915.21 802,982.47
Annual Debt Service (ADS):
$803,000 (rounded)
7
8
9
10
0 0 0
0 0 0
0 0 0
0 0 0
0
0
0
0
Before-Tax Cash Flow (BTCF)
(Chapter 9)
The next step in projecting an annual operating statement is to deduct the annual debt service (ADS) from NOI for all year Note that all of the cells below contain formulas and changes should ONLY be made on the Intro, Expenses, and Mortgage No inputs on this worksheet. Table 9.5 Projected Before-Tax Cash Flows from Operations Potential Gross Rent Vacancy Allowance Other Income Effective Gross Income Operating Expenses Management Fee Salary Expense Utilities Insurance Supplies Advertising Maintenance & repairs Property Taxes Total Expenses Net Operating Income Debt Service Before-Tax Cash Flow
1 2,346,100 176,000 2,170,100 102,000 2,272,100
2 2,463,400 98,500 2,364,900 111,200 2,476,100
3 2,586,600 103,500 2,483,100 116,700 2,599,800
4 2,677,100 160,600 2,516,500 118,300 2,634,800
5 2,770,800 166,200 2,604,600 122,400 2,727,000
113,600 204,000 109,000 36,700 21,700 33,100 188,300 300,000
123,800 211,100 112,800 38,000 22,500 34,300 194,900 300,000
130,000 218,500 116,700 39,300 23,300 35,500 201,700 300,000
131,700 226,100 120,800 40,700 24,100 36,700 208,800 375,000
136,400 234,000 125,000 42,100 24,900 38,000 216,100 375,000
1,006,400 1,265,700 803,000
1,037,400 1,438,700 803,000
1,065,000 1,534,800 803,000
1,163,900 1,470,900 803,000
1,191,500 1,535,500 803,000
462,700
635,700
731,800
667,900
732,500
bt service (ADS) from NOI for all years of the anticipated holding period. on the Intro, Expenses, and Mortgage worksheets.
6 2,867,800 172,100 2,695,700 126,700 2,822,400
7 0 0 0 0 0
8 0 0 0 0 0
9 0 0 0 0 0
10 0 0 0 0 0
141,100 242,200 129,400 43,600 25,800 39,300 223,700 375,000
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1,220,100 1,602,300 803,000
0 0 0
0 0 0
0 0 0
0 0 0
799,300
0
0
0
0
Income Tax Issues
(Chapter 10)
The next step in projecting an annual operating statement is to calculate taxes from operations for all years of the anticipat should ONLY be made on the Intro, Expenses, and Mortgage worksheets and in the yellow boxes below. Marginal Tax Rate:
40%
Input Depreciable Basis
Calcs for depreciation expense:
9,300,000
Tax Calculations Net Operating Income - Interest Expense - Depreciation Taxable Income (Loss) x Marginal tax rate Income taxes
1 1,265,700 633,900 324,100 307,700 0.40 123,100
2 1,438,700 619,900 338,200 480,600 0.40 192,200
3 1,534,800 604,700 338,200 591,900 0.40 236,800
Table 10.2 Projected After-Tax Cash Flows from Operations Potential Gross Rent Vacancy Allowance Other Income Effective Gross Income - Operating Expenses Net Operating Income - Debt Service Before-Tax Cash Flow - Income Taxes After-Tax Cash Flow
1 2,346,100 176,000 2,170,100 102,000 2,272,100 1,006,400 1,265,700 803,000 462,700 123,100 339,600
2 2,463,400 98,500 2,364,900 111,200 2,476,100 1,037,400 1,438,700 803,000 635,700 192,200 443,500
3 2,586,600 103,500 2,483,100 116,700 2,599,800 1,065,000 1,534,800 803,000 731,800 236,800 495,000
Input Cost Recovery Period /
4 1,470,900 588,200 338,200 544,500 0.40 217,800
27.5
5 1,535,500 570,400 338,200 626,900 0.40 250,800
(table is condensed - click Format / Row / Unhide to expand)
4 2,677,100 160,600 2,516,500 118,300 2,634,800 1,163,900 1,470,900 803,000 667,900 217,800 450,100
5 2,770,800 166,200 2,604,600 122,400 2,727,000 1,191,500 1,535,500 803,000 732,500 250,800 481,700
operations for all years of the anticipated holding period. Again, changes yellow boxes below. Input in yellow cells only. Depreciation Deduction
ery Period =
$
IRS Mid-month Convention*
338,200
$
324,100
* applies to first and last year
6 1,602,300 551,100 324,100 727,100 0.40 290,800
7 0 0 0 0 0.40 0
8 0 0 0 0 0.40 0
9 0 0 0 0 0.40 0
10 0 0 0 0 0.40 0
7 0 0 0 0 0 0 0 0 0 0 0
8 0 0 0 0 0 0 0 0 0 0 0
9 0 0 0 0 0 0 0 0 0 0 0
10 0 0 0 0 0 0 0 0 0 0 0
d - click Format / Row / Unhide to expand)
6 2,867,800 172,100 2,695,700 126,700 2,822,400 1,220,100 1,602,300 803,000 799,300 290,800 508,500
Property Disposition
(Chapter 11)
At some point in the future, a real estate investor may want to sell the property. Indeed, the analyst must estimate a future methods such as NPV and IRR. Inputs below (yellow boxes only) are for both the purchase and sales prices, their associa Input in yellow cells only.
Purchase price: Transaction costs:
11,444,500 150,000
Selling price: Selling costs:
17,800,000 890,000
Tax rate on capital gains: Tax rate on depreciation recapture:
20% 25%
Anticipated holding period: Mortgage balance:
Table 11.2 Estimated Income Tax Co Selling Price - Adjusted Basis (from Table 11.1) Gain on Disposal - Gain from depreciation recapture Long-Term Capital Gain
6 $ 6,750,124 (from Table 9.6)
Tax on depreciation recapture Tax on capital gain Total Tax Liability on Sale
Table 11.1 Estimate of Investor's Adjusted Tax Basis
Table 11.3 Estimate of After-Tax Equity
Purchase Price + Transaction Costs Initial Tax Basis - Cumulative Depreciation Adjusted Basis Prior to Sale + Selling Costs Adjusted Basis at Time of Sale
Selling Price - Selling Costs Net Sales Proceeds - Mortgage Balance Before-tax Equity Reversion - Taxes due on sale After-Tax Equity Reversion
$ $ $ $ $ $ $
11,444,500 150,000 11,594,500 2,001,000 9,593,500 890,000 10,483,500
analyst must estimate a future selling price to use valuation e and sales prices, their associated costs, and tax rates.
1.2 Estimated Income Tax Consequences
sted Basis (from Table 11.1)
from depreciation recapture rm Capital Gain
epreciation recapture
Tax Liability on Sale
$ $ $ $ $
17,800,000 10,483,500 7,316,500 2,001,000 5,315,500
$ $ $
500,300 1,063,100 1,563,400
$ $ $ $ $ $ $
17,800,000 890,000 16,910,000 6,750,100 10,159,900 1,563,400 8,596,500
1.3 Estimate of After-Tax Equity Reversion
ax Equity Reversion
x Equity Reversion
Ratio Analysis - Value
(Chapter 12)
Ratios are widely used to gauge the reasonableness of relationships between various measures of value and performance Income multipliers express the relationship between market value and operating income. These multipliers can also be used to estimate market value. Input in yellow cells only. Income Multipliers Gross Rent Multiplier:
Gross Income Multiplier:
Net Income Multilier:
Market Price 11,444,500
/
Gross Rents 2,346,100
Market Price 11,444,500
/
2,272,100
Market Price 11,444,500
/
1,265,700
=
Gross Rents 2,346,100
x
Input GRM 5.00
=
x
Input GIM 5.00
x
Input NIM 9.00
=
EGI =
NOI
Using Multipliers to Estimate Market Value Gross Rent Multiplier Method:
Gross Income Multiplier Method:
EGI 2,272,100
Net income Multilier Method:
NOI 1,265,700
=
s of value and performance. se multipliers can also be
4.88
5.04
9.04
$
11,730,500
$
11,360,500
$
11,391,300
Ratio Analysis - Financial & Profitability
(Chapter 12)
As shown in the text, ratio analysis also includes measures of financing ability (operating ratio, breakeven ratio, debt cover ratio, and loan-to-value ratio), and profitability (cap rate and equity dividend rate). Input in yellow cells only. Financial Ratios Operating Ratio:
Break-even Ratio:
Debt Coverage Ratio:
OE 1,006,400
/
EGI 2,272,100
(OE + ADS) 1,809,400
/
2,272,100
EGI
NOI 1,265,700
ADS /
803,000
/
Market Price 11,444,500
/
Market Price 11,444,500
1,265,700
/
Input cap rate 10.00%
Equity Dividend Rate: (before-tax)
BTCF 462,700
/
Initial Equity 3,594,500
Equity Dividend Rate: (after-tax)
ATCF 339,600
/
Initial Equity 3,594,500
Loan-to-value (LTV) Ratio:
Mortgage 8,000,000
Profitability Measures Capitalization Rate:
NOI 1,265,700
Using Cap Rate to Estimate Market Value:
NOI
tio, breakeven ratio, debt coverage put in yellow cells only.
=
44%
=
80%
=
1.58
=
70%
=
11.06%
=
$
12,657,000
=
12.87%
=
9.45%
Discounted Cash Flow (DCF) Analysis
(Chapter 13)
As discussed in the text, an internal rate of return (IRR) is the discount rate that will exactly equate the present value of a projected stream of cash flows with an initial equity investment. Alternatively, subtracting a initial equity investment from the present value of projected cash flows (discounted at a given discount rate) yields net present value (NPV). No inputs on this worksheet. Anticipated holding period: Purchase price: Transaction costs: Initial Investment Basis: Mortgage:
Initial Equity:
BTCF: BTER: Total:
6
Selling Price: - Selling Costs: Net Sales Proceeds: - Mortgage Balance: Before-tax Equity Reversion: - Taxes due on sale: After-Tax Equity Reversion:
11,444,500 150,000 11,594,500 8,000,000 3,594,500
17,800,000 890,000 16,910,000 6,750,100 10,159,900 1,563,400 8,596,500
0
1
2
3
4
5
6
7
8
9
10
(3,594,500)
462,700 0 462,700
635,700 0 635,700
731,800 0 731,800
667,900 0 667,900
732,500 0 732,500
799,300 10,159,900 10,959,200
0 0 0
0 0 0
0 0 0
0 0 0
Before-tax IRR:
ATCF: ATER: Total:
31.31%
0
1
2
3
4
5
6
7
8
9
10
(3,594,500)
339,600 0 339,600
443,500 0 443,500
495,000 0 495,000
450,100 0 450,100
481,700 0 481,700
508,500 8,596,500 9,105,000
0 0 0
0 0 0
0 0 0
0 0 0
After-tax IRR:
24.60%
Risk-Adjustment Methods
(Chapter 16)
Assumptions:
As discussed in the text, several different methods are available to assess the risk inherent in any real estate investment. This worksheet contains formulas to help illustrate the methods. Input in yellow cells only.
Anticipated holding period: Purchase price: Transaction costs: Initial Investment Basis: Mortgage:
Initial Equity:
6
Selling Price: - Selling Costs: Net Proceeds: - Mortgage Balance: Before-tax Reversion: - Taxes due on sale: After-Tax Reversion:
11,444,500 150,000 11,594,500 8,000,000 3,594,500
17,800,000 890,000 16,910,000 6,750,100 10,159,900 1,563,400 8,596,500
Payback Period 0 ATCF: ATER: Total: Cummulative: Initial Calc:
1
2
3
4
5
6
7
8
9
10
339,600
443,500
495,000
450,100
481,700
508,500
0
0
0
0
0
0
0
0
0
8,596,500
0
0
0
0
3,594,500
339,600
443,500
495,000
450,100
481,700
9,105,000
0
0
0
0
3,594,500
3,254,900
2,811,400
2,316,400
1,866,300
1,384,600
7,720,400
0
0
0
0
-
-
1
2
3
4
5
-
Payback Period:
5.06 years
Sensitivity Analysis Discount rate:
10.00%
PV of Equity
Variation in NOI
$
6,793,214
Note: discount rate applies to the calculations below as well.
Equity Value expected
% change in Equity Value
Input % variation:
10.00%
with - 10 % variation
5.61%
Variation in Sales Price
6,411,888
6,793,214
with - 10 % variation
Input % variation:
Calculations for Sensitivity Analysis
10.00%
14.05%
with + 10 % variation
expected
5,838,689
7,174,541
with + 10 % variation
6,793,214
7,747,740
DO NOT CHANGE !!
Calcs for change in NOI: After-Tax Cash Flow After-tax Equity Reversion
415,570
529,770
587,080
538,390
573,850
604,630
0
0
0
0
0
0
0
0
0
8,596,500
0
0
0
0
Total Cash Flow
415,570
529,770
587,080
538,390
573,850
9,201,130
0
0
0
0
Change in Equity Value:
7,174,541
-
6,793,214
=
381,326
Calcs for change in Sales Price: After-Tax Cash Flow After-tax Equity Reversion
339,600
443,500
495,000
450,100
481,700
508,500
0
0
0
0
0
0
0
0
0
10,287,500
0
0
0
0
Total Cash Flow
339,600
443,500
495,000
450,100
481,700
10,796,000
0
0
0
0
Change in Equity Value:
7,747,740
-
6,793,214
=
954,525
workbook example for Maegen's Magic Manor
This workbook has been designed to illustrate the process of forecasting the income and value of an investment property. is to accompany the example in the text (Maegen's Manor), the worksheet is flexible enough to accommodate almost any investment, including non-residential properties. Note that the individual worksheets are integrated and that they build upon be made in yellow boxes; the inserted formulas will make all necessary calculations. More in-depth explanations of the num in chapters 6, 9, 10, 11, 12, 13, and 16. Anticipated holding period:
(may be from 2 to 10 years)
6
Potential Gross Rent inputs: Type 1: Two-Bedroom Type 2: One-Bedroom Type 3: Studio Units
Units 50 150 80
Monthly Rent $858 $704 $528
Square Feet
Annual Rent / SF
Input in yellow cells only. (input for purchase price is o
Non-residential input: 0* Increase in rents: (to the following year)
2.5%
1 5.0%
3 3.5%
4 3.5%
5 3.5%
* fill in this box only if monthly rents above are current and need to be adjusted for the first year of o
1 Estimated vacancy rates:
2 5.0%
(for each year)
Other income:
4.7%
7.5%
2 4.0%
3 4.0%
4 6.0%
5 6.0%
(enter as a percentage of collectable rents)
Potential Gross Rent Estimate f
Source: Dr. Greg Smersh, Florida State University
lue of an investment property. Although its primary function h to accommodate almost any income-producing real estate egrated and that they build upon each other. Inputs need only n-depth explanations of the numbers used here can be found
Input in yellow cells only. (input for purchase price is on the Sale worksheet)
Annual gross rent estimate: 6
7
$2,288,900
8
9
8
9
eed to be adjusted for the first year of operations
6
7
10
6.0%
Potential Gross Rent Estimate for first year:
$2,346,100
Operating Expenses
(Chapter 6)
As discussed in the text, operating expenses for Maegen's Manor are expected to increase at an annual rate of 3.5% excep (which are 5% of EGI), and property taxes (which are expected to increase by 25% in year 4). Annual Operating Expense Increase:
Management Fee (% of EGI):
3.5%
0*
1
2
Increase in property taxes: (to the following and continuing years)
3
4
25.0%
* fill in this box only if property taxes below need to be adjusted for the first year of operatio
Potential Gross Rent Less : Allowance for Vacancies Plus : Other Income Effective Gross Income Operating Expenses Management Fee (% of EGI) Salary Expense Utilities Insurance Supplies Advertising Maintenance & Repairs Property Taxes
Current Expense Estimates: (from Table 5.6)
$ $ $ $ $ $ $
197,100 105,300 35,500 21,000 32,000 181,900 300,000
Net Operating Income (NOI)
Note: all numbers are ann
se at an annual rate of 3.5% except for management expenses Input in yellow cells only.
nt Fee (% of EGI):
5.0%
5
6
7
8
9
d to be adjusted for the first year of operations
Table 6.1 First-year Operating Forecast $ $ $
2,346,100 176,000 2,170,100 102,000 2,272,100
s:
113,600 204,000 109,000 36,700 21,700 33,100 188,300 300,000
1,006,400 $
1,265,700
Note: all numbers are annual and rounded to the nearest $100
Operating Statement
(Chapter 6)
The annual operating statement for all years of the anticipated holding period are shown below as well as the operating ex of the cells below contain formulas and changes should ONLY be made on the previous two worksheets. Table 6.2 Operating Forecast 1 2,346,100 176,000 2,170,100 102,000 2,272,100
2 2,463,400 98,500 2,364,900 111,200 2,476,100
3 2,586,600 103,500 2,483,100 116,700 2,599,800
4 2,677,100 160,600 2,516,500 118,300 2,634,800
5 2,770,800 166,200 2,604,600 122,400 2,727,000
113,600 204,000 109,000 36,700 21,700 33,100 188,300 300,000
123,800 211,100 112,800 38,000 22,500 34,300 194,900 300,000
130,000 218,500 116,700 39,300 23,300 35,500 201,700 300,000
131,700 226,100 120,800 40,700 24,100 36,700 208,800 375,000
136,400 234,000 125,000 42,100 24,900 38,000 216,100 375,000
15. Total Expenses
1,006,400
1,037,400
1,065,000
1,163,900
1,191,500
16. Net Operating Income
1,265,700
1,438,700
1,534,800
1,470,900
1,535,500
3 41.0%
4 44.2%
5 43.7%
1. 2. 3. 4. 5.
Potential Gross Rent Vacancy Allowance Other Income Effective Gross Income
6. Operating Expenses 7. Management Fee 8. Salary Expense 9. Utilities 10. Insurance 11. Supplies 12. Advertising 13. Maintenance & repairs 14. Property Taxes
Table 6.3 Forecasted Operating Expense Ratios 1 44.3%
2 41.9%
wn below as well as the operating expense ratio for each year. Not that all ous two worksheets. No inputs on this worksheet.
6 2,867,800 172,100 2,695,700 126,700 2,822,400
7 0 0 0 0 0
8 0 0 0 0 0
9 0 0 0 0 0
10 0 0 0 0 0
141,100 242,200 129,400 43,600 25,800 39,300 223,700 375,000
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1,220,100
0
0
0
0
1,602,300
0
0
0
0
6 43.2%
7 0.0%
8 0.0%
9 0.0%
10 0.0%
Mortgage Borrowing
(Chapter 9)
For Maegen's Manor, a mortgage for $8 million is expected (based on roughly 70% LTV). are 20 years with monthl interest rate of 8%. Inputs below calculate the annual debt service, portions due to interest and principal, and the mortgag anticipated holding period. Input in yellow cells only.
Mortgage Amount: $ Mortgage Term: Interest Rate: Amortization:
8,000,000 20 8.00% 12
(enter in years) (enter annual %) (annual = 1, monthly = 12)
Mortgage calculator: Monthly Debt Service: Annual Debt Service:
Annual Debt Service (A
Table 9.6 Amortization Schedule 1
2
3
4
5
6
Interest paid Principal paid Total Debt Service
633,889 169,093 802,982
619,855 183,128 802,982
604,655 198,327 802,982
588,194 214,789 802,982
570,367 232,616 802,982
551,060 251,923 802,982
Mortgage Balance
7,830,907
7,647,779
7,449,451
7,234,663
7,002,047
6,750,124
erms are 20 years with monthly amortization and an annual and principal, and the mortgage balance for each year of the
Monthly Debt Service: $ Annual Debt Service: $
66,915.21 802,982.47
Annual Debt Service (ADS):
$803,000 (rounded)
7
8
9
10
0 0 0
0 0 0
0 0 0
0 0 0
0
0
0
0
Before-Tax Cash Flow (BTCF)
(Chapter 9)
The next step in projecting an annual operating statement is to deduct the annual debt service (ADS) from NOI for all year Note that all of the cells below contain formulas and changes should ONLY be made on the Intro, Expenses, and Mortgage No inputs on this worksheet. Table 9.5 Projected Before-Tax Cash Flows from Operations Potential Gross Rent Vacancy Allowance Other Income Effective Gross Income Operating Expenses Management Fee Salary Expense Utilities Insurance Supplies Advertising Maintenance & repairs Property Taxes Total Expenses Net Operating Income Debt Service Before-Tax Cash Flow
1 2,346,100 176,000 2,170,100 102,000 2,272,100
2 2,463,400 98,500 2,364,900 111,200 2,476,100
3 2,586,600 103,500 2,483,100 116,700 2,599,800
4 2,677,100 160,600 2,516,500 118,300 2,634,800
5 2,770,800 166,200 2,604,600 122,400 2,727,000
113,600 204,000 109,000 36,700 21,700 33,100 188,300 300,000
123,800 211,100 112,800 38,000 22,500 34,300 194,900 300,000
130,000 218,500 116,700 39,300 23,300 35,500 201,700 300,000
131,700 226,100 120,800 40,700 24,100 36,700 208,800 375,000
136,400 234,000 125,000 42,100 24,900 38,000 216,100 375,000
1,006,400 1,265,700 803,000
1,037,400 1,438,700 803,000
1,065,000 1,534,800 803,000
1,163,900 1,470,900 803,000
1,191,500 1,535,500 803,000
462,700
635,700
731,800
667,900
732,500
bt service (ADS) from NOI for all years of the anticipated holding period. on the Intro, Expenses, and Mortgage worksheets.
6 2,867,800 172,100 2,695,700 126,700 2,822,400
7 0 0 0 0 0
8 0 0 0 0 0
9 0 0 0 0 0
10 0 0 0 0 0
141,100 242,200 129,400 43,600 25,800 39,300 223,700 375,000
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
1,220,100 1,602,300 803,000
0 0 0
0 0 0
0 0 0
0 0 0
799,300
0
0
0
0
Income Tax Issues
(Chapter 10)
The next step in projecting an annual operating statement is to calculate taxes from operations for all years of the anticipat should ONLY be made on the Intro, Expenses, and Mortgage worksheets and in the yellow boxes below. Marginal Tax Rate:
40%
Input Depreciable Basis
Calcs for depreciation expense:
9,300,000
Tax Calculations Net Operating Income - Interest Expense - Depreciation Taxable Income (Loss) x Marginal tax rate Income taxes
1 1,265,700 633,900 324,100 307,700 0.40 123,100
2 1,438,700 619,900 338,200 480,600 0.40 192,200
3 1,534,800 604,700 338,200 591,900 0.40 236,800
Table 10.2 Projected After-Tax Cash Flows from Operations Potential Gross Rent Vacancy Allowance Other Income Effective Gross Income - Operating Expenses Net Operating Income - Debt Service Before-Tax Cash Flow - Income Taxes After-Tax Cash Flow
1 2,346,100 176,000 2,170,100 102,000 2,272,100 1,006,400 1,265,700 803,000 462,700 123,100 339,600
2 2,463,400 98,500 2,364,900 111,200 2,476,100 1,037,400 1,438,700 803,000 635,700 192,200 443,500
3 2,586,600 103,500 2,483,100 116,700 2,599,800 1,065,000 1,534,800 803,000 731,800 236,800 495,000
Input Cost Recovery Period /
4 1,470,900 588,200 338,200 544,500 0.40 217,800
27.5
5 1,535,500 570,400 338,200 626,900 0.40 250,800
(table is condensed - click Format / Row / Unhide to expand)
4 2,677,100 160,600 2,516,500 118,300 2,634,800 1,163,900 1,470,900 803,000 667,900 217,800 450,100
5 2,770,800 166,200 2,604,600 122,400 2,727,000 1,191,500 1,535,500 803,000 732,500 250,800 481,700
operations for all years of the anticipated holding period. Again, changes yellow boxes below. Input in yellow cells only. Depreciation Deduction
ery Period =
$
IRS Mid-month Convention*
338,200
$
324,100
* applies to first and last year
6 1,602,300 551,100 324,100 727,100 0.40 290,800
7 0 0 0 0 0.40 0
8 0 0 0 0 0.40 0
9 0 0 0 0 0.40 0
10 0 0 0 0 0.40 0
7 0 0 0 0 0 0 0 0 0 0 0
8 0 0 0 0 0 0 0 0 0 0 0
9 0 0 0 0 0 0 0 0 0 0 0
10 0 0 0 0 0 0 0 0 0 0 0
d - click Format / Row / Unhide to expand)
6 2,867,800 172,100 2,695,700 126,700 2,822,400 1,220,100 1,602,300 803,000 799,300 290,800 508,500
Property Disposition
(Chapter 11)
At some point in the future, a real estate investor may want to sell the property. Indeed, the analyst must estimate a future methods such as NPV and IRR. Inputs below (yellow boxes only) are for both the purchase and sales prices, their associa Input in yellow cells only.
Purchase price: Transaction costs:
11,444,500 150,000
Selling price: Selling costs:
17,800,000 890,000
Tax rate on capital gains: Tax rate on depreciation recapture:
20% 25%
Anticipated holding period: Mortgage balance:
Table 11.2 Estimated Income Tax Co Selling Price - Adjusted Basis (from Table 11.1) Gain on Disposal - Gain from depreciation recapture Long-Term Capital Gain
6 $ 6,750,124 (from Table 9.6)
Tax on depreciation recapture Tax on capital gain Total Tax Liability on Sale
Table 11.1 Estimate of Investor's Adjusted Tax Basis
Table 11.3 Estimate of After-Tax Equity
Purchase Price + Transaction Costs Initial Tax Basis - Cumulative Depreciation Adjusted Basis Prior to Sale + Selling Costs Adjusted Basis at Time of Sale
Selling Price - Selling Costs Net Sales Proceeds - Mortgage Balance Before-tax Equity Reversion - Taxes due on sale After-Tax Equity Reversion
$ $ $ $ $ $ $
11,444,500 150,000 11,594,500 2,001,000 9,593,500 890,000 10,483,500
analyst must estimate a future selling price to use valuation e and sales prices, their associated costs, and tax rates.
1.2 Estimated Income Tax Consequences
sted Basis (from Table 11.1)
from depreciation recapture rm Capital Gain
epreciation recapture
Tax Liability on Sale
$ $ $ $ $
17,800,000 10,483,500 7,316,500 2,001,000 5,315,500
$ $ $
500,300 1,063,100 1,563,400
$ $ $ $ $ $ $
17,800,000 890,000 16,910,000 6,750,100 10,159,900 1,563,400 8,596,500
1.3 Estimate of After-Tax Equity Reversion
ax Equity Reversion
x Equity Reversion
Ratio Analysis - Value
(Chapter 12)
Ratios are widely used to gauge the reasonableness of relationships between various measures of value and performance Income multipliers express the relationship between market value and operating income. These multipliers can also be used to estimate market value. Input in yellow cells only. Income Multipliers Gross Rent Multiplier:
Gross Income Multiplier:
Net Income Multilier:
Market Price 11,444,500
/
Gross Rents 2,346,100
Market Price 11,444,500
/
2,272,100
Market Price 11,444,500
/
1,265,700
=
Gross Rents 2,346,100
x
Input GRM 5.00
=
x
Input GIM 5.00
x
Input NIM 9.00
=
EGI =
NOI
Using Multipliers to Estimate Market Value Gross Rent Multiplier Method:
Gross Income Multiplier Method:
EGI 2,272,100
Net income Multilier Method:
NOI 1,265,700
=
s of value and performance. se multipliers can also be
4.88
5.04
9.04
$
11,730,500
$
11,360,500
$
11,391,300
Ratio Analysis - Financial & Profitability
(Chapter 12)
As shown in the text, ratio analysis also includes measures of financing ability (operating ratio, breakeven ratio, debt cover ratio, and loan-to-value ratio), and profitability (cap rate and equity dividend rate). Input in yellow cells only. Financial Ratios Operating Ratio:
Break-even Ratio:
Debt Coverage Ratio:
OE 1,006,400
/
EGI 2,272,100
(OE + ADS) 1,809,400
/
2,272,100
EGI
NOI 1,265,700
ADS /
803,000
/
Market Price 11,444,500
/
Market Price 11,444,500
1,265,700
/
Input cap rate 10.00%
Equity Dividend Rate: (before-tax)
BTCF 462,700
/
Initial Equity 3,594,500
Equity Dividend Rate: (after-tax)
ATCF 339,600
/
Initial Equity 3,594,500
Loan-to-value (LTV) Ratio:
Mortgage 8,000,000
Profitability Measures Capitalization Rate:
NOI 1,265,700
Using Cap Rate to Estimate Market Value:
NOI
tio, breakeven ratio, debt coverage put in yellow cells only.
=
44%
=
80%
=
1.58
=
70%
=
11.06%
=
$
12,657,000
=
12.87%
=
9.45%
Discounted Cash Flow (DCF) Analysis
(Chapter 13)
As discussed in the text, an internal rate of return (IRR) is the discount rate that will exactly equate the present value of a projected stream of cash flows with an initial equity investment. Alternatively, subtracting a initial equity investment from the present value of projected cash flows (discounted at a given discount rate) yields net present value (NPV). No inputs on this worksheet. Anticipated holding period: Purchase price: Transaction costs: Initial Investment Basis: Mortgage:
Initial Equity:
BTCF: BTER: Total:
6
Selling Price: - Selling Costs: Net Sales Proceeds: - Mortgage Balance: Before-tax Equity Reversion: - Taxes due on sale: After-Tax Equity Reversion:
11,444,500 150,000 11,594,500 8,000,000 3,594,500
17,800,000 890,000 16,910,000 6,750,100 10,159,900 1,563,400 8,596,500
0
1
2
3
4
5
6
7
8
9
10
(3,594,500)
462,700 0 462,700
635,700 0 635,700
731,800 0 731,800
667,900 0 667,900
732,500 0 732,500
799,300 10,159,900 10,959,200
0 0 0
0 0 0
0 0 0
0 0 0
Before-tax IRR:
ATCF: ATER: Total:
31.31%
0
1
2
3
4
5
6
7
8
9
10
(3,594,500)
339,600 0 339,600
443,500 0 443,500
495,000 0 495,000
450,100 0 450,100
481,700 0 481,700
508,500 8,596,500 9,105,000
0 0 0
0 0 0
0 0 0
0 0 0
After-tax IRR:
24.60%
Risk-Adjustment Methods
(Chapter 16)
Assumptions:
As discussed in the text, several different methods are available to assess the risk inherent in any real estate investment. This worksheet contains formulas to help illustrate the methods. Input in yellow cells only.
Anticipated holding period: Purchase price: Transaction costs: Initial Investment Basis: Mortgage:
Initial Equity:
6
Selling Price: - Selling Costs: Net Proceeds: - Mortgage Balance: Before-tax Reversion: - Taxes due on sale: After-Tax Reversion:
11,444,500 150,000 11,594,500 8,000,000 3,594,500
17,800,000 890,000 16,910,000 6,750,100 10,159,900 1,563,400 8,596,500
Payback Period 0 ATCF: ATER: Total: Cummulative: Initial Calc:
1
2
3
4
5
6
7
8
9
10
339,600
443,500
495,000
450,100
481,700
508,500
0
0
0
0
0
0
0
0
0
8,596,500
0
0
0
0
3,594,500
339,600
443,500
495,000
450,100
481,700
9,105,000
0
0
0
0
3,594,500
3,254,900
2,811,400
2,316,400
1,866,300
1,384,600
7,720,400
0
0
0
0
-
-
1
2
3
4
5
-
Payback Period:
5.06 years
Sensitivity Analysis Discount rate:
10.00%
PV of Equity
Variation in NOI
$
6,793,214
Note: discount rate applies to the calculations below as well.
Equity Value expected
% change in Equity Value
Input % variation:
10.00%
with - 10 % variation
5.61%
Variation in Sales Price
6,411,888
6,793,214
with - 10 % variation
Input % variation:
Calculations for Sensitivity Analysis
10.00%
14.05%
with + 10 % variation
expected
5,838,689
7,174,541
with + 10 % variation
6,793,214
7,747,740
DO NOT CHANGE !!
Calcs for change in NOI: After-Tax Cash Flow After-tax Equity Reversion
415,570
529,770
587,080
538,390
573,850
604,630
0
0
0
0
0
0
0
0
0
8,596,500
0
0
0
0
Total Cash Flow
415,570
529,770
587,080
538,390
573,850
9,201,130
0
0
0
0
Change in Equity Value:
7,174,541
-
6,793,214
=
381,326
Calcs for change in Sales Price: After-Tax Cash Flow After-tax Equity Reversion
339,600
443,500
495,000
450,100
481,700
508,500
0
0
0
0
0
0
0
0
0
10,287,500
0
0
0
0
Total Cash Flow
339,600
443,500
495,000
450,100
481,700
10,796,000
0
0
0
0
Change in Equity Value:
7,747,740
-
6,793,214
=
954,525
Related Documents c2h70
Real Estate Investment Analysis 3p6u1l
November 2019 78
Real Estate Investment Analysis 3p6u1l
November 2019 123
Real Estate Investment Analysis (1).xls 3a1ac
December 2019 73
Project On Real Estate Investment 2e6e
November 2019 126
Real Estate Industry Analysis 4v384q
November 2019 59
Industry Analysis: Real Estate Industry 3y3e71
November 2021 0More Documents from "Harpreet Dhillon" 3v1y2u
Real Estate Investment Analysis 3p6u1l
November 2019 78
Lyrics And Tunes Of Tappe Te Boliyan For Bhangra b4517
November 2022 0
Punjabi Boliyan Collection 21405g
November 2019 596
Concept Of Rural Marketing m4c1a
December 2019 50
Comm 161 (010) Course Outline 5d6x6o
February 2022 0