Rental Property Cash Flow Calculator in Google Sheets

Photo by Jakub Żerdzicki on Unsplash

What this guide covers and what it excludes

This guide teaches you to build a rental property cash flow and returns calculator entirely inside Google Sheets, then publish it as a live web form using the GSheetPress GS2Web add-on. By the end you will have a calculator that accepts property-specific inputs and displays net operating income (NOI), monthly cash flow, annual cash flow, and cash-on-cash return automatically.

What is excluded: tax depreciation, capital gains treatment, property appreciation forecasting, insurance valuation, and legal or financial advice. The formulas use the inputs you provide; always verify local vacancy rates, maintenance benchmarks, and lender terms with qualified professionals before making investment decisions. The Consumer Financial Protection Bureau mortgage resources are a useful starting reference for loan cost assumptions.

Key terms explained

• Gross Rental Income: The total rent collected if the property were occupied 100% of the time for a full year.
• Vacancy Rate: The percentage of the year the property sits empty and earns no rent. A 5% vacancy rate means 5% of gross income is lost to empty periods.
• Effective Gross Income (EGI): Gross rental income minus vacancy losses. This is the realistic income you actually receive.
• Operating Expenses: Recurring costs to keep the property running — maintenance, property management fees, insurance, and property taxes. Mortgage payments are NOT operating expenses.
• Net Operating Income (NOI): Effective gross income minus all operating expenses. NOI tells you how profitable the property is before financing costs.
• Debt Service: The total annual mortgage (or loan) payments — principal plus interest.
• Cash Flow: NOI minus annual debt service. Positive cash flow means money in your pocket after paying the mortgage; negative cash flow means the property costs you money each month.
• Cash-on-Cash Return: Annual cash flow divided by the total cash you invested (down payment plus closing costs), expressed as a percentage. It tells you what return you are earning on your actual cash outlay.
• Loan-to-Value (LTV): The loan amount expressed as a percentage of the property purchase price. A 20% down payment gives an 80% LTV.
• Mortgage Payment (PMT): The fixed monthly payment on a standard amortising loan, calculated from principal, interest rate, and term.
• Occupancy Rate: The inverse of vacancy rate. If vacancy is 5%, occupancy is 95%.
• Cap Rate: NOI divided by the property purchase price, expressed as a percentage. It is a property-level return metric independent of financing.

Prerequisites

• A Google account with access to Google Sheets.
• GSheetPress GS2Web installed from the Google Workspace Marketplace. Open any sheet, choose Extensions → Add-ons → Get add-ons and search for GS2Web SaaS. Install it and grant the requested permissions.
• Your GSheetPress account must have a Calculator entitlement. Log in at GSheetPress and confirm your plan includes the Calculator feature before proceeding.
• A dedicated sample workbook created solely for this calculator. Do not use a workbook containing private financial data, because the sharing step later makes the spreadsheet editable by anyone with the link.

Set up the spreadsheet structure

Open your dedicated sample workbook and rename the first tab RentalCalc. Leave all other tabs alone. You will work only on this tab.

Build the rental property calculator

Open Extensions → GS2Web SaaS → Calculator → New Calculator. A dialogue box appears. Fill it in as follows:

• Name: Rental Property Cash Flow
• Number of inputs: 9
• Include graphs: No (leave unchecked)

Click Create. The add-on writes the header row automatically into A1:H1 with the labels: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules. Do not edit or delete this row.

Your input rows run from row 2 to row 10 (nine inputs). The button is in row 11. The merged Result Fields separator is in row 12. Result headings start at row 13, and result values start at row 14 onward.

Input row 2 — Monthly Rent

• A2 — Label: Monthly Rent ($)
• B2 — Input Type: Number
• C2 — Required: Check the Required checkbox (tick it)
• D2 — Data: Leave blank
• E2 — Default: 2000
• F2 — ToolTips: Enter the monthly rent you expect to charge tenants.
• G2 — Inputs: Leave blank (the add-on manages this column at runtime)
• H2 — Rules: Leave blank

