Multivariate Monte-Carlo Simulation and Economic Valuation of Complex Financial Contracts: An Excel Based Implementation.

• Main Authors: Timothy J Kyng, Otto Konstandatos
• Format: Article
• Language: English
• Published: Bond University
• Series: Spreadsheets in Education
• Online Access: http://sie.scholasticahq.com/article/4619-multivariate-monte-carlo-simulation-and-economic-valuation-of-complex-financial-contracts-an-excel-based-implementation.pdf

The economic valuation of complex financial contracts is often done using Monte-Carlo simulation. We show how to implement this approach using Excel. We discuss Monte-Carlo evaluation for standard single asset European options and then demonstrate how the basic ideas may be extended to evaluate options with exotic multi-asset multi-period features. Single asset option evaluation becomes a special case. We use a typical Executive Stock Option to motivate the discussion, which we analyse using novel theory developed in our previous works. We demonstrate the simulation of the multivariate normal distribution and the multivariate Log-Normal distribution using the Cholesky Square Root of a covariance matrix for replicating the correlation structure in the multi-asset, multi period simulation required for estimating the economic value of the contract. We do this in the standard Black Scholes framework with constant parameters. Excel implementation provides many pedagogical merits due to its relative transparency and simplicity for students. This approach also has relevance to industry due to the widespread use of Excel by practitioners and for graduates who may desire to work in the finance industry. This allows students to be able to price complex financial contracts for which an analytic approach is intractable.