Spreadsheets

EMI-to-income ratio in Excel: set up the formula

Set up the EMI-to-income ratio formula in Excel: pull total EMIs from your expense log with SUMIFS, divide by income and flag the cell above your own limit.

Editorial illustration for EMI-to-income ratio in Excel: set up the formula

The EMI-to-income ratio is your total monthly EMIs divided by your monthly income, shown as a percentage. In Excel you get the EMI total with SUMIFS on a Loan EMI category in your expense log, then divide it by the income cell. Add one conditional-formatting rule and the cell turns red when the ratio crosses a limit that you set yourself.

What do you need in the sheet before the formula works?

Two things: a log where every EMI is tagged with the same category name, and a small summary block. Say your log is on a sheet called Log with Date in column A, Category in column B, Amount in column C and Merchant in column D, with data from row 2 to row 500. Every home loan, bike loan and phone EMI debit gets the category Loan EMI, spelled identically each time. SUMIFS treats "Loan EMI" and "Loan EMIs" as different categories, so use a dropdown list to keep the spelling clean.

Then build the summary block on a second sheet. These cells are what the formulas below refer to:

  • B1: first day of the month you are checking, for example 1-Oct-2026 entered as a real date
  • B2: your monthly income for that month, typed in or pulled from the log
  • B3: total EMIs, which is the SUMIFS formula below
  • B4: the ratio
  • B5: your own limit, for example 35%

How do you total the EMIs with SUMIFS?

Put this in B3. It adds every Loan EMI amount dated from the first of the month up to, but not including, the first of the next month:

=SUMIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, "Loan EMI", Log!$A$2:$A$500, ">="&B1, Log!$A$2:$A$500, "<"&EDATE(B1,1))

The ratio in B4 is a simple division, wrapped so that an empty income cell does not show an error:

=IF(B2>0, B3/B2, 0)

Format B4 as a percentage through Home, Number, Percentage. If B1 is blank or typed as text, the dates will not compare properly, so check that it is right-aligned like a number.

What does it look like with real arithmetic?

Take a made-up month. Income is ₹80,000. The log holds three Loan EMI rows. The limit you chose in B5 is 35%.

CellWhat it holdsValue
EMI row 1Home loan₹24,000
EMI row 2Bike loan₹4,500
EMI row 3Phone EMI₹2,000
B3Total EMIs₹30,500
B4₹30,500 divided by ₹80,00038.1%
B5Your limit35%

At 35% your EMI ceiling would be ₹28,000, so this month is ₹2,500 over the line you set. That is a signal to look at, not a verdict. A ratio that jumps for one month may simply be a quarterly EMI landing together with a lower income month.

How do you make the cell warn you above your limit?

  1. Select B4.
  2. Choose Home, Conditional Formatting, New Rule, then "Use a formula to determine which cells to format".
  3. Enter =$B$4>$B$5 as the formula.
  4. Click Format, pick a red fill, and confirm.

Now the cell changes colour only when B4 is higher than whatever number sits in B5. Change B5 to 30% or 40% and the warning follows without editing the rule.

Which income should you divide by, and what else can you track?

Decide once whether you divide by take-home pay or by gross income, and keep using the same one. Lenders may define income their own way, so for anything involving a loan application, ask the lender how they calculate it rather than assuming your sheet matches.

If your income varies, replace B2 with an average of the last few months, for example =AVERAGE(B8:B10) pointing at three months of income cells. You can also copy the block across for each month and chart the ratio to see whether your EMIs are shrinking against income or not.

When does a sheet stop being enough?

Tagging every EMI by hand is the weak point. If you would rather import statements and let the categories fill in, SpendixAI can take a bank statement in PDF, CSV or Excel, let you review the rows before saving, and hold your own rules for what counts as a loan EMI. Until then, this block keeps the maths honest.

SPENDIXAI JOURNAL

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

Explore your reports