Input row 3 — Vacancy Rate

• A3 — Label: Vacancy Rate (%)
• B3 — Input Type: Number
• C3 — Required: Check the Required checkbox
• D3 — Data: Leave blank
• E3 — Default: 5
• F3 — ToolTips: Enter vacancy as a percentage (e.g. 5 means 5%). Typical residential vacancies range from 3% to 10%.
• G3 — Inputs: Leave blank
• H3 — Rules: Leave blank

Input row 4 — Annual Maintenance

• A4 — Label: Annual Maintenance ($)
• B4 — Input Type: Number
• C4 — Required: Check the Required checkbox
• D4 — Data: Leave blank
• E4 — Default: 1200
• F4 — ToolTips: Estimated yearly cost for repairs and upkeep. A common rule of thumb is 1% of property value per year.
• G4 — Inputs: Leave blank
• H4 — Rules: Leave blank

Input row 5 — Property Management Fee

• A5 — Label: Management Fee (%)
• B5 — Input Type: Number
• C5 — Required: Check the Required checkbox
• D5 — Data: Leave blank
• E5 — Default: 8
• F5 — ToolTips: Enter the property manager's fee as a percentage of effective gross income (e.g. 8 means 8%). Enter 0 if self-managing.
• G5 — Inputs: Leave blank
• H5 — Rules: Leave blank

Input row 6 — Annual Insurance

• A6 — Label: Annual Insurance ($)
• B6 — Input Type: Number
• C6 — Required: Check the Required checkbox
• D6 — Data: Leave blank
• E6 — Default: 1500
• F6 — ToolTips: Annual landlord insurance premium. Contact your insurer for an accurate figure.
• G6 — Inputs: Leave blank
• H6 — Rules: Leave blank

Input row 7 — Annual Property Tax

• A7 — Label: Annual Property Tax ($)
• B7 — Input Type: Number
• C7 — Required: Check the Required checkbox
• D7 — Data: Leave blank
• E7 — Default: 3000
• F7 — ToolTips: Check your local authority assessment for the accurate figure. This field is required because property tax varies widely by location.
• G7 — Inputs: Leave blank
• H7 — Rules: Leave blank

Input row 8 — Loan Amount

• A8 — Label: Loan Amount ($)
• B8 — Input Type: Number
• C8 — Required: Check the Required checkbox
• D8 — Data: Leave blank
• E8 — Default: 200000
• F8 — ToolTips: The mortgage principal — the amount you are borrowing, not the purchase price. Enter 0 for an all-cash purchase.
• G8 — Inputs: Leave blank
• H8 — Rules: Leave blank

Input row 9 — Annual Interest Rate

• A9 — Label: Annual Interest Rate (%)
• B9 — Input Type: Number
• C9 — Required: Check the Required checkbox
• D9 — Data: Leave blank
• E9 — Default: 7
• F9 — ToolTips: Enter your mortgage interest rate as a percentage (e.g. 7 means 7% per year). Check your lender's current rates.
• G9 — Inputs: Leave blank
• H9 — Rules: Leave blank

Input row 10 — Total Cash Invested

• A10 — Label: Total Cash Invested ($)
• B10 — Input Type: Number
• C10 — Required: Check the Required checkbox
• D10 — Data: Leave blank
• E10 — Default: 60000
• F10 — ToolTips: Down payment plus all closing costs and purchase fees. This is the actual cash you put in at settlement.
• G10 — Inputs: Leave blank
• H10 — Rules: Leave blank

The button row (row 11)

The add-on generates this row automatically. You should see:

• A11: Calculate (the button label — do not change)
• B11: Button
• D11: main
• E11: Reset

Do not edit row 11. The Reset label in E11 is correct add-on behaviour; the button on the web form will show the text from A11.

Row 12 — Result Fields separator

The add-on merges and labels this row as Result Fields. Leave it untouched.

Add result labels and formulas

All result labels go in column A starting at row 13. All result formulas go in column B of the same row. The formulas reference G2 through G10, which are the cells the web form writes to when a user submits values. Column E holds your defaults; column G holds live submission values.

