A Spreadsheet Tool to Simulate Equivalent Discrete Effective Returns Based on Combinations of Expected Continuous Returns and Volatility

In this spreadsheet tool (worksheet “Simulation”), we implement Equation (21.17) from John Hull (2022) using a Monte Carlo simulation as follows:

Input Cells (Parameters)

Cell B1: Initial capital S(0)
Cell B2: Expected continuously compounded annual return μ^
Cell B3: Investment horizon in years T
The initial capital, the expected continuously compounded annual return, and the investment horizon can be chosen freely.
Row 5: Annual volatility σ
The volatility values are predefined in the worksheet and increase in steps of 5 percentage points, ranging from 0% up to 300%.

Output Cells

For each combination of expected continuously compounded annual return and annual volatility:
- Row 11: The resulting final capital is calculated using Equation (21.17), assuming continuous compounding.
- Row 12: The equivalent discrete annual return is computed — that is, the discrete return that generates the same final capital as the continuously compounded return.

Beginning in Row 18, 1,000 simulation runs are performed for each combination of expected continuous return and volatility. In each simulation run, the equivalent discrete annual return is calculated according to the method in Row 12.

After the simulation runs, in:
- Row 13: The arithmetic mean of the 1,000 simulated equivalent discrete annual returns is determined for each parameter combination.
- Row 14: For comparison, the annual return is calculated that results when
- the random term (the ε part) in Equation (21.17) is omitted, and
- discrete compounding is assumed.
- Row 15: The absolute difference between the simulated average (Row 13) and the analytical comparison value (Row 14) is reported.

Reference:
Hull, John C. (2022): Options, Futures, and other Derivatives, Global Edition, 11th Edition, Pearson, pp. 491-492.

We used AI (Microsoft Copilot) to check the consistency of our concept.