Rental Property Cash Flow Spreadsheet: Tie the Pro Forma, the Bank, and Schedule E

A landlord owns a duplex. The spreadsheet they used to buy it said it would clear $366 a month. At the end of the first full year the bank account is down $10,235.74, and the tax preparer's Schedule E shows a loss of $5,217.69. The owner opens their rental property cash flow spreadsheet to find out which number is true. A spreadsheet built to produce one number can't answer that, because a rental property produces at least three.

All three numbers are correct. The pro forma is a forecast. The bank balance counts every dollar that moved, including principal and a new roof. Schedule E follows tax rules that skip principal, spread the roof over 27.5 years, and deduct depreciation that never leaves your account. A spreadsheet that shows only one of them leaves you guessing about the other two. Worse, it can't tell you whether the property is actually underperforming or just paid for a roof this year.

This article builds the version that ties all three together. One ledger, two tags per transaction, and a bridge that has to equal zero. Every figure below comes from one worked example, and every table ties to it.

One Duplex, Three Profits, None of Them Wrong

The property: a duplex bought in July 2024 for $340,000, with a $255,000 loan at 7.0 percent over 30 years. The monthly payment is $1,696.52 and the first payment was due in September 2024. Each unit rented for $1,575. The building's depreciable basis, after allocating 20 percent to land and adding capitalized closing costs, is $277,440. The year under review is calendar 2025, the first full year.

Four things happened that the pro forma did not see coming. The tenant in unit B moved out on July 31, and the new tenant moved in on September 16 at $1,625. The turnover cost $1,350 in paint and $500 in cleaning. The county tax bill came in at $4,610 instead of the seller's $4,080, and the insurance renewal went from $1,860 to $2,240. And in October the roof was replaced for $12,600.

Each row answers a different question. The pro forma asks whether the deal was priced right. Operating cash flow asks whether the property pays for itself in a normal month. The bank balance asks whether you need to move money in. Schedule E asks what goes on the tax return. A spreadsheet that shows only the bottom of one row leaves you unable to act on the other three.

Build the Ledger, Not the Summary

The tempting way to start a rental cash flow spreadsheet is a summary grid: months across the top, categories down the side, numbers typed into cells. That grid can't be reconciled to anything, because the typed numbers don't point back to transactions. Build a ledger first and let every summary come from it with SUMIFS.

The ledger tab has one row per bank transaction and six columns:

Column E uses the line names from the 2025 Schedule E: 3 Rents received, 5 Advertising, 7 Cleaning and maintenance, 9 Insurance, 10 Legal and other professional fees, 11 Management fees, 12 Mortgage interest, 14 Repairs, 15 Supplies, 16 Taxes, 17 Utilities, 19 Other. Lock both E and F with Data Validation lists so nobody types "repair" in one row and "Repairs" in the next. SUMIFS treats those as two different categories and says nothing about it.

Three tagging rules do most of the work:

• Split the mortgage payment into two rows. The bank shows one $1,696.52 debit. The ledger needs an Interest row tagged line 12 and a Principal row tagged n/a, using the split from the servicer's statement. In 2025 that totals $17,706.96 of interest and $2,651.28 of principal. IRS Publication 527 is explicit that you can't deduct principal payments.
• Capital items get class Capital and line n/a. Publication 527 lists a new roof as an improvement recovered through depreciation, over the same 27.5-year period as the building. If the roof also carries line 14, Schedule E overstates your deductions by $12,600. If you're not sure whether a job is a repair or an improvement, work through the repair versus improvement classification before you tag it.
• Security deposits are not rent. Publication 527 says not to include a deposit in income if you plan to return it. If you keep part of it because the tenant broke the lease terms, you include the part you keep in income that year. Unit B's old tenant paid a $1,575 deposit in 2024. On move-out the owner kept $620 for damage and refunded $955. That creates three rows: the $955 refund (Deposit, n/a), a $620 journal row moving the kept amount out of the deposit (Deposit, n/a, negative), and a matching positive $620 row tagged line 3 with class Deposit. The two journal rows net to zero cash but put $620 on Schedule E. The security deposit tracker handles the state-law side of the same money.

