Skip to main content
Renting vs Buying

The Rent vs Buy Spreadsheet

Pomegra Learn

The Rent vs Buy Spreadsheet

A simple seven-input spreadsheet resolves the rent-versus-buy decision far better than intuition or online calculators. You plug in your home price, down payment, mortgage rate, rent, inflation rates, expected returns, and hold period. The model outputs net present value: how much wealthier you are if you buy versus rent.

Key takeaways​

  • Seven inputs drive the decision: home price, down payment percentage, mortgage rate, annual rent, rent inflation rate, investment return rate, and years held.
  • The spreadsheet calculates net present value (NPV) by projecting all costs and benefits over your hold period, discounted to today's dollars.
  • An NPV greater than zero means buying wins; less than zero means renting wins.
  • The model is superior to ratio-based rules because it accounts for leverage, time value of money, and your specific hold period.
  • You can build this spreadsheet in Excel, Google Sheets, or Python; it requires no special software.

The seven inputs explained​

Input 1: Home purchase price The all-in cost to acquire the home. Do not use the asking price; use your expected actual purchase price after negotiation. Example: $450,000.

Input 2: Down payment percentage The fraction of purchase price you will fund with your own capital. Most mortgages require 3–20%. Example: 20% ($90,000).

Input 3: Mortgage interest rate The annual interest rate on the loan. Check current mortgage rates at Freddie Mac or your lender. Use a fixed rate (not a teaser ARM). Example: 6.5%.

Input 4: Annual rent The annual rent for a comparable unit. Query Zillow, Apartments.com, or local leasing agents. This must be for a home equivalent in size and location to the home you're considering buying. Example: $30,000 per year ($2,500 per month).

Input 5: Rent inflation rate The expected annual increase in rent. U.S. historical average is 2–3%. Some metros are higher; some lower. Use 2.5% as a baseline. Example: 2.5%.

Input 6: Investment return rate The annual return you expect from investing your down payment (and other capital) in equities or a balanced portfolio. Historical U.S. equity returns are 10% nominal (7% real inflation-adjusted). For a balanced portfolio, use 6–8%. Example: 7%.

Input 7: Hold period (years) How long you plan to own the home before selling. This is crucial and often ignored. Example: 10 years.

Building the model: skeleton and logic​

Create a spreadsheet with these columns:

  • Year (0, 1, 2, ..., N)
  • Home value (purchase price × appreciation rate^year)
  • Mortgage balance (declining as you pay down principal)
  • Rent (starting rent × (1 + inflation)^year)
  • Home costs (taxes + insurance + maintenance)
  • Mortgage payment (principal + interest; use amortization)
  • Equity if you sell (home value - mortgage balance - selling costs)
  • Wealth if you rent (down payment investment × (1 + return)^year + cumulative rent savings invested)

The spreadsheet compares these two paths year by year, then discounts them back to present value.

A worked example: $450,000 home, 10-year hold​

Inputs:

  • Home price: $450,000
  • Down payment: 20% = $90,000
  • Mortgage: 30-year loan, 6.5% rate, financing $360,000
  • Rent: $30,000 per year
  • Rent inflation: 2.5% annually
  • Expected investment return: 7% annually
  • Hold period: 10 years
  • Home appreciation: 3% annually (U.S. historical)
  • Annual home costs: 2.5% of home value (taxes + insurance + maintenance)
  • Selling costs: 6% of sale price

Year 0 (today):

  • If buy: Deploy $90,000 down. Net cash outflow: $90,000.
  • If rent: Invest the $90,000. Investment grows at 7%.

Year 1:

  • Mortgage payment (30-year, 6.5%): roughly $2,280/month = $27,360/year.

    • Of this, ~$23,400 is interest, ~$3,960 is principal.
    • Mortgage balance: $360,000 - $3,960 = $356,040.

  • Home value: $450,000 × 1.03 = $463,500.

  • Home costs (2.5% of value): $463,500 × 0.025 = $11,588.

  • Annual cash outflow for ownership: $27,360 (mortgage) + $11,588 (costs) = $38,948.

  • If rent: Rent = $30,000 × 1.025 = $30,750.

    • Down payment invested: $90,000 × 1.07 = $96,300.
    • Cumulative rent paid: $30,750 (you don't invest this; it's sunk).

Year 5:

  • Mortgage balance: roughly $310,000 (significantly reduced).

  • Home value: $450,000 × (1.03)^5 = $521,000.

  • Equity in home: $521,000 - $310,000 = $211,000.

  • Cumulative cash paid on mortgage: $27,360 × 5 = $136,800 (mostly interest).

  • If rent: Investment: $90,000 × (1.07)^5 = $126,100.

    • Rent paid: $30,000 × (1.025^5) = $33,800 per year; cumulative roughly $161,000.
    • You retained the $126,100 investment.

Year 10:

  • Mortgage balance: roughly $260,000.

  • Home value: $450,000 × (1.03)^10 = $603,600.

  • Equity in home: $603,600 - $260,000 = $343,600.

  • Less selling costs (6%): $603,600 × 0.06 = $36,216.

  • Net proceeds if you sell: $343,600 - $36,216 = $307,384.

  • If rent: Investment: $90,000 × (1.07)^10 = $176,900.

    • Cumulative rent paid: ~$329,000 (sunk).
    • Net wealth: $176,900 (investment only, since rent was paid).

