A financial model you can see
Banking and finance students can experience the joy of simulating price paths without previous programming experience. The same building blocks are useful for professionals outside quant teams who work on pricing models in Excel and do not use Python.
This lesson starts with one visible path. Random shocks become log-return increments; accumulated log-returns become prices. The next step is to wrap that calculation in a reusable function.
The model: ABM for log-returns, GBM for prices
The cumulative log-return follows arithmetic Brownian motion. Multiplying its exponential by the starting price gives a geometric Brownian motion price path.
Here, Z is a standard normal shock and dt is the time step in years. Drift is the instantaneous proportional drift of the price, while Vol is its annualized volatility. Subtracting Vol^2/2 converts the price drift to the log-price drift.
Drift scales with time
The deterministic part of each log-return increment is multiplied by dt.
Uncertainty scales with its square root
The random component is multiplied by SQRT(dt), so its variance scales with dt.
With a positive starting price, this exponential construction gives positive simulated prices. A plain arithmetic Brownian motion applied directly to the price can become negative.
The drift input is a continuous growth parameter. At 5% drift and a one-year horizon, the theoretical mean price is 100 * EXP(5%) = 105.127110.
Follow the calculation
The One path sheet keeps three intervals visible so you can follow every calculation. The longer simulation uses 52 steps for a weekly grid over one year.
| Input | Cell | Example |
|---|---|---|
| Starting price | D7 | 100 |
| Annual drift | D8 | 5% |
| Annual volatility | D9 | 20% |
| Horizon in years | D10 | 1 |
| Number of steps | D11 | 3 in the teaching sheet; 52 for weekly paths |
Set the inputs
Enter the starting price, drift, volatility, horizon and number of steps in D7:D11 on One path. Three intervals keep the example readable; change D11 to 52 for a weekly grid.
D7:D11Build the time grid
The sequence includes step zero. Multiplying the step numbers by dt places each observation on its time grid.
C16 and D16=SEQUENCE(n+1,1,0)=C16#*dtGenerate log-return increments
RANDARRAY supplies uniform draws and NORM.S.INV converts them into standard normal shocks. The deterministic change scales with dt; the random change scales with SQRT(dt).
E17=drift_dt+vol_dt*NORM.S.INV(RANDARRAY(n))Accumulate with SCAN
SCAN returns each successive cumulative log-return. VSTACK adds the starting value of zero.
F16=VSTACK(0,SCAN(0,E17#,SUM))Convert log-returns to prices
Exponentiate the cumulative log-returns and multiply by the starting price. The first output is the initial price.
G16=S0*EXP(F16#)
These short worksheet formulas use the named inputs and derived quantities already defined in the workbook.
One path in one formula
LET gives names to the inputs and intermediate calculations. Enter this formula in an empty part of the One path sheet and leave enough space for the spilled result.
It uses the inputs in D7:D11 and returns NumSteps + 1 prices, including time zero. Each fresh RANDARRAY call produces a new realization, so the numbers need not match the screenshot.
=LET(
S0_,$D$7,
Drift_,$D$8,
Vol_,$D$9,
Horizon_,$D$10,
NumSteps_,$D$11,
dt_,Horizon_/NumSteps_,
Z_,NORM.S.INV(RANDARRAY(NumSteps_)),
Increments_,(Drift_-Vol_^2/2)*dt_+Vol_*SQRT(dt_)*Z_,
LogReturns_,VSTACK(0,SCAN(0,Increments_,
LAMBDA(Previous_,Change_,Previous_+Change_))),
S0_*EXP(LogReturns_)
)RANDARRAY generates uniform draws; NORM.S.INV turns them into standard normal shocks. SCAN returns the running log-return and VSTACK includes time zero. The final line converts the cumulative log-returns into prices.
The compact teaching formula exposes the construction. The workbook's named simulation functions also protect the inverse-normal conversion at probability endpoints.
Make the calculation reusable
The workbook defines fx_ABM and fx_GBM. The ABM function creates independent increments and accumulates each path. The GBM function adjusts the log-price drift, calls the ABM function, and exponentiates the output.
The explanatory Text1_, Text2_ and subsequent LET bindings are retained inside the functions. They are named text values used to document the calculation, rather than a separate Excel comment syntax.
The optional sixth argument is the number of paths; omitting it gives one path. The optional seventh argument controls the output direction. The default V puts time in rows and paths in columns. H transposes the complete result, including time zero. Lower-case v and h also work.
=fx_GBM(100,5%,20%,1,52,1000)Time in rows, paths in columns.
53 rows × 1,000 columns.
Paths in rows, time in columns.
1,000 rows × 53 columns.
=fx_GBM(100,5%,20%,1,52,1000,"H")These calls generate separate random draws. For an orientation comparison, use fixed increments or a deterministic case.
Accumulate each path independently
For multiple paths, time runs down the rows and each column is an independent path. A lower-triangular matrix of ones turns each column of increments into its sequence of cumulative sums.
=LET(n_,ROWS(Data_),
MMULT(--(SEQUENCE(n_)>=SEQUENCE(1,n_)),Data_))The downloadable workbook retains this matrix-based construction. The native comparison below found it fastest among the three tested formulations in all five completed matrix scenarios.
fx_ABM: the complete commented function
=LAMBDA(x0_, mu_, sigma_, T_, n_, [paths_], [direction_],
LET(
Text1_, "Default to one path and vertical output",
m_, IF(ISOMITTED(paths_), 1, paths_),
layout_, IF(ISOMITTED(direction_), "V", UPPER(direction_)),
Text2_, "Length of each time step",
dt_, T_ / n_,
Text3_, "Generate uniform r.v.: one row per step, one column per path",
U_, RANDARRAY(n_, m_),
Text4_, "Inverse method: uniform -> standard normal (endpoints protected)",
Z_, NORM.S.INV(IF(U_ = 0, 2^-53, IF(U_ = 1, 1 - 2^-53, U_))),
Text5_, "ABM increments: drift plus a normal shock",
dX_, mu_*dt_ + sigma_*SQRT(dt_)*Z_,
Text6_, "Cumulative sums path by path: lower-triangular matrix of ones",
L_, --(SEQUENCE(n_) >= SEQUENCE(1, n_)),
cum_, MMULT(L_, dX_),
Text7_, "Every path starts at x0_",
path_, x0_ + VSTACK(SEQUENCE(1, m_, 0, 0), cum_),
Text8_, "V: time in rows, paths in columns; H: the transpose",
IF(layout_ = "V", path_, IF(layout_ = "H", TRANSPOSE(path_), NA()))
)
)fx_GBM: the complete commented function
=LAMBDA(S0_, mu_, sigma_, T_, n_, [paths_], [direction_],
LET(
Text1_, "Default to one path and vertical output",
m_, IF(ISOMITTED(paths_), 1, paths_),
layout_, IF(ISOMITTED(direction_), "V", UPPER(direction_)),
Text2_, "Ito: ln S drifts at mu_ - sigma_^2/2, not at mu_",
drift_, mu_ - sigma_^2/2,
Text3_, "Simulate ln S as an ABM, then take the exponential",
EXP(fx_ABM(LN(S0_), drift_, sigma_, T_, n_, m_, layout_))
)
)All functions in the LAMBDA library
Turn the model into an experiment
Set volatility to zero
The random component disappears. At time t, the price follows S0 * EXP(Drift * t).
Change volatility
Keep the random shocks fixed when comparing parameters, so that the comparison isolates the parameter change.
Change the horizon
The drift and random terms respond differently: dt versus SQRT(dt).
Increase the number of steps
You obtain a finer time grid. With constant drift and volatility, this construction already samples the model exactly at the selected times, subject to finite-precision random draws.
Save the shocks as values for a reproducible example. A fresh recalculation otherwise changes both the random draws and the resulting paths.
Checked in native Excel
Twelve native Excel checks passed on the updated Brownian-motion workbook. They cover output layouts, deterministic limits, omitted arguments, large terminal-value arrays and save/reopen persistence.
- ABM output orientation
Vertical/horizontal dimensions and deterministic transpose comparison
- GBM output orientation
Vertical/horizontal dimensions and deterministic transpose comparison
- ABM zero horizon
Every value equals its starting value
- GBM zero horizon
Every value equals its starting price, within floating-point tolerance
- ABM one step and omitted paths
Two observations and one default path; horizontal output also checked
- GBM one step and omitted paths
Two observations and one default path; horizontal output also checked
- Invalid direction
An unsupported direction returns an explicit #N/A
- 1,000 horizontal paths
52 steps give 1,000 rows and 53 columns
- 100,000 internal terminal values
Default-layout internal array evaluated inside an aggregate
- 100,000 horizontal terminal values
Terminal-value output evaluated in a 100,000-row column
- Visible single-path worksheet
Zero volatility and zero horizon give the deterministic limits
- Save, reopen and recalculate
Named functions persist and full native recalculation completes
After full native recalculation, the saved workbook contained no cached formula errors. An independent before/after audit preserved the existing inputs, unrelated formulas, defined names and scopes, three charts and their references, drawings and unrelated layout.
Compare the simulation with the theory
For a starting price of 100, drift of 5%, volatility of 20% and one year, the theoretical quantities are shown below.
| Quantity | Expression | Value |
|---|---|---|
| Mean terminal price | S0 * EXP(Drift * Horizon) | 105.127110 |
| Median terminal price | S0 * EXP((Drift - Vol^2/2) * Horizon) | 103.045453 |
| Standard deviation of log terminal price | Vol * SQRT(Horizon) | 0.200000 |
A simulated mean or median fluctuates around its theoretical value. For the sample mean, use its Monte Carlo standard error when assessing the discrepancy. If only terminal prices are required, draw from the terminal distribution directly rather than generating every intermediate point.
Native Excel performance
MMULT was fastest in all five completed matrix scenarios and remains the implementation in the downloadable workbook.
Every method used the same fixed increments. These timings measure cumulative sums, including MMULT's triangular matrix construction; random-number generation is excluded.
| Steps × paths | MMULT | REDUCE | MAKEARRAY |
|---|---|---|---|
| 52 × 20 | 1.12 | 2.07 | 5.38 |
| 52 × 1,000 | 24.89 | 70.82 | 247.04 |
| 252 × 20 | 14.08 | 30.54 | 100.63 |
| 252 × 1,000 | 418.99 | 2199.54 | 3551.13 |
| 1,000 × 20 | 210.10 | 497.63 | 1554.09 |
Microsoft Excel 16.0, build 20527, Windows 64-bit; multithreaded calculation enabled, 16 calculation threads reported.
The REDUCE alternative is a useful way to express the row-vector update, but growing VSTACK operations cost time beyond the additions themselves. Its tested implementation was slower in these scenarios. The MAKEARRAY version repeatedly sums prefixes and was slower again.
How the timings were measured
Every method used the same fixed numeric increments. Random-number generation was excluded.
The timed expression includes construction of MMULT's triangular matrix. Input construction, output reading, correctness checks and file saving were excluded.
Two warm-ups were followed by seven measured rounds, with cyclically rotated method order. Fast cases used repeated recalculations within each round; the table is the median of the seven round averages.
Range.Dirty was called before each timed Range.Calculate in manual calculation mode. Dirty-marking was excluded from the timed interval.
Every output cell was compared with an independent column-prefix reference. The maximum absolute discrepancy was 4.857e-17; all passed a tolerance of 1e-10 * MAX(1,ABS(reference)).
All output cells agreed with the independent cumulative-sum reference within the stated tolerance. A changed input was also checked to confirm that the formulas recalculated.
The tested row-vector REDUCE alternative
REDUCE updates all paths at one time step, then appends that row to the history. It avoids the square triangular matrix, but its growing VSTACK operations add costs beyond the arithmetic. This formulation was slower than MMULT in the completed tests.
=LET(
m_,COLUMNS(Data_),
DROP(
REDUCE(SEQUENCE(1,m_,0,0),SEQUENCE(ROWS(Data_)),
LAMBDA(acc_,i_,
VSTACK(acc_,TAKE(acc_,-1)+CHOOSEROWS(Data_,i_)))),
1)
)For one step and 100,000 paths, all three methods passed a scalar correctness check on the internal array. The two warm-ups averaged about 7.6 ms for MMULT, 27.6 ms for REDUCE and 141 seconds for MAKEARRAY.
The full seven-round timing sequence was stopped because the MAKEARRAY calculation was excessively slow. These are warm-up observations and are not formal medians or part of the five-scenario table.
The final MMULT-based functions were separately verified with 100,000 internal terminal values in the default layout and a 100,000-row terminal column in horizontal output.
These timings describe this machine, Excel build and tested formulas. They do not establish a universal performance ranking. MMULT creates an explicit Steps-by-Steps temporary matrix.
LinkedIn version
A concise introduction to the lesson
Banking and finance students can experience the joy of simulating price paths, even without previous programming experience.
The same tools are useful for professionals outside quant teams who build pricing models in Excel and do not use Python.
With modern Excel, both can turn a mathematical model into something they can see and use.
Of course, we can generate random shocks in separate cells and copy formulas down the worksheet.
But there is a simpler and more elegant solution using modern Excel functions: LET, RANDARRAY, and SCAN.
The first image shows the construction step by step: inputs, random increments, cumulative log-returns, and prices.
Using the inputs in cells D7:D11, the entire price path can also be computed with a single formula:
=LET(
S0_,$D$7,
Drift_,$D$8,
Vol_,$D$9,
Horizon_,$D$10,
NumSteps_,$D$11,
dt_,Horizon_/NumSteps_,
Z_,NORM.S.INV(RANDARRAY(NumSteps_)),
Increments_,(Drift_-Vol_^2/2)*dt_+Vol_*SQRT(dt_)*Z_,
LogReturns_,VSTACK(0,SCAN(0,Increments_,
LAMBDA(Previous_,Change_,Previous_+Change_))),
S0_*EXP(LogReturns_)
)That's it! One formula, and the entire price path automatically spills into the worksheet.
RANDARRAY generates the random numbers. SCAN accumulates the log-returns. VSTACK includes the starting point. The exponential converts the cumulative log-returns into prices.
The cumulative log-return follows arithmetic Brownian motion; the price follows geometric Brownian motion.
Even better, we can turn the calculation into a reusable Excel function using LAMBDA.
The complete workbook extends this example to multiple paths, with an optional number-of-paths argument and explanations alongside the calculation steps.
No VBA, no macros, no formulas to drag down. Just modern Excel.
For students, a way to understand the model. For practitioners, a reusable building block for pricing models.
For the full implementation and walkthrough, visit Excelling in Finance and download the complete workbook.
#Excel #QuantitativeFinance #MonteCarlo
Make the model your own.
Download the complete workbook, inspect the formulas, and change the inputs. It includes the single-path construction, multiple paths and the reusable commented LAMBDA functions.
Enter your email to unlock an immediate browser download. You can also choose to hear about new lessons; it is optional.
Suggest an improvement or a new problem.
Spotted something that could be clearer, or a pricing, risk or portfolio problem you would like to see solved in Excel? Send it here: reader suggestions shape the next lessons.