One check belongs on the ledger tab itself. Bank CSV exports sometimes open with the dates stored as text, and SUMIFS with a date range skips text dates without any error. Put =SUMPRODUCT(--ISTEXT(Ledger!A2:A2000)) in a visible cell. Anything above zero means some rent is missing from your monthly totals.

The Monthly View Comes From the Ledger

On a Monthly tab, put the first day of each month in B3:M3. Rent collected for January is:

=SUMIFS(Ledger!$D:$D,Ledger!$F:$F,"Rent",Ledger!$A:$A,">="&B$3,Ledger!$A:$A,"<="&EOMONTH(B$3,0))

Copy it down with "Operating", "Interest", and "Principal" in place of "Rent". Operating cash flow in row 8 is the sum of those four rows. Put capital and deposit rows below that line, in rows 9 and 10, never above it, so a roof never looks like an operating problem. Net cash in row 11 is =B8+B9+B10, and a running balance in row 12, =SUM($B$11:B11), shows how far the account drifted from where it started.

Two things are visible here that a single "monthly cash flow" cell hides. Three of twelve months had negative operating cash flow even before the roof: February's tax installment, March's insurance renewal, and August, when the second tax installment landed on top of the turnover and a month with no rent from unit B. And September's $1,625 deposit made the month look strong when it's money you owe back. That's why deposits sit in their own column. If your taxes and insurance run through the lender instead, the pattern shifts into the payment itself; the escrow analysis tracker covers that version.

The Bridge That Has to Equal Zero

This is the tab that makes every other tab trustworthy. Start from Schedule E line 21 and walk to the change in the bank balance. Every step is a rule you already tagged in the ledger.

Depreciation on line 18 is the building at the 3.636 percent year-two rate from the Publication 527 table ($10,087.72), plus the roof at 0.758 percent for an October placed-in-service month ($95.51). The deposit line is the net of all untagged deposit rows: $1,625 in, $955 out, and the $620 that moved to rent income. It equals the change in deposits you are holding.

Now the check cell: =ROUND(C7-SUM(Ledger!$D:$D),2). It must read 0.00. A second check compares =SUM(Ledger!$D:$D) to the December 31 balance minus the January 1 balance from the actual statements. The first check catches tagging errors. The second catches missing transactions.

The bridge only ties if every row carries exactly one meaning. An operating expense with no Schedule E line leaves the bridge off by that amount. A roof tagged both line 14 and Capital puts it off by $12,600. A deposit tagged as rent throws it off by the deposit. That's the value of the bridge: it's the only place in the workbook where a mistake has nowhere to hide. Add =COUNTIFS(Ledger!$F:$F,"Capital",Ledger!$E:$E,"<>n/a") next to it. That count must be zero, and when it isn't, it points you at the row.

Pro Forma Versus Actual: Where $4,587.50 Went

The bank figure is dominated by the roof, and the roof was always coming. The number that tells you whether you misjudged the property is operating cash flow against the pro forma, both before capex. The pro forma expected $6,281.76 before its reserve line. The ledger delivered $1,694.26. The gap is $4,587.50, and it breaks down line by line.

The flag formula is =IF(ABS(D5)>MAX(250,0.1*ABS(B5)),"INVESTIGATE",""). It fires when a line misses by more than $250 or 10 percent of its budget, whichever is larger. That keeps a $30 water bill from generating noise while still catching a small line that doubled.

Read the table the way a buyer would. Vacancy, the line every investor worries about, came in $297.50 short, close to budget. Unit B sat empty for 46 days, more than the 36.5 unit-days a 5 percent allowance on two units covers, and the $50 increase on the new lease recovered $175 of the difference. The damage was elsewhere. The $1,850 turnover sat inside the repairs line, which was budgeted as 5 percent of rent as if repairs were spread evenly over the year. The turnover cost calculator is built for exactly that gap. Taxes and insurance, the two lines the owner doesn't control, account for $910 together. The tax reset after purchase is covered in the reassessment calculator. The $760 of supplies and tax prep simply had no row in the pro forma.

