Why Your Numbers Are Lying to You (And How to Fix Them)
I spent three years running cash flow models that looked great on paper and still lost money every quarter. The problem wasn't the spreadsheet. It was the assumptions I never questioned. Most people skip the boring parts of Cash Flow Analysis Real Estate because they want to get to the "will this make me money?" answer faster. That rush is exactly how deals fall apart six months in. Start with the gross scheduled income, not the asking rent. I know that sounds obvious, but I've seen more investors build pro formas around market-rate comps for vacant units instead of what their actual tenants are paying. Here's the line-by-line breakdown most people botch: Gross Scheduled Income: Take every unit's actual lease rate. If a unit has been sitting vacant for two months and you fill it at market rate, that gap hits your Year 1 numbers. Document it. Don't smooth it over.
Vacancy and Credit Loss: The industry standard says 5%. Actual experience in most secondary markets runs closer to 7-8% when you factor in turnover costs, screening failures, and the months between tenants. I use a rolling 12-month actual vacancy rate from the property's prior operator if available. If the seller won't provide it, pull rent roll data from the county assessor and compare against asking rents on Zillow or Apartments.com for the same timeframe. The gap tells you something. Operating Expenses: This is where most models break. You need line items for property taxes, insurance, CAM, utilities, landscaping, payroll if there's on-site staff, management fees (typically 4-8% of effective gross income), repairs and maintenance, reserves for replacements, and administrative costs. I once had a $42,000 reserve request from a condo board that wasn't in any of the financials the seller provided. The reserve study was tucked in an appendix nobody read. Always read the reserve study. It tells you what capital expenditures are coming in the next 5-10 years and whether theHOA has been underfunding them. Noise Example: A duplex I analyzed showed $1,200/month in net cash flow. The seller's numbers looked clean. The catch was they had excluded property management at 8% because they managed it themselves. When I ran the model with professional management factored in, that $1,200 dropped to $230. The deal still worked at the purchase price, but barely. Without that adjustment I might have offered $20,000 too much.
The formula itself is straightforward: Net Operating Income equals Gross Income minus Operating Expenses. Cash Flow after Debt Service equals NOI minus monthly mortgage payments. But the work is in getting each line item right, not in the arithmetic. Debt Service deserves its own section because it's the single biggest variable. A 7% rate on a 30-year fixed wipes out cash flow that a 4.5% rate preserves. I always run at least three scenarios: current market rate, rate plus 100 basis points, and rate plus 200 basis points. If the deal only works at the base case, it's not a deal. It's a gamble. Here's something most tutorials won't tell you: cash-on-cash return is more useful than cap rate for evaluating investment properties. Cap rate ignores financing entirely. Two identical buildings in the same neighborhood can have the same cap rate but wildly different cash flows depending on how they're leveraged. If you're putting down 25% on one and 40% on the other, the cash-on-cash numbers will tell you which one actually puts money in your pocket after all expenses and debt service.
Get the Full Details
The tool I use is a basic Excel model with separate tabs for the rent roll, operating expenses, debt schedule, and summary metrics. I keep the rent roll tab raw and let the summary pull from it. That way if one tenant leaves, I'm not hunting through formulas to find where to adjust. I also flag every assumption in yellow cells so anyone looking at the model knows exactly what I estimated versus what I verified. It took me about 90 minutes to build my first proper model. Now I can set one up for a standard multifamily in about 15 minutes, assuming the seller's financials are complete and accurate, which they rarely are. The biggest limitation of any cash flow model is that it's a snapshot based on assumptions. Things change. Tenants leave. Roofs leak. Interest rates move. The model doesn't predict the future. It reveals whether the assumptions you're making are reasonable. If your vacancy number, expense growth rate, or rent escalation seems optimistic, the model will show you exactly how much damage that optimism causes. That's the point. Not to predict, but to stress-test. I also recommend running a sensitivity table on the three variables that matter most: vacancy rate, major repair timing, and interest rate. A 2% increase in vacancy or a $15,000 unplanned capital expenditure can flip a positive cash flow deal negative within a year. Knowing which variable kills your deal tells you where to focus your due diligence.
