This is Part 1 on the Excel.. Help is needed to see if it done correctly. If not, please fix.

An investor is interested in purchasing a multi-tenant office building in Miami, which has an asking price of $110 per SF. Assume that, apart from 2% purchasing cost, there are no additional acquisition-related costs. The building size is 60,390 SF. It is currently leased to three tenants:

• The first tenant is renting 24,156 SF for $25/SF/year. The lease will expire in 2 years.
• The second tenant is renting 10,266.3 SF for $23.5/SF/year. The lease will expire in 4 years and has an annual rent increase of 3%.
• The third tenant is occupying the remaining space for $22/SF/year and the lease will expire in 6 years. Rental increases of $2 per SF will occur at the beginning of the 2^nd and 4^th year.

After the first lease expires, assume a V&C of 5% of the PGI each year, which will increase to 10% once the second lease expires. The market rent is currently $19/SF/year and is expected to decrease by 4% each year for the next 3 years and then increase again at 2.25% each year for the next 4 years.

Operating expenses for all leases are $8.5/SF/year and will increase annually by 2% (i.e. with inflation). All three leases are leases with operating expense recovery, which amounts to a recovery of 75% of the operating expenses. The landlord covers the remaining 25% of operating expenses and an additional $2/SF/year in non-operating expenses. Non-operating expenses are expected to remain the same. You may assume different lease terms with regard to operating expenses for new leases. Please state your assumptions in the Excel spreadsheet if they differ from the old leases. A capital expenses reserve of $3/SF/year is furthermore required.

The building is depreciated over 39 years (mid-year convention for first and last year) and the value of improvements (building) is considered to be 80% of the purchasing price. The going out cap rate is 8.80% and the investor requires a return of 10%. Selling costs are 2% of the sales price. The investor expects to hold the building for 5 years. Assume an income tax of 35% and a capital gains tax of 15%.

Part 1 (Investment analysis without debt financing): Assuming that the investor wants to hold the property for 5 years, conduct a discounted cash flow analysis (DCF) to calculate the IRR and NPV (based on NOI and net selling price) for this investment.

• Based on these metrics, is the investment worth undertaking?