The decision this informs: $1,920 of the gap is permanent (taxes, insurance, utilities, and the missing admin line), because none of those go back down next year. At an unchanged rent, next year's operating cash flow starts $1,920 below the pro forma even with no turnover. Put the permanent variances back into the underwriting model and re-run it before you use this property's numbers to size your next purchase.

The capex reserve deserves its own line in the review. At 5 percent of rent the pro forma set aside $1,890 a year. The roof cost $12,600, leaving $10,710 uncovered even if the reserve had actually been funded from day one. Percentage reserves assume the property's components age evenly. A component schedule, like the one in the capital reserve calculator, would have flagged the roof at purchase.

The Schedule E Loss You Might Not Get to Use

It's tempting to count the tax loss as part of the return: depreciation shelters the cash flow and the leftover loss reduces tax on their salary. On this duplex, Schedule E reports a loss of $5,217.69 while the property produced $1,694.26 of operating cash. Whether that loss is worth anything this year depends on a number that isn't in the property spreadsheet at all.

Rental real estate is a passive activity. Under Internal Revenue Code section 469(i), an individual who actively participates can deduct up to $25,000 of rental losses against other income. That $25,000 is reduced by 50 percent of the amount by which modified adjusted gross income exceeds $100,000, so it reaches zero at $150,000. Married people filing separately get different, smaller limits. A loss you can't use is not lost. Section 469(b) carries it to the next year, and 469(g) releases it when you sell your entire interest in a fully taxable sale. But it doesn't reduce this year's tax bill.

In the sheet, with modified AGI in B30 and active participation (Yes or No) in B31:

• Allowance: =IF(B31="Yes",MAX(0,25000-0.5*MAX(0,B30-100000)),0)
• Usable loss: =MIN(MAX(0,-SchedE!B21),B32)
• Suspended and carried forward: =MAX(0,-SchedE!B21)-B33

Two caveats keep this honest. The allowance covers all of your rental real estate combined, so a second property's loss competes for the same $25,000. And passive income from another activity can absorb the loss regardless of your income. The sheet shows the simple case. If you have several rentals, or you may qualify under the separate real estate professional rules, take the numbers to your tax preparer instead of relying on this block. Still, the block answers the question buyers skip: at a household income of $165,000, the $1,147.89 of tax savings in the pitch is zero this year.

What to Set Up This Week

Use this order for one property and one year of statements.

1. Export 12 months of the property's bank account and paste them into a Ledger tab. Run the ISTEXT count and fix any text dates before anything else.
2. Tag every row with a Schedule E line and a cash class from locked validation lists. Split every mortgage payment into interest and principal from the servicer's statements.
3. Build the bridge and the two check cells. Do not look at any summary until both read zero.
4. Put your original pro forma next to the actuals and flag the variances. Separate the permanent ones from the one-time ones.
5. Add the passive loss block with your real modified AGI, and remove any tax benefit from next year's projection that the block says you can't use.

If you track rent by tenant for collections, keep that in a separate rent ledger. The two answer different questions, and the bank ledger should only hold what actually cleared.

The Recommendation

Stop tracking rental cash flow as one number. Keep one ledger per property per year, tag each transaction twice, and make the bridge between Schedule E and the bank balance equal zero before trusting any total. On this duplex, doing that turns "the property lost $10,235.74" into three clear findings. The property really cleared $141.19 a month, not $366. $1,920 of that shortfall is permanent and belongs in your next underwriting. The roof was a reserve failure, not a performance failure. And the tax loss is worth $1,147.89 or nothing, depending on a salary line that never appears on the rental's own spreadsheet.

The ledger is the part you build. The baseline it's measured against is the part most investors never write down properly. The SheetCraft Rental Property Analyzer produces that baseline in one place: an itemized operating expense analysis with its own line for taxes, insurance, management, maintenance reserve, vacancy, and utilities, plus the mortgage analysis tab with the amortization schedule that gives you the interest and principal split for the ledger. Run your purchase numbers through it, keep that tab frozen as the pro forma, and add the Ledger and Bridge tabs from this article next to it. Each year's actuals then have a fixed target, and the variance table tells you which lines to fix in the model before you buy the next property.