Real Estate and Rent Increase

Document Sample
Real Estate and Rent Increase Powered By Docstoc
					                                                Chapter 9
                                               Effective Rents
Spreadsheet Limitations: Net Lease with free rent has begins payment year two.



Data Input Box:
Inerest Rate                                       10% Adjustments
Year 1 Rents                                           Step Amount               $    1.00
Net Lease with Steps                           $ 10.00 Percent CPI Adj.               100%
Net with CPI Adjustment                          11.00 Yr 2 CPI                      2.00%
Gross Lease                                      17.50 CPI Growth                    1.00%
Gross with Exp. Stop                             15.50 Yr 1 Expenses                  4.00
Gross with Exp. Stop &CPI                        14.50 Expense Growth                 0.50
Year 2 Rents                                           Expense Stop                   4.00
Net Lease with Free Rent                         14.50


NET LEASE with STEPS:
Year                               1             2           3          4            5
Net Rent              $                10.00     11.00       12.00      13.00        14.00
Average Rent                                   $ 12.00
Present Value                                    44.77
Effective Rent                                   11.81


NET LEASE with FREE RENT:
Year                               1              2          3          4            5
Net Rent                           0              14.50      15.50      16.50        17.50
Average Rent                                      12.80
Present Value                                     45.76
Effective Rent                                    12.07


NET LEASE with CPI ADJUSTMENT:
Year                        1                     2          3          4            5
Expected CPI                -                     2.00%      3.00%      4.00%        5.00%
Net Rent                       11.00               11.22      11.56      12.02        12.62
Average Rent                                      11.68
Present Value                                     44.00
Effective Rent                                    11.61


GROSS LEASE:
Year                               1              2          3          4            5
Gross Rent                             17.50      17.50      17.50      17.50        17.50
Less Expenses                           4.00       4.50       5.00       5.50         6.00
Net Rent                               13.50      13.00      12.50      12.00        11.50
Average Rent                                      12.50
Present Value                                     47.74
Effective Rent                                    12.59
GROSS LEASE with EXPENSE STOP:
Year                       1           2        3       4        5
Gross Rent                     15.50   15.50    15.50   15.50    15.50
Less Expenses                   4.00    4.50     5.00    5.50     6.00
Plus Reimbursement              0.00    0.50     1.00    1.50     2.00
Net Rent                       11.50   11.50    11.50   11.50    11.50
Average Rent                           11.50
Present Value                          43.59
Effective Rent                         11.50


GROSS LEASE with EXPENSE STOP and CPI ADJUSTMENT:
Year                       1            2      3        4        5
Expected CPI               -           2.00%   3.00%    4.00%    5.00%
Gross Rent                    14.50     14.79   15.23    15.84    16.64
Less Expenses                  4.00      4.50   5.00     5.50     6.00
Plus Reimbursement             0.00      0.50   1.00     1.50     2.00
Net Rent                      10.50    10.79   11.23    11.84    12.64
Average Rent                           11.40
Present Value                          42.84
Effective Rent                         11.30
per year


per year

per year
                                                   Chapter 10
                                      Mortgage-Equity Capitalization

Spreadsheet Limitations: Five year Holding Period; loan based on DCR and resale based on terminal cap rate.

Data Input Box:
NOI                                     50,000
Projected Increase in NOI                3.00% per year
Operating Expenses                      30.00% of EGI
Loan:
  Debt Coverage Ratio                      1.20
  Interest                              11.00%
  Term                                       20 years
  Payments per year                          12

Holding Period                               5 years
Terminal Cap Rate                       11.00%
Before-tax Equity Yield                 12.00%


Loan Constant          12.3863%
Loan Amount             336,394
Loan Balance            305,493          in year          5

                            Year        1            2         3           4          5          6
Net Operating Income                    50,000      51,500    53,045      54,636     56,275     57,964


Debt Service                            41,667      41,667    41,667      41,667     41,667
Before-tax Cash Flow                     8,333       9,833    11,378      12,970     14,609

DCR                                         1.20      1.24       1.27       1.31       1.35

Cash Flows from Sale in Year                   5

Sales Price                            526,943
Mortgage Balance                       305,493
Before-tax Cash Flow                   221,450


Present Value of Operations at          12.00%      39,910
Present value of Reversion at           12.00%     125,657
Total Equity                                       165,567

