FORECAST, TREND, SLOPE and RSQ Equivalents in Python

A trend line is the first forecast most reports make: sales are growing by about seven units a month, so next quarter will look like this. In Excel it is SLOPE and INTERCEPT, TREND to extend a column of fitted values, FORECAST.LINEAR for a single projected point, and RSQ or CORREL to say how well the line fits. All of them are ordinary least-squares regression on one variable, which NumPy computes in one call. This page, part of Excel Formula Equivalents in pandas, reproduces each function — the results below match the same formulas recalculated in LibreOffice to many decimal places — and then applies them per product, with dates, and with the caution a straight-line forecast deserves.

Prerequisites

pip install numpy pandas openpyxl

monthly.xlsx holds eight months of sales for two products: Widgets, growing steadily, and Gadgets, nearly flat with noise.

Step 1 — SLOPE, INTERCEPT, RSQ and CORREL

numpy.polyfit(x, y, 1) fits a straight line and returns its coefficients, slope first. numpy.corrcoef gives the correlation, and its square is RSQ:

import numpy as np
import pandas as pd

monthly = pd.read_excel("monthly.xlsx")
w = monthly[monthly["product"] == "Widgets"].reset_index(drop=True)
x = np.arange(1, len(w) + 1)            # period numbers 1..8
y = w["sales"].to_numpy(dtype=float)

slope, intercept = np.polyfit(x, y, 1)
r = np.corrcoef(x, y)[0, 1]
print(f"SLOPE {slope:.6f}  INTERCEPT {intercept:.6f}  CORREL {r:.6f}  RSQ {r**2:.6f}")

The slope is 7.404762 units per month and the intercept 112.928571, with a correlation of 0.982 and an R² of 0.965 — identical to =SLOPE(B2:B9, A2:A9), =INTERCEPT(...), =CORREL(...) and =RSQ(...) over the same numbers. Note the argument order: Excel's regression functions take the y values first (SLOPE(known_ys, known_xs)), while polyfit takes x first. Swapping them in Excel gives the regression of x on y, a different line — a mistake worth checking for when porting a workbook.

Step 2 — TREND and FORECAST.LINEAR

TREND returns fitted values for known or new x values, and FORECAST.LINEAR a single projected value. Both are the fitted line evaluated at x:

fitted = np.polyval([slope, intercept], x)
future_x = np.array([9, 10, 11])
projection = slope * future_x + intercept
print(np.round(fitted, 1).tolist())
print(np.round(projection, 2).tolist())

The projection for period 9 is 179.57 and for period 10 is 186.98 — =TREND(B2:B9, A2:A9, 9) and =FORECAST.LINEAR(10, B2:B9, A2:A9) give the same figures. The older FORECAST function is identical to FORECAST.LINEAR; Excel kept it for compatibility.

Step 3 — How far off is the line? STEYX

A projection without an error estimate invites false precision. Excel's STEYX is the standard error of the fitted values — the typical distance of the actual points from the line — computed from the residuals with n − 2 degrees of freedom:

residuals = y - fitted
steyx = np.sqrt((residuals ** 2).sum() / (len(y) - 2))
print(round(steyx, 6))
print("roughly:", [f"{p:.0f} ± {2 * steyx:.0f}" for p in projection])

STEYX is 3.755, matching the spreadsheet. A rough band of two standard errors around each projection — about ±8 units here — is a far more honest thing to show than a single number, though it understates the uncertainty of points far beyond the data, where the slope's own error grows.

Step 4 — Dates as the x axis

When a sheet uses the date column as x — =SLOPE(B2:B9, A2:A9) with real dates in column A — Excel regresses on date serial numbers, so the slope is a change per day. Python needs numbers too; convert the dates to days since a start date to get the same per-day slope, or keep period numbers for a per-month one:

days = (w["month"] - w["month"].iloc[0]).dt.days.to_numpy()
per_day, _ = np.polyfit(days, y, 1)
print(f"{per_day:.4f} per day  ~ {per_day * 30.44:.2f} per month")

Months have different lengths, so the per-day slope converted to a month is close to, but not exactly, the period-number slope of 7.40. Choose the x axis that matches the question: period numbers treat every month as one step, which is what monthly reports usually mean; days are right for irregularly spaced observations. Either way, state the unit next to the slope.

Step 5 — A trend for every product

The same fit applied per group gives each product its own slope and fit quality — a table Excel would need a block of formulas per product to build:

def trend_stats(group):
    xs = np.arange(1, len(group) + 1)
    ys = group["sales"].to_numpy(dtype=float)
    m, c = np.polyfit(xs, ys, 1)
    r2 = np.corrcoef(xs, ys)[0, 1] ** 2
    return pd.Series({"slope_per_month": round(m, 2), "r2": round(r2, 3), "next_month": round(m * (len(ys) + 1) + c, 1)})

