Spreadsheets

Rent vs buy comparison sheet in Excel using your own inputs

A rent vs buy sheet in Excel with every assumption in a labelled cell: the same monthly budget either pays an EMI or pays rent and invests the rest.

Editorial illustration for Rent vs buy comparison sheet in Excel using your own inputs

A fair rent-versus-buy sheet gives both options the same monthly budget. The buyer pays EMI, maintenance and property tax; the renter pays rent and invests the down payment plus whatever is left of that budget. After N years, compare what each side holds: the buyer holds the home's value minus the loan still owed, and the renter holds the investment pot. Every figure depends on assumptions you type in, and the verdict can flip when they change.

Put every assumption in a labelled cell

All numbers below are invented to show the mechanics; replace each with your own.

  • Price ₹80,00,000, down payment 20% (₹16,00,000), loan ₹64,00,000.
  • Loan rate 8.5% a year, tenure 20 years, so the EMI in B12, =PMT(B3/12, B4*12, -B2), comes to about ₹55,541.
  • Maintenance ₹4,000 a month and property tax ₹12,000 a year (₹1,000 a month).
  • Equivalent rent ₹25,000 a month, rising 5% a year.
  • Assumed return on invested money 7% a year and assumed growth in the home's value, tried at three levels.
  • Comparison horizon: 10 years.

Cell map used in the formulas below: B1 price, B2 loan amount (=B1-down payment), B3 loan rate, B4 tenure in years, B5 maintenance per month, B6 property tax per month, B7 down payment, B8 yearly growth in home value, B9 first-year rent per month, B10 yearly rent rise, B11 return on invested money, B12 EMI.

What does each side pay and hold?

The buyer's monthly outgo is EMI + maintenance + tax, about ₹60,541. The renter pays ₹25,000 in year one and invests the difference of roughly ₹35,541, plus the ₹16,00,000 down payment kept in the pot from day one. Rent rises each year, so the difference shrinks slowly.

The formulas for the buyer's side

  1. Home value after N years: =B1*(1+B8)^N, where B8 is the assumed yearly growth in value.
  2. Loan still owed after N years: =-FV(B3/12, N*12, -B12, B2). The result is a positive balance.
  3. Buyer's equity: home value minus the loan owed.

The formulas for the renter's side

  1. One row per year. Rent per month: first year =B9, then =previous*(1+B10).
  2. Monthly difference invested: =B12+B5+B6-rent for that year.
  3. Pot at the end of the year: =previous pot*(1+B11/12)^12+FV(B11/12, 12, -difference). The first year's previous pot is the down payment.
  4. After year N, the last pot is the renter's holding.

What do the results look like?

With these invented inputs and 10 years, the equity and the pot come out as follows for three assumptions about growth in the home's value:

Home value growth a yearBuyer holds (equity)Renter holds (pot)Buyer minus renter
3%₹62,71,726₹84,01,772-₹21,30,046
5%₹85,51,552₹84,01,772₹1,49,780
7%₹1,12,57,606₹84,01,772₹28,55,834

At 5% growth the buyer holds ₹85,51,552 against ₹84,01,772. The ordering changes when the growth assumption moves, which is the point: the answer lives in the assumptions, not in the formulas.

Which input moves the answer most?

Usually the growth you assume in the home's value, because it works on the whole price and not only on the down payment. In the table above, shifting that one assumption by two percentage points swings the gap by roughly ₹23 lakh to ₹27 lakh, far more than any change in maintenance or property tax. Do not trust a result until you have seen how it behaves across a low, middle and high value for that cell and for the investment return.

How do I stress-test the result?

  • Change the growth in home value to something low, even zero.
  • Change the investment return to a low number and see how much of the gap disappears.
  • Make rent rise faster, and make the EMI rate a point higher.
  • Shorten the horizon to 5 years, where upfront costs weigh more.

This sheet informs a decision; it does not make it, and it is not advice on whether to buy or rent. Since it relies on the rent and costs you really pay, it helps to keep a record: SpendixAI can import your bank statements so that the rent, maintenance and EMI rows you type in come from what you actually paid.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports