FINS2624 Session 1, 2012
Please read these instructions carefully before you start.
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.
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
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
er − 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
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.
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...