trends = monthly.sort_values("month").groupby("product")[["sales"]].apply(trend_stats)
print(trends)

Widgets grow by 7.40 a month with an R² of 0.96 — a trend worth stating. Gadgets grow by 1.70 a month with an R² of 0.82 on a small, noisy base; the slope is real but the projection is less reliable. Reporting R² next to each slope stops readers treating every line as equally trustworthy, and a minimum — say 0.7 — can decide whether a projection is shown at all. Sort by month before fitting: the period numbers assume rows are in time order.

Put the formulas in the workbook

When readers will add next month's figure themselves, write the trend as Excel formulas next to the data instead of static numbers, so the slope and projection update with the sheet:

from openpyxl import load_workbook

w.assign(period=x)[["period", "month", "sales"]].to_excel("widgets_trend.xlsx", index=False)
wb = load_workbook("widgets_trend.xlsx")
ws = wb.active
n = ws.max_row
ws["E1"], ws["F1"] = "slope per month", f"=SLOPE(C2:C{n},A2:A{n})"
ws["E2"], ws["F2"] = "R squared", f"=RSQ(C2:C{n},A2:A{n})"
ws["E3"], ws["F3"] = "next month", f"=_xlfn.FORECAST.LINEAR(MAX(A2:A{n})+1,C2:C{n},A2:A{n})"
wb.save("widgets_trend.xlsx")
print([ws[f"F{i}"].value for i in (1, 2, 3)])

FORECAST.LINEAR is one of the newer functions that must be stored with the _xlfn. prefix, or Excel shows #NAME?; Fix #NAME? in Formulas Written from Python explains why. SLOPE and RSQ are old enough to need no prefix. Recalculated, the three cells give the same 7.40, 0.965 and 179.6 as the Python code.

Step 6 — Where a straight line stops being a forecast

A linear trend assumes the next months continue the recent pattern at a constant rate. That is a reasonable short-term summary and a poor long-term forecast: it cannot see seasonality, saturation or a change in direction, and extrapolating twelve months from eight months of data mostly measures optimism. Excel's FORECAST.ETS handles seasonality with exponential smoothing; Python libraries such as statsmodels offer similar methods, but their algorithms and defaults differ, so do not expect them to reproduce Excel's numbers. For reports, a sensible rule is to project only a few periods ahead, show the error band, and label the figures as a trend, not a forecast.

Common pitfalls

• Excel's y-first argument order. SLOPE(known_ys, known_xs); polyfit(x, y, 1). Swapping them fits a different line.
• Unsorted rows. Period numbers assume time order; sort by date first.
• Dates used directly. Convert to period numbers or days; know the unit of the slope.
• Long extrapolations. Project a few periods, with an error band, and revisit the fit every time new data arrives.

Performance notes

polyfit on a few dozen points takes microseconds; fitting thousands of product series with groupby().apply takes a second or two. For very many series, the slope and intercept can be computed in closed form from grouped sums of x, y, x² and xy, which vectorises completely.

Conclusion

np.polyfit(x, y, 1) gives Excel's SLOPE and INTERCEPT (note the reversed argument order), np.corrcoef gives CORREL and, squared, RSQ, and evaluating the line reproduces TREND and FORECAST.LINEAR; STEYX comes from the residuals. Use period numbers or days since a start date as x, fit per group to compare products, report R² and an error band beside every slope, and keep straight-line projections short.

Frequently asked questions

What is the Python equivalent of SLOPE and INTERCEPT? numpy.polyfit(x, y, 1) returns the slope and intercept of the least-squares line, the same values SLOPE(y, x) and INTERCEPT(y, x) return. Note Excel puts the y values first; polyfit takes x first.

How do I reproduce FORECAST.LINEAR or TREND? Fit the line with polyfit and evaluate it at the new x: slope * x_new + intercept. numpy.polyval(coefficients, x_new) does the same for several points, like TREND with new x values.

Can I use dates as the x values? Yes, but convert them to numbers first — Excel uses date serial numbers, so SLOPE over dates is a change per day. In Python, use a period number (1, 2, 3 …) for a change per month, or days since a start date to match Excel's per-day slope.

Does NumPy reproduce FORECAST.ETS? No. FORECAST.ETS uses Excel's implementation of exponential triple smoothing with automatic seasonality detection. Python libraries such as statsmodels offer exponential smoothing, but their parameters and results differ, so do not expect matching numbers.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• Add a Trendline to an Excel Chart with Python — letting the chart draw the same line.
• Calculate Moving Averages with pandas rolling — smoothing without assuming a straight line.
• Running Totals and Year-Over-Year Growth in pandas — growth as a comparison rather than a fit.