Excel model is all over the place

I'm trying to calculate cash flows at different occupancy rates, but when I try to find the NPV of the property at those assumed occupancy rates, the whole model goes awry. I don't know what's happening.

Any pointers on how to run these analyses more efficiently?

Real Estate Modeling Course

  • Real-life RE Modeling Tests from actual Interviews
  • Various asset classes including multi-family, commercial and more
  • Huge discount - until more tests and cases added

Comments (11)

Jan 3, 2021 - 11:50am

So when I ran the basic analysis - at an assumed 100% occupancy rate - the NPV and cashflows were at a certain number. But when I started running sensitivities at different occupancy rates, starting with 10% and gradually moving up, and auto filled the next ten cells, the NPV and cash flows at 100% occupancy were way different than the original analysis at 100% occupancy.

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
Jan 3, 2021 - 6:51pm

I'm out of my "excel guy" days so not best person to ask for shortcuts. My only advise with this type of situation is to just not be afraid to grunt it out and rebuild if you need to. Being fast is only a "skill" in fast paced world of transactions (like IB or IS/DE), being methodical and accurate (which takes time) is more apt for your type of role.  

Jan 4, 2021 - 12:40pm

Sounds like you have a fucked up circular reference somewhere. How big is the model? Like redever said you'll have to audit the formulas. It probably went wrong with the interest reserve or capital sizing somewhere. 

can you look at a previously saved version of the model and try and back into where things got messed up? 

Start Discussion

Total Avg Compensation

May 2021 Investment Banking

  • Director/MD (9) $911
  • Vice President (35) $364
  • Associates (194) $234
  • 2nd Year Analyst (110) $151
  • Intern/Summer Associate (96) $145
  • 3rd+ Year Analyst (26) $145
  • 1st Year Analyst (402) $131
  • Intern/Summer Analyst (327) $82