Total Equity                                       165,567
Mortgage                                           336,394
Total Present Value                                501,961

Check:
Cash Flow Summary
                            Year        0            1          2          3           4          5
BTCF           (165,567)   8,333   9,833   11,378   12,970   236,059

       BTIRR    12.00%
rminal cap rate.
                                          Chapter 10
                                Highest and Best Use Analysis




Data Input Box:
                         Office              Retail        Apartment   Warehouse
NOI yr 1               $500,000            $600,000        $400,000     $400,000
Return or r              13.00%              12.00%          12.00%      10.00%
Growth or g               3.00%               4.00%           3.00%       2.00%
Building Costs         4,000,000           4,000,000       4,000,000   4,000,000


HIGHEST and BEST USE ANALYSIS:

                                        Implied Property   Building    Implied Land
       Use        Year 1 NOI    R             Value          Costs         Value
Office             $500,000    10.00%      5,000,000       4,000,000     1,000,000
Retail              600,000     8.00%      7,500,000       4,000,000     3,500,000
Apartment           400,000     9.00%      4,444,444       4,000,000      444,444
Warehouse           400,000     8.00%      5,000,000       4,000,000     1,000,000
                                  Oakwood Apartment Example

Building Name                     Oakwood
Address                           1234 Elm Street
City                              Anywhere
State                             USA
Total Units                                     95
Unit Size                                   1,100
Building Size (SF)                        104,500

Analysis Begin Date                          1/1/2000
Holding Period                                      5
Discount Rate                                 11.00%
Terminal Rate                                  9.00%
Selling Cost                                   5.00%

              Inputs              Apartment Unit
Name                              Two Bedroom
Units                                          95
Monthly Rent                           $1,200.00
Lease Term (Yrs)                               1
Market Monthly Rent                    $1,250.00
Market Rent Increase                       3.00%

Laundry Income/unit/year                     $120.00
Laundry Income increase                        3.00%

Market Vacancy Rate                            5.00%
Credit Loss Rate                               1.00%

                                  % of EGI              $ Amount    $ per Unit      Change %
Real Estate Taxes                              0.00%        $87,000           $0.00       2.50%
Office Expenses                                0.00%        $20,000           $0.00       3.00%
Insurance                                      0.00%             $0        $150.00        3.00%
Repairs & Maintenance                          0.00%             $0        $550.00        3.00%
Advertising                                    0.00%         $8,000           $0.00       3.00%
Management                                    12.00%             $0           $0.00       0.00%
Utilities                                      0.00%        $45,000           $0.00       3.00%
Miscellaneous Expenses                         0.00%        $15,000           $0.00       3.00%



             Outputs
Year                                               1             2             3             4             5
Income:
Current Rent                           $1,368,000         $0                  $0            $0            $0
Market Rent from lease renewals                 $0 $1,467,750          $1,511,783    $1,557,136    $1,603,850
Laundry Income                            $11,400     $11,742             $12,094       $12,457       $12,831
Potential Gross Income (PGI)           $1,379,400 $1,479,492          $1,523,877    $1,569,593    $1,616,681
Less: Vacancy                             $68,400    $73,388             $75,589       $77,857       $80,193
Less: Credit Loss                         $13,794    $14,795             $15,239       $15,696       $16,167
Effective Gross Income (EGI)    $1,297,206 $1,391,310     $1,433,049     $1,476,040   $1,520,322

Expenses:
Real Estate Taxes                 $87,000     $89,175        $91,404       $93,689      $96,032
Office Expenses                   $20,000     $20,600        $21,218       $21,855      $22,510
Insurance                         $14,250     $14,678        $15,118       $15,571      $16,039
Repairs & Maintenance             $52,250     $53,818        $55,432       $57,095      $58,808
Advertising                        $8,000      $8,240         $8,487        $8,742       $9,004
Management                       $155,665    $166,957       $171,966      $177,125     $182,439
Utilities                         $45,000     $46,350        $47,741       $49,173      $50,648
Miscellaneous Expenses            $15,000     $15,450        $15,914       $16,391      $16,883
Total Expenses                   $397,165    $415,267       $427,279      $439,641     $452,361

Net Operating Income (NOI)       $900,041    $976,042     $1,005,770     $1,036,400   $1,067,960

Expenses % of EGI                  30.62%      29.85%          29.82%       29.79%       29.75%


