What are the excel formulas used to compute annual returns, equally weighted por

Question

What are the excel formulas used to compute annual returns, equally weighted portfolio, average return and standard deviation for Home Depot in the spreadsheet (in bold)?

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.

Explanation / Answer

(a)

Let us assume:

Opening Price data is in cell A1, Closing price (or Opening price of next year) data is in cell B1 & annual dividend data is in cell C1.

Then:

Formula for Annual Returns:

=(B1 - A1 + C1) / A1

(b) For "Equally Weighted Portfolio":

Let us assume:

Annual Return data for stock A is in cell A1, Annual Return data for stock B is in cell B1.

Formula for Average return:

=AVERAGE(A1:B1)

(c) For "Equally Weighted Portfolio":

Let us assume:

Variance data for stock A is in cell A1, Variance data for stock B is in cell B1, Covariance data (between A & B) is in cell C1, then

Formula for Portfolio Standard Deviation:**

=SQRT(0.25*A1 + 0.25*B1 + 0.5*C1)

**

1. Formula for portfolio variance for equal-weighted portfolio for stocks A & B is:

(0.5)2 x (Variance)A + (0.5)2 x (Variance)B + 2 x 0.5 x 0.5 x CovarianceA,B

In the excel formula, we need to write 0.25 for (0.5)2 & 0.5 for (2 x 0.5 x 0.5 ).

2. We can compute the individual stocks variance & covariance as follows.

Let's assume there are 20 data sets (observations) from cell A1 to A20 (for stock A). Then,

(a) Formula for Variance for stock A is:

=VAR(A1:A20)

(b) Formula for Standard Deviation for stock A is:

=STDEV(A1:A20)

(c) If we have the data set for stock B in cells B1 to B20, then

Formula for Covariance between A & B is:

=COVAR(A1:A20, B1:B20)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.