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?”
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?
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.