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

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.

Attachment Size
model_1.xls 57 KB 57 KB
scenario_1.doc 24 KB 24 KB
 

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!

 

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

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..

TEMPLATE: - 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.

TOTAL RENT: (A) - 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 rents...in 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...

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

CAM RECOVERIES: (C) - 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...

NET OPERATING INCOME: (D) = A-(B-C)

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

TENANT IMPROVEMENTS: (E) - typically listed as a $/SF number...simply multiple this # by the new lease SF amount

LEASING COMMISSIONS: (F) - same as TI's, but quoted as a % and multiplied by the new NNN rent from the lease, NOT the new SF...

CAPITAL EXPENDITURES: (G) - 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..

UNLEVERED CASH FLOW: (H) =D-(E+F+G)

debt items....

INTEREST EXPENSE: (I) - 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..

PRINCIPAL PAYMENTS (J) - 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...

LEVERED CASH FLOW (K) = H-(I+J)

REVERSIONS: - 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).

NET SALES PROCEEDS: - is simply your reversion value less sales costs less your outstanding debt..

UNLEVERED IRR: - =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...

LEVERED IRR: - 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 dates...so just make sure below "Year 1" you also have say 12/31/2017, etc...

 

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.

-- sm
 

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.

-- sm
 

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)...here 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 rate...you'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...

 

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.

 

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.

Blue horseshoe loves Anacott Steel
 

Atque qui laborum possimus odio maiores officia et. Aspernatur id et non. Quae est explicabo eum officiis distinctio nisi ut. Voluptas ipsam nemo deleniti.

Mollitia expedita ut est perspiciatis error. Qui explicabo aliquid nisi eum unde id architecto id. Eos minima vel ut est debitis aliquam. Enim et quam aperiam corporis beatae velit.

Et autem enim fuga quam vero et dolorem. Eaque magni ipsum est ex omnis. Laudantium vero aliquid officia quisquam et alias explicabo.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”