{"id":2362,"date":"2020-04-18T23:47:58","date_gmt":"2020-04-18T15:47:58","guid":{"rendered":"https:\/\/www.fabianmoa.com\/?p=2362"},"modified":"2020-04-25T15:55:14","modified_gmt":"2020-04-25T07:55:14","slug":"portfolio-optimization-using-solver-in-excel","status":"publish","type":"post","link":"https:\/\/fabianmoa.com\/2020\/04\/18\/portfolio-optimization-using-solver-in-excel\/","title":{"rendered":"Portfolio Optimization using Solver in Excel"},"content":{"rendered":"\n[et_pb_section fb_built=&#8221;1&#8243; _builder_version=&#8221;4.4.1&#8243; custom_padding=&#8221;4px|||||&#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>Let&#8217;s say you have a client who wants to construct a stock portfolio, and she chose the following stocks:<\/p>\n<ul>\n<li>Apple (AAPL)<\/li>\n<li>Boeing Airlines (BA)<\/li>\n<li>Netflix (NFLX)<\/li>\n<li>Tesla (TSLA)<\/li>\n<\/ul>\n<p>Your client has stated that the objective of maximizing the Sharpe ratio of her portfolio. Her maximum risk tolerance is based on a standard deviation of 30% per annum.<\/p>\n<p>You collected the monthly data of the stocks mentioned above and computed the<span>\u00a0<\/span><strong>monthly returns<span>\u00a0<\/span><\/strong>(from 1 Jan 2015 to 1 Dec 2019) on a<span>\u00a0<\/span><strong>continuously compounded<\/strong><span>\u00a0<\/span>basis. The data file can be found:<span>\u00a0<\/span><a href=\"https:\/\/drive.google.com\/file\/d\/1e7A7d0v6vv1Wg19izQh9Eu1s9NDBRuln\/view?usp=sharing\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">here<\/a>. For the purpose of this exercise, you assume the risk-free rate is 4% per annum.<\/p>\n<p>Use Excel to compute the optimal weights for each stock in order to achieve the client&#8217;s objective.<\/p>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<\/p>\n<p><strong><u>Steps<\/u><\/strong>:<\/p>\n<ol>\n<li>Compute the covariance of each stock.<\/li>\n<li>Compute the average monthly return of each stock.<\/li>\n<li>Based on an initial weight, we will compute the portfolio&#8217;s monthly return and standard deviation.<\/li>\n<li>Then, we will annualize the portfolio return and standard deviation.<\/li>\n<li>We then use Solver to find the optimal weights based on the client&#8217;s objective.<\/li>\n<\/ol>\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_slider _builder_version=&#8221;4.4.1&#8243; hover_enabled=&#8221;0&#8243;][et_pb_video_slider_item src=&#8221;https:\/\/youtu.be\/prFCWrfJHBs&#8221; _builder_version=&#8221;4.4.1&#8243; show_image_overlay=&#8221;off&#8221; hover_enabled=&#8221;0&#8243;][\/et_pb_video_slider_item][\/et_pb_video_slider][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"<p>Let&#8217;s say you have a client who wants to construct a stock portfolio, and she chose the following stocks: Apple (AAPL) Boeing Airlines (BA) Netflix (NFLX) Tesla (TSLA) Your client has stated that the objective of maximizing the Sharpe ratio of her portfolio. Her maximum risk tolerance is based on a standard deviation of 30% [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2396,"comment_status":"open","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,54,56,53,52],"_links":{"self":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/posts\/2362"}],"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=2362"}],"version-history":[{"count":0,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/posts\/2362\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/media\/2396"}],"wp:attachment":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/media?parent=2362"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/categories?post=2362"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/tags?post=2362"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}