Monte-Carlo Simulation for Wealth Planning in Excel

Financial Modeling | 0 comments

I was inspired to write this piece after teaching a reading in the CFA Level 3 syllabus for the June 2020 exam. In Reading 28 on Overview of Private Wealth Management, it touched on a term called “Capital Sufficiency Analysis” (or Capital Needs Analysis), which is a process of determining if a client has, or is likely to accumulate sufficient financial resources to meet his/her objectives.

The two methods for evaluating capital sufficiency analysis are:

  • Deterministic forecasting
  • Monte Carlo simulation

With the textbook showing only the results of the Monte Carlo simulation, I thought to myself, “Why not do a tutorial to show how Monte Carlo simulation can be implemented in Excel?”

Case:

Imagine you have a 70-year old client with a portfolio valued at $2,000,000. As her wealth manager, you propose a mix of asset classes:

  • Government bonds (risk-free): with an expected return of 4% per annum.
  • Equities: with an expected return of 12% and a standard deviation of return of 30%.

Based on capital market expectations and the client’s risk profile, a proposed allocation is 40% invested in equities and the remaining 60% goes to government bonds. (For my demonstration, I will assume that equity returns follow a normal distribution)

Your client indicated that she would like to withdraw $180,000 out of her account every year (let’s assume the withdrawal happens at the end of the year). She would like to donate $100,000 to charity at the age of 80 (i.e. her investment horizon is 10 years).

What are her chances of achieving her objective?

———————————–

So, given such a case, how can we implement Monte Carlo simulation to this analysis?

Let’s start!

First, we will input the parameters and build the model.

Next, I will use the Data Table to implement the Monte Carlo simulation. Though VBA would be a more efficient way to implement, I prefer using Data Table as it is easier for most to understand.

—————–

This tutorial is for education purposes only. For professional advice, please seek a qualified financial planner/wealth manager.