Spreadsheets

Yearly and per-day cost of subscriptions with formulas

Work out the yearly and per-day cost of subscriptions in a sheet: a billing-frequency column, IFS to annualise, and a per-day figure.

Editorial illustration for Yearly and per-day cost of subscriptions with formulas

To annualise a subscription, multiply the amount by how many times a year it bills: 12 for monthly, 4 for quarterly, 2 for half-yearly, 1 for annual. One IFS formula does it across a list, and dividing the yearly cost by 365 gives the cost per day. Seeing ₹99 a month as ₹1,188 a year changes how you judge it.

How should the list be set up?

Columns: A service, B amount per payment, C billing frequency typed as Monthly, Quarterly, Half-yearly or Annual, D yearly cost, E per-day cost, F next renewal date. Keep the spelling of the frequency identical in every row, or use a drop-down (Data, Data validation, List of items).

What is the IFS formula for the yearly cost?

In D2:

  • =IFS(C2="Monthly",B2*12,C2="Quarterly",B2*4,C2="Half-yearly",B2*2,C2="Annual",B2)

IFS tests each condition in order and returns the matching result. It works in Excel 2019 and 365 and in Google Sheets. In older Excel, build a small table of frequency and multiplier in H2:I5 and use =B2*VLOOKUP(C2,$H$2:$I$5,2,FALSE). That version also makes it easy to add Fortnightly later. If a frequency is misspelt, IFS returns #N/A, which is a helpful warning.

How do I get the per-day and per-month figures?

  • Per day, E2: =D2/365. In a leap year this is a hair off, which does not matter here.
  • Per month, if you want it: =D2/12.
  • Total per year: =SUM(D2:D30) at the bottom of the list.

A hypothetical list with the arithmetic done:

ServicePayment (₹)BillingPer year (₹)Per day (₹)
Video streaming499Quarterly1,9965.47
Music59Monthly7081.94
Cloud storage130Monthly1,5604.27
Plan A149Monthly1,7884.90
Software3,599Annual3,5999.86

Check the arithmetic: video streaming at 499 four times a year is ₹1,996, and 1,996 divided by 365 is about ₹5.47 a day. The yearly total of the five rows is 1,996 plus 708 plus 1,560 plus 1,788 plus 3,599, which is ₹9,651. Divided by 12, that is ₹804.25 a month, and divided by 365, about ₹26.44 a day.

The same sheet settles the monthly-versus-annual question. Say a service charges ₹149 a month or ₹1,499 for a year. Twelve monthly payments cost 149 times 12, which is ₹1,788, so the annual plan is ₹289 cheaper. In months, 1,499 divided by 149 is about 10.06, so the annual plan only pays off if you would really use the service for roughly ten months or more. Add a column with =C2/B2 for that break-even.

How do I stop paying for forgotten renewals?

Add the start or last-payment date in G and compute the next renewal in F with =EDATE(G2,12) for annual plans, =EDATE(G2,3) for quarterly, or by frequency: =EDATE(G2,IFS(C2="Monthly",1,C2="Quarterly",3,C2="Half-yearly",6,C2="Annual",12)). Then colour any renewal within 30 days of today using conditional formatting with the formula =AND($F2>=TODAY(),$F2<=TODAY()+30). An annual plan is the easy one to miss, since it bills once and the charge is large when it comes.

Free trials deserve a row too. Put the trial's end date in the renewal column and the amount that will be charged afterwards in the payment column, so the sheet warns you before the first payment and not after it. A yearly figure beside a trial makes the decision simpler: is this worth ₹1,788 a year, or not?

Is it worth keeping a sheet for this?

For a dozen subscriptions, yes. A sheet is quick and shows the true yearly figure. If your subscriptions are scattered across several cards and UPI mandates, SpendixAI Pro also has bills alongside budgets and savings goals, which may be easier than maintaining dates by hand.

Review checklist

  • List every recurring charge from three months of statements.
  • Annualise each one.
  • Note the renewal date.
  • Decide, for each, keep, pause or cancel.
SPENDIXAI JOURNAL

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

Explore your reports