Please did this I will pay twice, just do it ...I am totally forgot about
Homework Project for Chapter 7 Due date: Sunday June 16th, 11:45 p.m. Method of submission: email the excel sheet with your name to
[email protected] Project Introduction The purpose of this project is to use the Markowitz optimization software and historical data to determine the optimal weights. Ideally a sophisticated optimization software will allow you to enter variance, covariance, and expected return separately. The software we are using for this project is more basic and will simply use the historical return information you provide as inputs and use an iteration approach instead of actually mathematically calculating the efficient frontier. However it should still give you a mostly accurate result as long as it only has a few asset classes (2 or 3 should be optimal) to decide. Section I. Choose a period and how to download prices for different asset classes and calculate return The first step is to enter the price information in the “calculate returns” tab in the excel sheet. For this software, Markowitz mean variance optimization strategy is going to be based on fixed weights determined using historical data. We are going to use the following mutual funds as proxies for each asset classes: Vanguard Total Stock Market Index Fund (VTSMX) for stocks, Vanguard Total Bond Market Index Fund (VBMFX) for bonds, and Vanguard Global Capital Cycles Fund Investor Shares (VGPMX)[footnoteRef:1] as proxy for precious metals. I have already entered the price and calculated return for the first two asset classes as examples, you only need to do this for VGPMX. [1: Vanguard Global Capital Cycles Fund Investor Shares was previously known as Vanguard Precious Metal and Mining, it changed its name in 2018.] You can find the stock price information of each mutual fund at Yahoo Finance. Simply go to http://finance.yahoo.com/, at the left upper corner, enter the ticker symbol of the mutual fund then press “get quotes”, that should lead you to the page containing information about the mutual fund. To find the mutual fund’s historic price information, click “historical prices” on the left side of the page. This should lead you to a page similar to this (using VTSMX as an example): http://finance.yahoo.com/q/hp?s=YHOO For Markowitz mean variance optimization, we are going to use data from 1/1/2003 to 1/1/2011 to determine the weights as it has sufficient years to cover both bull markets and bear markets, so please download monthly stock price data from 1/1/2003 to 1/1/2011 (you can choose the frequency of data at right side of the set date range box, use the adjusted close price). The download to spreadsheet option is at the bottom of the page. Now you have the monthly price of the mutual funds, you calculate the monthly return for them. Since the monthly price Yahoo finance gives us is the price of the first day of the month, the return formula at time t is simply (Pt+1-Pt)/Pt, so the return for February will be the price of March minus the price of February, then divided by the price of February, using this formula to calculate the monthly return for the mutual funds, note you won’t have the return for the last month of your data period. There are several prices in the data for each month, use Adjusted price for calculation Section II. Use the portfolio optimization software to find optimal portfolio Next step is to copy the returns you calculated in the previous section to the “portfolio” tab for each asset classes (asset class names are in the row with blue background). We are also going to impose a minimum weight restriction of 10% for stocks and bonds and maximum weight of 100% for all asset classes (we will discuss this in next module). Once you have entered the return, you can click the “Run Markowitz Optimizer” button on the upper right corner. You may need to enable the macro choices under security option for this to run. Once you click the button, the following screen should appear. Please change the risk free rate to 0.3 (this is the monthly risk free rate) and select the asset class returns cells (don’t include the date) for the “return serious Matrix”. Please also check “Constrain weights” option and select the minimum and maximum weight cells for “Min/Max weight constraints”. It should looks like the following screen before you click on “calculate portfolio weights” button: You should get two new additional tabs: “Chart1” and “Sheet1”, “Chart1” should looks like the following, which gives you the efficient frontier, the capital market line, and the tangible point. If you click on “sheet1”, it should tell you the weight for each asset class under “Optimal Portfolio at Tangency Point”. This is the optimal risky assets allocation. Please save the weights as we will use it in the next project. Save the excel, rename it with your name and submit it to me through email. Homework Project for Chapter 7 Due date: Sunday June 1 6 th , 11:45 p.m. Method of submission: email the excel sheet with your name to
[email protected] Project Introduction The purpose of this project is to use the Markowitz optimization software and historical data to determine the optimal weights. Ideally a sophisticated optimization software will allow you to enter variance, covariance, and expected return separately. The software we are using for this project i s more basic and will simply use the historical return information you provide as input s and use an iteration approach instead of actually mathematically calculating the efficient frontier . However it should still give you a mostly accurate result as long as it only has a few asset classes (2 or 3 should be optimal) to decide. Section I. Choose a period and h ow to download prices for different asset classes and calculate return The first step is to enter the price information in the “calculate returns” t ab in the excel sheet. For this software, Markowitz mean variance optimization strategy is going to be based on fixed weights determined using historical data. We are going to use the following mutual funds as proxies for each asset classes: Vanguard Tot al Stock Market Index Fund (VTSMX) for stocks, Vanguard Total Bond Market Index Fund (VBMFX) for bonds, and Vanguard Global Capital Cycles Fund Investor Shares (VGPMX) 1 as proxy for precious metals . I have already entered the price and calculated return for the first two asset classes as example s , you only need to do this for VGPMX . You can find the stock price information of each mutual fund at Yahoo Finance. Simply go to http://finance.yahoo.com/ , at the left upper corner, enter the ticker symbol of the mutual fund then press “get quotes”, that should lead you to the page containing information about the mutual fund. To find the mutual fund’s historic price information, click “historical prices” on the left side of the page. This should lead you to a page similar to this (using VTSMX as an example): http://finance.yahoo.com/q/hp?s=YHOO F or Markowitz mean variance optimization, we are going to use data from 1/1/2003 to 1 /1/2011 to determine the weights as it has sufficient years to cover both bull markets and bear markets , so please download monthly stock price data from 1/1/2003 to 1/1/2011 (you can choose the frequency of data at right side of the set date range box, use the adjusted close price). The download to spreadsheet option is at the bottom of the page. 1 Vanguard Global Capital Cycles Fund Investor Shares was previously known as Vanguard Precious Metal and Mining, it changed its name in 2018.