Why Your Cre Analysis Keeps Producing Weird Numbers
I built my first commercial real estate cash flow model back when I was twenty-three and thought "sensitivity analysis" was something you did after a blood test. Four years and three failed office deals later, I finally understood that the spreadsheet itself isn't the problem. The problem is knowing which cell actually moves the needle and which cell is just decoration. A proper Commercial Real Estate Cash Flow Analysis Worksheet isn't a fancy template with conditional formatting and dropdown menus. It's a skeleton of hard-coded inputs on the left, a timeline of yearly cash flows in the middle, and a handful of output metrics on the right. That's it. Everything else is noise.
How to Build a Commercial Real Estate Cash Flow Analysis Worksheet That Doesn't Lie to You
Start with a blank sheet. Set up five tabs: Inputs, Pro Forma, CapEx, Financing, and Summary. Don't combine them. When I first tried stacking everything into one tab, I lost track of which rent roll assumption was feeding which revenue line. It took me three weeks to untangle it. On the Inputs tab, list these in column A with their values in column B: Purchase Price Loan Amount and LTV Interest Rate Amortization Period Projected Vacancy Rate Starting Monthly Rent per Unit or per SF Annual Rent Growth Operating Expenses (tied to NOI) Property Tax Rate Insurance Cost Management Fee Percentage Reserved for CapEx as a Percentage of Gross Income
Everything else flows from those. Put a thin yellow box around every input cell. If it's yellow, someone can change it. If it's not yellow, it should be a formula referencing a yellow cell. If you find a white cell that somehow contains a magic number like 0.042, hunt down where that came from and either hard-code a comment explaining it or replace it with a reference to an input. Move to the Pro Forma tab. Create a column for Year 0 through Year 10. Year 0 is the acquisition year with purchase costs. Years 1 through 10 are operating years. Row by row, pull from the Inputs tab using = references. Never type a number into a formula cell. I've seen this mistake more times than I can count, usually at 11 PM before a closing. The top section calculates Gross Scheduled Income, subtracts Vacancy Credit to get Effective Gross Income, then subtracts Operating Expenses for Net Operating Income. Keep this part simple. Complicating NOI with debt service at this stage is the most common error I see in junior analyst models.
Get the Full Details
Below NOI, add a financing section. Calculate annual debt service using the PMT function against the loan amount, rate, and amortization period. Subtract debt service from NOI to get before-tax cash flow. This is your key number. Now the part everyone skips. CapEx. Real properties age. Roof replacements happen around year seven in Class B multifamily. HVAC systems die around year ten. Parking lots crack by year eight. If you're running a 10-year hold and assuming zero capital expenditures beyond routine maintenance, your model is fiction. Set aside 3 to 5 percent of Effective Gross Income annually for reserves, and create a separate CapEx schedule that spikes in specific years for known replacement cycles. In the Summary tab, calculate Equity Multiple, IRR, Cash-on-Cash Return, and the Debt Service Coverage Ratio. DSCR is the metric lenders actually care about, not IRR. If your DSCR falls below 1.20 in any year, the deal probably won't qualify for conventional financing, no matter how pretty the IRR looks on paper.
Here's a practical detail most guides won't tell you: build in a leakage assumption. Every commercial property loses income to tenant improvements, leasing commissions, and renewal concessions. I used to lump this into vacancy, which made my numbers look artificially clean. Once I started modeling TI and leasing costs separately as a percentage of expiring rent each year, my actual cash flow typically dropped by 8 to 12 percent compared to what the simplified version showed. That difference is the difference between a deal that closes and one that kills you in year four. One specific edge case I ran into involved a small apartment complex where the property tax reassessment hit in year three. The assessor valued the property at nearly double the purchase price because of nearby development. My original model had property taxes locked to the purchase-year assessment with a 2 percent annual increase. I needed to adjust the input cell for Year 3 onward to reflect the new assessed value multiplied by the millage rate. Without that adjustment, the DSCR in Year 3 looked fine and in Year 4 it dropped to 1.08, which would have breached the loan covenants. I added a note in the Inputs tab flagging the reassessment year and built a scenario toggle so the underwriting team could see both the base case and the reassessment impact side by side. Saved us from walking into a refinancing trap. The hardest part about this workflow isn't the math. It's discipline. Most people build models that look impressive and perform poorly because they optimize for appearance instead of accuracy. A clean model with ugly yellow cells beats a beautifully formatted one every time.
If you want a starting point, I've put together a basic version of this framework. Download the Commercial Real Estate Cash Flow Analysis Worksheet here: [insert link]. It includes the five-tab structure, the leakage assumptions I mentioned, and a note in every cell explaining what feeds what. You'll need to adjust the rent growth and expense ratios for your market. A national average won't work if you're underwriting a property in Tulsa versus Austin. The percentages in the template are placeholders. Treat them like that.
Common Pitfalls That Break These Models
The first mistake is over-projecting rent growth. Ten years out, nobody knows what the market will look like. I usually run rent growth at 2 to 3 percent annually for stable markets and let the sensitivity table do the heavy lifting for optimism. The second mistake is ignoring operating expense growth. Property taxes and insurance don't stay flat. They climb. Build in a 3 to 4 percent annual increase for OPEX unless your market has a known cap on assessments. The third mistake is treating the worksheet as a one-size-fits-all tool. A retail strip center has a completely different cash flow profile than a Class A office building. Office leases are long but vacancies kill you. Retail has CAM charges that reduce your expense burden but also create collection risk. Industrial properties are simpler but have shorter lease terms now. Adapt the model to the asset class. Don't force a multifamily template onto a warehouse deal and call it done. Here's the uncomfortable truth: a Commercial Real Estate Cash Flow Analysis Worksheet will never fully capture risk. It gives you a number. That number is only as good as your inputs. If you're uncertain about the market rent, run three scenarios. If you're uncertain about vacancy, build in a stress case. If you're uncertain about CapEx timing, model the worst-case repair schedule. The worksheet is a tool, not an oracle. Using it like one is how people lose money on deals that looked fine on paper.
I've also learned to stop including exit cap rates in the base case of most worksheets. Exit valuation is where models go to die because everyone has a different opinion on what the market will cap out at. Instead, I keep the exit cap as a separate sensitivity variable and report a range of possible equity multiples rather than a single point estimate. It's less visually appealing but more honest. The best version of this worksheet is the one you've been living with for six months and constantly updating. The first one you build will be wrong. The second one will be less wrong. By the fifth deal, you'll have enough institutional knowledge to know which assumptions matter and which ones are just there to fill space. That's the whole point.
What the Commercial Real Estate Cash Flow Analysis Worksheet Won't Tell You
It won't warn you about a tenant about to file bankruptcy. It won't factor in a upcoming zoning change. It won't account for the new apartment complex being built three blocks away that will eat your occupancy. It won't tell you that the roof inspector found active leaks during the due diligence period. All of that requires human judgment. The worksheet is a snapshot based on the data you feed it. Garbage in, garbage out is not a criticism. It's a description of how the tool works. Use it as a starting point for discussion, not as a reason to stop thinking. That's the only advice worth anything at the end of this.
