# Commercial Real Estate Valuation Model1 by Haritah

VIEWS: 71 PAGES: 8

• pg 1
```									Commercial Real Estate Valuation and Financial Feasibility

Author

1759 Avenida del Sol
Boca Raton, FL 33432

Phone: (561) 417-5515
Email: info@questorconsulting.com
www.questorconsulting.com

Summary
The purpose of this spreadsheet model is twofold: 1) to estimate the valuation of commercial property and
2) to determine the financial feasibility of an investment in the subject property for a 10 year holding
period. The uncertainty in this model lies in the many economic factors to be input as assumptions by the
user. The most important assumptions in this analysis are the Net Operating Income assumptions (Annual
Gross Rent, Vacany and Collection Loss Factor, and Operating Expenses) and the Cap Rate at Purchase.
Special attention should be given to these assumptions.

Keywords: Real Estate, Property Valuation

Discussion
Commercial property valuation is estimated using 2 factors: 1) The current Net Operating Income (NOI
Gross Rent Roll minus vacancy and collection losses and operating expenses) and 2) The Cap Rate. The
Cap Rate for the property is best estimated by analyzing comparables. The Market Value and Cap Rate
can be calculated using the following formulas:

Cap Rate = NOI / Sale Price

Market Value = (NOI / (Cap Rate (%) * 100)) * 100

The financial feasibility of the investment is expressed in two factors: 1) The Net Present Value of Real
Cash Flow and 2) the After Tax Real Rate of Return. The model calculates these values automatically by
creating Pro Forma statements based on the assumptions input by the user.

Risk Analysis with Crystal Ball
Crystal Ball enhances your Excel model by letting you create probability distributions that describe the
uncertainty surrounding specific input variables. This model includes multiple probability distributions,
which all vary based on market conditions. These distributions are referred to in Crystal Ball as
"assumptions." Each assumption cell is colored green and is marked by an Excel note (mouse over the
cell to view the note). To view the details of an assumption, highlight the cell and either select Define
Assumption from the Define menu or click on the Define Assumption button on the Crystal Ball toolbar.

This model also includes several Crystal Ball forecasts: Property Valuation, NPV of Real Cash Flow, and
After Tax IRR, all shown in light blue. Forecasts are equations, or outputs, that you want to analyze after a
After Tax IRR, all shown in light blue. Forecasts are equations, or outputs, that you want to analyze after a
simulation. During a simulation, Crystal Ball saves the values in the forecast cells and displays them in a
Forecast Chart, which is a histogram of the simulated values. To view the forecast with Crystal Ball,
highlight the cell and either select Define Forecast from the Define menu or click on the Define Forecast
button on the toolbar.

When you run a simulation, Crystal Ball generates a random number for each assumption (based on how
the assumption has been defined) and places that new value in the cell. Excel then recalculates the
model. You can test this by selecting Single Step from the Run menu or clicking on the Single Step button
on the toolbar.

After you run a simulation, you will see the Property Valuation, NPV of Real Cash Flow, and After Tax IRR
forecast charts. You can view the statistics or percentile values of the run, or you can enter values into the
input fields on the frequency chart view. What is the chance that you will breakeven, i.e. have a NPV of
zero? What is the chance you will lose money? (Hint: enter a number into the lower left field and use the
Enter button on your keyboard to accept the value.)

To view which of the two assumptions had the greatest impact on the forecast, use the Sensitivity Chart.
Which variable causes the most variation in the target forecast? Can you somehow reduce this source of
uncertainty and improve your overall forecast? Change the assumption parameters or the assumption
types, run a new simulation (hint: don't forget to hit the reset button before beginning a new simulation!),
and compare the results. You can also generate a report by selecting Create Report from the Run menu or
clicking on the Create Report button on the toolbar.
commercial property and
r a 10 year holding
ut as assumptions by the
ome assumptions (Annual
he Cap Rate at Purchase.

perating Income (NOI -
nd 2) The Cap Rate. The
ket Value and Cap Rate

Present Value of Real
e values automatically by

ons that describe the
bability distributions,
Crystal Ball as
note (mouse over the
either select Define
e Crystal Ball toolbar.

of Real Cash Flow, and
ou want to analyze after a
ou want to analyze after a
and displays them in a
st with Crystal Ball,
on the Define Forecast

sumption (based on how
en recalculates the
on the Single Step button

h Flow, and After Tax IRR
can enter values into the
en, i.e. have a NPV of
er left field and use the

se the Sensitivity Chart.
ow reduce this source of
rs or the assumption
ning a new simulation!),
port from the Run menu or
Commercial Real Estate Valuation and Financial Feasibility
ASSUMPTIONS                                                                                   Commercial Real Estate Valuation and Financial Feasibility
Annual Gross Rent, first year                      182,400              NOI
Vacancy and Collection factor                           5%          Assumptions
Operating Expenses, first year                      55,540                                INSTRUCTIONS:
Annual % change in rent                               3.0%
Annual % change in expenses                           3.0%                                1.) Conduct comparables analysis of similar properties to estimate the cap rate at purchase.
Loan to Value ratio                                  75.0%                                2.) Define all assumption distributions to reflect current and local market conditions.
Stated Annual Interest rate                           6.0%                                3.) Run simulation.
Loan Term (years)                                        25                               4.) Analyze results.
Percent of price in improvements                     85.0%
CPI Annual Increase                                   3.0%
After tax, Real Discount rate                         8.0%
Model developed by Questor Consulting LLC, Better Analysis. Better Decisions.
Cap Rate assumed at date of sale                      8.0%
For information regarding this model or for requests, please call us at (561) 417-5515.
Transaction costs as % of sales price                 9.0%
Cap Rate at Purchase                                  9.1%

DEFINED VARIABLES                                                 Source
Recovery period (years)                                 39   Omnibus Budget Reconciliation Act of 1993
Income tax rate (Corporate)                         15.0%    IRS - Varies based on taxable income
Capital Gains tax rate                               5.0%    IRS - Varies based on income tax bracket

Property Valuation:                       \$   1,299,558.50

ECONOMIC VALUES, CALCULATED BY MODEL
Loan Amount                                        974,669
Equity Required                                    324,890
Mortgage Loan Constant                               7.82%

DECISION ANALYSIS FACTORS
year       0              1             2            3           4            5            6            7           8           9          10         11
Real Cash Flow to Owner                                      (324,890)         35,781       204,398      195,471     192,448      189,509      186,650      183,870     181,165     178,535    935,104           0
Present Value of Real Cash Flow                              (324,890)         33,131       175,238      155,171     141,455      128,977      117,621      107,286      97,878      89,312    433,134           0

NPV of Real Cash Flow:                    \$   1,154,313.90
After Tax Real Internal Rate of Return:             45.71%

PROFORMA INCOME STATEMENT
year         0           1              2            3           4            5            6            7           8           9          10         11
Annual Gross Rental Income                                        na         182,400        187,872      193,508     199,313      205,293      211,452      217,795     224,329     231,059    237,991    245,130
Vacancy and Collection Losses                                     na          (9,120)        (9,394)      (9,675)     (9,966)     (10,265)     (10,573)     (10,890)    (11,216)    (11,553)   (11,900)   (12,257)
Effective Rental                                                  na         173,280        178,478      183,833     189,348      195,028      200,879      206,905     213,113     219,506    226,091    232,874
Operating Expenses                                                na         (55,540)       (57,206)     (58,922)    (60,690)     (62,511)     (64,386)     (66,318)    (68,307)    (70,356)   (72,467)   (74,641)
Net Operating Income                                              na         117,740        121,272      124,910     128,658      132,517      136,493      140,588     144,805     149,150    153,624    158,233
Interest Expense                                                  na         (58,480)       (57,414)     (56,284)    (55,087)     (53,817)     (52,472)     (51,045)    (49,533)    (47,930)   (46,232)   (44,431)
Depreciation (cost recovery)                                      na         (28,324)       (28,324)     (28,324)    (28,324)     (28,324)     (28,324)     (28,324)    (28,324)    (28,324)   (28,324)   (28,324)
Taxable Income                                                    na          30,936         35,534       40,302      45,247       50,376       55,698       61,219      66,949      72,895     79,069     85,478
Income Tax Liability                                              na          (4,640)        (5,330)      (6,045)     (6,787)      (7,556)      (8,355)      (9,183)    (10,042)    (10,934)   (11,860)   (12,822)
Net Income After Tax                                              na          26,296         30,204       34,257      38,460       42,820       47,343       52,036      56,906      61,961     67,208     72,657

Developed by Questor Consulting LLC   (561) 417-5515
PROFORMA CASH FLOW STATEMENT
year       0           1           2         3           4          5          6         7           8          9         10         11
Annual Gross Rental Income                                           na        182,400     187,872    193,508    199,313    205,293    211,452    217,795    224,329    231,059    237,991    245,130
Vacancy and Collection Loses                                         na         (9,120)     (9,394)    (9,675)    (9,966)   (10,265)   (10,573)   (10,890)   (11,216)   (11,553)   (11,900)   (12,257)
Effective Rental                                                     na        173,280     178,478    183,833    189,348    195,028    200,879    206,905    213,113    219,506    226,091    232,874
Operating Expenses                                                   na        (55,540)    (57,206)   (58,922)   (60,690)   (62,511)   (64,386)   (66,318)   (68,307)   (70,356)   (72,467)   (74,641)
Net Operating Income                                                 na        117,740     127,444    124,910    128,658    132,517    136,493    140,588    144,805    149,150    153,624    158,233
Debt Service                                                         na        (76,245)     94,732     94,732     94,732     94,732     94,732     94,732     94,732     94,732     94,732          na
Income Tax Liability                                                 na         (4,640)     (5,330)    (6,045)    (6,787)    (7,556)    (8,355)    (9,183)   (10,042)   (10,934)   (11,860)         na
Equity Dividend (cash to owner)                                      na         36,854     216,845    213,597    216,602    219,693    222,870    226,137    229,495    232,947    236,496          na
Down Payment/Reversion                                            (324,890)      na          na         na         na         na         na         na         na         na     1,020,206          na
Total Cash Flow to Owner                                          (324,890)     36,854     216,845    213,597    216,602    219,693    222,870    226,137    229,495    232,947 1,256,702           na
Purchasing Power Adjustment                                          na           103%        106%       109%       113%       116%       119%       123%       127%       130%       134%          na
Real Cash Flow to Owner                                           (324,890)     35,781     204,398    195,471    192,448    189,509    186,650    183,870    181,165    178,535    935,104          na

MORTGAGE LOAN AMORTIZATION SCHEDULE
year       0          1           2          3          4          5          6          7          8          9          10         11
Balance Owed, beginning of year                                                974,669     956,904    938,073    918,112    896,954    874,526    850,752    825,552    798,840    770,525    740,512
Annual Mortgage Payment                                                         76,245      76,245     76,245     76,245     76,245     76,245     76,245     76,245     76,245     76,245     76,245
Interest Portion of Payment                                                    (58,480)    (57,414)   (56,284)   (55,087)   (53,817)   (52,472)   (51,045)   (49,533)   (47,930)   (46,232)   (44,431)
Amortization of principal                                                       17,765      18,831     19,961     21,158     22,428     23,774     25,200     26,712     28,315     30,014     31,814
Balance Owed, end of year                                                      956,904     938,073    918,112    896,954    874,526    850,752    825,552    798,840    770,525    740,512    708,697

ANALYSIS OF REVERSION ON SALE
Net Operating Income Projected, Year After Sale (Year 11)                        158,233
Cap Rate At Sale Date                                                              8.00%
Capitalized Value (Sale Price)                                                 1,977,909
Transaction Cost                                                                (178,012)
Net Sales Price                                                                1,799,897
Book Value At Sales Date (cost-dep)                                            #########
Capital Gain ( Net Price - BV)                                                   783,576
Capital Gains Tax                                                                 39,179
Mortgage Balance Owed                                                            740,512
Reversion in nominal dollars to owner at sales date                            1,020,206

42956.2
74533.82

Developed by Questor Consulting LLC   (561) 417-5515
47966.58

0.012696

867.6558

253913

0.732727

Developed by Questor Consulting LLC   (561) 417-5515
0.01824
0.015802

0.043248

0.030238
0.092582

0.023333

0.018334

0.009235

Developed by Questor Consulting LLC   (561) 417-5515

```
To top