Fix and Flip Funding Gap Calculator in Excel: The Cash Hard Money Never Sends

A fix and flip funding gap calculator in Excel exists to answer one question your lender will not answer for you: how much of your own money has to sit in this deal, and on which day does the requirement peak. The term sheet says 90 percent of purchase, 100 percent of rehab. Your brain hears "10 percent down." On a $148,000 purchase that sounds like $14,800. The deal below needs $64,205 of liquid cash at its worst moment, and the day it needs that is week 16, four months after you already spent everything you thought the deal required.

Nobody defaults on a flip because the ARV was wrong. They default because they ran out of cash in month four with a house that has no kitchen, an interest clock at 11.5 percent, and a contractor who stops showing up. The funding gap is not a rounding error in your underwriting. It is the thing that decides whether you finish.

The Funding Gap Is Three Separate Holes, Not One

Most flip spreadsheets have a cell called "cash to close" and nothing after it. That cell covers roughly 45 percent of what the deal will actually pull out of your bank account. Three distinct mechanisms create the rest, and they behave differently, so they need separate blocks in the model.

Hole 1: the ARV ceiling quietly defunds your rehab

Hard money term sheets stack two constraints, and the tighter one wins. Take the deal: purchase $148,000, rehab budget $86,000, ARV $325,000. Advance rate is 90 percent of purchase and 100 percent of rehab, but total loan is capped at 65 percent of ARV.

Requested loan is =B3*B6+B4*B7, which is $133,200 plus $86,000, or $219,200. The ARV ceiling is =B5*B8, or $211,250. The lender funds =MIN(B17,B18), which is $211,250. The purchase advance is protected because it funds first, so the entire $7,950 shortfall comes out of the rehab holdback. Your 100 percent rehab financing is actually 90.76 percent, and you find out at the closing table if you find out at all.

Put the flag directly in the model so it cannot be skimmed past:

=IF(B22>0,"ARV CAP BITES: "&TEXT(B22,"$#,##0")&" of rehab is yours","Rehab fully funded")

Hole 2: draws are reimbursements, not advances

This is the one that surprises first-time flippers, and it is the largest single component of the gap. The rehab holdback is not sitting in your account. You pay the contractor, then you request a draw, then an inspector drives out, then the lender wires funds. Nine to seventeen business days later the money lands. Until then you have financed the work yourself, at 100 percent.

Every dollar of rehab passes through your checking account before any of it comes back. On an $86,000 rehab with a $24,000 peak draw, the float alone is a $24,000 cash requirement that appears nowhere on the term sheet.

Hole 3: interest is monthly, revenue is a single event

Interest, taxes, utilities, insurance, and inspection fees hit every month. The property pays you exactly once, at the closing table, seven months later. Nothing offsets holding cost in the interim, so every month of carry is straight cash out of pocket, stacking on top of the draw float.

Build the Loan Sizing Block First

Everything downstream depends on how much the lender actually funds, so that block goes at the top and every other formula points at it.

The funded ratio in B23 is the workhorse cell. It converts every contractor invoice into the portion the lender will reimburse, and it is what makes the draw schedule below calculate itself when you change the ARV or the cap.

Cash to close is a simple sum, but it needs all seven lines, not the three that appear on the loan estimate.

That $28,837 is the number the lender will quote you. Keep it visible, because the whole point of the model is to show how far it is from the truth.

The Draw Schedule Is Where the Real Money Hides

Build the draw table on its own tab with one row per draw. Two columns matter more than the dollar amounts: the out of pocket portion, and the days to fund.

Column D is =ROUND(C5*Deal!$B$23,0) and column E is =C5-D5. Absolute reference on B23 so you can drag it. Add a column H for the week funds land, =G5+F5/7, where G is the week you paid the invoice. That single column is what turns a static budget into a timeline.

Ask the lender for the days to fund in writing before you sign, and ask for the worst case, not the average. A lender who says "usually about a week" is quoting you their processing time, not the inspector's calendar. Seventeen days on draw 4 is what a real schedule looks like when the inspection lands the week of a holiday.

Your Funding Gap Is a Peak, Not a Sum

