Photo by Gabe Pierce on Unsplash
Why holding period changes everything
A rent-versus-buy comparison is not a single number — it is a curve. In the first few years of ownership, transaction costs (agent fees, legal costs, stamp duty) have not yet been spread over enough months to make sense. As the years pass, equity builds, rent savings accumulate, and the maths tips toward buying. This calculator makes that curve visible by letting anyone enter their own figures and instantly see the crossover.
Before you start, be clear about what this model includes and what it excludes.
- Included: mortgage payment, deposit opportunity cost, maintenance allowance, transaction costs on purchase and sale, net rental cost.
- Excluded: capital-gains tax on the sale, income tax on interest earned on the deposit alternative, detailed mortgage amortisation, insurance, property management fees, and local government charges. These vary by country and personal circumstance and must be verified locally.
Key terms explained
- Mortgage: A loan secured against a property, repaid in monthly instalments over a set term.
- Deposit (down payment): The upfront cash you pay toward the purchase price; the lender finances the rest.
- Amortisation: The process of repaying a loan in equal instalments, where each payment covers interest first and then reduces the principal.
- Opportunity cost: The return you give up by using money for one purpose instead of the best alternative — here, the investment return you forgo by tying cash up in a deposit rather than keeping it invested.
- Holding period: How many years you plan to own the property before selling. This is the most sensitive variable in the model.
- Transaction costs: One-off fees paid when buying or selling: stamp duty (transfer tax), conveyancing, agent commission, and similar charges.
- Maintenance allowance: A percentage of the property value set aside annually for repairs. A commonly cited rule of thumb is 1–2 % of value per year, but this varies greatly by property age and condition.
- Net sale proceeds: What remains after repaying the outstanding mortgage balance and paying selling costs out of the sale price.
- PMT function: A Google Sheets built-in function that calculates the fixed periodic payment for a loan given an interest rate, number of periods, and loan amount.
- Nominal vs real return: A nominal return is before adjusting for inflation; a real return is after. This model uses nominal figures throughout for simplicity.
Prerequisites
- A Google account with access to Google Sheets.
- A GSheetPress account with the Calculator entitlement active. You can review plan details at GSheetPress pricing.
- The GS2Web SaaS add-on installed in Google Sheets (Extensions menu → Add-ons → Get add-ons, search GS2Web SaaS, install, and authorise).
- A dedicated sample workbook — do not use a file containing personal financial data, because the publishing step makes the spreadsheet editable by anyone with the link.
Step 1 — Create a dedicated sample workbook
- Open Google Sheets and click the + icon to create a blank spreadsheet.
- Rename it something like RentVsBuy-Calculator-Sample. This name appears in the GSheetPress publish sheet, so keep it recognisable.
- Confirm the file is in the same Google account that holds your GSheetPress subscription, otherwise the add-on will not find your entitlement.
Step 2 — Create the calculator shell
- With the sample workbook open, click Extensions in the menu bar.
- Hover over GS2Web SaaS (or the installed add-on name if it differs).
- Click Calculator, then New Calculator.
- In the dialog that appears, enter a calculator name such as Rent vs Buy Analyser.
- Set the number of inputs to 9. Do not count the button row.
- When asked about graphs, choose No for this model.
- Click Create (or the equivalent confirmation button). The add-on generates a new tab in your workbook with headers already in row 1: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules across columns A through H.
Because you chose 9 inputs, the generated layout is: input rows 2–10, button row 11, merged Result Fields separator row 12, result headings in row 13, and your result values starting from row 14 onward.
Step 3 — Fill in the nine input rows
Each section below covers one input row. Column G holds the current value submitted from the web form; column E holds the default shown when the form first loads. Never store result formulas in column G — it is overwritten on every web submission.
Row 2 — Home purchase price
- A2 — Label: Home purchase price ($)
- B2 — Input Type: Number
- C2 — Required: Tick the Required checkbox
- D2 — Data: Leave blank
- E2 — Default: 400000
- F2 — ToolTips: Enter the full asking or agreed purchase price in dollars.
- H2 — Rules: Leave blank
Row 3 — Deposit percentage
- A3 — Label: Deposit percentage (%)
- B3 — Input Type: Number
- C3 — Required: Tick the Required checkbox
- D3 — Data: Leave blank
- E3 — Default: 20
- F3 — ToolTips: Enter as a whole number, e.g. 20 means 20 %. Do not enter a decimal.
- H3 — Rules: Leave blank
Row 4 — Annual mortgage interest rate
- A4 — Label: Annual mortgage interest rate (%)
- B4 — Input Type: Number
- C4 — Required: Tick the Required checkbox
- D4 — Data: Leave blank
- E4 — Default: 6.5
- F4 — ToolTips: Enter as a whole number, e.g. 6.5 means 6.5 % per year.
- H4 — Rules: Leave blank
Row 5 — Mortgage term in years
- A5 — Label: Mortgage term (years)
- B5 — Input Type: Number
- C5 — Required: Tick the Required checkbox
- D5 — Data: Leave blank
- E5 — Default: 30
- F5 — ToolTips: Typical values are 15, 20, or 30 years.
- H5 — Rules: Leave blank
Row 6 — Holding period in years
- A6 — Label: Holding period (years)
- B6 — Input Type: Number
- C6 — Required: Tick the Required checkbox
- D6 — Data: Leave blank
- E6 — Default: 7
- F6 — ToolTips: How many years you plan to own before selling. Must be less than or equal to the mortgage term.
- H6 — Rules: Leave blank
Row 7 — Monthly rent (alternative cost)
- A7 — Label: Monthly rent ($)
- B7 — Input Type: Number
- C7 — Required: Tick the Required checkbox
- D7 — Data: Leave blank
- E7 — Default: 2000
- F7 — ToolTips: The monthly rent you would pay for a comparable property if you did not buy.
- H7 — Rules: Leave blank
Row 8 — Annual property value growth rate
- A8 — Label: Annual property growth rate (%)
- B8 — Input Type: Number
- C8 — Required: Tick the Required checkbox
- D8 — Data: Leave blank
- E8 — Default: 3
- F8 — ToolTips: Expected annual percentage increase in the property value. This is an assumption, not a guarantee.
- H8 — Rules: Leave blank
Row 9 — Annual maintenance allowance
- A9 — Label: Annual maintenance (% of value)
- B9 — Input Type: Number
- C9 — Required: Tick the Required checkbox
- D9 — Data: Leave blank
- E9 — Default: 1
- F9 — ToolTips: Enter as a whole number, e.g. 1 means 1 % of purchase price per year.
- H9 — Rules: Leave blank
Row 10 — Deposit opportunity cost rate
- A10 — Label: Deposit opportunity cost rate (%)
- B10 — Input Type: Number
- C10 — Required: Tick the Required checkbox
- D10 — Data: Leave blank
- E10 — Default: 5
- F10 — ToolTips: The annual return you could earn by investing the deposit instead of using it to buy a home. Common benchmarks are index-fund long-run averages (verify locally).
- H10 — Rules: Leave blank
Row 11 — Button row (generated, do not delete)
The add-on generated the button row automatically. Confirm it reads:
- A11: Calculate (the button label)
- B11: Button
- D11: main
- E11: Reset
Do not change B11, D11, or E11. You may edit A11 if you want a different button label such as Compare now.
Step 4 — Add helper cells for intermediate calculations
To keep result formulas readable, place intermediate calculations in a helper area. Use column J, starting at J2, on the same tab. These cells are never shown to the user — they are just internal working space.
For each helper cell below: click the cell, type the formula starting with =, and press Enter.
Named helpers in column J
The G-column cells hold the current input values from the web form. G2 = purchase price, G3 = deposit %, G4 = interest rate %, G5 = mortgage term years, G6 = holding period years, G7 = monthly rent, G8 = property growth %, G9 = maintenance %, G10 = opportunity cost rate %.
Enter a short label in column I beside each helper so you can read the sheet easily:
| Cell | Label in col I | Formula in col J | What it calculates |
|---|---|---|---|
| J2 | Deposit $ | =G2*(G3/100) | Cash deposit amount |
| J3 | Loan amount | =G2-J2 | Amount borrowed |
| J4 | Monthly rate | =(G4/100)/12 | Monthly interest rate |
| J5 | Total months | =G5*12 | Mortgage term in months |
| J6 | Hold months | =G6*12 | Holding period in months |
| J7 | Monthly payment | =IF(J4=0,J3/J5,-PMT(J4,J5,J3)) | Fixed monthly mortgage payment |
| J8 | Future value | =G2*((1+(G8/100))^G6) | Estimated property value after holding period |
| J9 | Bal after hold | =IF(J4=0,J3*(1-J6/J5),J3*((1+J4)^J6-(1+J4)^J5)/((1+J4)^J5-1)) | Outstanding loan balance at end of holding period |
| J10 | Sell costs | =J8*0.06 | Estimated selling costs at 6 % of sale value (verify locally) |
| J11 | Net proceeds | =J8-J9-J10 | Cash in hand after repaying mortgage and selling costs |
| J12 | Buy total cost | =(J7*J6)+(G9/100*G2*G6)+(G2*0.04) | Total outflows: payments + maintenance + purchase transaction costs at 4 % (verify locally) |
| J13 | Rent total | =G7*12*G6 | Total rent paid over holding period |
| J14 | Opp cost total | =J2*(((1+(G10/100))^G6)-1) | Compound growth foregone on deposit |
| J15 | Net buy cost | =J12+J14-J11 | True net cost of buying: outflows + opportunity cost minus sale proceeds |
| J16 | Saving | =J13-J15 | Positive = buying cheaper; negative = renting cheaper |
The outstanding balance formula in J9 uses the standard amortisation identity. If the monthly rate is zero (interest-free loan), it falls back to a straight-line pro-rata of the original loan. For a real mortgage this branch will not trigger, but guarding against division by zero avoids a formula error if a user enters 0.
The selling cost percentage (6 %) and purchase transaction cost percentage (4 %) are illustrative placeholders representing typical agent commissions and government duties in some markets. Replace these constants with figures accurate for your region. You may make them additional inputs if your audience spans multiple markets.
Step 5 — Add result rows below the separator
Row 12 on the calculator tab is the merged Result Fields separator (generated by the add-on). Row 13 holds result headings. Your result labels and formulas go from row 14 onward.
Enter the following. In column A enter the label; in column B enter the formula. The formula references the J-column helpers you just built — this keeps the result cells clean and avoids circular references.
| Row | A — Result label | B — Formula |
|---|---|---|
| 14 | Monthly mortgage payment ($) | =ROUND(J7,2) |
| 15 | Deposit amount ($) | =ROUND(J2,0) |
| 16 | Loan amount ($) | =ROUND(J3,0) |
| 17 | Estimated property value at sale ($) | =ROUND(J8,0) |
| 18 | Outstanding mortgage at sale ($) | =ROUND(J9,0) |
| 19 | Net sale proceeds ($) | =ROUND(J11,0) |
| 20 | Total buying outflows ($) | =ROUND(J12,0) |
| 21 | Total rent over period ($) | =ROUND(J13,0) |
| 22 | Deposit opportunity cost ($) | =ROUND(J14,0) |
| 23 | True net cost of buying ($) | =ROUND(J15,0) |
| 24 | Net saving from buying vs renting ($) | =ROUND(J16,0) |
| 25 | Verdict | =IF(J16>0,"Buying appears cheaper by $"&TEXT(ABS(ROUND(J16,0)),"#,##0"),"Renting appears cheaper by $"&TEXT(ABS(ROUND(J16,0)),"#,##0")) |
The verdict in row 25 compares the true net cost of buying (including opportunity cost, minus sale proceeds) against the total rent paid. A positive saving means buying came out ahead over the stated holding period.
Step 6 — Test with default values before publishing
Before publishing, verify every formula using the default inputs.
- Select cells E2:E10 (the nine default values).
- Copy them (Ctrl+C or Cmd+C).
- Click cell G2.
- Right-click and choose Paste special → Values only. This copies the numbers without overwriting G with formulas. Column G is overwritten by web submissions, so never put formulas there.
- Check that J2:J16 and B14:B25 now show numbers rather than errors.
Expected results with default inputs
Defaults: purchase price $400,000 · deposit 20 % · rate 6.5 % · term 30 years · holding 7 years · rent $2,000/month · growth 3 % · maintenance 1 % · opportunity cost 5 %.
| Result label | Expected value (approx.) |
|---|---|
| Monthly mortgage payment | $2,023 |
| Deposit amount | $80,000 |
| Loan amount | $320,000 |
| Property value at sale | $491,970 |
| Outstanding mortgage at sale | $283,980 |
| Net sale proceeds | $178,470 |
| Total buying outflows | $213,940 |
| Total rent over period | $168,000 |
| Deposit opportunity cost | $32,820 |
| True net cost of buying | $68,290 |
| Net saving from buying | $99,710 |
| Verdict | Buying appears cheaper by $99,710 |
Small rounding differences of a few dollars are expected due to floating-point arithmetic. If you see a large discrepancy, check that G2:G10 contain plain numbers, not formulas, and that J9 references J4 correctly.
Alternative scenario — short holding period
Change G6 to 3 (three-year hold). At three years, purchase transaction costs have not had time to be offset by equity and rent savings. With the other defaults unchanged, the true net cost of buying should exceed total rent paid, and the verdict should switch to Renting appears cheaper. This confirms the model captures the crossover effect described at the top of the article.
Frequently asked questions
Does the model account for rent increases over time?
No. The current model multiplies the monthly rent input by 12 and then by the holding period, which assumes rent stays flat. To model rent growth, replace J13 with a formula that compounds rent annually. For example, if you add a tenth input row for annual rent growth rate (stored in G11 after extending the input count to 10), you could use =G7*12*((1-(1+(G11/100))^G6)/(-(G11/100))) as a geometric series sum. This is an enhancement you can add once the base model works correctly.
Why does the opportunity cost matter so much?
When you pay a $80,000 deposit, you are choosing not to invest that cash elsewhere. If a diversified index fund returns 5 % annually, $80,000 grows to roughly $112,820 over seven years — a gain of $32,820 that you forgo. Ignoring this makes buying look cheaper than it really is. The opportunity cost input lets you adjust this assumption; if you believe your deposit money would otherwise earn nothing (perhaps kept in a low-interest savings account), you can set the rate to 1 or 2 % instead. For context on long-run market returns, the U.S. Federal Reserve statistical releases and similar central bank publications provide historical rate data for your region.
Can I share this calculator publicly without exposing my own data?
Yes, provided you built it in a dedicated sample workbook containing only example numbers, not real personal finances. The GSheetPress publishing step requires the spreadsheet to be shared so anyone with the link can edit it. As long as the file contains nothing sensitive, that is safe. Never use a workbook that also holds personal budgets, bank details, or other private information. See the GSheetPress support page if you need help managing workbook permissions.
Troubleshooting
Formula shows #DIV/0! or #VALUE!
- Check that G2:G10 contain plain numbers. If any cell shows a formula or text, the PMT and exponent calculations will fail.
- Confirm J4 is calculated as a number before J7 references it. Open J4 and press F2 to inspect the formula.
- If you entered 0 for the interest rate, the IF branch in J7 handles it — but entering a negative rate is not supported and will produce incorrect results.
The verdict always says Renting is cheaper regardless of inputs
- Check J16. If it is always negative, check J15 (net buy cost) and J11 (net sale proceeds). A common mistake is a missing minus sign in front of J11 in the J15 formula.
- Verify J10 is not larger than J8 (selling costs should not exceed property value).
The add-on does not appear in the Extensions menu
- Confirm the add-on is installed: Extensions → Add-ons → Manage add-ons. If GS2Web SaaS is absent, reinstall it from the Google Workspace Marketplace.
- Confirm you are signed in to the same Google account that holds the GSheetPress subscription. Entitlements are tied to the account email. Visit your GSheetPress account to verify which email is registered.
Result rows show the word FALSE or a blank instead of a number
- This usually means the formula in column B of the result row references a helper cell that is itself returning FALSE or empty. Open the referenced J-cell and check its own formula step by step.
- Make sure you pasted values into G2:G10 (not formulas) when testing. If G2 contains
=E2instead of a plain number, J2 will still work during spreadsheet testing, but on the published form the add-on overwrites G with the submitted value as a plain number — so this is fine for production but confirms you should use Paste Special during local testing to simulate real form behaviour.
Numbers look right in the sheet but wrong on the published form
- After any change to the sheet, you must republish. Go to Extensions → GS2Web SaaS → Calculator → Publish Calculator and follow the confirmation steps. Republishing the same existing calculator retains its public link.
- Check that the result rows (14 onward) are within the range the add-on is configured to display. If you added rows above row 14 after initial creation, the separator row may have shifted. For further help, visit the GSheetPress support page.
Practical tips for accurate results
- Transaction cost percentages (4 % purchase, 6 % sale) are illustrative. In the United Kingdom, for example, stamp duty land tax rates are tiered and differ from these figures. Always replace constants with locally verified values or make them user inputs.
- The maintenance allowance of 1 % is a rough average. Older properties or those in harsh climates may need 2–3 %. A new build may need less in the early years. Consider making this a transparent input so users can adjust it.
- Mortgage rates change. The default of 6.5 % reflects rates in some markets at the time of writing. Replace the default with the current prevailing rate in your audience's market.
- This model uses a fixed-rate mortgage assumption. Variable-rate mortgages introduce uncertainty that a simple PMT formula cannot capture without scenario inputs.
- For a deeper look at how calculators can be structured in GSheetPress, see the calculator feature overview. If you want to add a summary table output to your calculator, the table feature documentation explains how to embed tabular results alongside calculated fields.
Publish and test your web calculator
- Share the spreadsheet so the add-on can publish it. Click Share (top right), set General access to Anyone with the link, set the role to Editor, and click Done. Important: this setting allows anyone who obtains the spreadsheet URL to edit the file. Use only the dedicated sample workbook, never a file with personal data.
- Select the calculator tab in your workbook (the tab the add-on created).
- Click Extensions → GS2Web SaaS → Calculator → Publish Calculator.
- Wait for the confirmation message. The add-on will open or update a sheet called something like Calculator Link (the exact name may vary by add-on version).
- In the Calculator Link sheet, find the row where the Sheet Name column matches your calculator tab name (Rent vs Buy Analyser or whatever you named it).
- Click the URL in column C (Link, the public calculator link). Do not use column B (Edit Link), which opens the spreadsheet itself.
- If the row does not appear or the link looks stale, click Get Links (a button or menu option in the add-on) to refresh the sheet.
- On the public calculator page, enter the default test values: $400,000 purchase price, 20 % deposit, 6.5 % rate, 30-year term, 7-year hold, $2,000 rent, 3 % growth, 1 % maintenance, 5 % opportunity cost.
- Click the Calculate button on the web form and verify the results match the expected table in Step 6: monthly payment approximately $2,023, verdict Buying appears cheaper by approximately $99,710.
- Then change the holding period to 3 years and recalculate. Confirm the verdict switches to renting being cheaper, confirming the model responds correctly to holding-period changes.
- If you want to embed the calculator in a webpage, copy the iframe code from column D of the Calculator Link sheet and paste it into an HTML block on your site.
- Republishing the same calculator in future (after edits) retains the same public link — you do not need to update embedded iframes.