Comparison:

  • Buy path net proceeds: $307,384.
  • Rent path net wealth: $176,900.
  • NPV of buying: $307,384 - $176,900 = $130,484.

Conclusion: In this scenario, buying wins by ~$130,000 over 10 years. But this assumes 3% home appreciation (conservative) and 7% investment returns (moderate). Change either assumption, and the verdict shifts.

Sensitivity analysis: how small changes flip the decision​

The spreadsheet's real power emerges when you vary assumptions.

Scenario: Home appreciation drops to 2% (overheated market).

  • Year 10 home value: $450,000 × (1.02)^10 = $549,000.
  • Equity: $549,000 - $260,000 - $36,000 = $253,000.
  • NPV of buying: $253,000 - $176,900 = $76,100.
  • Buying still wins, but by a smaller margin.

Scenario: Hold period is 5 years instead of 10.

  • Year 5 home value: $450,000 × (1.03)^5 = $521,000.

  • Mortgage balance: ~$325,000.

  • Equity: $521,000 - $325,000 = $196,000.

  • Less selling costs (6%): $31,260.

  • Net proceeds: $164,740.

  • Rent path (5 years): Investment $126,100.

  • NPV of buying: $164,740 - $126,100 = $38,640.

  • Buying still wins, barely. Transaction costs now consume a larger share of gains.

Scenario: Hold period is 3 years (very short).

  • Year 3 home value: $450,000 × (1.03)^3 = $491,300.

  • Mortgage balance: ~$345,000.

  • Equity: $491,300 - $345,000 = $146,300.

  • Less selling costs: $29,480.

  • Net proceeds: $116,820.

  • Rent path (3 years): Investment $110,210.

  • NPV of buying: $116,820 - $110,210 = $6,610.

  • Buying barely wins. A 1% appreciation shortfall flips the verdict to renting.

These scenarios show why time horizon is not a secondary detail—it is often the decisive factor.

Building the spreadsheet: step-by-step​

In Excel or Google Sheets:

  1. Create a table with columns: Year, Home Value, Mortgage Balance, Rent, Home Costs, Mortgage Payment, Home Equity, Investment Value.

  2. Rows: 0 through your hold period.

  3. Year 0 formulas:

    • Home Value: =purchase_price
    • Mortgage Balance: =(purchase_price - down_payment)
    • Rent: =annual_rent
    • Home Costs: =home_value * cost_percentage
    • Mortgage Payment: Use PMT function: =PMT(rate/12, years*12, -(purchase_price - down_payment))
    • Home Equity: =home_value - mortgage_balance
    • Investment Value: =down_payment * (1 + return_rate)^0

  4. Year 1 formulas (copy down for remaining years):

    • Home Value: =Year0_Home_Value * (1 + appreciation_rate)
    • Mortgage Balance: =PMT formulas track this; use amortization.
    • Rent: =previous_year_rent * (1 + rent_inflation)
    • Home Costs: =home_value * cost_percentage
    • Mortgage Payment: (unchanged from Year 0)
    • Home Equity: =home_value - mortgage_balance - (home_value * 0.06) [selling costs]
    • Investment Value: =down_payment * (1 + return_rate)^year

  5. At the end, calculate NPV: Home Equity (Year N) minus Investment Value (Year N).

In Python:

import numpy as np

home_price = 450000
down_pct = 0.20
rate = 0.065
annual_rent = 30000
rent_inflation = 0.025
returns = 0.07
hold_years = 10
appreciation = 0.03
home_costs_pct = 0.025

down = home_price * down_pct
principal = home_price - down

# Monthly mortgage payment
monthly_rate = rate / 12
months = 360
monthly_payment = principal * (monthly_rate * (1 + monthly_rate)**months) / ((1 + monthly_rate)**months - 1)

home_values = []
mortgage_balance = principal
for year in range(hold_years + 1):
    hv = home_price * (1 + appreciation)**year
    home_values.append(hv)
    
# Calculate net proceeds from selling
final_home_value = home_values[hold_years]
final_mortgage_balance = principal * (((1 + monthly_rate)**months - (1 + monthly_rate)**(months - hold_years*12)) / ((1 + monthly_rate)**months - 1))
equity = final_home_value - final_mortgage_balance
selling_costs = final_home_value * 0.06
net_proceeds = equity - selling_costs

# Investment value (down payment + saved rent invested)
investment_value = down * (1 + returns)**hold_years

npv = net_proceeds - investment_value
print(f"NPV of buying: ${npv:,.0f}")
if npv > 0:
    print("Buy")
else:
    print("Rent")

When the spreadsheet disagrees with gut feel​

The spreadsheet often contradicts intuition because intuition overweights recent trends and underweights time value of money.

You might feel that a home in an expensive market is "always" a good investment because prices have risen for five years. The spreadsheet asks: at current prices, what is the expected return going forward? If the price-to-rent ratio is 20, the expected return is only 3–4% annually—lower than stocks, and barely above inflation. Intuition says "buy into this momentum"; the spreadsheet says "wait or rent."

Conversely, a declining market might feel dangerous. But if the price-to-rent ratio has fallen to 12 and your hold period is 15 years, the spreadsheet says buying is prudent even if prices fall another 10% in year 1.

Trust the spreadsheet, not your gut.

Process​

Next​

The spreadsheet requires one input that is often guessed: hold period. How long do you actually plan to own? A 5-year hold reverses many decisions that a 20-year hold would favor. The next article quantifies breakeven horizons and shows why short-term ownership is almost always a renter's game.