Excel Budgeting and Forecasting Functions: A Practical Guide

Formulas & functions · Updated November 18, 2026

Excel budgeting and forecasting functions help you total planned and actual spending, compare results with a budget, and estimate future values from historical data. Start with SUM and SUMIFS for totals, IF for budget checks, and FORECAST.LINEAR or FORECAST.ETS for projections; the right choice depends on whether you are summarizing a plan or predicting what may happen next.

What budgeting and forecasting functions do

A budget records expected income and expenses for a future period, while a forecast estimates likely results using current information and past patterns. In Excel, formulas can connect those tasks: a budget table sets targets, actual transactions are summarized against them, and forecasting methods extend a trend or seasonal pattern into future periods.

These are not separate, dedicated “budget functions.” Excel provides general-purpose functions that can be combined in a workbook. For example, SUM adds a planned expense column, SUMIFS totals transactions that meet category and date conditions, and IF labels an over-budget result. Forecasting functions use historical observations to estimate later values. A formula can calculate consistently, but it cannot guarantee that assumptions or source data are sound.

A practical workbook commonly separates assumptions, budget targets, transaction records, and summary outputs. This makes it easier to see whether a result comes from a changed assumption, a new transaction, or a formula. Use consistent date values and category names, and avoid mixing manually typed totals into ranges that are meant to calculate automatically.

Build a budget with core functions

Begin with a simple table in which expense categories appear in A2:A5 and their monthly planned amounts appear in B2:B5: Rent, Groceries, Transport, and Utilities, with amounts of $1,200, $500, $400, and $900. A total at the bottom gives the planned monthly outflow.

With planned amounts in B2:B5, enter this formula in B6:

=SUM(B2:B5)

The result is $3,000: 1,200 + 500 + 400 + 900. SUM is useful for totals, but it does not distinguish categories or periods; for that, use conditional totals.

For actual spending, suppose transaction categories are in A2:A5 and amounts are in C2:C5. If the categories are Rent, Groceries, Rent, and Utilities, and the amounts are $1,500, $480, $200, and $850, use this formula to total Rent:

=SUMIF(A2:A5,"Rent",C2:C5)

The result is $1,700, the sum of the two Rent entries. SUMIF applies one condition. SUMIFS applies multiple conditions, which is handy when a transaction list contains dates, categories, and amounts. Its sum range comes first, followed by pairs of criteria ranges and criteria.

For a dated transaction list with dates in A2:A13, categories in B2:B13, and amounts in C2:C13, put a date within May 2026 in F1. This formula totals Utilities transactions dated in that month:

=SUMIFS(C2:C13,A2:A13,">="&EOMONTH(F1,-1)+1,A2:A13,"<="&EOMONTH(F1,0),B2:B13,"Utilities")

If the May Utilities entries are $120 and $80, the result is $200. EOMONTH identifies the month’s final date, and the first date is calculated from the previous month-end. The date range is inclusive. Keep the date column as actual Excel dates rather than text that merely looks like a date.

Compare actual results with the plan

A budget comparison usually needs a variance, not just two totals. If C2 contains a budget of $500 and D2 contains actual spending of $540, the difference is $40 over plan. You can calculate actual minus budget directly, then use IF to turn that value into a readable status.

With budget in C2 and actual spending in D2, use:

=IF(D2>C2,"Over budget","Within budget")

The result is “Over budget.” This version treats an exact match as within budget. If your reporting convention needs a separate “On budget” label, add another condition with nested IF logic or use a separate comparison formula. Decide whether an overspend should be shown as a positive or negative variance and label the column clearly; either convention can work if it is applied consistently.

Percentage variance helps compare categories of different sizes. With the same $500 budget in C2 and $540 actual in D2, use:

=IFERROR((D2-C2)/C2,0)

The result is 8%. Format the cell as a percentage. IFERROR returns zero if the calculation produces an error, such as when the budget is zero; that can make a report look less informative than it is. For a budget that may be zero, consider displaying a label such as “N/A” instead, or handle that case explicitly so readers do not mistake a missing percentage for a genuine zero variance.

Forecast future values from past data

Excel offers different approaches to forecasting. FORECAST.LINEAR estimates a value along a straight-line relationship between known observations. It can be a reasonable starting point for a steady trend, but it does not model recurring seasonal changes. FORECAST.ETS uses an exponential smoothing method intended for time-series data and can account for seasonality when the data supports it. Neither function knows about upcoming business changes unless you represent those changes in the assumptions or model.

For a simple linear example, put period numbers 1 through 4 in A2:A5 and revenue values $100, $110, $120, and $130 in B2:B5. To estimate period 5, use:

=FORECAST.LINEAR(5,B2:B5,A2:A5)

The result is $140. The observations rise by $10 each period, so a straight-line extension adds another $10. This example is deliberately simple: real sales or costs can fluctuate, and a trend should not automatically be treated as a reliable prediction. Check whether the period numbers or dates are evenly spaced and whether the historical period is representative.

For monthly data with dates in A2:A13, matching values in B2:B13, and a target date in A14, FORECAST.ETS can estimate the value for A14. Its optional seasonality argument can be set to 12 when the data is monthly and an annual pattern is appropriate. This method needs a suitable history of time-series observations; a short or irregular series may not support a meaningful seasonal estimate. Review Excel’s result against a chart and a straightforward baseline, rather than assuming a sophisticated method is automatically more accurate.

