{"id":2355,"date":"2020-04-18T23:36:58","date_gmt":"2020-04-18T15:36:58","guid":{"rendered":"https:\/\/www.fabianmoa.com\/?p=2355"},"modified":"2020-04-25T15:56:27","modified_gmt":"2020-04-25T07:56:27","slug":"monte-carlo-simulation-for-wealth-planning-in-excel","status":"publish","type":"post","link":"https:\/\/fabianmoa.com\/2020\/04\/18\/monte-carlo-simulation-for-wealth-planning-in-excel\/","title":{"rendered":"Monte-Carlo Simulation for Wealth Planning in Excel"},"content":{"rendered":"\n\n[et_pb_section fb_built=&#8221;1&#8243; _builder_version=&#8221;4.4.1&#8243; custom_padding=&#8221;29px|||||&#8221;][et_pb_row _builder_version=&#8221;4.4.1&#8243;][et_pb_column _builder_version=&#8221;4.4.1&#8243; type=&#8221;4_4&#8243;][et_pb_text _builder_version=&#8221;4.4.1&#8243; hover_enabled=&#8221;0&#8243;]<p>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 &#8220;<strong>Capital Sufficiency Analysis<\/strong>&#8221; (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.<\/p>\n<p>The two methods for evaluating capital sufficiency analysis are:<\/p>\n<ul>\n<li>Deterministic forecasting<\/li>\n<li>Monte Carlo simulation<\/li>\n<\/ul>\n<p>With the textbook showing only the results of the Monte Carlo simulation, I thought to myself, &#8220;Why not do a tutorial to show how Monte Carlo simulation can be implemented in Excel?&#8221;<\/p>\n<p><strong><u>Case<\/u><\/strong>:<\/p>\n<p>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:<\/p>\n<ul>\n<li>Government bonds (risk-free): with an expected return of 4% per annum.<\/li>\n<li>Equities: with an expected return of 12% and a standard deviation of return of 30%.<\/li>\n<\/ul>\n<p>Based on capital market expectations and the client&#8217;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)<\/p>\n<p>Your client indicated that she would like to withdraw $180,000 out of her account every year (let&#8217;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).<\/p>\n<p><strong>What are her chances of achieving her objective?<\/strong><\/p>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<\/p>\n<p>So, given such a case, how can we implement Monte Carlo simulation to this analysis?<\/p>\n<p>Let&#8217;s start!<\/p>\n<p>First, we will input the parameters and build the model.<\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=&#8221;4.4.1&#8243;][et_pb_column _builder_version=&#8221;4.4.1&#8243; type=&#8221;4_4&#8243;][et_pb_video src=&#8221;https:\/\/youtu.be\/R1PFb5B4zZo&#8221; _builder_version=&#8221;4.4.1&#8243; hover_enabled=&#8221;0&#8243;][\/et_pb_video][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=&#8221;4.4.1&#8243;][et_pb_column _builder_version=&#8221;4.4.1&#8243; type=&#8221;4_4&#8243;][et_pb_text _builder_version=&#8221;4.4.1&#8243; hover_enabled=&#8221;0&#8243;]<p><span>Next, I will use the\u00a0<\/span><strong>Data Table<\/strong><span>\u00a0to 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.<\/span><\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=&#8221;4.4.1&#8243;][et_pb_column _builder_version=&#8221;4.4.1&#8243; type=&#8221;4_4&#8243;][et_pb_video src=&#8221;https:\/\/youtu.be\/jtVAJ0yVOSc&#8221; _builder_version=&#8221;4.4.1&#8243; hover_enabled=&#8221;0&#8243;][\/et_pb_video][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=&#8221;4.4.1&#8243;][et_pb_column _builder_version=&#8221;4.4.1&#8243; type=&#8221;4_4&#8243;][et_pb_text _builder_version=&#8221;4.4.1&#8243; hover_enabled=&#8221;0&#8243;]<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<\/p>\n<p><em>This tutorial is for education purposes only. For professional advice, please seek a qualified financial planner\/wealth manager.<\/em><\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n\n","protected":false},"excerpt":{"rendered":"<p>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 &#8220;Capital Sufficiency Analysis&#8221; (or Capital Needs Analysis), which is a process of determining if a client has, or [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2399,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[29],"tags":[36,20,30,31,32],"_links":{"self":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/posts\/2355"}],"collection":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/comments?post=2355"}],"version-history":[{"count":0,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/posts\/2355\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/media\/2399"}],"wp:attachment":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/media?parent=2355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/categories?post=2355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/tags?post=2355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}