The instructions are in the attachments however in STEP 2 part 3, you are asked for the company assigned. The company that has been assigned to me is INCO Homes Corporation. Also, please see the the time frame from which you have to find the data.1. SP500 data
2. Gold data
3. Three Month Treasury Bill data
4. My company (B C B Bancorp Inc) data
GOAL OF PROJECT 2:
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 the Costco, Dell, US T-Bills Portfolio Excel Model discussed in lectures.
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 2014. 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 2014. 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 2014. 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 2014. Don’t delete the “dates” column – you’ll need it later on. Note: FRED’s TB3MS series reports the monthly interest rate (in percentage terms) on the first day of each month.
? Please see Appendix A for the company assigned by the last 6 digits of your student ID number. In worksheet “A3”: use CRSP to download the following monthly data for your assigned company through December 2014: 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. Note: If data for your assigned company is missing, please e-mail Ajaz
University of Toronto, Department of Economics, ECO 204, 2015 – 2016
2
for what to do next. Note: Here’s how you access U of T “restricted” data bases from off-campus locations: http://guides.library.utoronto.ca/proxy.
? In worksheet “A4”: use FRED to download monthly gold prices through December 2014. 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 2014.
? 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 increased or decreased/stayed constant versus the number of times that the S&P 500 returns increased/decreased or remained constant.
(b) The second table showing the number of times that the returns on gold increased/decreased or remained constant versus the number of times that the S&P 500 returns increased or decreased/stayed constant.
Here is an example showing the number of times that American Express and S&P 500 returns went up or decreased/stayed the same (from December 1972 to December 2011):
University of Toronto, Department of Economics, ECO 204, 2015 – 2016
3
S&P 500 Monthly Returns
Increased
Decreased or
No Change
Total
American Express
Monthly Returns
Increased
183
81
264
Decreased or No Change
100
101
204
Total
283
185
468
? In worksheet “A8”: copy and paste the S&P 500 index series (from worksheet “A1”). Next, use the “vlookup” function to pull monthly gold prices (from worksheet “A4”) into worksheet “A8”. Delete all rows with missing data. Compute the sample covariance between S&P 500 returns and Gold returns:
(a) for the entire data set (b) for data through December 1978 (c) for data after January 1979 through December 2014
Comment on your results (within worksheet “A8”).
? In worksheet “A9”: Suppose it is January 1st, 2014 and your client wants to invest funds for a month in a portfolio consisting of a “risk free asset” and shares in the company assigned to you (on the basis of the last 5 digits of your ID number). Use the January 1st 2014 monthly interest rate in worksheet “A2” as the risk free rate in January 2014.
? Assume your client has a mean-variance utility function over portfolio return and variance. Calculate the value of the utility parameter ?? at which 100% of the portfolio is invested in the “risky” asset.
? Use Solver to calculate the fraction of the portfolio invested in the risky asset and the risk free asset for the case when the mean-variance utility parameter is:
? If your ID # ends with the numbers 0, 2, 4, 6, 8 then use: ??=1.02 ×Value of parameter ?? in step ? ? If your ID # ends with the numbers 1, 3, 5, 7, 9 then use: ??=0.98 ×Value of parameter ?? in step ?
? In worksheet “A10”: Suppose it is January 1st, 2014. Use Solver to construct a synthetic asset formed by blending a fraction of your assigned company’s share, a fraction of a share in an ETF which tracks the S&P 500, and a fraction of a troy ounce of gold.
? In worksheet “A11”: Suppose it is January 1st, 2014 and your client wants to invest funds for one month in a portfolio consisting of a risk free asset and a “synthetic” asset formed by blending a fraction of your assigned company’s share, a fraction of a share in an ETF which tracks the S&P 500, and a fraction of a troy ounce of gold. Use the January 1st 2014 monthly interest rate in worksheet “A2” as the risk free rate in January 2014.
? Assume your client has a mean-variance utility function over portfolio return and variance. Calculate the value of the utility parameter ?? at which 100% of the portfolio is invested in the “synthetic” risky asset.
? Use Solver to calculate the fraction of the portfolio invested in the “synthetic” risky asset and the risk free asset for the case when the mean-variance utility parameter is:
? If your ID # ends with the numbers 0, 2, 4, 6, 8 then use: ??=1.02 ×Value of parameter ?? in step ? ? If your ID # ends with the numbers 1, 3, 5, 7, 9 then use: ??=0.98 ×Value of parameter ?? in step ?
University of Toronto, Department of Economics, ECO 204, 2015 – 2016
4
Comment on your results (within worksheet “A11”).
? In worksheet “A12”: Suppose it is January 1st, 2014 and your client wants to invest funds for one month in a portfolio consisting of a risk free asset and a “synthetic” asset formed by blending a fraction of your company’s share, a fraction of a share in an ETF which tracks the S&P 500, and a fraction of a troy ounce of gold. Use the January 1st 2014 monthly interest rate in worksheet “A2” as the risk free rate in January 2014.
? Identify the least risky of the following risky assets: your assigned company, S&P 500, and gold.
? Assume your client has a mean-variance utility function over portfolio returns and variance. Calculate the value of the utility parameter ?? for which 100% of the portfolio is invested in the “synthetic risky” asset (this value of ?? should be identical to the value of ?? in task ?-?)
?Use the following value for the mean-variance utility parameter:
? If your ID # ends with the numbers 0, 2, 4, 6, 8 then use: ??=1.02 ×Value of parameter ?? in step ? ? If your ID # ends with the numbers 1, 3, 5, 7, 9 then use: ??=0.98 ×Value of parameter ?? in step ?
Use Solver to calculate the fraction of the portfolio invested in the “synthetic” risky asset and the risk free asset subject to the constraint that the portfolio risk is less than or equal to the risk of the least risky asset identified in step ?. Use Solver to generate “limits” and “sensitivity” reports.
Comment on your results (within worksheet “A12”).
? Use the “limits” and “sensitivity” reports in task ? to answer the following question: “what is the impact on the portfolio return in step ? when the risk of the least risky asset in step ? increases by 1%? Explain your answer in the relevant report (i.e. answer or sensitivity report).
TECHNICAL INSTRUCTIONS FOR PROJECT 2:
Submission: Please submit this project through the “Project 2 Assignment” Tab in ECO 204 Blackboard by 11 pm, Wednesday, December 30th, 2015. The Excel file must be named Lastname_Firstname. Penalty for late submissions: 5% for each day after deadline. It is your responsibility to name the file as it appears in ROSI. Failure to do so will result in a 5% penalty per incident.
If Blackboard is officially down at the due time and date (see example1) then the deadline of this project is automatically extended by 24 hours; if after 24 hours Blackboard is still down then the deadline of this project is automatically extended by 24 hours, and so on. Do not e-mail the project to the course staff.
1 For example: the project is due on December 1st 11 pm and Blackboard is “down for maintenance on December 1st from 6 pm to midnight”.