Money tips

Bill and EMI reminder template in Google Sheets, with formulas

Build a bill and EMI reminder in Google Sheets: columns, next-due formulas, days-left, colour rules and a total due this week, with honest limits on alerts.

Editorial illustration for Bill and EMI reminder template in Google Sheets, with formulas

You can build a working bill and EMI reminder in Google Sheets in about twenty minutes: one row per bill, a formula for the next due date, a formula for days left, and a colour rule that highlights anything due within three days. A sheet will not send you an alert on its own, so pair it with a calendar event or look at it on payday. The columns and formulas below also work in Excel.

Which columns do I need?

ColumnHeadingWhat goes in it
ABill or EMIName, such as Home loan EMI
BAmountIn rupees
CDue dayDay of the month, 1 to 28
DNext due dateFormula
EDays leftFormula
FStatusFormula
GPaid this monthA tick box

Keep due days at 28 or below for monthly bills. Days like 31 do not exist in every month and the simple formula below would roll over into the next month.

What are the formulas?

  • Next due date in D2: =IF(DATE(YEAR(TODAY()), MONTH(TODAY()), C2)>=TODAY(), DATE(YEAR(TODAY()), MONTH(TODAY()), C2), DATE(YEAR(TODAY()), MONTH(TODAY())+1, C2)). It picks this month's date if it has not passed, else next month's.
  • Days left in E2: =D2-TODAY().
  • Status in F2: =IF(G2=TRUE, "Paid", IF(E2<=3, "Due soon", "OK")).
  • Total due in the next seven days: =SUMIFS($B$2:$B$30, $E$2:$E$30, "<=7", $G$2:$G$30, FALSE).

Format column D as a date. If the status shows an error, check that column C holds numbers, not text.

How do I colour the urgent rows?

  1. Select A2:G30.
  2. Open Format, then Conditional formatting.
  3. Choose Custom formula is and type =AND($E2<=3, $G2=FALSE).
  4. Pick a red or orange fill and save.

Now any unpaid bill due within three days stands out. Add a second rule with =$G2=TRUE for a grey fill on paid rows.

How do I handle an EMI with a fixed number of instalments?

Add two columns: instalments left and last instalment date. Say a loan has 24 instalments of ₹8,500 and you have paid 9. Instalments left are 24 - 9 = 15 and the amount still to pay is 15 x 8,500 = ₹1,27,500. The made-up figures are for illustration; take the real ones from your loan schedule. Use =H2-COUNTIF(I2:I30, "paid") style counting if you log each payment, or update the number by hand each month.

When does a sheet stop being enough?

A sheet is a good start for ten or twelve bills. When you also want your actual payments, categories and months of history, SpendixAI offers bills, budgets and savings goals on its Pro plan and imports statements as PDF, CSV or Excel. Until then, the sheet does the job.

How do I get a nudge before the weekend?

Add a column for the date to act: =WORKDAY(D2, -2) returns the date two working days before the due date, skipping Saturdays and Sundays. If your due date is Monday the 8th, the action date comes out as Thursday the 4th, so you are not scrambling on Sunday. WORKDAY also accepts a list of holiday dates as a third argument if you want to skip festival holidays.

Test the sheet with one dummy bill before trusting it. Put a due day of today's date plus two, tick nothing, and confirm that the days left show 2 and the row turns coloured. Then change the due day to yesterday's and confirm that the next due date jumps to next month. If both behave, copy the pattern down for the rest of your bills.

Monthly routine

  • Tick bills as paid when the bank debit appears.
  • Untick everything on the first of the month.
  • Check the loan schedule once a quarter against your list.
  • Remove closed loans so the sheet stays short.
SPENDIXAI JOURNAL

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

Explore your reports