Newbie tried modeling... looking for guidance, not the answers.

hazardpolicy's picture
Rank: Monkey | 52

I've been struggling for a few months now to break into the RE world while trying to coach myself on modeling. I found a scenario from online and I am beyond stuck. I'm pretty confident I haven't done this correctly. Would anyone be able to take a look and guide me to resources so I could complete? This has been a work in progress! (Am I right to assume this would be easier to model with ARGUS? Have never used it).
I do understand the terminology, but may not realize all the implications.

-- please disregard the appearance. I was going to format it once I pushed through.

If anyone is willing to walk me through the whole thing - and give some coaching or pointers - I would be so grateful.

Office spreadsheet icon model_1.xls57 KB
Microsoft Office document icon scenario_1.doc24 KB

Comments (26)

May 17, 2017

Not opening the files yet.

Is this an office building? If so yes it would be modeled in Argus but the 10-year cash flow would be exported into excel and the returns modeled there. To understand the process better start by doing a SF then MF, etc. Buy staiger's book on Amazon. It will teach you "everything" you need to know.

May 18, 2017

Thank you so much! A few questions for clarification..
- ARGUS would take the assumptions and create the 10 year cash flow? I know the DCF can be calculated in Excel but I didn't know when ARGUS came into the valuation process.
- when you say start with SF, MF.. I'm guessing you mean single family, multi-family?
And as soon as I saw your recommendation for Staiger's book last night, I purchased it. Thank you so much for the leads!

May 18, 2017

Argus is best for Office and Retail. It models step ups in rent, time it would take to lease vacant space etc. You have to enter all the inputs. Set the "run" for whatever time frame you like, 10-years is typical. Once exported into excel you model around the DCF. However modeling MF(multi-family) and learning how to "write" rent escalations in formulas is a good base.

-Caveat about Staiger' book. There a few things wrong with the book in the later chapters, the benefit far outweighs these errors. It will help you learn to edit your model and fix it.

Best Response
May 18, 2017

This is actually a pretty tricky scenario as far as entry level modeling goes. Above posts are correct, almost all RE firms will use Argus for office/retail/industrial. however, many firms will then export the CFs into excel in order to perform a DCF, or manipulate it to fit their company's platform. To me, the biggest reason to use Argus is to handle leases with base year stops, etc - basically any terms that are not NNN. If you don't know the difference between these two I'd recommend brushing up on that first. If you have any detailed questions feel free to post below or just PM me.

Below is a step-by-step approach I would use if I wanted to build out a BASIC office/retail CF model, and determine the IRR, EMx, Profit, etc...

HOPE THIS HELPS! PM me if you have any questions/issues. Sure there are some mistakes in and certainly a 1000 ways to approach this...this is just my way. Others please feel free to chime in and add your 2cents..

- make your assumptions box on the left hand side. with the returns box just above it on the left hand side. Placement doesn't matter - I just like to have the output metrics next to the input ones, so you can easily see the effect the changes have on the returns.

- Closing/Y1 this would be the current in-place NNN rate * occupied SF
- Y2 and beyond would be: Prior year rent grown at your growth rate (say 3%) PLUS the NEW RENT from a new lease which would be the MLA rent at that year multiplied by whatever the new lease SF is...
- ***Remember to grow your MLA you're inputs box you have your in-place current NNN rate for tenants that are current in the building. Below that you should have the MLA NNN rate, which is what you would theoretically charge NEW tenants to occupy the building IN YEAR 1. Say the tenant takes space in year 5....remember to grow that MLA to account for inflation, etc,.. Yes they'res a ton of assumptions to this, but Argus does it this way so I think it's best to model it how the market/competitors would...

- Closing/Y1 would be the underwritten CAM expense (say $/SF) multiplied by the bldg's GLA

- would be the CAM expense calculated above * the bldg's occupancy at that year.
...The difference between the two is the "leakage" that flows down to your NOI. Again this assumed the property is NNN. Even if it's not...most brokers/leasing agents will quote your term sheets in NNN as well regardless of the structure for simplicity sake and also so one can compare easier...

= A-(B-C)

below the line items...I show these as positive numbers even though they're expenses (just for presentation sake..)

- typically listed as a $/SF number...simply multiple this # by the new lease SF amount

- same as TI's, but quoted as a % and multiplied by the new NNN rent from the lease, NOT the new SF...

- Closing/Y1 number is typically quoted as a $/SF amount. Take this # and * by the bldg's GLA. then for Y2 and beyond just grow it at a certain growth rate... Arguably, after year 5 or so, depending on the age of the bldg,..your Capex reserve should increase due to larger wear and tear. But this is purely subjective - I've seen shops do it both ways..


debt items....

- google the IPMT calc - PM me if you have questions... I like to split the interest payments in a separate amortization/debt table...and do a sum of monthly amounts to just be that much more precise. Especially given you're model looks like it may need to be built out monthly...with a separate CF table that shows annual CF's summed from your monthly formulas..

- same as above...but just use the PPMT formula in excel...

Another thing work noting, is depending on the firm and type of debt you use, you may want to make this section as dynamic as possible. Unlevered CF is generic...but debt terms is the part that makes the deal truly unique to your firm and the type of debt you're using. If its simple secured mortgage debt...maybe it has interest only provisions, prepayment penalties, etc....these are all things you would want to build into it once you have the basics down...

= H-(I+J)

- common is to take the Forwrad 12 NOI and divide it by a cap rate in your input assumptions box. Don't forget to deduct the sales cost for the deal (say 1.5% of the sale price).

