Spreadsheets

TREND function: project the next six months of spending

Use the TREND function in Excel to project six months of spending in one spill formula, chart actual against projected, and avoid array-entry mistakes.

Editorial illustration for TREND function: project the next six months of spending

TREND fits a straight line through your past monthly totals and returns a whole list of projected values at once. The syntax is =TREND(known_y's, known_x's, new_x's): your totals, the month numbers that go with them, and the month numbers you want projected. In Excel 365 and 2021 the six results spill into the cells below on their own.

How do I lay the sheet out?

  1. Put the month numbers 1 to 12 in A2:A13 and the twelve past totals in B2:B13.
  2. Put the next six month numbers, 13 to 18, in A14:A19. Type the month names, for instance Nov 2026 to Apr 2027, in a column beside them for readability.
  3. In C14 enter =TREND($B$2:$B$13,$A$2:$A$13,$A$14:$A$19)

In Excel 365 or 2021 press Enter and the six results fill C14:C19. In an older Excel, select C14:C19 first, type the formula, and confirm with Ctrl+Shift+Enter; Excel then wraps the formula in braces and treats the six cells as one block. If the spill area is not empty, 365 shows a #SPILL! error until you clear it. A one-cell version that builds its own month numbers is =TREND($B$2:$B$13,$A$2:$A$13,SEQUENCE(6,1,13,1)).

What do the projections look like?

Say your last twelve totals, in made-up rupees, were 30,000; 31,000; 33,000; 32,000; 35,000; 36,000; 38,000; 37,000; 40,000; 41,000; 43,000 and 42,000. They add up to ₹4,38,000, an average of ₹36,500, and the fitted line rises by about ₹1,203 a month. TREND then returns:

MonthMonth numberProjected spend (₹)
Nov 20261344,318
Dec 20261445,521
Jan 20271546,724
Feb 20271647,927
Mar 20271749,129
Apr 20271850,332

Each projection is about ₹1,203 above the one before, since a straight line adds the same step every time. Six months of this adds up to ₹2,83,951, a handy figure for a half-year cash plan, as long as you remember where it came from.

How do I chart actual against projected?

  • Make column B the actual totals for months 1 to 12 and column C the projections for months 13 to 18, leaving the cells that do not apply empty.
  • To join the two lines, set C13 to =B13 so the projected line starts where the actual one ends.
  • Select the month names and both columns, choose Insert, Line, and format the projected series as a dashed line in another colour.
  • Empty cells are left as gaps by default. If your chart draws them as zero, use Select Data, Hidden and Empty Cells, and choose Gaps.

How is TREND different from FORECAST.LINEAR?

Both draw the same straight line. FORECAST.LINEAR answers for one month number, while TREND answers for many at once. If you give TREND a single new month, you get the same figure as FORECAST.LINEAR. TREND also has a fourth argument that forces the line through zero; leave it out for spending, because there is no reason a household with no history would start at zero.

What should make me doubt the projection?

  • A festival or school-fee month inside the next six. The line cannot see them coming.
  • One lumpy month in the past twelve, which tilts the whole line.
  • A big change in your life, like a move or a new EMI, after which old months no longer describe you.
  • Fewer than about nine or ten past months, which makes the line unstable.

What to do with the result

Treat the six numbers as a starting plan, put them next to what you actually spend each month, and reset the exercise when the gap grows. Keeping totals tidy by category helps, and SpendixAI reports can supply monthly figures from imported statements so you do not retype them.

SPENDIXAI JOURNAL

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

Explore your reports