Your emergency fund in months is the savings you have set aside divided by your average monthly essential spending. In Excel, take the balance in one cell, calculate the last six months of essential spend with SUMIFS, divide by six, and divide the balance by that average. The answer tells you how many months of essentials the money would cover. This guide does not tell you what the number should be, only how to measure it honestly.
Which spending counts as essential?
Decide on a list before you write any formula. Typical essentials are rent or home loan EMI, groceries, utilities, transport to work, insurance premiums, school fees and medicines. Dining out, travel and shopping are normally left out. Put your chosen categories on a sheet called Essentials in cells A2:A10, one per row. You can change this list later without touching the formula.
How do you get average monthly essential spend?
Assume your log is on a sheet called Log: Date in column A, Category in column B and Amount in column C, rows 2 to 1000. This formula adds essential spending over the six complete months before the current month, then divides by six:
=SUMPRODUCT(SUMIFS(Log!$C$2:$C$1000, Log!$B$2:$B$1000, Essentials!$A$2:$A$10, Log!$A$2:$A$1000, ">="&EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-6), Log!$A$2:$A$1000, "<"&DATE(YEAR(TODAY()),MONTH(TODAY()),1)))/6
The trick is that SUMIFS accepts a whole range as its category criterion and returns one total per essential category. SUMPRODUCT then adds those totals together. Put this formula in B2. In B1 type your emergency savings balance, counting only money kept for emergencies and not your wedding fund or next year's holiday money.
The months-covered cell is then:
=IF(B2>0, ROUND(B1/B2, 1), 0)
What does a worked example show?
Say your essentials over the last six complete months add up to ₹4,56,000. The average is ₹4,56,000 divided by 6, which is ₹76,000 a month. You have ₹2,28,000 in a separate savings account earmarked for emergencies.
| Item | Calculation | Result |
|---|---|---|
| Six-month essentials | Sum from the log | ₹4,56,000 |
| Average monthly essentials | ₹4,56,000 divided by 6 | ₹76,000 |
| Emergency savings | Balance you typed | ₹2,28,000 |
| Months covered | ₹2,28,000 divided by ₹76,000 | 3.0 |
Why use a six-month average instead of one month?
A single month can mislead. If you pay an annual insurance premium or school fees in April, one month looks heavy and the next looks light. Averaging six months smooths most of that. If lumpy yearly payments still distort the result, extend the window to twelve months by changing -6 to -12 and the final /6 to /12.
Is there a right number of months?
That depends on how steady your income is, how many people depend on it and what your commitments are, so no formula can answer it for you. A person with a fixed salary and no dependants might feel differently from a household with irregular income and a home loan. If you want a view on a target, a qualified financial planner can discuss it with your whole situation in front of them.
It helps to write the assumptions on the sheet itself: the date you last updated the balance, which categories count as essential and the window you used. When you look at the cell in a stressful month, a one-line note saves you from trusting a number whose basis you have forgotten.
What should you check each month?
- Is the savings balance in B1 the real, current figure from your passbook or app?
- Did any essential category get renamed in the log so SUMIFS stopped matching it?
- Does the Essentials list still match how you think about necessary spending?
- Has a large one-off, such as a medical bill, distorted the average? Note it rather than quietly deleting it.
Once the categories are clean, the same Essentials list also gives you a monthly figure you can compare against the previous six months, so you can see whether the cushion is growing because you save more or because you spend less.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

