Gouda Studios Agent Tools

Rental property analysis spreadsheet: cash flow, cap rate and cash-on-cash return formulas

Short answer: Enter the price, deposit, loan rate and term, rent, vacancy and every operating expense. Net operating income (NOI) is effective rent minus operating expenses. Cash flow is NOI minus the mortgage. Cap rate is NOI divided by price, and cash-on-cash return is yearly cash flow divided by the cash you put in.

Inputs

• Purchase price, deposit (down payment) % and closing costs
• Interest rate, loan term in years, principal and interest or interest only
• Rent per month (or per week x 52 / 12 in NZ, AU and the UK)
• Vacancy %, property management % of collected rent
• Property tax or council rates, insurance, maintenance %, HOA or body corporate fees

Core formulas (Google Sheets and Excel)

• Loan = Price x (1 minus Deposit %)
• Monthly mortgage (principal and interest): =PMT(Rate/12, Years*12, -Loan)
• Interest-only payment: =Loan*Rate/12
• Effective rent = Annual rent x (1 minus Vacancy %)
• Operating expenses = management + tax + insurance + maintenance + HOA (not the mortgage)
• NOI = Effective rent minus Operating expenses
• Cap rate = NOI / Price
• Annual cash flow = NOI minus 12 x Monthly mortgage
• Cash-on-cash return = Annual cash flow / (Deposit + Closing costs + Repairs)
• DSCR = NOI / Annual mortgage payments
• Gross yield = Annual rent / Price

Worked example

A $300,000 house, 20% down ($60,000) plus $9,000 closing costs, a $240,000 loan at 7% over 30 years, rent of $2,400 a month, 5% vacancy, 8% management, $3,600 property tax, $1,500 insurance and maintenance at 5% of rent.

• Mortgage: =PMT(0.07/12,360,-240000) = $1,596.73 a month, or $19,160.71 a year
• Effective rent: $28,800 minus 5% = $27,360
• Operating expenses: $2,188.80 management + $3,600 tax + $1,500 insurance + $1,440 maintenance = $8,728.80
• NOI: $18,631.20, so the cap rate is 6.2%
• Cash flow: $18,631.20 minus $19,160.71 = about minus $530 a year
• Cash-on-cash: about minus 0.8% on $69,000 invested, and DSCR is 0.97
• Break-even rent: about $2,454 a month

What the example tells you

The house looks fine on gross yield (9.6%) and comes close to the 1% rule screen ($2,400 is 0.8% of the price), but at 7% interest it loses a little money every month. Quick rules like the 1% rule and the 50% rule are only screens. The full NOI and cash flow math is what tells you whether a deal works, and a 10-year projection shows whether appreciation and loan paydown make up for thin cash flow.

Compare more than one property

Put each property in its own column with the same rows, then highlight the best value in each row. Side by side, it is easy to see that a cheaper property with lower rent can still beat a nicer one on cash-on-cash return.

FAQ

What is a good cap rate?

It depends on the market and property type. Lower cap rates are common in expensive, low-risk areas and higher ones in cheaper or riskier areas. Compare against similar properties nearby rather than one fixed number.

Does this work outside the US?

Yes. The formulas are the same anywhere. Use your own currency, enter weekly rent x 52 / 12 if rent is quoted weekly, and include council rates or body corporate fees as expenses.

Should I include the mortgage in NOI?

No. NOI excludes financing, which is why it is used for cap rate. Cash flow is the number after the mortgage.

Is this financial advice?

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

More spreadsheet guides

• Credit card benefits tracker spreadsheet: how to track statement credits and annual fees
• Credit card churning spreadsheet template: track bonuses, minimum spend and your x/24 count

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