Spreadsheets

Fixed vs variable expenses: percentage formula in a sheet

Split spending into fixed and variable expenses in a sheet with a category lookup and SUMIFS, then see what percentage of the month is already committed.

Editorial illustration for Fixed vs variable expenses: percentage formula in a sheet

Tag each category as Fixed or Variable with a lookup table, add the tag to every transaction, then use SUMIFS to total each type and divide by total spend. In the example below, ₹32,500 of fixed spending in a ₹60,000 month means about 54 percent of the month was already committed before you decided anything.

What counts as fixed and what counts as variable?

Fixed expenses are costs that stay roughly the same and are owed whether or not you change your habits: rent, EMIs, insurance premiums, school fees, a broadband plan, society maintenance. Variable expenses move with your choices: groceries, dining, fuel, shopping, entertainment. Some sit between the two, like electricity, which has a base charge but moves with use. Pick a side for each and stay with it, noting the grey ones.

How do I tag categories with a lookup?

  1. Make a sheet called Map with Category in column A and Type in column B. List each category once, with Fixed or Variable beside it.
  2. In your Log, add column E called Type. Assuming Category is in B, put in E2: =XLOOKUP(B2,Map!$A$2:$A$30,Map!$B$2:$B$30,"Unmapped"). This needs Excel 365 or 2021, or Google Sheets.
  3. For older Excel, use =IFERROR(VLOOKUP(B2,Map!$A$2:$B$30,2,FALSE),"Unmapped") instead.
  4. Fill the formula down the Log.
  5. Filter column E for Unmapped. Any row showing it has a category missing from the Map; add it.

How do I total each type and get the percentage?

Use a small summary block. Fixed total: =SUMIFS($C$2:$C$500,$E$2:$E$500,"Fixed"). Variable total: =SUMIFS($C$2:$C$500,$E$2:$E$500,"Variable"). Fixed share: =H2/(H2+H3), where H2 holds the fixed total and H3 the variable total. For a single month, add the date pair to each SUMIFS, using the month's start date and EDATE to mark the end.

CategoryTypeSpent (₹)
RentFixed20,000
Home loan EMIFixed10,000
Insurance premium, monthly shareFixed2,500
GroceriesVariable12,000
Dining outVariable5,000
FuelVariable4,000
ShoppingVariable6,500

Fixed adds up to 20,000 plus 10,000 plus 2,500, which is 32,500. Variable is 12,000 plus 5,000 plus 4,000 plus 6,500, which is 27,500. Total spending is 60,000. Fixed share is 32,500 divided by 60,000, about 54.2 percent, and variable is about 45.8 percent. With income of ₹80,000, fixed costs take 32,500 divided by 80,000, about 40.6 percent of income.

A second view is useful: committed spending as a share of income, which is the fixed total divided by income. If income dips in a month, say by a delayed payment, the fixed bills still arrive, and this figure shows how tight that month feels. Track it beside the fixed share of spending, since the two answer different questions.

What does the split tell me?

  • A high fixed share means less room to adjust in a bad month; the variable part is where you can act quickly.
  • A rising fixed share over a year often points to a new EMI or a rent increase, not everyday habits.
  • A variable line that swings widely, such as dining or shopping, is the first place to look when a month overshoots.
  • Compare fixed spending with income, not only with total spend, to see how much of your income is spoken for.

What should I double-check?

  • Every category is mapped; the Unmapped filter finds the rest.
  • Category names in the Log match the Map exactly, including spelling and spaces.
  • The fixed and variable totals add up to the overall total spend.
  • One-off costs like a repair are tagged as variable, so they do not distort the fixed figure.

If your category list changes often, SpendixAI lets you create categories and sub-categories and keeps corrections as history for later imports, so the tagging work carries forward; the Fixed or Variable idea still lives in your own sheet, as a column you decide.

SPENDIXAI JOURNAL

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

Explore your reports