Modelling Test - Please tear it apart :)
Hi all,
I currently have a modelling test lined up and I suspect it will be a development model. Most of my experience is investment/income producing assets so I haven't really modelled much development in my time although I think I understand most the concepts....
I found the following modelling test on WSO and the instructions were to complete it in 2 hours. I managed to do it in this time and I have also attached a copy of my completed model - any input would be much appreciated.
I had to convert the model to xls to be able to upload it to the forum so I hope everything works. Not sure if the macro will work but if all else fails, a manual goal seek will be just as effective.
Modeling Exercise
All inputs below should be flexible assumptions
Development Program
* 200,000 SF office building
* Land purchase price: $20M ($100 per FAR)
* Closing Costs: 1% of purchase price
* Hard Costs: $300 psf
* Soft Costs: (excluding TI's, LC's and Debt): 15% of hard costs
* TI's: $60 psf - paid at tenant occupancy
* LC's: $18 psf - paid six months before tenant occupancy
Construction & Lease-up
* 24 Month Construction Period, beginning at land close date
* Costs spent evenly over construction period
* 2 Tenant Lease-up of equal size (one tenant at construction completion; one 6 months after completion)
* Lease up to 95%
* Rent $4.25 NNN
* Free Rent: 3 months free
* Annual rental bumps: 3%
* Annual Operating Expenses during Lease-Up: $16 psf
Debt Assumptions
* 60% LTC
* Rate: 5% all-in interest rate
* All equity drawn first; then debt
* Use available cash flow to offset debt costs, as available
Hold Period:
* 5 years after stabilization
* Exit Cap Rate: 5.5%
* Transaction Fees: 1.5%
Joint Venture Structure
* LP invests 95% of required equity / GP invests 5%
* GP receives a 20% promoted interest over a 12% IRR to the LP
Required Output
* Required Project Equity, Net Profit, IRR and ROC (Return on Capital)
* Required LP (after promote) Equity, Net Profit, IRR and ROC (Return on Capital)
Thanks!
First of all, great attempt.
Second, you need tabs, since this is construction mutlitple. You need to get accustomed to input tabs and export tabs. This will come in handy to check your formula errors.
For this type of exercise you need 4-6 tabs.
1) Overview tab, has the inputs from debt, inflation, escallation, equity inputs i.e. lp splits underneath this you have year 1-5 sum if statements that extract themselves from the rest of the model. Make sure you know what a sources and uses chart is, incorporate it to get the total cost of uses so you can get LTC and your financing.
2) rental revenue and expense revenue in a PGI>Vacancy>EGI>Expenses>NOI>Debt Service>NCF. Calculate on a monthly basis using formulas ties to escallation in 1st tab
3) amortization chart, make the debt chart based of whichever financing you want or need
4) Construction chart, have the expense breakdown with all the sub categories of expenses.
5) Make a monthly waterfall distribtion chart, have this tie in to the 1st page on your returns space where you should have room for.
Think you did a good job on the first pass as well. Good point above about the IRR/XIRR. Only other thing I'd add, and it's not really about the model itself but just a 'heads up' going forward, is that at least in my experience it's really important to separate/breakout the operating expenses as best you can (CAM, MF, Insurance & RET should suffice). I don't believe this isn't the case in every market, but in some of the ones I cover the insurance and real estate taxes will increase as the build-out is completed (mainly taxes) and the occupancy rises (insurance). The taxes on the improvements catch up at a later date than the initial purchase of the land. It's also more expensive in some cases to insure an occupied building than a vacant/shell building. Finally, the management fee is usually quoted on a % of EGR (sometimes with a minimum), so this will obviously fluctuate with various occupancy. This is largely irrelevant if you are doing an industrial/retail deal where the leases are mostly NNN, but it can really f up your residual/terminal NOI if you don't account for this in an office or apartment deal. I realize for this exercise it's not as important, but keep it in mind in the real world.