Important: Before testing formulas inside the sheet (before publishing), copy the defaults from E2:E10 into G2:G10 so the formulas have values to work with. Select E2:E10, copy, click G2, then choose Edit → Paste special → Values only. Never store result formulas in column G — the add-on overwrites G cells on every web submission.

Row 13 — heading row

Type the following directly into the cells:

1. Click cell A13 and type: Result
2. Click cell B13 and type: Value

Row 14 — Gross Annual Rental Income

Click cell A14 and type: Gross Annual Rental Income ($)

Click cell B14, type the following formula, and press Enter:

=G2*12

This multiplies monthly rent (G2) by 12 months.

Row 15 — Vacancy Loss

Click cell A15 and type: Vacancy Loss ($)

Click cell B15 and enter:

=B14*(G3/100)

This takes gross annual income and multiplies by the vacancy rate expressed as a decimal.

Row 16 — Effective Gross Income (EGI)

Click cell A16 and type: Effective Gross Income ($)

Click cell B16 and enter:

=B14-B15

Row 17 — Management Fee

Click cell A17 and type: Management Fee ($)

Click cell B17 and enter:

=B16*(G5/100)

The management fee percentage (G5) is applied to effective gross income, not gross income, because managers are typically paid only on rent actually collected.

Row 18 — Total Operating Expenses

Click cell A18 and type: Total Operating Expenses ($)

Click cell B18 and enter:

=G4+B17+G6+G7

This adds: annual maintenance (G4) + management fee (B17, calculated in row 17) + annual insurance (G6) + annual property tax (G7).

Row 19 — Net Operating Income (NOI)

Click cell A19 and type: Net Operating Income ($)

Click cell B19 and enter:

=B16-B18

Row 20 — Monthly Mortgage Payment

Click cell A20 and type: Monthly Mortgage Payment ($)

Click cell B20 and enter:

=IF(G8=0,0,PMT(G9/100/12,30*12,-G8))

This uses Google Sheets' built-in PMT function. It assumes a 30-year amortising loan. G9/100/12 converts the annual percentage rate to a monthly decimal rate. 30*12 is 360 monthly payments. -G8 makes the loan amount positive in the result. If the loan amount is zero (all-cash purchase), the formula returns zero.

Note on the loan term: This model fixes the term at 30 years. If your loan has a different term, you can replace 30 with a tenth input row for loan term. For this guide, 30 years is stated as the assumption.

Row 21 — Annual Debt Service

Click cell A21 and type: Annual Debt Service ($)

Click cell B21 and enter:

=B20*12

Row 22 — Annual Cash Flow

Click cell A22 and type: Annual Cash Flow ($)

Click cell B22 and enter:

=B19-B21

Row 23 — Monthly Cash Flow

Click cell A23 and type: Monthly Cash Flow ($)

Click cell B23 and enter:

=B22/12

Row 24 — Cash-on-Cash Return

Click cell A24 and type: Cash-on-Cash Return (%)

Click cell B24 and enter:

=IF(G10=0,"N/A — no cash invested",ROUND(B22/G10*100,2))

This divides annual cash flow by total cash invested and multiplies by 100 to express the result as a percentage, rounded to two decimal places. If cash invested is zero, it returns a readable message rather than a division error.

Row 25 — Cap Rate

Click cell A25 and type: Cap Rate (%) — based on purchase price input

Cap rate requires the purchase price, not the loan amount. Because this calculator uses loan amount and cash invested, we can derive an implied purchase price as Loan Amount + Cash Invested to give an indicative cap rate. This is an approximation; use a dedicated field for the actual purchase price if precision matters.

Click cell B25 and enter:

=IF((G8+G10)=0,"N/A",ROUND(B19/(G8+G10)*100,2))

Worked example with expected results

Use the default values set in column E to verify your formulas. After pasting the defaults into G2:G10, you should see the following results in column B:

