Rental Property Bad Debt Allowance Calculator in Excel: Model What Actually Collects

Your 12-unit in Dayton billed $180,000 of rent last year. The bank took in $171,400. That $8,600 gap never appeared anywhere in your model, because your pro forma carried a single line called "vacancy and credit loss, 5%" and called it a day. A rental property bad debt allowance calculator in Excel exists to split that line in two, since the halves behave nothing alike and cost wildly different amounts.

Vacancy is a unit sitting empty. You never billed anyone, you lost the rent, you re-rent it in 30 days. Bad debt is a unit that is occupied, billed, and not paying. You cannot re-rent it. You cannot enter it. In most states you cannot start the clock for another 30 days after the first missed payment. Rolling both into one percentage is the most common reason a deal that penciled at a 1.20 debt service coverage ratio reports 1.06 at its first annual review.

One Non-Payer Costs What Six Vacant Months Cost

Price both events on the same unit at $1,250 a month and the asymmetry stops being theoretical.

One default costs 5.8 months of vacancy on the same unit. That ratio is why the two risks need separate inputs, separate assumptions, and separate lines in the pro forma.

The input block

Build an Inputs sheet with the eleven cells that drive everything downstream. Nothing here is a plug. Every one of them is knowable.

The calculation block sits directly underneath:

• B14, gross cost per event: =(B8*B4)+B4+B9+B10-B11 returns $7,900. The lone B4 in the middle is the turn month after lockout, which most models forget because the tenant is already gone by then.
• B15, net cost per event: =B14*(1-B12) returns $7,268. Set B12 from your own history, not from what the collection agency claims. Eight percent is generous for a judgment against someone who just spent four months not paying rent.
• B16, annual bad debt allowance: =B7*B15 returns $7,268.
• B17, allowance as a percent of GPR: =B16/B5 returns 4.04%.
• B18, vacancy loss: =B5*B6 returns $10,800, and it stays on its own line forever.

Total economic loss on this building is 10.0% of gross potential rent. The combined 5% line was off by half, and half of $180,000 is not a rounding difference.

The Eviction Calendar Drives the Allowance More Than the Tenant Does

Hold the building constant. Same $1,250 rent, same $1,100 legal cost, same tenant who stops paying in March. Change only the county where the case gets filed.

Same tenant, same rent, and the allowance moves 607 basis points. Legal cost is held at $1,100 across every row to isolate the calendar, which means the bottom two rows are optimistic: housing court attorneys in Cook County and New York bill $2,500 to $4,500 per case. A national "use 2% for bad debt" default is a guess about a courthouse you have never walked into.

Get the number from your county clerk's docket, not from a state summary. Filing to judgment and judgment to sheriff's lockout are two separate queues, and the second one is where the months disappear.

Measure It From the T-12 Instead of Guessing

On a property you own or are under contract to buy, stop estimating. Build a Collections sheet with one row per month: A month, B charges posted, C collected in period, D collected late, E written off, F concessions.

• Gross collection rate: =SUM(C5:C16)/SUM(B5:B16)
• True uncollected fraction: =1-(SUM(C5:C16)+SUM(D5:D16))/SUM(B5:B16)

Three things wreck this calculation, and all three are common enough to check every time.

Late is not lost

A model that treats anything not received by the 5th as bad debt overstates the allowance by three to four times. The tenant who pays on the 14th every month for six years is a cash management problem, not a credit loss. Keep columns C and D separate and flag the difference: =IF(AND(D5>0,E5=0),"LATE, NOT LOSS",""). Price the late payer into your operating account balance, not into your NOI.

The seller who never writes anything off

A T-12 showing $700 of bad debt on $180,000 of rent looks like a well-run building. Then you pull the accounts receivable aging as of the first and last day of that same window. Receivables went from $4,900 to $18,600. Nothing was written off because writing off is a decision, and the seller chose not to make it while marketing the property.

The real number: =Write_offs+(AR_end-AR_begin) returns $14,400, which is 8.0% of gross potential rent, not 0.39%. Request the aging report at both endpoints on every deal. If the seller will not produce it, underwrite the allowance at the top of the class band and let the price reflect that.