Net Operating Income             $900,041    $976,042     $1,005,770     $1,036,400   $1,067,960
PV Factors                        0.90090     0.81162        0.73119        0.65873      0.59345
Present Value                    $810,848    $792,178       $735,410       $682,709     $633,782


Resale                         $12,227,545                              Sum PV NOI  $3,654,927
Selling Cost                      $611,377                              PV Resale   $6,893,630
Net Resale                     $11,616,168                              Value      $10,548,557
PV Factor                          0.59345
PVResale                        $6,893,630
                                                        Implied Change in Value          15.92%
         6

        $0
 $1,651,966
    $13,216
$1,665,181
   $82,598
   $16,652
$1,565,931


  $98,433
  $23,185
  $16,520
  $60,572
   $9,274
 $187,912
  $52,167
  $17,389
 $465,452

$1,100,479

   29.72%


$1,100,479
                                          Chapter 11 Lease Calculations
                                            Given Lease Data, Solve for NOI
                  Spreadsheet Assumption: Vacancy rate does not take effect until the beginning of year 4.
                                      Corresponds with Homework Problem 10-1


Current Market Rent                                                                   $15.00    per s.f.
Gross square feet of building                                                        100,000    s.f.
Net rentable square feet of building                                                  96,000    s.f.
Projected Increase in Market Rent                                                      4.00%    per year
Management costs                                                                       5.00%    of Effective Gross Income
Estimated increase in CPI                                                              4.00%    per year
Vacancy rate starting year 4                                                           5.00%    per year

SUMMARY LEASE INFORMATION

                                       Current
 Tenant                 Sq. ft.         Rent         Current      Remaining Expense stop                     CPI adjustme
                                       per s.f.       Rent        term (yrs)  per s.f.

Tenant 1                70,000             $14.00     $980,000               3          $4.00
Tenant 2                     0             $14.00            0               3           4.00
Tenant 3                     0             $14.00            0               3           4.00
Tenant 4                10,000             $14.50      145,000               4           4.25
Tenant 5                     0             $15.00            0               5           4.45
Tenant 6                16,000             $15.00      240,000               5           4.45

Total                   96,000                        1,365,000

                                                    Note: Expense stop excludes management expenses

Summary of Expense Information

                                  Dollars            per s.f.

Property tax                             $148,800          1.55 Level for 2 years then 10% increase, then level
Insurance                                  14,400          0.15   increase              4.00% per year
Utilities                                 120,000          1.25   increase              5.00% per year
Janitorial                                 76,800          0.80   increase              3.00% per year
Maintenance                                67,200          0.70   increase              3.00% per year

Subtotal (before mgt)                     427,200          4.45 (before management expenses)

Management                                $68,250          0.71         5.00% of EGI

Total                                    $495,450         $5.16




PROJECTED BASE INCOME
Year                                   1           2           3           4           5

Tenant 1                        980,000     980,000     980,000    1,181,107   1,181,107
Tenant 2                              0           0           0            0           0
Tenant 3                              0           0           0            0           0
Tenant 4                        145,000     145,000     145,000      145,000     175,479
Tenant 5                              0           0           0            0           0
Tenant 6                        240,000     240,000     240,000      240,000     240,000

       Total base             1,365,000    1,365,000   1,365,000   1,566,107   1,596,586

Tenant 1                         19,600      39,592      59,984           0      23,622
Tenant 4                          2,900       5,858       8,875      11,953           0
Tenant 6                              0       4,800       9,696      14,690      19,784

       Total CPI                 22,500      50,250      78,555      26,643      43,406

Total Base and CPI            1,387,500    1,415,250   1,443,555   1,592,750   1,639,992

                          Expense Reinmursements
Tenant 1                       $31,500    $39,445       $58,570          $0      $8,979
Tenant 2                             0          0             0           0           0
Tenant 3                             0          0             0           0           0
Tenant 4                         2,000      3,135         5,867       7,099           0
Tenant 5                             0          0             0           0           0
Tenant 6                             0      1,816         6,187       8,158      10,210
Total Reimbursements            33,500     44,396        70,625      15,256      19,189

Potential Gross Income        1,421,000    1,459,646   1,514,180   1,608,006   1,659,181
Vacancy                               0            0           0      80,400      82,959
Effective Gross Income        1,421,000    1,459,646   1,514,180   1,527,606   1,576,222