Use financial functions carefully

Some budgets need to account for regular loan payments. PMT calculates a fixed payment for a loan using a constant interest rate and number of periods. For an illustrative $12,000 loan, a nominal annual rate of 6%, monthly payments, and a 36-month term, use:

=PMT(6%/12,36,-12000)

The result is approximately $365.06 per month. The annual rate is divided by 12 to produce a monthly rate, and the term is expressed as 36 monthly periods. The negative present value makes the payment display as a positive cash outflow. This is a simplified calculation: fees, variable rates, payment timing, and the lender’s specific terms can change the real amount. Check actual terms with the lender or a qualified professional. This example is for spreadsheet illustration, not financial advice.

Other financial functions may be useful for specialized schedules, but first confirm that their assumptions fit the question. A payment formula does not decide whether a loan is affordable, and a forecast does not account for an event that is absent from its inputs. For a household or business budget, document the rate, time period, and assumptions beside the result so another reader can understand it.

Choose a method that fits the data

Use the simplest approach that answers the question. A monthly spending plan often needs SUMIFS and a comparison against fixed targets, not a statistical forecast. A sales projection based on a gradual, stable trend may be suitable for FORECAST.LINEAR. A series with recurring annual peaks and enough consistent history may be a candidate for FORECAST.ETS.

  • Use SUM to add a known range, such as all monthly budget amounts.
  • Use SUMIF or SUMIFS to summarize transactions by one or several conditions, such as category and month.
  • Use IF to create labels or apply a simple decision rule, such as flagging an overspend.
  • Use FORECAST.LINEAR when a straight-line relationship is a reasonable assumption.
  • Consider FORECAST.ETS for time-series data where a recurring seasonal pattern is plausible and the observations are sufficiently regular.

Compare a forecast with recent actuals and, where possible, test it on earlier periods: make a forecast using only data available at that past point, then compare the estimate with what actually happened. This does not guarantee future accuracy, but it can reveal whether a method consistently misses turning points or seasonal peaks. Write down assumptions such as expected price changes, hiring, or one-time expenses separately from the function’s statistical output.

Common spreadsheet problems

Incorrect totals often come from inconsistent categories, dates stored as text, or ranges that stop before the newest transaction. SUMIFS requires matching range sizes; if its sum range and criteria ranges do not cover corresponding rows, results may be wrong or an error may appear. Use a clearly maintained transaction table and check that new rows are included in formulas. Excel tables can help formulas and references expand as records are added, though a workbook still needs sensible column names and clean data.

Another common issue is confusing budget with forecast. A budget is a target or approved plan; a forecast is an estimate based on current evidence. If an organization revises a forecast after spending changes, that does not necessarily mean the original budget changed. Keeping separate columns or sheets for original budget, actuals, and current forecast preserves that distinction.

Forecasts can also mislead when unusual one-off events dominate the history, when dates are missing, or when the business has changed. A linear trend projected far beyond its source data can produce unrealistic values, while seasonality detection cannot explain a new product launch or policy change by itself. Plot the historical data, investigate outliers, and make any manual adjustments visible instead of silently changing source figures.

Workflow and version notes

In Excel for Microsoft 365 on Windows, a useful workflow is to create a transaction table, add separate budget targets, summarize transactions by month and category with SUMIFS, and then calculate variances. Add a chart only after the underlying totals reconcile with the source records. For a time-series projection, Excel’s Data > Forecast Sheet command can create a forecast visualization from a date-and-value series; review the options and output rather than treating the generated chart as a guarantee.

Many commonly used functions here, including SUM, SUMIF, SUMIFS, IF, IFERROR, EOMONTH, PMT, and FORECAST.LINEAR, are available in modern desktop Excel. FORECAST.ETS is available in current Excel releases, including Microsoft 365. Excel for the web and Mac may present some commands or interface details differently, and availability can depend on the exact release. The formulas shown use commas as argument separators; regional settings may require semicolons in formulas entered on some systems. XLOOKUP, FILTER, and other newer functions are not required for the basic workflow described here.

Frequently Asked Questions

What is the best Excel function for a monthly budget?
There is no single best function. SUM totals a budget column, while SUMIFS is usually more useful for actual transactions that need to be grouped by month and category. Use IF for a simple status check against a target.

What is the difference between FORECAST and FORECAST.LINEAR?
FORECAST is a legacy function name for linear forecasting, while FORECAST.LINEAR states that method explicitly. Both estimate a value from known x and y observations; neither models seasonal patterns.

Can Excel forecast expenses?
Yes. Provide historical expense values with corresponding dates or periods and use an appropriate method such as FORECAST.LINEAR or FORECAST.ETS. The output is an estimate, so account for known future changes and check whether the history represents expected conditions.

Why does SUMIFS return zero?
Check that the criteria match the source data exactly, that dates are real date values, and that the sum and criteria ranges cover the same records. Extra spaces or inconsistent category spelling can also prevent a match.

Should I use a budget or a forecast for planning?
Use a budget to record the plan or target and a forecast to estimate likely results based on current information. Keeping both lets you compare performance with the original plan while still updating expectations as circumstances change.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.