Instructions Please read these instructions carefully before you start. Groups You may cooperate on this assignment in groups consisting of up to three students. If you prefer to work alone or with only one other student that is ?ne, too. Either way, make sure to enter the student IDs (including the letter) and names of all students in your group in the appropriate cells (B1:B6) on the Answers sheet. There will be draconian punishments for students that fail to do this. Submission You must submit a hard copy of the Answers sheet at your tutorial in week 8.
You should also submit a copy of the assignment (the entire workbook) on Blackboard, before 9 a. m. on September 10. Name your ?le “Assignment First student ID”, where First student ID is the student ID (including the letter) of the ?rst student in your group. Please only submit one hard copy and one soft copy per group. The Excel sheet You can download the assignment from the same folder on Blackboard where you found this document. Each non-empty cell in the sheet has a color which depends on its contents. Green cells contain given data. Blue cells contain given functions.
You should not change the values of either green or blue cells. Orange cells are empty, and the assignment consists in ?lling these out correctly. When you download the workbook most sheets are in protected mode, so that you will only be able to change the cells you are supposed to change. If you ?nd this annoying you may disable the protection by right-clicking the name of any sheet at the bottom of the workbook and select Unprotect Sheet. The password is FINS2624. The Questions and Solver example sheets are already unprotected, as this is necessary to use the solver.
You are required to enter your names in the Answers-sheet and you have the option to play around in the Solver example-sheet, but your actual work will 1 be done in the Questions-sheet. Here you will write functions that use the data given in the Daily returns sheet as well as the data generated in the Covariance matrix-sheet to construct an optimal balanced portfolio. The Daily returns-sheet contains daily logarithmic return data for ?ve U. S. stocks and a proxy for the daily risk-free return for trading days in the period January 1, 2006 to December 31, 2011.
We will pretend that the entire market consists of these ?ve stocks and the risk-free asset. This is obviously not the case, but it makes the assignment manageable. The log return between time 0 and 1 is de?ned as r = ln P1 P0 Expressing returns in this way makes it easier to annualize them (as you will be asked to do below). They relate to the arithmetic returns that we have been P1 – 1, in a straight-forward way and we can always transusing in class, e. g. P0 form a log return into an equivalent arithmetic return by taking the exponential function of the log return and deducting one: P1 r – 1 = eln( P0 ) – 1 = P1 -1 P0 Given the log return between time 0 and time 1, r1 , and the log return between time 1 and time 2, r2 , we can calculate the log return between time 0 and time 2 as r1 + r2 . To see this, note that: P1 P2 er1 +r2 – 1 = er1 er2 – 1 = eln( P0 ) eln( P1 ) – 1 = P1 P2 P2 -1= -1 P0 P1 P0 By the same logic, if we have T returns in a year we can get the return over the T entire year as t=1 rt . The Answers-sheet contains cells for entering your student IDs and names, as well some linked cells that will allow us to easily correct your work.
The other sheets will be explained as we go through the assignment. 2 The Excel Solver At one point in the assignment you will have to solve an optimization problem. If you know how to do this in Excel you may skip this section. Otherwise you should read it carefully. The tool used to do this in Excel is an Add-in called the Solver. You may not have this activated in Excel, in which case you will have to activate it. In Excel 2010 this is done by clicking File – Options – AddIns. Select Excel Add-ins from the drop down menu at Manage and click Go. Make sure that Solver Add-in is checked and click OK.
The solver will now be accessible under the Data-tab. Instructions to load the solver in Excel 2007 can be found here. Instructions to load the solver in Excel 97 to 2003 can be found here. The solver allows you (among other things) to solve maximization problems numerically. To do this you select a cell whose value you want to maximize. This cell should contain a function (your objective function) that depends on the values in some other cells. You can then specify which of these cells (your arguments) the Solver is allowed to change in order to ?nd the optimum.
To see how this works go to the Solver example-sheet in the workbook. Cell C4 contains a function that depends on the values in cells C5 and C6. To make excel ?nd the values in C5 and C6 that maximizes the value in C4, select cell C4 and click the Solver button. At Set Objective you can enter the cell containing your objective function. This should already be C4. Underneath you can specify the type of problem you’re solving. In our case we want to solve a maximization problem, so we select Max. In By changing cells you specify the cells the solver is allowed to change.
In our case that is C5 and C6. Click the arrow icon at the far right of the box and select these cells. Finally, uncheck the box saying Make Unconstrained Variables Non-Negative as we don’t want to limit the solution to positive values. You don’t have do worry about constraints or solution methods. Just click OK. The solver will now attempt to ?nd the values of X and Y that maximizes f and enter them in cells C4 and C5. A dialogue box may appear informing you that the solver is done. If that happens, select Keep Solver Solution and click OK.
There is no need to generate any sensitivity report. You’ll see that the suggested values are very close to the analytical solution to the maximization problem, X = 1 , y = 2 . 2 3 3 Assignment In this assignment you’ll solve the portfolio choice problem in an investment universe with ?ve risky assets. Each problem below forms a part in this overarching exercise. Go to the Questions-sheet in your excel ?le and start solving the problems now. Throughout, you may assume that there are 252 trading days in a year and that the return series are stationary. A.
In cells C4 to H4 enter functions that estimate the expected daily return of all assets. Hint: The (minimum-variance unbiased) estimator for the mean of a distribution is the sample mean: µ = ˆ useful for such calculations. B. In cells C5 to H5 enter functions that annualize your daily estimates of the expected returns, i. e. express them on an annual basis. Hint: If the log return for period 1 is r1 and the log return for period 2 is r2 , the log return over both periods is r1 + r2 . C. In cells C6 to H6 enter functions that estimate the (annual) excess return of all assets using your values in cells C5 to H5.
Hint: To annualize excess returns you must take the di?erence between the annualized returns, not annualize their di?erence. D. In cells C7 to H7 enter functions that estimate the daily variance of returns for all assets. Hint: Remember that you are estimating the variance from a random sample rather than from the entire population. The (minimum-variance unbiased) estimator for the variance of a distribution is: sX = ˆ2 1 N -1 N i=1 1 N N xi . Time series of historic returns i=1 are available in the Daily returns sheet. The excel-function AVERAGE is (xi – µ)2 ˆ
Time series of historic returns are available in the Daily returns sheet. The excel-function VAR is useful for such calculations. E. In cells C8 to H8 enter functions that annualize your estimates of the daily variance of returns for all assets. You may assume that daily returns are independent. That is a standard assumption that is appropriate in an e?cient market, which Sid will discuss in a coming lecture. Hint: Recall that the variance of the sum of two independent variables, X and Y , is the sum of their variance: V ar(X + Y ) = V ar(X) + V ar(Y ). F. In cells C9 to H9 enter functions for the annualized standard deviation of returns. From here on we will only be concerned with annualized variables. G. In cells C10 to G10 enter functions for the Sharpe-ratio for all stocks. H. Assume for now that you hold an equally weighted portfolio made up of the ?ve stocks. Since weights must sum to one by de?nition, specifying the weight of the ?rst four stocks will implicitly specify the weight of the ?fth stock (Merck). There is already a function in cell G13 that ensures that this is the case.
The weights of the ?rst four stocks are entered in cells C13 to F13. Enter functions in cells C14 to G14 that calculate the weighted return of each asset, i. e. each asset’s contribution to the portfolio return. I. In cells C16 to C18 enter functions that calculate the properties of the portfolio de?ned by the weights in cells C14 to G14. Hint: You will ?nd the covariance matrix in the Covariance matrix-sheet helpful for calculating the portfolio variance. It corresponds to the covariance matrices we have used in class. For instance, the entry in ell C5 (in the Covariance matrix-sheet) is the covariance between the weighted returns of Exxon Mobil and Caterpillar. J. Assume that the current risk-free rate is that of December 30, 2011. This interest rate happens to be zero and is already entered in cell C19. In cell C21 enter a formula for the Sharpe-ratio of your portfolio. K. Use the Excel Solver and the formulas you have already entered to ?nd the optimal risky portfolio, P * , in this market. Hint: A portfolio is de?ned by its weights, so you are asked to ?nd the weights of the optimal portfolio.
Use the solver to enter these weights in cells C13 to F13 (and implicitly in cell G13). These optimal weights will be di?erent from the weights you started o? with. L. Assuming that you have preferences represented by the utility function 1 U = E(r) – 2 As 2 and a risk aversion parameter A = 4, enter a function in cell C25 that calculates the fraction of your wealth that you should invest in the optimal risky portfolio. Note that the utility function used here assumes that you express E(r) and s in decimal form, e. g. 0. 2 for 20%.