1. This assignment will require you to analyze time series of monthly returns. Start by retrievingMONTHLY data for the period of December 31, 2007 – December 31, 2013 from Yahoo Finance website (http://finance.yahoo.com/) for
Ford (ticker = F) (for Students with last names starting from A-R)
IBM (ticker = IBM) (for Students with last names starting from S-Z)
(if you use the wrong company, no points will be allotted for this question)
Instructions for downloading the data from Yahoo!:
To obtain the monthly data, click on “Historical Prices” on the left panel. Select Date Range. Make sure Monthly is selected and then click on “Get Prices”. Scroll down and click on “Download to Spreadsheet”.
For those using MAC: MAC does not automatically save the file as an excel file. So, after you click on “Download to Spreadsheet”, save the file as a .csv file. Then, open Excel (select ALL FILES) and open this file in excel. Before you start doing anything, save this file as a .xls file (.csv file does not retain the formulae and cell references after closing the file).
Instructions for sorting the data by DATE:
Re-sort the data so that the most previous date is at the bottom of your spreadsheet (i.e. highlight DATE columns and click on SORT, make sure “expand the selection” is selected, then click on SORT and sort by Date and order by ‘Oldest to Newest’).
Calculating Returns:
Use the ‘Adjusted Close’ column to obtain returns for each period. Remember that the Adjusted Close column has already adjusted the prices for dividends and stock splits so you do not have to adjust for it again. Just use the adjusted close column to obtain the returns.
Rt
Pt
1
Pt 1
Calculate the following:
a. Average Monthly returns for Ford or IBM or Dell (based on your last name).
b. Standard Deviation and variance of monthly returns for Ford or IBM or Dell (based on your last name).
2. On 30 January 2002, you bought one share of ABC for $80. On 30 January 2003, the stock split 2for 1. On 31st July 2003, the stock splits 2 for 1 and on 31st January 2004, the stock price is $25.
a. Calculate the cumulative adjustment factor
b. Calculate the return since you bought the stock.
Page 1 of 2
3. A stock has the following year-end prices and dividends:
Year
1
2
3
4
5
6
Price
$43.12
49.07
51.19
47.24
56.09
67.21
Dividends
$0.55
0.60
0.63
0.72
0.81
Calculate:
a. Returns for each year
b. Arithmetic average returns
c. Geometric average returns.
4. Below is the stock price and split data for ABC Company.
Date
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
Closing
Price
88.45
92.67
113.87
112
115.45
116.87
121.76
118.9
98.66
92.56
Stock Splits
3 for 2
2 for 1
1.5 for 1
2 for 1
a. Calculate the stock-adjusted returns for each year and its average mean, variance, and standard deviation.
b. If you bought 200 shares of this stock in the beginning of 2000, how many shares would you have at the end of 2009?