- is simply your reversion value less sales costs less your outstanding debt..

- =XIRR formula...but make sure you use the property's total net pruchase price as the negative outflow number....and the net sales proceeds BEFORE YOU TAKE OUT DEBT as the final cash inflow number. The #'s in between will be your Unlevered CFs...

- same cocnept as above. Except the intial negative outflow will be the equity portion of the deal (purchase price * (1-leverage %)). #'s in the middle will be levered CFs....and final inflow will be the net sales proceeds (remember to back out outstanding debt here)...

to use the XIRR, you need to have your just make sure below "Year 1" you also have say 12/31/2017, etc...

    • 5
May 19, 2017

to value a company, you use both. unlevered free cash flow is calculated as the cash generated by the business. because this is unlevered, you can think of it as free cash to retire debt (cash flow from ops, investing, non-debt-related financing). once you have a stream of unlevered free cash flows, you can then apply a discounted cash flow to arrive at a value. various ways to value the tail years, but usually either an ebitda multiple or perpetual growth of 1-3% (ish). you'll usually see this sensitized by discount rate (a range around wacc).

irr would be used to analyze an investment, calculating your "break-even" discount rate.

May 19, 2017

you can get this stuff in any vault guide / finance book / investopedia. start doing some research. might also be worth looking into a wall street prep course or something if you're thinking of learning modeling. interviewers will definitely expect you to have your financial statements and modeling down cold coming in.

May 19, 2017

when you say the "tail years" what do you mean? When we calculate FCF's we're basically projecting what the CF will be then we use the cost of capital to discount to a NPV. Are the tail years outside the years we have projected?

Thanks for your help. I have been researching....still trying to make sense of it getting there though.

May 19, 2017

yeah, basically. say you project out seven years. you can discount these cash flows back, but realistically, the company won't stop producing cash after that. it will continue to exist (you presume) and conduct business and generate cash and value. so what you'll usually see is either an ebitda multiple of the last projected year or a gordon growth calculation off the last cash flow assuming a perpetual growth rate. this valuation of the "terminal value" will also be discounted back to today along with your projected cash flows to arrive at a business valuation as of today.

May 19, 2017

That makes a lot of sense now.

May 19, 2017


May 18, 2017

Also - just taking an extremely quick glance at your model...some thoughts:
- TI and Capex goes below the line not inclusive of NOI
- Capex gets applied every year/month
- I would use some growth rates to your metrics...
- assuming you still need to build out the debt levered CFs..
- Missing a reversion amount in your closing year. If you want to look at IRR, NPV you will need this..
- Didn't review the term sheet...but your inputs can't be right. Bldg cost is 32M and you're making 8M PER MONTH in NOI? just isnt realistic. Your Net profit WITHOUT a reversion is over 500M
- LEasing commissions are a one time charge in the month/year of the lease start date (same approach as TI's) you're charging it monthly
- Discount rate needs to be in a percentage format...right now you're applying a 350% discount rate...which is why your NPV is so low give the returns you're showing....Something more realistic for an asset like this would be 7-8%...maybe a little higher given the 10% cap rate..

I guess above all else...when you're done building a model..ALWAYS step back and look at it logically, right? I mean right off the bat, I'd say something is wrong given the extremely high monthly rent given the property's purchase price. Typically you see a purchase price going in cap rate (Closing NOI / Purchase Price) maybe 1.5-2% lower than the underwritten exit cap (rarely will you ever underwrite a deal that shows a lower terminal cap than the going in cap rate...). If we take your Month1 NOI and annualize it, then divide it by the purchase price to get a Y1 cap're saying your Y1 cap rate is ~321%.... That's simply just not right...

Hope this all helps. Keep up the approach and the diligence.. Believe me RE is a simple business when it comes to the numbers and modeling, that's why im in it...

May 18, 2017

I cant tell you how much I appreciate the thorough explanation and walk through. First thing I did was write down everything here. I cannot wait to tie these notes in with the modeling text I just purchased and start cleaning this model up. Some of the items are definitely fuzzy to me, but I'm so excited to get back to it. The term sheet is tricky with all the different curve balls..I think it helped introduce a lot of the concepts I already messed up.

I don't have any specific questions at the moment.. thank you so much for offering your input. I will be following up!

+1 to everybody here. I can't get over how nice everyone is. I owe you all a round.

May 19, 2017

WSO is offering a course for free right now. Signup is probably still listed on the homepage. If you really want an intense deep dive then I'm sure the Wall Street Prep or Breaking Into Wall Street will be suggested.

I'd suggest doing the free course here on WSO as it will help give you the basics needed and then if you feel you want a real intense course hop into Wall Street Prep. I've done WSP and it was very helpful and I think it's gaining more and more credibility as my local CFA chapter was partnering with them for a modeling course.

    • 1
May 19, 2017

You can pick up a copy of Investment Banking: Valuations, Leveraged Buyouts and Mergers & Acquistitions by Pearl and Rosenbaum

May 19, 2017

Try macabacus, it has a lot of modelling material on it, but seriously I'd simply pony up the $200 for a decent online course, it is worth it.

May 19, 2017

I have drafted a model strictly following the terms stated in the term sheet you uploaded.
I am not sure where you found this term sheet but it is very vague and missing some key information (at least I think so..)

Shoot me a PM with your e-mail, if you want I will send you my copy of the model.

    • 1
May 19, 2017

will do! I'm still in my newbie member stage, so once I have permissions I'll shoot you a PM! The feedback would be excellent, and I'd love to see someone's interpretation. Thanks for taking a look!!

May 19, 2017