BRRRR calculator spreadsheet: cash left in the deal, refinance loan and cash flow formulas

Short answer: Add the purchase price, rehab and closing costs to get your all-in cost. The refinance loan is the after repair value (ARV) times the lender's loan-to-value. Cash left in the deal is all-in cost plus refinance costs minus the new loan. Then run NOI and cash flow on the new mortgage, and divide yearly cash flow by the cash left in to get cash-on-cash return.

Skip the setup: the Rental Property Investment Analyzer Spreadsheet (Google Sheets + Excel, $6.99, no macros, no logins) has all of this built in, with a dashboard and example rows. Get it on Gumroad.

Inputs

Purchase price, purchase closing costs and rehab budget
After repair value (ARV) from recent comparable sales
Refinance loan-to-value (LTV) %, rate, term and refinance closing costs
Rent per month, vacancy %, management %, tax, insurance and maintenance %

Core formulas (Google Sheets and Excel)

All-in cost = Price + Purchase closing + Rehab
Refinance loan = ARV x LTV
Cash left in deal = All-in cost + Refi closing minus Refinance loan
Monthly mortgage: =PMT(Rate/12, Years*12, -Refinance loan)
NOI = Rent x 12 x (1 minus Vacancy) minus operating expenses
Annual cash flow = NOI minus 12 x Monthly mortgage
Cash-on-cash = Annual cash flow / Cash left in deal (if cash left in is 0 or less, the return is unlimited, so look at cash flow instead)
DSCR = NOI / (12 x Monthly mortgage), many DSCR lenders look for about 1.0 to 1.25 or more

Worked example

Buy at $150,000 with $5,000 closing costs and a $40,000 rehab, so $195,000 all-in. ARV is $250,000 and the lender refinances at 75% LTV at 7.5% over 30 years, with $4,000 refinance costs. Rent is $2,100 a month with 5% vacancy, 8% management, $3,000 tax, $1,400 insurance and 5% maintenance.

Refinance loan: $250,000 x 75% = $187,500
Cash left in: $195,000 + $4,000 minus $187,500 = $11,500
Mortgage: =PMT(0.075/12,360,-187500) = $1,311.03 a month, or $15,732.33 a year
Effective rent: $25,200 minus 5% = $23,940
Operating expenses: $1,915.20 management + $3,000 tax + $1,400 insurance + $1,260 maintenance = $7,575.20
NOI: $16,364.80, cash flow about $632 a year
Cash-on-cash: about 5.5% on $11,500 left in, DSCR about 1.04

What the example tells you

The refinance pulled out most of the cash, which is the point of BRRRR, but the bigger loan leaves thin cash flow and a DSCR barely above 1. Test a lower ARV and a higher rate before you buy. If the deal only works at the best-case ARV, it is a risky BRRRR.

Stress test it

Add a second column with ARV 10% lower, rehab 20% over budget and rate 1 point higher. If cash left in and cash flow are still acceptable there, the deal has a margin of safety.

FAQ

What LTV do lenders refinance at?

It varies by lender and loan type. Many investment property cash-out refinances are quoted around 70 to 75% LTV. Keep LTV as an input and use your lender's number.

Is there a seasoning period before refinancing?

Many lenders require you to own the property for a period before they use the new appraised value. Ask your lender, and model holding costs for that time.

Is there a ready-made template?

Yes. The rental analyzer below has cash flow, cap rate, cash-on-cash, DSCR and a multi-property comparison for Google Sheets and Excel, and you can enter your refinance loan as the financing.

Is this financial advice?

No. It is an estimating method. Check numbers with a lender, accountant or local agent before buying.

More spreadsheet guides

Contact: m0f@mail.grokbot.com | Machine-readable: llms.txt, llms-full.txt, tools.json, openapi.json | Source: GitHub