Monte Carlo Forecasting in Excel: RAND, NORM.INV and FORECAST.LINEAR

Monte Carlo forecasting in Excel means turning a single-number forecast into a range: you build a trend forecast, add an assumed spread around it, and let RAND draw the same forecast hundreds or thousands of times. Excel’s built-in FORECAST.LINEAR returns one point value; NORM.INV(RAND(), mean, standard_dev) is the piece that makes the forecast probabilistic. This guide shows the full chain with a worked example whose arithmetic you can check by hand.

What Monte Carlo forecasting is used for in research

Research budgets, enrolment counts, sample-processing volumes and publication counts are usually forecast as one number, even though everyone knows the real outcome will land above or below it. A Monte Carlo forecast reports the spread: for example, the range that contains the middle 90% of simulated outcomes. For the method itself, see what Monte Carlo simulation is; this page covers only the Excel forecasting workflow.

Two separate ideas are often mixed up under “forecast in Excel”, and keeping them apart is the main point of this page:

• Deterministic forecast: a single predicted value from a fitted trend. In Excel this is FORECAST.LINEAR.
• Probabilistic (Monte Carlo) forecast: many simulated values around that prediction, summarised as percentiles. In Excel this adds RAND and NORM.INV.

Install or access (official source only)

Nothing needs to be installed beyond Excel itself; every function used here is a built-in worksheet function documented on Microsoft Support (links in the Sources section). Version matters in one place: Microsoft’s documentation states that FORECAST.LINEAR was introduced in Excel 2016, and that the older FORECAST function is kept for backward compatibility and will eventually be deprecated, with FORECAST.LINEAR as the suggested replacement. Both take the same arguments.

Step-by-step: build a Monte Carlo forecast in Excel

The example below is an illustration with made-up numbers, not data from any real programme. Suppose five years of a yearly figure (in thousands) are in cells A2:B6.

Cell	Year (x)	Value (y)
Row 2	1	100
Row 3	2	108
Row 4	3	119
Row 5	4	125
Row 6	5	138

1. Get the trend forecast. In D1 enter =FORECAST.LINEAR(6, B2:B6, A2:A6). The syntax is FORECAST.LINEAR(x, known_y's, known_x's) and all three arguments are required. Excel fits a straight line a + bx by linear regression, where b = Σ(x − x̄)(y − ȳ) / Σ(x − x̄)² and a = ȳ − b·x̄. For these five points the mean of y is 118, the slope b is 9.3 and the intercept a is 90.1, so D1 returns 90.1 + 9.3 × 6 = 145.9.
2. State the spread as an explicit assumption. In D2 enter 6 as the standard deviation of the forecast error. FORECAST.LINEAR does not return a spread, so this number is your modelling choice (in practice, take it from the residuals of your own history or from expert judgement) and should be reported as an assumption.
3. Draw one simulated outcome. In F2 enter =NORM.INV(RAND(), $D$1, $D$2). NORM.INV(probability, mean, standard_dev) returns the value below which that share of a normal distribution falls; RAND() supplies a random probability each time the sheet calculates.
4. Repeat it. Fill F2 down to F1001 for 1,000 trials. Each row holds its own RAND(), so each row is an independent draw.
5. Summarise the trials. In H1 enter =AVERAGE(F2:F1001); in H2 enter =PERCENTILE.INC(F2:F1001, 0.05); in H3 enter =PERCENTILE.INC(F2:F1001, 0.95). The H2:H3 pair is the simulated 90% range.
6. Freeze a run you want to report. Press F9 and every RAND cell redraws. To keep a result, replace the formulas with their values: Microsoft’s RAND page describes entering =RAND() in the formula bar and pressing F9 to turn the formula into a fixed number, and the same idea applies if you copy the F column and paste it back as values.

Forecast in Excel, forecasting with Excel, Excel forecast: which function?

Searches such as “forecast in Excel”, “excel forecasting” and “forecasting using Excel” usually mean one of two things. If you want a single trend prediction, use FORECAST.LINEAR as in step 1. If you want a range of outcomes, add steps 2 to 5. For smoothing-based forecasts see the exponential smoothing formula and moving averages; for a catalogue of Excel’s statistical functions see Excel statistics functions.

Repeating the model with a Data Table