SUMMARY OF OPERATING EXPENSES

                           Reimbursable expenses
Property tax                   148,800     148,800      163,680     163,680     163,680
Insurance                       14,400      14,976       15,575      16,198      16,846
Utilities                      120,000     126,000      132,300     138,915     145,861
Janitorial                      76,800      79,104       81,477      83,921      86,439
Maintenance                     67,200      69,216       71,292      73,431      75,634
Total before management        427,200     438,096      464,325     476,146     488,460
per s.f.                           4.45        4.56         4.84        4.96        5.09

                          Non reimbursable expenses
Management                        71,050      72,982     75,709      76,380      78,811
                                Total expenses
Total Expenses                   498,250     511,078    540,034     552,526     567,271


PROJECTED NET OPERATING INCOME
Year                                  1           2           3           4           5
Base income                        $1,365,000   $1,365,000    $1,365,000    $1,566,107   $1,596,586
Plus CPI Adjustment                    22,500       50,250        78,555        26,643       43,406
Plus Reimbursements                   $33,500      $44,396       $70,625       $15,256      $19,189
Total Potential Income             $1,421,000   $1,459,646    $1,514,180    $1,608,006   $1,659,181
Less Vacancy                                0            0             0        80,400       82,959
Effective Gross Income              1,421,000    1,459,646     1,514,180     1,527,606    1,576,222
Less operating expenses:
 Reimbursable expenses               427,200       438,096       464,325      476,146       488,460
 Non reimbursable expenses            71,050        72,982        75,709       76,380        78,811
NOI                                 $922,750      $948,568      $974,146     $975,080    $1,008,951


Asking price                       $8,500,000
Capitalization rate                    10.86% (1st year NOI / Price)

Average compound increase in NOI                     2.63% (years 1 to 6)
of year 4.




Effective Gross Income




             CPI adjustment


                   50.00%
                   50.00%
                   50.00%
                   50.00%
                   50.00%
                   50.00%




se, then level
        6

1,181,107
        0
        0
  175,479
        0
  291,997

1,648,583

  47,717
   3,510
       0

  51,226

1,699,809


 $18,334
       0
       0
   1,336
       0
       0
  19,670

1,719,479
   85,974
1,633,505




 163,680
  17,520
 153,154
  89,032
  77,903
 501,289
     5.22


  81,675

 582,964



       6
$1,648,583
    51,226
   $19,670
$1,719,479
    85,974
 1,633,505

   501,289
    81,675
$1,050,541
                                                       Chapter 11
                                              Basic Investment Analysis
                                            Spreadsheet Assumptions: 5 year holding period.




Data Input Box:
Purchase price           $8,500,000
Resale Price              9,700,000 <--- This is usually a function of the 6th year NOI and the terminal capitalization ra




TAXABLE INCOME & AFTER-TAX CASH FLOW FROM OPERATIONS:
Year                                      0          1                             2           3
Purchase price                 ($8,500,000)
Net Operating Income (NOI)                     922,750                      948,568     974,146
Sales Price
Cash flow                      ($8,500,000)    922,750                      948,568     974,146

IRR                                          13.46%

Discount rate                 14.00%

Present Value of Cash flows            $8,336,061.59

6th Year NOI             $1,050,541
Terminal Cap Rate            10.83%
Purchase Cap Rate            10.86%
period.




OI and the terminal capitalization rate.




                        4              5

                  975,080     1,008,951
                              9,700,000
                  975,080    10,708,951
                                                        Chapter 11
                                             After Tax Investment Analysis
   Spreadsheet Assumptions: 5 year holding period. Assumes passive losses used in year they are incurred. No PAL limitati
Also assumes land costs to be 15% of purchase price.



Data Input Box:
Purchase price                      $8,500,000                    Tax Considerations:
Building Value                      $7,225,000                    Depreciation (in years)            39
Ordinary income tax rate                36.00%                    Capital gains tax rate        20.00%
Loan-to-Value                           70.00%                    Depreciation recapture        25.00%
Loan Interest                           10.00%
Loan term                                    20 years             Building Square Footage:
Payments per year                            12                   Gross                        100,000
Resale Price                         9,700,000                    Net                           96,000
Holding Period                                5 years
Selling costs                            0.00% of sale price
Equity discount rate                    13.00%
Reinvestment rate                        6.00%

