Multi-Variable Monte Carlo Simulation for Portfolio Analysis in Excel

Authors

Tom Arnold, University of RichmondFollow
C. Mitchell Conover, University of RichmondFollow
Joseph Farizo, University of RichmondFollow
Nancy Tran, University of RichmondFollow

Abstract

Monte Carlo (MC) simulation has several applications in finance, including risk management, valuation, and portfolio management. In this paper, we use multiple correlated Geometric Brownian motion (GBM) processes that allow for very robust portfolio analysis. In this treatment, five correlated funds make up a portfolio. MC is used to not only forecast possible future portfolio and fund outcomes, but to also evaluate the benefits and costs of a hedging strategy and to consider the effects of different correlation structures between the funds on portfolio performance. Because the programming is in Excel, the analysis is very accessible and available to analysts to not only perform MC, but to also understand the underlying mechanics.

KEY TAKEAWAYS:

Combining Excel’s =LAMBDA and =MAKEARRAY functions allows a user to create user-defined functions that generate multi-factor Monte Carlo Geometric Brownian motion (GBM) simulations that are correlated. In this paper, we demonstrated up to five factors, however, the model is generalizable to more than five correlated variables.

A hedging strategy analysis is possible by allowing the simulation to output values at each time-step prior to recording the final outcome. One can determine if the hedging strategy is too strict or lax in regard to mitigating risk and if the strategy is worth the cost relative to its risk reduction benefit.

Monte Carlo simulation allows for a type of scenario analysis to determine the effects of varying parameters from their initial values (possibly historic values) to other possible values that may occur in the future. We provide an example in which correlation values between funds are made more positive and more negative relative to their initial values.

Document Type

Working Paper

Publication Date

8-4-2025

Publisher Statement

Please note that downloads of this working paper are for private/personal use only. Do not cite without permission.

Recommended Citation

Arnold, Tom; Conover, C. Mitchell; Farizo, Joseph; and Tran, Nancy, "Multi-Variable Monte Carlo Simulation for Portfolio Analysis in Excel" (2025). Finance Faculty Publications. 81.
https://scholarship.richmond.edu/finance-faculty-publications/81