Now begin using the Excel spreadsheet that you downloaded for this assignment. E
ID: 2757022 • Letter: N
Question
Now begin using the Excel spreadsheet that you downloaded for this assignment.
Enter annual price and dividend data into the spreadsheet provided; adjust stock prices and dividends for any stock splits that occurred during the coverage period.(DONE)
Find the open price quote in the first day reported of January of 2013 and enter it into your spreadsheet.(DONE)
Find the dividends that were paid last year and enter it into your spreadsheet. If your firm pays quarterly dividends (like Caterpillar), add the 4 dividends paid that year to get annual dividends.(DONE)
Continue this process to 2012, 2011, and so on to complete the table for your firm. Enter all price and dividend information into the spreadsheet (in the columns that say Student Company).(DONE)
There is a column for Caterpillar so you can follow the process in your entry and calculations. Note that Caterpillar had a 2:1 stock split in 1997 and 2005 and I have adjusted the price information for that split on my spreadsheet. If you have stock splits, you will have to make similar adjustments to stock price and dividends. Download your company’s price information (from Yahoo) to a spreadsheet and attach it to your submission.
After you have entered all of your company data (DONE), compute the company's annual return. Then compute the company's average annual return (at the bottom of your company column) as well as the standard deviation of that return.
Now form an equally-weighted portfolio and compute the portfolio's annual return for all years (you will need to create a portfolio column if there is not one there). Then compute the average annual return as well as the standard deviation of that return.
Finally, compute the average standard deviation of the stocks in the portfolio.
Caterpillar (example) Student company Data HOME DEPOT OPEN PRICE IN JANUARY Unadjusted Caterpillar Dividend Actual Adjusted Annual January Annual Equally-weighted portfolio Price data Adj Price* For year Dividend Dividend Return open price Dividend Return January, 2013 92.95 371.8 2012 2.02 8.08 -2.42% 63.57 1.16 January, 2012 97.33 389.32 2011 1.8 7.2 5.03% 42.41 1.04 January, 2011 94.38 377.52 2010 1.72 6.88 66.70% 35.2 0.944 January, 2010 57.65 230.6 2009 1.68 6.72 32.11% 29.15 0.9 January, 2009 44.91 179.64 2008 1.56 6.24 -35.96% 23.07 0.9 January, 2008 72.56 290.24 2007 1.32 5.28 19.72% 27.13 0.9 January, 2007 61.71 246.84 2006 1.1 4.4 8.54% 41.3 0.675 January, 2006 split 2:1 57.87 231.48 2005 0.91 3.64 22.33% 40.39 0.4 January, 2005 96.1 192.2 2004 0.78 1.56 16.48% 42.99 0.325 January, 2004 83.17 166.34 2003 0.7 1.4 82.52% 35.75 0.26 January, 2003 45.95 91.9 2002 0.7 1.4 -10.89% 24.21 0.21 January, 2002 52.35 104.7 2001 0.69 1.38 12.25% 51.01 0.17 January, 2001 47.25 94.5 2000 0.665 1.33 0.47% 45.69 0.16 January, 2000 47.69 95.38 1999 0.625 1.25 3.77% 68.63 0.11334 split 3:2 January, 1999 46.56 93.12 1998 0.55 1.1 -2.87% 60.38 0.07667 January, 1998 split 2:1 48.5 97 1997 0.45 0.9 32.08% 59 0.06334 split 2:1 January, 1997 74.12 74.12 1996 0.375 0.375 N/A 50.25 0.0511 split 3:2 Average Return 16.82% Standard deviation 29.18%Explanation / Answer
Caterpillar (example) Student company Data HOME DEPOT OPEN PRICE IN JANUARY Unadjusted Caterpillar Dividend Actual Adjusted Annual January Annual Equally-weighted portfolio Price data Adj Price* For year Dividend Dividend Return open price Dividend Return January, 2013 92.95 371.8 2012 2.02 8.08 -2.42% 63.57 1.16 0.52629097 0.25104548 January, 2012 97.33 389.32 2011 1.8 7.2 5.03% 42.41 1.04 0.234375 0.1423375 January, 2011 94.38 377.52 2010 1.72 6.88 66.70% 35.2 0.944 0.23993139 0.45346569 January, 2010 57.65 230.6 2009 1.68 6.72 32.11% 29.15 0.9 0.30255743 0.31182872 January, 2009 44.91 179.64 2008 1.56 6.24 -35.96% 23.07 0.9 -0.11647623 -0.23803811 January, 2008 72.56 290.24 2007 1.32 5.28 19.72% 27.13 0.9 -0.32130751 -0.06205375 January, 2007 61.71 246.84 2006 1.1 4.4 8.54% 41.3 0.675 0.03924239 0.06232119 January, 2006 split 2:1 57.87 231.48 2005 0.91 3.64 22.33% 40.39 0.4 -0.05117469 0.08606265 January, 2005 96.1 192.2 2004 0.78 1.56 16.48% 42.99 0.325 0.21160839 0.1882042 January, 2004 83.17 166.34 2003 0.7 1.4 82.52% 35.75 0.26 0.4874019 0.65630095 January, 2003 45.95 91.9 2002 0.7 1.4 -10.89% 24.21 0.21 -0.52127034 -0.31508517 January, 2002 52.35 104.7 2001 0.69 1.38 12.25% 51.01 0.17 0.12015758 0.12132879 January, 2001 47.25 94.5 2000 0.665 1.33 0.47% 45.69 0.16 -0.33192481 -0.16361241 January, 2000 47.69 95.38 1999 0.625 1.25 3.77% 68.63 0.11334 split 3:2 0.13851176 0.08810588 January, 1999 46.56 93.12 1998 0.55 1.1 -2.87% 60.38 0.07667 0.02468932 -0.00200534 January, 1998 split 2:1 48.5 97 1997 0.45 0.9 32.08% 59 0.06334 split 2:1 0.17538985 0.24809493 January, 1997 74.12 74.12 1996 0.375 0.375 N/A 50.25 0.0511 split 3:2 Average Return 16.82% Standard deviation 29.18% Average return = 0.1142688 Standard deviation = 0.2483647 For equally weighted portfolio we have to take weights as 0.5 for both the stocks.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.