If you wrap a whole model in a What-If Data Table (Data tab, What-If Analysis, Data Table), note that Microsoft’s Data Table article says data tables recalculate every time the worksheet recalculates, which slows large models. Excel has a calculation option that recalculates the worksheet automatically but not the data tables; with that option on, you update the tables manually by selecting the table formulas and pressing F9. The fill-down layout above avoids this because it needs no Data Table.

Output and how to read it

In this example the underlying distribution is a normal with mean 145.9 and standard deviation 6. Its exact 5th and 95th percentiles can be checked with the same function: =NORM.INV(0.05, 145.9, 6) gives about 136.0 and =NORM.INV(0.95, 145.9, 6) gives about 155.8. Your 1,000-trial percentiles in H2 and H3 should land near those two values and will differ slightly every time you press F9, because every run draws new random numbers. More trials narrow that run-to-run wobble.

• The point forecast (145.9) is the centre of the simulation, so AVERAGE of the trials should sit close to it.
• The 5th-to-95th percentile range is the headline output: about 90% of simulated outcomes fall inside it.
• The range is only as good as the assumptions. It reflects the straight-line trend and the spread of 6 you chose. A different spread assumption gives a different range from the same history.

The method does not set a seed. Excel’s RAND takes no arguments, so there is no way to ask for the same sequence again; our Excel random number guide covers the same point. To make a reported result reproducible, freeze the values (step 6) and keep that sheet. For the regression idea behind the prediction, see y-hat, the predicted value.

Troubleshooting

Symptom	Function	Cause documented by Microsoft
#VALUE!	FORECAST.LINEAR	x is nonnumeric.
#N/A	FORECAST.LINEAR	known_y’s or known_x’s is empty, or the two ranges have different numbers of data points.
#DIV/0!	FORECAST.LINEAR	The variance of known_x’s is zero (all x values identical).
#VALUE!	NORM.INV	Any argument is nonnumeric.
#NUM!	NORM.INV	probability is less than or equal to 0 or greater than or equal to 1, or standard_dev is less than or equal to 0. Check that the spread cell holds a positive number.
Numbers keep changing	RAND	RAND returns a new value every time the worksheet calculates, including when you enter a formula elsewhere or press F9. Freeze the result when you need a fixed set.

One edge case: Microsoft documents RAND as returning a number greater than or equal to 0 and less than 1, while NORM.INV returns #NUM! for a probability of exactly 0. If you ever see #NUM! in one trial row after pressing F9, that row drew exactly 0 or the spread cell is not a positive number; recalculating clears the first case.

Alternatives in Google Sheets

The same two-function pattern exists in Google Sheets, according to Google’s function documentation.

Excel	Google Sheets	Documented behaviour
NORM.INV(probability, mean, standard_dev)	NORMINV(x, mean, standard_deviation)	Google describes it as returning the value of the inverse normal distribution function for a specified value, mean and standard deviation.
RAND()	RAND()	Google describes it as returning a random number between 0 inclusive and 1 exclusive.

In Sheets the formula would read =NORMINV(RAND(), D1, D2).

FAQ

Does Excel have a built-in forecasting function?

Yes. FORECAST.LINEAR(x, known_y's, known_x's) predicts a value along a fitted linear trend. It has existed since Excel 2016; the older FORECAST still works but is kept only for backward compatibility.

Does FORECAST.LINEAR give a range or confidence interval?

No. It returns one predicted value. The range in a Monte Carlo forecast comes from the spread you assume and the NORM.INV(RAND(), ...) draws.

Can I set a random seed in Excel?

Not for RAND: the function takes no arguments, so the sequence cannot be requested again. Freeze a run by converting the formulas to values.

Sources (official documentation, date checked: 2026-10-29)

• Microsoft Support, FORECAST and FORECAST.LINEAR functions
• Microsoft Support, NORM.INV function
• Microsoft Support, RAND function
• Microsoft Support, Calculate multiple results by using a data table
• Google Docs Editors Help, NORMINV (https://support.google.com/docs/answer/3094022)
• Google Docs Editors Help, RAND (https://support.google.com/docs/answer/3093438)

The worked example uses invented numbers for illustration; the arithmetic (slope 9.3, intercept 90.1, forecast 145.9) can be reproduced with the formulas shown.