Equity                                2,550,000
Loan                                  5,950,000
Annual Loan Payment                     689,025
Mortgage Balance                      5,343,245            year               5


SUMMARY LOAN INFORMATION:
End of Year                             1              2                3             4           5
Payment                                 689,025       689,025          689,025      689,025     689,025
Mortgage Balance                      5,851,543     5,742,776        5,622,620    5,489,883   5,343,245
Interest                                590,569       580,259          568,869      556,288     542,388
Principal                                98,457       108,767          120,156      132,738     146,637


TAXABLE INCOME & AFTER-TAX CASH FLOW FROM OPERATIONS:
Taxable Income (Loss):
Net Operating Income (NOI)    922,750    948,568    974,146                        975,080    1,008,951
Less: Interest                590,569    580,259    568,869                        556,288      542,388
     Depreciation             185,256    185,256    185,256                        185,256      185,256
Taxable Income (Loss)         146,925    183,053    220,020                        233,536      281,306
Tax (Savings) at 36%            52,893    65,899      79,207                        84,073      101,270


Cash flow from operations:
Net Operating Income (NOI)            $922,750       $948,568         $974,146    $975,080 $1,008,951
Less Debt Service (DS)                 689,025        689,025          689,025     689,025    689,025
Before Tax Cash Flow                  $233,725       $259,542         $285,121    $286,054   $319,925
Less Tax                                52,893         65,899           79,207      84,073    101,270
After-tax Cash Flow                   $180,832       $193,643         $205,913    $201,981   $218,655
AFTER-TAX CASH CLOW FROM SALE IN YEAR 5:
Sales Price                              9,700,000
Sales costs                                      0
Mortgage Balance                         5,343,245
Before-tax cash flow                     4,356,755

Original Cost Basis                   8,500,000
Accumulated Depreciation                926,282
Adjusted Basis                        7,573,718                    <-- We could have included capital improvements in here, b

Capital Gain                          2,126,282
Depreciation recapture                  926,282
Price appreciation                    1,200,000

Tax on price appreciation               240,000
Tax on depreciation recapture           231,571
Total capital gain tax                                 471,571
After-Tax Cash Flow from Sale                        3,885,184


CASH FLOW SUMMARY
BTIRR on Equity
Year                                    0               1                2            3            4
Before-Tax Cash Flow                ($2,550,000)      $233,725         $259,542    $285,121     $286,054
After-Tax Cash Flow                 ($2,550,000)      $180,832         $193,643    $205,913     $201,981

Before-Tax IRR (BTIRR)                      20.08%
After-Tax IRR (ATIRR)                       15.45%

Effective Tax Rate                          23.05%

AFTER-TAX NET PRESENT VALUE
PV of ATCFo                                           $696,943
PV of ATCFs                                           2,108,722
Total Present Value                                   2,805,666
Less Original Equity Investment                      (2,550,000)
After-Tax Net Present Value                             255,666                   Use Goal Seek to solve for ATNPV. "Goal

Modified AT IRR (using reinvestment rate)               14.45% (also called Adjusted IRR)

BEFORE-TAX INVESTMENT ANALYSIS MEASURES
Price/Gross Square Foot                   $85.00
Price/Net Square Foot                     $88.54
Capitalization Rate                       10.86%
First Year Debt Coverage Ratio (DCR)         1.34                                 NOI/DS
Modified IRR (6% reinvestment rate)       18.28%                                  Take into account reinvestment rate.
NPV (18% discount rate)                  771,455
Profitablility Index (18% discount rate)    1.30                            1.30 NPV of BTCF/Equity Investment
BT Equivalent Yield                       24.15%                                 ATIRR/(1 - tax rate)
y are incurred. No PAL limitations considered.




            <-- Alternately, we could use PMT, IPMT, and PPMT to solve.
            <-- Alternately, we could use PMT, IPMT, and PPMT to solve.
            <-- Alternately, we could use PMT, IPMT, and PPMT to solve.




            <--- If we had capital improvements, these would have their own depreciation schedule.
capital improvements in here, but it would make this even more complex. Thankfully, none are shown.




                   5
               $4,676,680
               $4,103,839




ek to solve for ATNPV. "Goal Seek" can be found under the Tools menu.




count reinvestment rate.

F/Equity Investment

				
DOCUMENT INFO
Description: Real Estate and Rent Increase document sample