Rental Property Escrow Analysis Tracker in Excel: The Payment Increase Nobody Underwrites

Your mortgage payment on a rental is not fixed. Principal and interest are fixed. The other line, the escrow deposit that funds property taxes and insurance, gets recomputed every twelve months by your servicer, and that recomputation can move the payment by several hundred dollars on thirty days of notice. A rental property escrow analysis tracker in Excel is the difference between seeing that letter coming and finding out in February that the property you underwrote at $204 a month of cash flow is now losing money.

The math itself is simple. What makes it dangerous is the delivery: one new number, in a statement you did not request, at a moment you did not choose, on a property you already decided was profitable. Most landlords read the new payment, absorb it, and never learn that the increase had two separate components governed by two different federal rules, and that one of those rules lets the servicer compress the catch-up into two months instead of twelve.

The two thirds of your payment that are actually fixed

Take a single family rental bought for $265,000 with a $198,750 loan at 7.125 percent. Principal and interest come to $1,339 a month and will be $1,339 a month in 2054. At closing the lender set the escrow deposit off the seller's tax bill of $2,760 and a quoted landlord policy of $1,480, so $4,240 a year, $353 a month. Total payment $1,692.

That $1,692 is what went into the underwriting model. It is also 79 percent fixed and 21 percent floating, and nobody wrote that down.

In year two both floating pieces move at once. The county reassesses at the sale price and the tax bill goes to $3,910. The dwelling fire policy renews at $2,340 after two hard years in the property market. Annual disbursements are now $6,250 instead of $4,240, which is $520.83 a month instead of $353.

The escrow deposit rose 47 percent. That alone is not the problem. The problem is that the account spent the whole year collecting $353 a month against bills that came in at $6,250, and somebody has to make up the difference.

What the servicer does in the annual escrow analysis

Once a year the servicer runs an escrow account analysis under Regulation X. It projects the next twelve months of disbursements, projects the month by month balance, and compares the lowest projected balance against a target. The target is the cushion, and federal rule caps it at one sixth of estimated annual disbursements, which is two months of deposits. The servicer then sends an annual escrow account statement within thirty days of the close of the computation year, and the new payment usually starts the following month.

Three outcomes come out of that comparison, and they are not interchangeable:

• Surplus. The projected balance exceeds the target. If the surplus is $50 or more the servicer refunds it within thirty days. Under $50 it can be refunded or credited forward.
• Shortage. The balance is positive but below the target. If the shortage is one month's escrow payment or more, the servicer must let you repay it over at least twelve months.
• Deficiency. The balance is negative. The servicer advanced its own money to pay your tax bill. Repayment can be demanded in as few as two monthly payments.

That last line is the one nobody reads until it applies to them. A shortage buys you a twelve month spread by rule. A deficiency does not.

Building the rental property escrow analysis tracker in Excel

One tab per property, or one column per property if you are running more than six doors. The input block sits in column B and everything else derives from it.

B10 and B11 are the only two cells requiring judgment, and they are where the work is. If you bought in a reassessment state, B10 is not a guess, it is the assessed value reset times the local millage, and the post purchase reassessment model gives you the number directly. B11 comes from your carrier, not from a national average. Ask your agent for the renewal indication in month nine, not month twelve.

Projected annual tax:

B14 =B7*(1+B10)

Projected annual insurance:

B15 =B8*(1+B11)

Then the four numbers that drive everything downstream. Projected annual disbursements in B16 with =B14+B15+B9, which returns $6,250. Required monthly deposit in B17 with =B16/12, which returns $520.83. The maximum cushion the servicer is allowed to hold in B18 with =B16/6, which returns $1,041.67. That cushion figure is the target your projected low point has to clear.

The twelve month ledger that finds your low point

The single number that decides your new payment is the lowest balance the account is projected to hit, and you cannot get it from an annual total. Taxes and insurance do not leave the account evenly. They leave in lumps, and the lump timing is what creates the hole.

Lay out twelve rows, one per month, with beginning balance, deposit, disbursement, and ending balance. Ending balance in G25 is =D25+E25-F25, and the next row's beginning balance points at it. Here is the computation year for this property at the old $353 deposit, with the insurance renewal paid in June and the tax bill paid in December.

Pull the low point into B20 with =MIN(G25:G36). It returns negative $1,308. The account did not run thin, it ran through zero, because the December tax bill arrived $1,150 larger than the deposit schedule was built for and the June insurance renewal had already eaten $860 of the buffer.

Total catch-up required in B21 is =B18-B20, the distance from where the account will be to where the rule says it has to be: $1,041.67 minus negative $1,308, which is $2,349.67.

Shortage or deficiency: the split that costs $545 a month

