To carry out this problem set, you will need a computer running Microsoft Excel. This problem set
comes with a spreadsheet PS2.xls that you can find on the blackboard site. You will have to install
two add-ons (‘solver’ and ‘data analysis’) in Excel if you haven’t done so already and this document
provides you with the required instructions.
This problem set is due by 11:59pm PT on Tuesday, March 8, 2022. Your solutions should be in
the form of a single Excel file with each question answered in a separate sheet. Make sure to include
the names of all people in your team somewhere obvious inside the file. Submit the file on Blackboard.
It is all team members’ responsibility that the problem set is submitted on time. It is sufficient if one
of the team members makes a submission.
Problem 1
This problem guides you through a global asset allocation problem with multiple regional equity
indices. The exercise walks you through finding the tangency portfolio in excel both using
optimization using solver.
Please download the spread sheet PS2.xls available on the course web site. The tab “Problems 1&2”
contains monthly return data on the MSCI index return for the US, Pacific,
and Europe as well as the 30-day Treasury bill return for January 2001 until August 2019,
i.e., for 19 years. The Europe index contains 16 European countries. The Pacific index contains
5 developed markets in the Pacific region, specifically, Australia, Hong Kong, Japan, New
Zealand, and Singapore.
- Calculate excess returns on the US, Pacific and European indices from the perspective of a US
investor by subtracting the 30-day Treasury bill returns from the equity index returns. - Compute
- monthly mean excess returns on the US, Pacific and European indices.
Collect the mean excess returns in a 1 by 3 vector. - the standard deviations and correlations between the mont