University of Toronto

Topics: Investment, Rate of return, Risk-free interest rate Pages: 7 (1650 words) Published: March 17, 2014
University of Toronto, Department of Economics, ECO 204, 2013 - 2014

University of Toronto | Department of Economics | ECO 204 | 2013 - 2014| Ajaz Hussain INDIVIDUAL PROJECT 2
Due by 11 pm, Monday, December 23rd, 2013 through the “Project 2 Assignment Tab” on ECO 204 Blackboard Worth: 10% of final grade

Please submit Project 2 in a single Excel file consisting of multiple worksheets. All worksheets must be properly labelled and formatted.
PURPOSE:
Develop your Excel and financial modeling skills
SITUATION AND AUDIENCE:
You’re a portfolio manager at an asset management firm.
READINGS:
ECO 204 Excel videos on Youtube and Lecture 10 Excel model.
DATA AND TOOLS:
❶ Install the FRED Excel add-in from here.
❷ Download the following data:
① In worksheet “A1”: use FRED to download the S&P 500 Index (SP500) monthly series through December 2012. To do this, type “SP500” in cell A1  type “m” in cell A3  click the button “Get FRED data”. You’ll have to delete some rows to get a data set through December 2012. Don’t delete the “dates” column – you’ll need it later on.

Note: FRED’s SP500 monthly series reports the S&P 500 index at the close of the last trading day of the previous month. For example, the February 1st 1957 value is the S&P 500 index on the last trading day of January 1957. ② In worksheet “A2”: use FRED to download the US 3 month T-Bills (TB3MS) interest rates series through December 2012. To do this, type “TB3MS” in cell A1  type “m” in cell A3  click the button “Get FRED data”. You’ll have to delete some rows to get a data set through December 2012. Don’t delete the “dates” column – you’ll need it later on.

1

University of Toronto, Department of Economics, ECO 204, 2013 - 2014

Note: FRED’s TB3MS series reports the monthly interest rate (in percentage terms) on the first day of each month.
③ Please see this list for the name of a company assigned to the last 5 digits of your student ID number. In worksheet “A3”: use CRSP to download the following monthly data for your assigned company through December 2012: share price on the last trading day of each month, returns, and returns without dividends. Don’t delete the “dates” column – you’ll need it later on. ④ In worksheet “A4”: use FRED to download monthly gold prices through December 2012. Don’t delete the “dates” column – you’ll need it later on.

Note: FRED has several series on (daily) gold prices. You can choose any one of these daily series (can you “force” FRED to give you gold prices on a monthly basis?) ⑤ In worksheet “A5”: copy and paste your company’s “date”, prices, returns, and returns without dividends series from worksheet “A3” into worksheet “A5”. Next, use the “vlookup” function to “pull” the S&P 500 monthly series, the US 3-month T-Bills monthly interest rate series, and the monthly gold price series into worksheet “A5”. Delete all rows with missing data.

Note: The data set in worksheet “A5” will consist of data from the first month of your company’s data set (in worksheet “A3”) through December 2012.
⑥ In worksheet “A5”: create the following additional columns of data: 





Monthly returns on the S&P 500 index.
Monthly returns on gold.
A “dummy variable” which takes the value 1 if your company’s returns were positive and 0 otherwise. A “dummy variable” which takes the value 1 if the S&P 500 returns were positive and 0 otherwise. A “dummy variable” which takes the value 1 if gold returns were positive and 0 otherwise.

⑦ In worksheet “A6”: compute the sample mean and standard deviation of your company’s returns, S&P 500 returns, “gold” returns, and the monthly interest rate on US 30-day T-Bills. Also, compute the sample covariance between your company’s returns, S&P 500 returns, and “gold” returns. Comment on your results (within worksheet “A7”).

TASKS:
❶ In worksheet “A7”: use “pivot tables” to make the following two tables: (a) The first table showing the number of times that your company’s returns...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Architecture Design, the Royal Ontario Museum in Toronto, Canada Essay
  • university of toronto Research Paper
  • university Essay
  • University of Southease Essay
  • University Essay
  • University Essay
  • University Essay
  • Essay about Yale University Investment Fund

Become a StudyMode Member

Sign Up - It's Free