Here is where the tracker earns its keep, because that $2,349.67 is not one number to the servicer. It is two, and they are governed by different paragraphs.

The deficiency is the negative part, computed in B22 with =MAX(0,-B20), which is $1,308. That is money the servicer already advanced on your behalf. The shortage is the rest, B23 with =B21-B22, which is $1,041.67, the amount needed to rebuild the cushion.

The shortage is at least one month's escrow deposit, so it gets spread over twelve months by rule: =B23/12, or $86.81. The deficiency is also at least one month's deposit, and the servicer may require it in as few as two payments: =B22/2, or $654.00.

Model both outcomes, because you do not control which one you get. The generous servicer folds everything into a single twelve month line. The strict one runs the deficiency over two months. Same $2,349.67, two very different Februaries.

The gap between the two year two columns is exactly $545 a month. That is not a rate change, a vacancy, or a repair. It is a paragraph of Regulation X, and it is knowable in advance if you have run the ledger.

Put the worst case in B26 with =B4+B17+B24+B25 and the spread case in B27 with =B4+B17+B21/12, then flag it. A reserve trigger like =IF(B26-(B4+B5)>400,"FUND RESERVE NOW","MONITOR") turns the tracker into something that tells you to move money rather than something you have to remember to read.

What the reset does to cash flow and to your next refinance

The property rents for $2,450. After a 5 percent vacancy allowance, 8 percent management on collected rent, and a 10 percent maintenance and capital reserve, cash available for debt service is $1,896.30 a month. Against the original $1,692 payment that is $204.30 of monthly cash flow, or $2,451.60 a year. That is the number in the underwriting model.

Read the last row before the middle two. Once the catch-up burns off, this property settles at $36.47 a month. It was never a $204 a month rental. It was a $36 a month rental with a tax and insurance assumption that had not been tested yet, and the escrow analysis is simply the moment the test gets graded.

There is a second effect that lands later and hits harder. Your debt service coverage ratio does not care about the catch-up, because a catch-up repays a balance rather than an expense. It cares very much about the new tax and insurance figures inside net operating income. At year one levels, NOI is $18,515.60 against $16,068 of annual debt service, a DSCR of 1.15. At year two levels, NOI is $16,505.60 and DSCR is 1.03.

Annual debt service is the one place the model needs principal and interest alone rather than the full payment, so keep it in its own cell:

B31 =B4*12

A DSCR of 1.03 is below the 1.20 or 1.25 minimum most portfolio lenders set. The escrow analysis did not just cost you a year of cash flow. It quietly closed the refinance you were planning for month eighteen, and you will discover that at application, months after the letter you filed away.

The portfolio view: stacked analysis dates

One property with a $364 payment increase is an annoyance. Four properties whose computation years all close on December 31 is a January you will remember. Servicers set the computation year from the origination date, so a year in which you bought aggressively produces a cluster of analyses in the same month.

Add a roll-up tab with one row per property, the computation year end, and the projected catch-up. Sum the exposure by month with =SUMIFS(Catchup,StatementMonth,"January") so you are budgeting against a calendar rather than reacting to envelopes.

Larkspur and Orchard reprice in the same month, $853.06 of additional monthly obligation starting in February, on a portfolio whose combined cash flow was budgeted at roughly half that. Nothing went wrong operationally. Occupancy is full, rent is collected, no capital item failed. The portfolio simply repriced its two floating line items on the same day, and no one wrote the date down.

What to do before your next escrow statement

Run this sequence on every financed property, in this order:

1. Find the computation year end on last year's escrow statement. That is your date, and it varies by loan, not by calendar.
2. Pull the actual tax bill, not the seller's, and the actual renewal quote, not last year's premium. Enter them in B7 and B8 as dollars, and set B10 and B11 to zero if you already have the real numbers.
3. Build the twelve month ledger at your current deposit and read the low point. If it is negative, you have a deficiency and a two month demand is legal.
4. Compute both catch-up scenarios and reserve for the worse one. The spread is not your choice.
5. Recompute DSCR at the new tax and insurance figures before you count on any refinance in the next eighteen months.
6. Sum the exposure by statement month across the portfolio and check for clusters.

The reason this is worth an hour per property is that it converts a surprise into a scheduled event. A $364 monthly increase you funded in October is a line item. The same $364 arriving in February, alongside $489 from the duplex down the street, is how landlords end up selling a performing asset for liquidity reasons.

The Rental Property Analyzer carries the escrow block described here wired into the rest of the model, so the projected deposit, the cushion target, the low point ledger, and both catch-up scenarios flow straight through to monthly cash flow, DSCR, and cash on cash without you rebuilding the links per property. Enter the loan, the real tax bill, and the renewal quote, and it tells you what the January statement is going to say while there is still time to fund it.