The negative cash flow with these defaults is intentional — it shows the calculator correctly models a scenario where mortgage costs slightly exceed NOI. Raising rent to $2,200 or reducing the loan to $180,000 produces positive cash flow, illustrating how the tool supports scenario comparison.

Alternative scenario — lower leverage

Change G8 (Loan Amount) to 150000 and G10 (Cash Invested) to 110000, keeping everything else at the defaults. Expected results:

Positive cash flow appears because the smaller loan reduces debt service below NOI. This is exactly the kind of financing scenario comparison a property investor needs. You can build a more comprehensive comparison model using the GSheetPress table feature alongside your calculator for side-by-side display.

Frequently asked questions

Why does my PMT formula return a negative number?

Google Sheets' PMT function returns a negative value by default because it represents a cash outflow (money leaving your account). The formula in this guide uses -G8 (a negative loan amount as the present value argument) to flip the result to a positive number. If you see a negative monthly payment, check that the present value argument in your PMT formula has a leading minus sign: PMT(G9/100/12, 30*12, -G8).

Can I model a 15-year mortgage instead of 30 years?

Yes. In cell B20, replace the fixed 30 with a reference to an additional input row. Add a tenth input row (row 10 becomes Loan Term in Years, shift Total Cash Invested to row 11, adjust the button to row 12, the separator to row 13, and result rows accordingly). Then reference the new G10 cell in the PMT formula as PMT(G9/100/12, G10*12, -G8). Update all row references throughout the result section to match the shifted layout. Remember the button is always at row N+2, where N is the number of inputs.

What if a user enters 0 for Total Cash Invested?

The cash-on-cash formula uses an IF check: IF(G10=0, "N/A — no cash invested", ...). This prevents a division-by-zero error and shows a readable message. The cap rate formula similarly guards against a zero denominator. Never suppress errors with a blanket IFERROR(...,0) because that hides genuine data-entry mistakes that would produce silently wrong results.

Troubleshooting

If problems persist, the GSheetPress support page provides contact options and documentation for the add-on.

Publish and test your web calculator

Before publishing, you must share the spreadsheet so the add-on can serve it publicly. This step makes your spreadsheet accessible to anyone with the link in Editor mode.

Sharing warning: The next step grants anyone holding the spreadsheet URL the ability to edit it. Use only the dedicated sample workbook — never a workbook containing private or financial data you do not want others to see or change.

1. In your sample workbook, click the blue Share button (top right).
2. Under General access, change the setting from Restricted to Anyone with the link.
3. Set the role dropdown to Editor.
4. Click Done.

Now publish the calculator:

1. Make sure you are on the RentalCalc tab.
2. Open Extensions → GS2Web SaaS → Calculator → Publish Calculator.
3. Wait for the confirmation message. The add-on creates a Calculator Link sheet in your workbook.
4. Click on the Calculator Link sheet tab. Find the row where the Sheet Name column matches RentalCalc.
5. Open the URL in column C (the public Link column) — this is the live web calculator. Do not use column B (Edit Link), which opens the backend.
6. If you do not see the row or the link looks outdated, click Get Links inside the add-on menu to refresh the sheet.

Test the published calculator:

1. Open the public link in a new browser window.
2. Enter the nine worked-example default values: Monthly Rent 2000, Vacancy Rate 5, Annual Maintenance 1200, Management Fee 8, Annual Insurance 1500, Annual Property Tax 3000, Loan Amount 200000, Annual Interest Rate 7, Total Cash Invested 60000.
3. Click Calculate.
4. Confirm the results match the expected values in the worked example table above — NOI approximately $15,276, annual cash flow approximately -$691, cash-on-cash return approximately -1.15%.
5. Click Reset and confirm all fields return to their defaults.
6. Optionally, copy the Iframe code from column D of the Calculator Link sheet and paste it into an HTML block on your website to embed the calculator. For more embedding options, see the GSheetPress calculator details page.

Republishing the same calculator tab retains its existing public link — your URL does not change when you update formulas and republish. Finish by confirming the live results on the public URL, not by publishing a blog post or sharing the edit link.