Double counting vacancy

Rent on an empty unit was never billed, so it can never be bad debt. If column B comes from a rent roll of scheduled rent instead of charges actually posted, you are counting the same loss twice and your EGI is wrong in the conservative direction, which feels safe and costs you deals. Tie it out: =IF(ABS(SUM(B5:B16)-(B5-B18))>500,"CHECK BILLING SOURCE","OK").

Roll Rates Turn Today's Aging Into Next Quarter's Write-Off

The trailing twelve tells you what happened. Roll rates tell you what is about to. Pull the percentage of each aging bucket that moves to the next bucket the following month, averaged over the last year, then chain them.

The cumulative column is one formula copied down: =PRODUCT(D5:$D$9). The mixed anchor makes each row multiply its own roll rate by every roll rate below it. Reserve per row is =B5*E5, and the total allowance is =SUMPRODUCT(B5:B9,E5:E9).

Your balance sheet says $15,250 is owed to you. About $7,463 of that is not money, it is a story about money. Booking the reserve is what keeps you from spending it twice, once in your distribution and once in your refinance package.

The operational payoff is the trigger. The $3,600 sitting in the 90-plus bucket was current five months ago, and the roll rate flagged it in month two. Put a decision rule on the sheet instead of on your calendar: =IF(F9>B4*2,"START FILING","HOLD"). When the reserve on the oldest bucket passes two months of rent, negotiating is over and the only variable left is how many more months you fund.

Where the Number Goes and What It Changes

Vacancy, concessions, and bad debt each get their own line between gross potential rent and effective gross income. Here is the same 12-unit under both treatments, with operating expenses held flat at $79,200 so the only variable is the loss block.

The gap is $168,354 of value and 14 basis points of coverage on a building most people would describe as small. You underwrite to a 1.20, the lender agrees, you close at $1,400,000. Twelve months later the operating statement produces a 1.06 and someone at the bank runs the covenant test. Nothing went wrong at the property. The model just billed rent it was never going to collect.

Where to start when you have no history

That last row is worth a cell of its own. On a Section 8 unit, the housing authority pays 65% to 75% of contract rent by direct deposit and defaults on essentially none of it. Only the tenant portion carries credit risk. Blend it: =(1-B22)*B23, where B22 is the HAP share and B23 is the class rate for the tenant portion. A C-class building at a 4.0% class rate with 70% HAP coverage prices out at a 1.2% allowance. Underwriting voucher rent at the same risk as market rent is how buyers argue themselves out of the most reliable cash flow on the block.

Build It This Week

1. Export 12 months of posted charges, not scheduled rent, from your property management software or your ledger.
2. Pull the AR aging as of the first and last day of that window. Run =Write_offs+(AR_end-AR_begin) and compare it to what the income statement claims.
3. Split collected in period from collected late. Recompute the true uncollected fraction.
4. Look up the practical notice-to-lockout window in your county docket and set B8 from that, not from a state-level article.
5. Count actual default events over the last three years and divide by three to set B7. If the answer is zero, your screening is working or your sample is too small. Use the class band.
6. Give vacancy, concessions, and bad debt three separate lines above effective gross income. Delete the combined percentage from every template you own.
7. Rerun the DSCR and the cap rate valuation with the modeled EGI before you send the loan package.

Then look at what the allowance is actually buying you. One default event costs $7,268 in Ohio. Screening 12 applicants properly, with income verification, prior landlord contact, and a full credit pull, runs about $540 a year. Avoiding a single eviction pays for 13 years of screening. Moving your minimum from 2.5x income to 3.0x costs you two weeks of extra vacancy on a turn and cuts the input in B7 by more than any collections process ever will.

If you would rather not build the input block, the roll-rate chain, the T-12 tie-out, and the three-line loss stack from scratch, SheetCraft's Rental Property Analyzer ships with the credit loss module already wired: separate vacancy, concession, and bad debt lines feeding effective gross income, a per-event eviction cost calculator driven by your county timeline, an aging schedule with roll-rate reserves, and a DSCR panel that updates the moment the allowance changes. You enter charges and collections. It tells you what the building actually earns and which unit is about to stop paying for it.