part 1 NPV Option 1 $110 per SF Initial outlay (110*60390) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 tenant 1 inflows -6642900 0 603900 603900 603900 603900 603900 outflows (operating (selling ex PGI 205326 209432.5 213621.2 217893.6 222251.5 12078 12078 12078 12078 12078 386496 382389.5 378200.8 373928.4 369570.5 Year 0 Year 1 Year 2 year 3 year 4 year 5 (v&c) tenant 2 net incomeinflows 19324.8 19119.47 37820.08 37392.84 36957.05 367171.2 363270 340380.7 336535.6 332613.5 Net flows -6642900 581744.3 613480.4 598138.1 630035.1 622089.1 NPV PV at 10% -6642900 528858.5 507008.6 449390 430322.4 386268.4 -4341052 0 241258.1 248495.8 255950.7 263629.2 271538.1 IRR Net flows PV at 1% Year 0 -6642900 -6642900 Year 1 507008.6 501988.7 Year 2 449390 440535.2 year 3 430322.4 417666.7 year 4 386268.4 371196.3 year 5 -4341052 -4130362 NPV -9041875 IRR does not exist in this case This is because NPV is less than zero Conclusion: The investment is not viable outflows (operating (Selling ex PGI 87263.55 89008.82 90789 92604.78 94456.87 4825.161 4969.916 5119.013 5272.584 5430.761 149169.3 154517.1 160042.7 165751.8 171650.4 (v&c) 7458.467 7725.853 8002.133 8287.591 17165.04 tenenat 3 net incomeinflows 0 141710.9 571289.4 146791.2 623224.8 152040.5 623224.8 157464.2 675160.2 154485.4 675160.2 outflows (operating (selling ex 0 220725.5 11425.79 225140 12464.5 229642.8 12464.5 234235.6 13503.2 238920.3 13503.2 PGI 339138.2 385620.3 381117.5 427421.4 422736.7 (v&c) 16956.91 19281.02 19055.88 21371.07 21136.83 Net income 322181.3 366339.3 362061.7 406050.3 401599.8 Part 2 period 30 years rate 7% compounded monthly loan amoun70% of (60390*110) annual interest expenses A(1+r/compounding periods)^n*compounding periods Year 1 year2 year 3 year 4 year 5 principal 4650030 4986181 5346632 5733140 6147589 loan total formula 4650030(1+0.07/12)^12 4986181(1+0.07/12)^13 5346632(1+0.07/12)^14 5733140(1+0.07/12)^15 4650030(1+0.07/12)^16 New NPV Year 0 Year 1 Year 2 year 3 year 4 year 5 Net flows Interest expense -6642900 0 581744.3 336150.7 612828.7 360451 594653.7 386508 625734.8 414448.7 617498.6 444409.2 NPV IRR Year 0 Year 1 Year 2 year 3 year 4 year 5 Net flows Interest expense -6642900 0 581744.3 336150.7 612828.7 360451 594653.7 386508 625734.8 414448.7 617498.6 444409.2 NPV IRR does not exist since NPV is negative = 4650030 ounding periods amount 4986181 5346632 5733140 6147589 6591998 net flows -6642900 245593.6 252377.7 208145.7 211286.1 173089.4 -5552408 net flows -6642900 245593.6 252377.7 208145.7 211286.1 173089.4 -5552408 interest for the year 336150.7 360451 386508 414448.7 444409.2 A. Interest rates ARM 1 Initial inte margin profit 4% 2.50% 1.500% ARM II ARM III 6.50% 5.50% 2.50% 2% 4.00% 3.50% ARM 1 Year 1 year2 year 3 year 4 year 5 principal 603900 1028498 2055009 3675583 6728826 amount 1028498 1789335 3675583 6728826 12619929 Inflows (marinterest fo 842343.36 424598.4 2564536.5 760836.7 5363637.7 1620574 9968683.6 3053243 18992552 5891102 principal 603900 1434590 3605081 9618659 27353757 amount 1434590 3605081 9618659 27353757 83257447 inflows 842343.36 5258210.1 14377799 41970332 131351085 interest for the year 830689.9 2170491 6013578 17735098 55903690 principal principal 603900 1255793 2718683 6141130 14507814 amount amount 1255793 2718683 6141130 14507814 35932720 Inflows interest for the year interest for the year 651893.1 1462890 3422447 8366684 21424906 ARM II Year 1 year2 year 3 year 4 year 5 ARM III Year 1 year2 year 3 year 4 year 5 788105.45 3656333.3 8394856.8 20175951 50886185 B. Effective interest ra Nominal rate ARMI 4% ARMII 6.50% ARMIII 5.50% Year 1 C. NPV ARM 1 Balance PV at 10% 417744.9 379768.1 n=1 Year 2 Year 3 Year 4 Year 5 1803700 3743063 6915440 13101450 NPV ARM II year 1 year 2 year 3 year 4 year 5 11653.5 3087719 8364221 24235235 75447396 NPV 1490661 2812219 4723339 8134970 17540956 PV at 10% 10594.09 2551834 6284163 16552992 46846897 72246480 ARM 111 PV at 10% year 1 year 2 year 3 year 4 year 5 136212.3 2193443 4972409 11809268 29461279 NPV 123829.4 1812763 3735844 8065889 18293136 32031462 IRR Year 1 Year 2 Year 3 Year 4 Year 5 ARM 1 Balance 417744.9 1803700 3743063 6915440 13101450 NPV ARM II year 1 year 2 year 3 year 4 year 5 11653.5 3087719 8364221 24235235 75447396 NPV PV at 99% 379768.1 1252569 474971.9 440968.4 419811.2 2968089 PV at 10% 5856.03 779707.3 1061369 1545379 2417569 5809880 ARM 111 PV at 10% year 1 year 2 year 3 year 4 year 5 136212.3 2193443 4972409 11809268 29461279 NPV No IRR 68448.39 553885.8 630968.4 753027.2 944030.9 2950361 Balance 417744.9 1803700 3743063 6915440 13101450 interest for the year 11653.5 3087719 8364221 24235235 75447396 interest for the year interest for the year 136212.3 2193443 4972409 11809268 29461279