What are the annual cash flows for periods 1 to 11; what is the terminal value; what is the before and after tax NPV and IRR (use end of period); what is the division of value between cash flows and resale? Do you recommend purchasing the property? why or why not?

EXCEL PROJECT - FIRE 305 Real Estate Investment Analysis You have been hired to perform an investment analysis for a high net worth individual to determine if they should purchase a commercial real estate investment. The investment includes office, retail and townhome land. The following are the income characteristics: Office - 50,000 square feet is leased to a national tenant, Funeral Directors Association for $40 per square foot for the next 20 years. The rent will not increase, and there are expense stops of $8.50 per square foot and there is no risk of vacancy or credit loss. 20,000 square feet is leased to an accounting firm, Dewey Cheatham, and Howe for $36 per square foot for the next three years with no rent increases and there is no risk of vacancy or credit loss. At the end of three years, they will vacate the space and it will take six months to lease the space at which time the market rent is projected to be $40 per square foot with annual increases of 2 percent. The lease term for this space is 15 years and the vacancy and credit loss on this space is 1 percent. Office expenses for all tenants are $7.25 per square foot and increase by 3 percent per year. Retail - 15,000 square feet is leased to Trader Joe's for $45 per square foot with no rent increases on a triple net basis for the next 20 years, and there is no risk of default. They pay percentage rent of 2.5 percent over the natural breakpoint. They currently have annual store sales of $20 million and expect sales to grow by 8 percent per year. 30,000 square feet is leased to HH Gregg for $35 per square foot triple net basis for the next 15 years with no rent increases. However, percentage rent is 4 percent over the natural breakpoint. Current store sales are $24 million and are expected to grow by 10 percent per year. Caribou Coffee leases 1,200 square feet for $50 per square foot with rent increases of 4 percent per year on a triple net basis for 15 years, and there is no risk of credit loss. Townhome Land - There is acreage to support 120 townhomes. These lots are under contract to three different builders: Eagle of Virginia, Ryan Homes, and HHHunt. Each builder will take down (purchase) 10 lots per year at an initial price of $75,000 and lot prices will increase by $10,000 per year until all lots are sold. The development cost of each lot is $30,000 and will increase by $5,000 per year. Holding costs are $1,000 based on the remaining lots, and the cost of sale for each lot is 6 percent. Financing - Third Fifth Bank is providing 70 percent loan to value at 6.75 percent amortized over 25 years with a 10 year call on the office and retail area only. No financing is available on the land. Taxes - Your investor has an ordinary income tax rate of 28 percent. The long term capital gain taxes are 20 percent, and recapture is 25 percent. Investment Criteria /Assumptions- The property is offered for $45 million. The terminal cap rate is 7.50 percent, and the discount rate is 9.5 percent. The holding period is 10 years. Assume that the mixed use land value is $10 million. Also, assume that the cost of sale at the end of the holding period is 1 percent. Questions: What are the annual cash flows for periods 1 to 11; what is the terminal value; what is the before and after tax NPV and IRR (use end of period); what is the division of value between cash flows FIRE 305, Principles of Real Estate - Spring 2016 and resale? Do you recommend purchasing the property? Explain why or why not. Show all calculations - PGI, EGI, Expenses, NOI, Taxes, etc. If unclear about something, clearly state your assumptions. FIRE 305, Principles of Real Estate - Spring 2016 Mixed Use Project Dylan Cohan FIRE 305, Principles of Real Estate, Tenant Assumptions Tenant Funeral Directors Association Accounting Firm-Dewey Cheat New Retail-Trader Joe's Retail-Sales Ratail-HH Gregg Current Store Slaes Caribou Coffee Townhome Land Remaining Lease Term 20 Years 3 Years 15 Years 20 Years Annual 15 Years Annual 15 Years Annual 30 Financing Assumption LTV Rate Call Amortization BEFORE TAX CASH FLOWS Funeral Directors Association Accounting Firm-Dewey Cheat New Retail-Trader Joe's Retail-Trader Joe's over Natural BEP Ratail-HH Gregg Ratail-HH Gregg over Natural BEP Caribou Coffee Townhome Land TOTAL LESS VACANCY & CREDIT Funeral Directors Association Accounting Firm-Dewey Cheatham New Retail-Trader Joe's Retail-Sales Ratail-HH Gregg Current Store Slaes Caribou Coffee Townhome Land TOTAL EGI Funeral Directors Association Accounting Firm-Dewey Cheatham New Retail-Trader Joe's Retail-Sales Ratail-HH Gregg Current Store Slaes Caribou Coffee Townhome Land TOTAL EXPENSES Funeral Directors Association Accounting Firm-Dewey Cheatham New Retail-Trader Joe's Retail-Sales Ratail-HH Gregg Current Store Slaes Caribou Coffee Townhome Land-Development Cost Townhome Land-Holding Cost Townhome Land-Cost of Sales TOTAL NET OPERTAING INCOME Funeral Directors Association Accounting Firm-Dewey Cheatham New Retail-Trader Joe's Retail-Sales Ratail-HH Gregg Current Store Slaes Caribou Coffee Townhome Land TOTAL TERMINAL VALUE TERMINAL VALUE Cost of Sales Gross Leasable Area 50000 20000 20000 15000 1 30000 1 1200 120 Rent $40 $36 $40 $45 $20,000,000 $35 $24,000,000 $50 $75,000 28% 20.00% 25.00% 682,474.36 478,800.00 0 50000 20000 20000 15000 $40 $36 $40 $45 30000 $35 1200 $50 $75,000 Vacancy & Credit Loss 0.00% 0.00% 2.00% 0.00% 8.00% 0.00% 10.00% 4.00% $10,000 Income Taxes Ordinary LT Cap Rates Recapture CRA CRA-MF 70% 6.75% 10 Years 25 Net Present Value Before Tax Cash Flows. Net Operating Income Present value Factor Annual Increases 1 2,000,000.00 720,000.00 675,000.00 1,050,000.00 60,000.00 2,250,000.00 6,755,000.00 - 0% 0% 1% 0% 0% 0% 0% 0% Expenses Expenses Stop $7.25 $7.25 $7.25 $0.00 $0.00 $0.00 $0.00 $0.00 $8.50 None None None None None None None $30,000.00 Investment Criteria Purchase Price Land Value Mixed Used Value Mixed Used Land Val Townhome Value Townhome Land Valu 2 ### 720,000.00 ### 675,000.00 ### 6,000.00 62,400.00 ### ### ### ### ### ### ### ### ### ### - 3 2,000,000.00 720,000.00 675,000.00 1,050,000.00 111,600.00 64,896.00 2,850,000.00 7,471,496.00 - Expenses Increases Holding Cost 3% 3% 3% $5,000.00 $1,000.00 45,000,000.00 Terminal Cap 7.50% 3,600,000.00 Discount Rate 9.50% 36,616,500.00 Holding Period 10 Years 10,000,000.00 Cost of Sales 1% 8,383,500.00 (4,783,500.00) PERIODS 4 5 6 2,000,000.00 ### 2,000,000.00 400,000.00 675,000.00 1,050,000.00 227,760.00 67,491.84 3,150,000.00 7,570,251.84 4,000.00 4,000.00 Cost on Sales Valuation Unleveraged NPV $11,071,315 Unleveraged IRR 13.328% Accepted Investment ### 8 2,000,000.00 848,966.40 ### 118,437.16 ### 650,698.56 75,919.14 ### 865,945.70 675,000.00 181,912.13 1,050,000.00 820,768.42 78,955.91 5,672,582.16 8,323.20 8,323.20 ### ### 8,489.66 ### ### ### ### ### ### 8,489.66 8,659.46 8,659.46 ### ### 8,832.65 ### ### ### ### ### ### 8,832.65 9,009.30 9,009.30 816,000.00 832,320.00 675,000.00 675,000.00 5,244.48 59,664.04 ### 1,050,000.00 355,536.00 496,089.60 70,191.51 72,999.17 ### 5,186,072.81 ### ### 8,160.00 ### ### ### ### ### ### 8,160.00 6% 7 9 10 ### 2,000,000.00 883,264.60 900,929.90 ### 675,000.00 250,465.11 324,502.31 ### 1,050,000.00 ### 1,213,629.78 82,114.14 85,398.71 ### 6,249,460.71 11 ### 918,948.50 ### 404,462.50 ### ### 88,814.66 ### 9,189.48 9,189.48 2,000,000.00 720,000.00 675,000.00 1,050,000.00 60,000.00 2,250,000.00 6,755,000.00 425000 ### 720,000.00 675,000.00 ### 6,000.00 62,400.00 ### ### 2,000,000.00 720,000.00 675,000.00 1,050,000.00 111,600.00 64,896.00 2,850,000.00 7,471,496.00 2,000,000.00 396,000.00 675,000.00 1,050,000.00 227,760.00 67,491.84 3,150,000.00 7,566,251.84 ### 2,000,000.00 807,840.00 823,996.80 675,000.00 675,000.00 5,244.48 59,664.04 ### 1,050,000.00 355,536.00 496,089.60 70,191.51 72,999.17 ### 5,177,749.61 ### 840,476.74 675,000.00 118,437.16 ### 650,698.56 75,919.14 ### 2,000,000.00 857,286.24 675,000.00 181,912.13 1,050,000.00 820,768.42 78,955.91 5,663,922.70 ### 874,431.95 675,000.00 250,465.11 ### ### 82,114.14 ### 2,000,000.00 891,920.60 675,000.00 324,502.31 1,050,000.00 1,213,629.78 85,398.71 6,240,451.41 362,500.00 145,000.00 3,600,000.00 1,000.00 540,000.00 4,648,500.00 373,375.00 149,350.00 ### ### ### ### ### ### ### ### 612,000.00 ### 384,576.25 153,830.50 3,969,000.00 1,000.00 684,000.00 5,192,406.75 396,113.54 79,222.71 1,350,000.00 189,000.00 2,014,336.25 407,996.94 ### 163,198.78 ### ### ### ### ### ### 571,195.72 420,236.85 168,094.74 588,331.59 425,000.00 ### 173,137.58 ### ### ### ### ### ### ### ### 598,137.58 425,000.00 178,331.71 603,331.71 ### ### 183,681.66 ### ### ### ### ### ### ### ### 608,681.66 425,000.00 189,192.11 614,192.11 ### 194,867.88 619,867.88 1,637,500.00 575,000.00 675,000.00 1,050,000.00 60,000.00 ### 2,106,500.00 ### 570,650.00 675,000.00 ### 6,000.00 62,400.00 ### ### 1,615,423.75 566,169.50 675,000.00 1,050,000.00 111,600.00 64,896.00 (1,804,000.00) 2,279,089.25 1,603,886.46 316,777.29 675,000.00 1,050,000.00 227,760.00 67,491.84 1,611,000.00 5,551,915.60 ### 1,579,763.15 644,641.22 655,902.06 675,000.00 675,000.00 5,244.48 59,664.04 ### 1,050,000.00 355,536.00 496,089.60 70,191.51 72,999.17 ### 4,589,418.02 ### 667,339.15 675,000.00 118,437.16 ### 650,698.56 75,919.14 ### 1,575,000.00 678,954.53 675,000.00 181,912.13 1,050,000.00 820,768.42 78,955.91 5,060,590.99 ### 690,750.29 675,000.00 250,465.11 ### ### 82,114.14 ### 1,575,000.00 702,728.49 675,000.00 324,502.31 1,050,000.00 1,213,629.78 85,398.71 5,626,259.30 ### 714,891.14 675,000.00 404,462.50 ### ### 88,814.66 ### ### 793,088.14 ### ($45,000,000) 2,106,500.00 ### 1 0.9132420091 0.834010967 2,279,089.25 0.7616538514 5,551,915.60 ### 4,589,418.02 0.6955742935 0.635227665 0.5801165893 ### 0.52978684 5,060,590.99 ### ### 0.4838235978 0.441848034 0.4035141867 ### 909,759.01 675,000.00 404,462.50 ### ### 88,814.66 ### OK Present value Sum of Present Value(Period 0 ($45,000,000) $1,923,744 $1,791,185 $1,735,877 Using NPV and IRR Function NPV $11,071,315 IRR 13.328% AFTER TAX FLOWS Total Value Mixed Use Improvements No of Years CRA Townhome Improvements No of Years CRA $2,790,311 Total Value of the Property Value from Net revesion Value from Cash Flows Income Taxes Ordinary LT Cap gains Recapture $2,662,398 $2,549,543 $2,448,433 $2,355,569 $33,952,485 ### ### 39 682,474.36 ### 27.50 478,800.00 0 Net Oprating Income -Interest Expenses -Cost Recovery Allowance-Mixed Use -Cost Recovery Allowance-Townhome Taxable Income Ordinary Tax Rate Income Tax 1 2 0 2,106,500.00 ### 2,126,250.00 ### 682,474.36 682,474.36 478,800.00 478,800.00 ### ### 28% 28% (309,401.83) After Tax Equity Reversion Gross Sales Price ### Less: Cost of Sales 793,088.14 Net Sales Price ### -Less: Taxes 7,283,782.37 AFTER-Before Mortgage Payoff ### -Less-Mortgage Payoff ### AFTER ### Recapture (CRA) Tax Annual Depriciation Holding period Total Deprication Recature Tax Rate Recapture Tax Long Term Capital gain Net Sales Price ### -Less Cost of Acquisition ### -Less Cost Recovery Allowance ### Profit/(Loss) ### LT Cap Gain Rate 20% LT Cap Gain Taxes 4,380,596.47 Total Taxes Due on Sales Long Term Capital Gain Recapture (CRA)Tax Net Oprating Income -Less: Debt Service -Less: Income Taxes After Tax Cash Flows After Tax Equity Reversion 3 2,279,089.25 2,054,212.46 682,474.36 478,800.00 (936,397.57) 28% (262,191.32) Net Present Value After Tax Cash Flows Net Oprating Income Present Value Factors Present Value Sum of Present Value (Period 0 ### ### 18.34% 0 PERIODS 4 5 6 5,551,915.60 ### 4,589,418.02 2,014,507.10 ### 1,926,875.14 682,474.36 682,474.36 682,474.36 478,800.00 478,800.00 478,800.00 2,376,134.14 ### 1,501,268.52 28% 28% 28% 665,317.56 352,581.68 420,355.19 7 ### ### 682,474.36 478,800.00 ### 28% 496,312.64 8 5,060,590.99 1,827,013.59 682,474.36 478,800.00 2,072,303.04 28% 580,244.85 9 10 ### 5,626,259.30 ### 1,713,215.74 682,474.36 682,474.36 478,800.00 478,800.00 ### 2,751,769.20 28% 28% 671,419.88 770,495.38 5,551,915.60 2,642,440.00 665,317.56 2,244,158.04 ### ### 496,312.64 ### 5,060,590.99 2,642,440.00 580,244.85 1,837,906.14 ### 5,626,259.30 ### 2,642,440.00 671,419.88 770,495.38 ### 2,213,323.92 ### 2,244,158.04 ### 1,526,622.83 ### PERIOD 4 5 6 7 $2,244,158.04 ### ### ### 0.6955742935 0.635227665 0.5801165893 0.52978684 $1,560,978.64 $887,790.75 $885,619.23 $886,673.17 1,837,906.14 70% 6.75% $2,642,440 4,380,596.47 2,903,185.90 7,283,782.37 2,279,089.25 2,642,440.00 (262,191.32) (101,159.43) (535,940.00) (185,363.17) (101,159.43) 1 2 ### ($535,940.00) ($185,363.17) 1 0.9132420091 0.834010967 ### ($489,442.92) ($154,594.92) 3 ($101,159.43) 0.7616538514 ($77,048.47) ### 53.97% 46.03% 1,161,274.36 10 Years 11,612,743.59 25% 2,903,185.90 2,106,500.00 ### 2,642,440.00 ### (309,401.83) (535,940.00) (185,363.17) After Tax Cash Flows ### ### ### FINANCING 28% LTV 20% Int Rate 25% Amortization Period 25 Years Call 10 Years Repayment TAX CALCULATION Using NPV and IRR Function NPV IRR $3,861,770 $11,071,315 ### 4,589,418.02 ### 2,642,440.00 352,581.68 420,355.19 ### 1,526,622.83 Total Value of the Property Value from Net revesion Value from Cash Flows ### ### ### 75.35% 24.65% ### ### 8 9 10 $1,837,906.14 ### ### 0.4838235978 0.441848034 0.4035141867 $889,222.36 $891,346.63 ### Calculation Of Natural BEP For Retail Joe Sales 20000000 21600000 Natural BEP 27000000 27000000 -7000000 -5400000 Pay@2.5% Calculation Of Natural BEP For HHG Sales 24000000 26400000 Natural BEP 26250000 26250000 -2250000 150000 Pay@4% 6000 23328000 25194240 27209779 29386562 27000000 27000000 27000000 27000000 -3672000 -1805760 209779.2 2386562 5244.48 59664.04 31737486 27000000 4737486 118437.2 34276485 27000000 7276485 181912.1 37018604 27000000 10018604 250465.1 39980093 43178499.945 27000000 27000000 12980093 16178499.945 324502.3 404462.49864 29040000 31944000 35138400 38652240 42517464 26250000 26250000 26250000 26250000 26250000 2790000 5694000 8888400 12402240 16267464 111600 227760 355536 496089.6 650698.6 46769210 26250000 20519210 820768.4 51446131 26250000 25196131 1007845 56590745 62249819.042 26250000 26250000 30340745 35999819.042 1213630 1439992.7617 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 31500000 30983810 30432777.175 29844549.634 29216616.735 28546298.364 27830733.504 27066868.015 26251441.606 25380973.915 24451749.654 23459802.756 22400899.442 21270520.154 20063840.264 18775709.482 17400629.872 15932732.389 14365751.825 12693000.073 10907337.578 9001142.8645 6966280.0079 4794063.9084 2475223.2222 2126250 2091407.175 2054212.4593 2014507.1003 1972121.6296 1926875.1396 1878574.5115 1827013.591 1771972.3084 1713215.7392 1650493.1016 1583536.686 1512060.7123 1435760.1104 1354309.2178 1267360.39 1174542.5164 1075459.4362 969688.24818 856777.50493 736245.28651 607577.14335 470223.90053 323599.31382 167077.5675 33626250 33075217.175 32486989.634 31859056.735 31188738.364 30473173.504 29709308.015 28893881.606 28023413.915 27094189.654 26102242.756 25043339.442 23912960.154 22706280.264 21418149.482 20043069.872 18575172.389 17008191.825 15335440.073 13549777.578 11643582.865 9608720.0079 7436503.9084 5117663.2222 2642300.7897 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 2642440 30983810 30432777.175 29844549.634 29216616.735 28546298.364 27830733.504 27066868.015 26251441.606 25380973.915 24451749.654 23459802.756 22400899.442 21270520.154 20063840.264 18775709.482 17400629.872 15932732.389 14365751.825 12693000.073 10907337.578 9001142.8645 6966280.0079 4794063.9084 2475223.2222 -139.21029197 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 60000000 59016785 57967202.988 56846774.189 55650716.447 54373924.807 53010949.732 51555973.838 50002787.073 48344760.2 46574816.513 44685401.628 42668451.238 40515356.697 38216928.274 35763355.932 33144167.457 30348183.761 27363471.165 24177290.468 20776042.575 17145210.449 13269297.154 9131759.7119 4714938.4925 4050000 3983632.9875 3912786.2017 3837157.2578 3756423.3602 3670239.9245 3578239.1069 3480028.2341 3375188.1274 3263271.3135 3143800.1147 3016264.6099 2880120.4586 2734786.577 2579642.6585 2414026.5254 2237231.3034 2048502.4039 1847034.3036 1631967.1066 1402382.8738 1157301.7053 895677.5579 616393.78055 318258.34824 64050000 63000417.988 61879989.189 60683931.447 59407139.807 58044164.732 56589188.838 55036002.073 53377975.2 51608031.513 49718616.628 47701666.238 45548571.697 43250143.274 40796570.932 38177382.457 35381398.761 32396686.165 29210505.468 25809257.575 22178425.449 18302512.154 14164974.712 9748153.4925 5033196.8407 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 5033215 59016785 57967202.988 56846774.189 55650716.447 54373924.807 53010949.732 51555973.838 50002787.073 48344760.2 46574816.513 44685401.628 42668451.238 40515356.697 38216928.274 35763355.932 33144167.457 30348183.761 27363471.165 24177290.468 20776042.575 17145210.449 13269297.154 9131759.7119 4714938.4925 -18.159296433 50332150