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.
Thanks for the feedback Shervin - aside from the presentation of my data which I didn't build in as I was trying to replicate an exam-scenario, would you say that the thought process and major calculations for the model are correct?
I'll definitely take on board your comments though and work on presentation for such models in case I do land a job!
everything looked correct.
Only 1 thing, be careful with irr.xirr for construction and for low return deals. Anything with multiple negative cash flows and not enough return could give you potentially MANY different IRRs.
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.
Thanks MonkeyWrench, good to have in mind going forwards, especially if I land the job!
Thanks again, I've never come across this/wasn't aware of this so good to know. If this happens, are there any warning signs that you have this problem? Would the irr function break or is it more subtle than that?
I got very similar answers. Good job. One of the things I wasn’t sure about was the 4.25 rent. Wasn’t sure if it was 4.25 / SF / month or 51 / SF / year or it was a typo and the rent was supposed to be 42.5 / SF / year.
Assuming the rent is 4.25 / SF / month my answers are very close.
That's good news and encouraging too! I also assumed the rent was 4.25/sf/month because when treating it annually, the IRR is pretty much 0%
Et quibusdam inventore sed fuga sit est eum. Fuga tempora voluptas eum dolore. Voluptatem distinctio quasi possimus dolorem qui eligendi possimus.
Sit aut rerum ab ipsam esse voluptatem. Incidunt a maiores nam maxime aut quas autem dolore.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...