Here is the mistake that makes most flip calculators useless: they add up cash to close, unfunded rehab, and holding costs, print one number, and call it the cash requirement. That double counts the reimbursed portion and it completely misses timing. The requirement is not a total. It is the deepest point of a running balance.

Build a timeline tab: column A week, column B event, column C cash out, column D cash in, column E running position. E5 is =D5-C5, and E6 down is =E5+D6-C6. That is the entire engine.

Two formulas turn this into a decision. The gap itself is =-MIN(E5:E30), which returns $64,205. The date it hits is =INDEX(A5:A30,MATCH(MIN(E5:E30),E5:E30,0)), which returns week 16.

The lender quoted $28,837. The deal requires $64,205, and it requires it 112 days after closing, long after most flippers have stopped watching their bank balance. That is a 2.23x multiple on the number in your head.

The interest line has two versions and the term sheet will not tell you which

Some lenders charge interest on the full committed loan from day one, including the undrawn rehab holdback. Others charge only on the drawn balance. Same 11.5 percent, very different bill. Full balance is =$B$19*$B$9/12, a flat $2,024 a month, $14,168 over seven months. Drawn balance accrues daily against the actual outstanding, =F6*Deal!$B$9/365*((A6-A5)*7) down a balance column, which totals about $12,299. The difference is $1,869 you either budget for or discover. Ask which structure applies, and put the answer in a toggle cell.

Stress Test the Gap Before You Sign, Not After

The base case is the least useful scenario in the model, because the base case is the one that will not happen. Run four variants off the same timeline.

Read the third row carefully, because it is the counterintuitive one. A two month delay costs you $7,481 in carry and extension fees, but it does not raise the peak cash requirement at all. The peak happens in week 16, mid rehab, before the delay exists. Delays destroy profit. Overruns destroy solvency. They are different risks and your reserve has to be sized against the second one.

The bottom row is the whole argument for building this thing. A 12 percent overrun, a two month delay, and a $13,000 price miss are each individually ordinary. Together they convert a $29,221 profit into a $774 loss, and they do it on a deal that penciled at a perfectly respectable 72 percent of ARV going in.

What To Do With the Number

Before wiring earnest money on any hard money deal, work this list top to bottom:

1. Get the ARV cap and the advance rate in writing and run =MIN() against both. If the cap bites, you have less rehab financing than the headline says, and the shortfall is yours in cash.
2. Ask for the worst case days from draw request to wire, not the average. Put that number in the model, not the number in the marketing deck.
3. Ask whether interest accrues on the full commitment or the drawn balance, and price both.
4. Build the running position column and read =-MIN(). That is your funding gap. Ignore any total that adds line items together.
5. Add a full 12 percent rehab contingency as unfunded cash. A lender already at the ARV ceiling will not fund overruns without re-underwriting the whole loan.
6. Confirm your liquid reserve covers the peak plus the contingency, in cash, in an account you can reach in 48 hours. Not a HELOC you have not drawn, not a partner who has verbally agreed.

For this deal the reserve requirement is =ROUNDUP((-MIN(E5:E30)+B4*0.12)/1000,0)*1000, which is $75,000. The lender described this as 10 percent down on a $148,000 house. The number in your head was $14,800. The number that keeps you solvent is five times that.

The recommendation: do not sign a hard money term sheet until the peak of your running position, plus a full unfunded contingency, sits liquid in an account you control. If it does not, the fix is not a better contractor or a faster lender. It is a smaller deal. A flip you can carry through week 16 at 26 percent returns beats a flip you cannot carry at 45 percent, because the second one never reaches the closing table.

If you would rather not wire the loan sizing block, the funded ratio, the draw timeline, and the peak formula together from an empty sheet, SheetCraft's Flip and BRRRR Calculator already has the funding gap engine built in: ARV cap logic that shows exactly how much rehab gets defunded, a draw schedule that converts contractor invoices into reimbursement timing, a running cash position that reports the peak and the week it lands, both interest accrual structures on a toggle, and a reserve test that flags the deal before you commit. You enter the term sheet and the scope of work. It tells you how much cash the deal will actually demand, and on which day.