Spreadsheets

Spread an annual prepaid expense across 12 months in a sheet

Spread an annual insurance or software payment evenly across 12 months in a sheet, and see cash paid versus monthly cost, with formulas you can copy.

Editorial illustration for Spread an annual prepaid expense across 12 months in a sheet

To amortise an annual prepaid expense, divide the amount by the number of months it covers and show that figure in each month of cover. Your bank statement shows one big payment in a single month, while the cost sits evenly across twelve. A small schedule with EDATE and IF handles it, and it also tells you how much is still unused at 31 March.

Why does a monthly view need the spread?

Say you pay ₹14,400 for an annual insurance policy in October. If you look at spending month by month, October looks alarming and the other eleven months look light. Neither is the real picture. The policy costs ₹1,200 for each month it protects you, and that is the figure to compare with your other monthly costs.

The same happens with annual software subscriptions, a domain and hosting renewal, a professional membership, or a one-year warranty on an appliance.

How do you set up the schedule?

Keep the inputs in a small block at the top, and the schedule below it.

CellMeaningExample
B1Amount paid₹14,400
B2Months covered12
B3First month of cover (1st of the month)1-Oct-2026
B4Monthly cost=ROUND(B1/B2, 2)

Under it, list months in column A starting at A7. In A7 put =B3 and in A8 put =EDATE(A7,1), then fill down for as many months as you want to see, say 24.

What goes in the monthly cost column?

In B7 use a formula that gives the monthly cost only while the cover lasts:

=IF(AND(A7>=$B$3, A7<EDATE($B$3,$B$2)), $B$4, 0)

In C7 put the cash actually paid that month, so the first row picks up the whole premium:

=IF(A7=$B$3, $B$1, 0)

Fill both down. Column D can hold the running unused balance, which is the prepaid amount still sitting on the shelf:

=$B$1 - SUM(B$7:B7)

What does the cash-versus-cost comparison show?

MonthCash paidCost for the monthPrepaid left
Oct 2026₹14,400₹1,200₹13,200
Nov 2026₹0₹1,200₹12,000
Dec 2026₹0₹1,200₹10,800
Mar 2027₹0₹1,200₹7,200
Sep 2027₹0₹1,200₹0

Count the months from October to March and you get six. So by 31 March 2027 the cost recognised in that financial year is ₹7,200 and ₹7,200 is still prepaid for the next year. That split is exactly what your accountant will ask for at year end.

What if the amount does not divide evenly?

₹10,000 over 12 months is ₹833.33 a month, which does not add back to ₹10,000. Use ROUND for the first eleven months and let the last month take the difference: in the final row use =$B$1 - SUM(B$7:B17). A small fix in one row is better than a sheet that never balances.

Which payments are worth spreading?

  • Anything annual or half-yearly that is large relative to your monthly budget.
  • Payments where the benefit clearly runs month by month, such as insurance, software or a membership.
  • Skip the small ones. A ₹299 yearly renewal does not need its own schedule.

If you want a record of what was actually paid, SpendixAI keeps the real transaction with its account, so the year's total is there while this sheet shows the steady monthly cost.

What if you cancel the policy early?

Stop the schedule at the cancellation month. Count the months used, multiply by the monthly cost, and compare it with any refund you receive. If the policy ran four months and cost ₹1,200 a month, the cost recognised is ₹4,800; whatever the insurer refunds is separate. Write the refund in its own row on the sheet with the date, so the unused balance closes to zero rather than hanging around for years.

Next step: add a second block below for each other annual payment, point the schedule at a new amount and start date, and add the cost columns together for a steady monthly total.

SPENDIXAI JOURNAL

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

Explore your reports