Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Efficient Frontier and CAPM Exercise Efficient Frontier and CAPM Exercise. Estimated completion time: for Excel experts 2 hours, for Excel novices 20 hours. 1. The file 2023FallDataFileWRDS...

1 answer below »
Efficient Frontier and CAPM Exercise
Efficient Frontier and CAPM Exercise.
Estimated completion time: for Excel experts 2 hours, for Excel novices 20 hours.
1. The file 2023FallDataFileWRDS contains monthly closing prices (col D), cash dividends (C)
and cum split factors (E) for GE, AAPL and PG (stocks in that order). The file
2023FallTBills contains annualized TBill rates (divide by 1200 to convert to monthly %).
a. Create a sheet titled “PriceData” with 10 columns: Date, GEPrice, GEDiv, GEFact,
AAPLPrice, AAPLDiv, AAPLFact, PGPrice, PGDiv, PGFact. Dates should run from
Dec 2013 to Dec 2021, i.e. 97 rows of data.
. Create a sheet “ReturnData” with 5 columns: Date, GE, AAPL, PG, MKT Index.
Compute returns for each stock for each month by taking the natural log of [that month’s
price with the dividend added if any] divided by the cum split factor minus the natural log
of the previous month’s price divided by the factor. That is, compute the continuously
compounded return Rt=ln((Pt+Dt)/Ft)-ln(Pt-1/Ft-1) instead of a simple arithmetic return Rt=
(Pt+Dt -Pt-1)/Pt-1. Pt denotes the price for a given month and Pt-1 denotes the price for the
prior month. Dates run from Jan 2014 to Dec 2021; no Dec 2013; 96 rows of returns. ln()
is the natural logarithm function in Excel, so your formula might be =ln((D5 +C5)/E5)-
ln(D6/E6) if factors are in col F and dividends in col C. For MKT Index; copy and
eformat to % the repeated Value-Weighted Index returns in col G; make sure dates align.
Sort all tabs in reverse chronological order, e.g. Dec2021 to Jan2014. Check that your
formulas are still co
ect, ie ln(this month’s adj price) minus ln(prior), not vice versa.
2. Create a sheet titled “PG”. First, without the use of functions average() and stdev(), compute
the average and the std. deviation of PG’s returns. Copy the dates and PG returns from
“ReturnData” into the sheet, sum the return column and divide by the number of observations
(=96) to get the mean Ravg. Create a column with (Rt -Ravg)2, sum it, divide by the number of
observations minus one (=95), take a square root (sqrt()) of the result to get the st. deviation.
Verify next to it that the Excel functions average() and stdev() give identical answers.
3. Create a sheet “PortfolioReturns” (like “ReturnData”) in which you generate returns on
portfolios with the follwng respective weights for the three stocks: (30, 20, 50), (40, 40, 10),
(10, 40, 50), (33 1/3, 33 1/3, 33 1/3), (50, 25, 25), (25, 50, 25), (25, 25, 50), (10, 20, 70), (20,
10, 70), (70, 20, 10). For example, (25, 50, 25) means that you put 25% in GE, 50% in AAPL
and 25% in PG. Prepend a column of dates and three columns of individual stock returns.
Append a column with Index returns. You end up with 15 columns: Date, the three stocks,
the 10 portfolios labeled P1-P10, and Index. Dates Jan 2014 to Dec 2021, i.e. 96 rows. At the
ottom of each return column compute the mean and the st. dev. of monthly returns using
functions AVERAGE() and STDEV(). Note: XXXXXXXXXX ≠ 33 1/3. Enter (1/3), not 0.3333.
4. Create a sheet “Frontier”. Copy the results from 3. into a transposed 14rows x 3cols table.
Columns are: portfolio label, stdev, mean. Rows are the 14 assets. Create an efficient frontier
graph with mean on the Y-axis and st. deviation on the X-axis. Use “XY Scatter” from the
graph menu. Each portfolio should be represented by a labeled dot. Assume the risk free
(TBill) rate of 0.05%= XXXXXXXXXXAdd the Capital Market Line which goes through point (0,
0.05%) and is tangent to the efficient frontier (eyeball to tell which of the 14 points it should
go close to). You can do this last part (CML) by hand/i.e. using the draw tool.
5.
a. Create a sheet “Regressions”. Copy the date column from “ReturnData” into Col A.
From the 2023SpringTBills.xlsx file copy 96 Tbill rates for the co
ect months (use
the right-hand col where the rates are decompounded to monthly and formatted to %).
Place the result in Col B. Add 4 cols C-F in which you compute the excess returns on
the three stocks and Index over the TBill rates.
. Create a sheet “Beta”. Repeatedly (3 times) use the regression function in Excel to
compute betas for the three stocks. Regression can be found in Excel under Data ->
Data Analysis; scroll to Regression. If it doesn’t appear, go to Office icon -> Excel
options -> Add-ins, and add the Analysis Tool pack. Use a DESB computer if your
home one lacks this function. In the regression window, Y-variable is the column
ange of stock excess returns, the X-variable is always the column range of excess
MKT Index returns. Beta will be the slope coefficient in the output range. (It will be
labeled “XVariable” if you don’t include header row). In “ReturnData”, add a row
labeled Beta at the bottom. Copy the tree betas. Add the market beta for Index (=?
think a little:-). Compute the 10 betas for the portfolios by weighting the stock betas.
6.
a. Create a sheet “SML”. Copy the 14 betas from Part-5b into a column. Copy the
co
esponding 14 return means from Part 3 into another column. Append a row in the table
for TBill with its mean 0.01%=0.0001 and beta=0. Prepend a col of labels.

. Create two new sheets. As in Part 1 and 2, create 10 rows of monthly price data for GE,
AAPL and PG from Dec 2021 to Sep 2022 into a new sheet “NewPriceData”. Compute 9
ows of returns from Jan 2022 to Sep XXXXXXXXXXprices  9 returns) for the four securities
(GE, AAPL, PG, Index) in “NewReturnData”. Add 10 columns for Portfolios 1-10 and
compute their returns. As before, at the bottom of the “NewReturnData” compute the means
and st. deviations for the four securities and the 10 portfolios.

c. Overwrite the old means for the stocks, the MKT and the portfolios in the “SML” sheet
with the new means. (We have the betas predicted by the XXXXXXXXXXdata, but the realized
mean returns for XXXXXXXXXXAlso copy/overwrite the TBills 2022 mean with 0.40%=0.0040.

d. Plot the (new) mean returns against (old) betas on a graph using “XY Scatter” with Y-axis
as the mean and X-axis as beta. Label your stocks and portfolios. Use the Draw tool to trace
the Security Market Line through TBill and MKT.


Format (%s, graph labels, table titles) the entire project in Excel and Word appropriately
like for a business presentation. Bad formatting incurs a penalty up to 10%.

Must do
To ensure your success, the project proceeds in two stages.
Stage 1: Complete parts 1–3, attempt to do parts 4–6 asap or by Week 2. You can upload the
partial spreadsheet if you are unsure about anything, not required. Ask lots of questions about
Stage 1 or Stage 2 during Week 2-3, to fix all e
ors. Then,
Stage 2: Complete parts 4–6 by the deadline. Follow the instructions for submission.

Important: What to submit to receive credit
Upon final completion (Stage 2) upload two files on Canvas:

1. Upload on Canvas your Excel book (not to exceed 350 KB) by Sunday 11:59pm on the due
date. Your name must appear in cells A1 of the “PriceData” and the “NewPriceData”. Your file
must be named using the LastFirst.xlsx convention: if you are Adam Smith, then the file name is
SmithAdam.xlsx. Follow the file naming convention to receive credit.

2. Create in Word and upload on Canvas by Sunday 11:59pm on the due date a three page
document. The three pages are: (1) A cover page with your name, (2) The table and graph from
Part 4, (3) The table and graph from Part 6. Make sure your graphs and tables show well.

FRED Graph
    FRED Graph Observations
    Federal Reserve Economic Data
    Link: https:
fred.stlouisfed.org
    Help: https:
fredhelp.stlouisfed.org
    Economic Research Division
    Federal Reserve Bank of St. Louis
    TB3MS    3-Month Treasury Bill Secondary Market Rate, Discount Basis, Percent, Monthly, Not Seasonally Adjusted
    Frequency: Monthly
    observation_date    TB3MS
     XXXXXXXXXX    0.72
     XXXXXXXXXX    0.62
     XXXXXXXXXX    0.24
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.16
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.19
     XXXXXXXXXX    0.21
     XXXXXXXXXX    0.27
     XXXXXXXXXX    0.25
     XXXXXXXXXX    0.23
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.19
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.16
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.16
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.16
     XXXXXXXXXX    0.13
     XXXXXXXXXX    0.11
     XXXXXXXXXX    0.12
     XXXXXXXXXX    0.17
     XXXXXXXXXX    0.15
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.56
     XXXXXXXXXX    0.41
     XXXXXXXXXX    0.36
     XXXXXXXXXX    0.28
     XXXXXXXXXX    0.29
     XXXXXXXXXX    0.31
     XXXXXXXXXX    0.20
     XXXXXXXXXX    0.09
     XXXXXXXXXX    0.11
     XXXXXXXXXX    0.10
     XXXXXXXXXX    0.08
     XXXXXXXXXX    0.08
     XXXXXXXXXX    0.09
     XXXXXXXXXX    0.05
     XXXXXXXXXX    0.05
     XXXXXXXXXX    0.07
     XXXXXXXXXX    0.06
     XXXXXXXXXX    0.08
     XXXXXXXXXX    0.05
     XXXXXXXXXX    0.04
     XXXXXXXXXX    0.03
     XXXXXXXXXX    0.03
     XXXXXXXXXX    0.03
     XXXXXXXXXX    0.03
     XXXXXXXXXX    0.03
     XXXXXXXXXX    0.03
     XXXXXXXXXX    0.03
     XXXXXXXXXX    0.04
     XXXXXXXXXX    0.05
     XXXXXXXXXX    0.14
     XXXXXXXXXX    0.05
     XXXXXXXXXX    0.05
     XXXXXXXXXX    0.04
     XXXXXXXXXX    0.01
     XXXXXXXXXX    0.02
     XXXXXXXXXX    0.02
     XXXXXXXXXX    0.02
     XXXXXXXXXX    0.06
     XXXXXXXXXX    0.10
     XXXXXXXXXX    0.05
     XXXXXXXXXX    0.04
     XXXXXXXXXX    0.05
     XXXXXXXXXX    0.02
     XXXXXXXXXX    0.02
     XXXXXXXXXX    0.02
     XXXXXXXXXX    0.02
     XXXXXXXXXX    0.04
     XXXXXXXXXX    0.11
     XXXXXXXXXX    0.10
     XXXXXXXXXX    0.11
     XXXXXXXXXX    0.12
     XXXXXXXXXX    0.12
     XXXXXXXXXX    0.13
     XXXXXXXXXX    0.10
     XXXXXXXXXX    0.09
     XXXXXXXXXX    0.28
     XXXXXXXXXX    0.33
     XXXXXXXXXX    0.27
     XXXXXXXXXX    0.25
     XXXXXXXXXX    0.25
     XXXXXXXXXX    0.32
     XXXXXXXXXX    0.37
     XXXXXXXXXX    0.37
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.38
     XXXXXXXXXX    0.66
     XXXXXXXXXX    0.75
     XXXXXXXXXX    0.80
     XXXXXXXXXX    0.85
     XXXXXXXXXX    0.92
     XXXXXXXXXX    0.95
     XXXXXXXXXX    0.97
     XXXXXXXXXX    1.00
     XXXXXXXXXX    1.00
     XXXXXXXXXX    1.00
     XXXXXXXXXX    1.00
     XXXXXXXXXX    1.00
     XXXXXXXXXX    1.00
     XXXXXXXXXX    1.06
     XXXXXXXXXX    1.09
     XXXXXXXXXX    1.12
     XXXXXXXXXX    1.14
     XXXXXXXXXX    1.16
     XXXXXXXXXX    1.17
     XXXXXXXXXX    1.17
     XXXXXXXXXX    1.17
     XXXXXXXXXX    1.17
     XXXXXXXXXX    1.17
     XXXXXXXXXX    1.17
     XXXXXXXXXX    1.02
     XXXXXXXXXX    1.04
     XXXXXXXXXX    1.07
     XXXXXXXXXX    1.05
     XXXXXXXXXX    1.08
     XXXXXXXXXX    1.10
     XXXXXXXXXX    1.07
     XXXXXXXXXX    1.12
     XXXXXXXXXX    1.12
     XXXXXXXXXX    1.15
     XXXXXXXXXX    1.16
     XXXXXXXXXX    1.15
     XXXXXXXXXX    1.16
     XXXXXXXXXX    1.20
     XXXXXXXXXX    1.30
     XXXXXXXXXX    1.31
     XXXXXXXXXX    1.36
     XXXXXXXXXX    1.34
     XXXXXXXXXX    1.34
     XXXXXXXXXX    1.36
     XXXXXXXXXX    1.40
     XXXXXXXXXX    1.47
     XXXXXXXXXX    1.55
     XXXXXXXXXX    1.45
     XXXXXXXXXX    1.56
     XXXXXXXXXX    1.62
     XXXXXXXXXX    1.63
     XXXXXXXXXX    1.54
     XXXXXXXXXX    1.56
     XXXXXXXXXX    1.73
     XXXXXXXXXX    1.57
     XXXXXXXXXX    1.54
     XXXXXXXXXX    1.59
     XXXXXXXXXX    1.57
     XXXXXXXXXX    1.67
     XXXXXXXXXX    1.70
     XXXXXXXXXX    1.81
     XXXXXXXXXX    1.83
     XXXXXXXXXX    1.71
     XXXXXXXXXX    1.74
     XXXXXXXXXX    1.85
     XXXXXXXXXX    2.09
     XXXXXXXXXX    1.96
     XXXXXXXXXX    1.97
     XXXXXXXXXX    2.01
     XXXXXXXXXX    2.19
     XXXXXXXXXX    2.16
     XXXXXXXXXX    2.11
     XXXXXXXXXX    2.04
     XXXXXXXXXX    2.04
     XXXXXXXXXX    1.79
     XXXXXXXXXX    1.38
     XXXXXXXXXX    1.44
     XXXXXXXXXX    1.60
     XXXXXXXXXX    1.18
     XXXXXXXXXX    0.97
     XXXXXXXXXX    1.03
     XXXXXXXXXX    0.97
     XXXXXXXXXX    0.76
     XXXXXXXXXX    0.64
     XXXXXXXXXX    0.72
     XXXXXXXXXX    0.92
     XXXXXXXXXX    1.01
     XXXXXXXXXX    0.98
     XXXXXXXXXX    0.93
     XXXXXXXXXX    1.15
     XXXXXXXXXX    1.22
     XXXXXXXXXX    1.17
     XXXXXXXXXX    1.28
     XXXXXXXXXX    1.59
     XXXXXXXXXX    1.45
     XXXXXXXXXX    1.41
     XXXXXXXXXX    1.60
     XXXXXXXXXX    1.90
     XXXXXXXXXX    2.07
     XXXXXXXXXX    2.23
     XXXXXXXXXX    2.24
     XXXXXXXXXX    2.54
     XXXXXXXXXX    2.41
     XXXXXXXXXX    2.32
     XXXXXXXXXX    2.25
     XXXXXXXXXX    2.60
     XXXXXXXXXX    2.61
     XXXXXXXXXX    2.49
     XXXXXXXXXX    2.31
     XXXXXXXXXX    2.60
     XXXXXXXXXX    2.84
     XXXXXXXXXX    2.90
     XXXXXXXXXX    2.99
     XXXXXXXXXX    3.21
     XXXXXXXXXX    3.11
     XXXXXXXXXX    3.10
     XXXXXXXXXX    3.08
     XXXXXXXXXX    3.07
     XXXXXXXXXX    3.06
     XXXXXXXXXX    3.29
     XXXXXXXXXX    3.16
     XXXXXXXXXX    3.37
     XXXXXXXXXX    3.53
     XXXXXXXXXX    3.58
     XXXXXXXXXX    3.31
     XXXXXXXXXX    3.04
     XXXXXXXXXX    2.44
     XXXXXXXXXX    1.53
     XXXXXXXXXX    1.30
     XXXXXXXXXX    1.13
     XXXXXXXXXX    0.91
     XXXXXXXXXX    0.83
     XXXXXXXXXX    0.91
     XXXXXXXXXX    1.69
     XXXXXXXXXX    2.44
     XXXXXXXXXX    2.63
     XXXXXXXXXX    2.67
     XXXXXXXXXX    2.77
     XXXXXXXXXX    2.82
     XXXXXXXXXX    2.70
     XXXXXXXXXX    2.80
     XXXXXXXXXX    2.95
     XXXXXXXXXX    2.84
     XXXXXXXXXX    3.21
     XXXXXXXXXX    3.20
     XXXXXXXXXX    3.38
     XXXXXXXXXX    4.04
     XXXXXXXXXX    4.05
     XXXXXXXXXX    4.15
     XXXXXXXXXX    4.49
     XXXXXXXXXX    4.35
     XXXXXXXXXX    3.96
     XXXXXXXXXX    3.31
     XXXXXXXXXX    3.23
     XXXXXXXXXX    3.29
     XXXXXXXXXX    2.46
     XXXXXXXXXX    2.30
     XXXXXXXXXX    2.30
     XXXXXXXXXX    2.48
     XXXXXXXXXX    2.30
     XXXXXXXXXX    2.37
     XXXXXXXXXX    2.25
     XXXXXXXXXX    2.24
     XXXXXXXXXX    2.42
     XXXXXXXXXX    2.39
     XXXXXXXXXX    2.29
     XXXXXXXXXX    2.29
     XXXXXXXXXX    2.33
     XXXXXXXXXX    2.24
     XXXXXXXXXX    2.39
     XXXXXXXXXX    2.28
     XXXXXXXXXX    2.30
     XXXXXXXXXX    2.48
     XXXXXXXXXX    2.60
     XXXXXXXXXX    2.72
     XXXXXXXXXX    2.73
     XXXXXXXXXX    2.72
     XXXXXXXXXX    2.73
     XXXXXXXXXX    2.69
     XXXXXXXXXX    2.73
     XXXXXXXXXX    2.92
     XXXXXXXXXX    2.82
     XXXXXXXXXX    2.78
     XXXXXXXXXX    2.74
     XXXXXXXXXX    2.83
     XXXXXXXXXX    2.87
     XXXXXXXXXX    2.91
     XXXXXXXXXX    2.92
     XXXXXXXXXX    2.89
     XXXXXXXXXX    2.90
     XXXXXXXXXX    2.93
     XXXXXXXXXX    2.99
     XXXXXXXXXX    3.18
     XXXXXXXXXX    3.32
     XXXXXXXXXX    3.38
     XXXXXXXXXX    3.45
     XXXXXXXXXX    3.52
     XXXXXXXXXX    3.52
     XXXXXXXXXX    3.52
     XXXXXXXXXX    3.53
     XXXXXXXXXX    3.54
     XXXXXXXXXX    3.47
     XXXXXXXXXX    3.48
     XXXXXXXXXX    3.48
     XXXXXXXXXX    3.46
     XXXXXXXXXX    3.50
     XXXXXXXXXX    3.53
     XXXXXXXXXX    3.57
     XXXXXXXXXX    3.64
     XXXXXXXXXX    3.84
     XXXXXXXXXX    3.81
     XXXXXXXXXX    3.93
     XXXXXXXXXX    3.93
     XXXXXXXXXX    3.93
     XXXXXXXXXX    3.89
     XXXXXXXXXX    3.80
     XXXXXXXXXX    3.84
     XXXXXXXXXX    3.84
     XXXXXXXXXX    3.92
     XXXXXXXXXX    4.03
     XXXXXXXXXX    4.09
     XXXXXXXXXX    4.38
     XXXXXXXXXX    4.59
     XXXXXXXXXX    4.65
     XXXXXXXXXX    4.59
     XXXXXXXXXX    4.62
     XXXXXXXXXX    4.64
     XXXXXXXXXX    4.50
     XXXXXXXXXX    4.80
     XXXXXXXXXX    4.96
     XXXXXXXXXX    5.37
     XXXXXXXXXX    5.35
     XXXXXXXXXX    5.32
     XXXXXXXXXX    4.96
     XXXXXXXXXX    4.72
     XXXXXXXXXX    4.56
     XXXXXXXXXX    4.26
     XXXXXXXXXX    3.84
     XXXXXXXXXX    3.60
     XXXXXXXXXX    3
Answered 3 days After Feb 28, 2023

Solution

Mayuri answered on Mar 03 2023
35 Votes
PriceData
    DATE    GEPrice    GEDiv    GEFact    AAPLPrice    AAPLDiv    AAPLFact    PGPrice    PGDiv    PGFact
    12/31/13    28.0300006866    0.2199999988    0.125    561.0200195313        28    81.4100036621        1
    1/31/14    25.1299991608        0.125    500.6000061035        28    76.6200027466    0.6014999747    1
    2/28/14    25.4699993134    0.2199999988    0.125    526.2399902344    3.0499999523    28    78.6600036621        1
    3/31/14    25.8899993896        0.125    536.7399902344        28    80.5999984741        1
    4/30/14    26.8899993896        0.125    590.0900268555        28    82.5500030518    0.643599987    1
    5/30/14    26.7900009155        0.125    633    3.2899999619    28    80.7900009155        1
    6/30/14    26.2800006866    0.2199999988    0.125    92.9300003052    0    4    78.5899963379        1
    7/31/14    25.1499996185        0.125    95.5999984741        4    77.3199996948    0.643599987    1
    8/29/14    25.9799995422        0.125    102.5    0.4699999988    4    83.1100006104        1
    9/30/14    25.6200008392    0.2199999988    0.125    100.75        4    83.7399978638        1
    10/31/14    25.8099994659        0.125    108        4    87.2699966431    0.643599987    1
    11/28/14    26.4899997711        0.125    118.9300003052    0.4699999988    4    90.4300003052        1
    12/31/14    25.2700004578    0.2300000042    0.125    110.3799972534        4    91.0899963379        1
    1/30/15    23.8899993896        0.125    117.1600036621        4    84.2900009155    0.643599987    1
    2/27/15    25.9899997711    0.2300000042    0.125    128.4600067139    0.4699999988    4    85.1299972534        1
    3/31/15    24.8099994659        0.125    124.4300003052        4    81.9400024414        1
    4/30/15    27.0799999237        0.125    125.1500015259        4    79.5100021362    0.662899971    1
    5/29/15    27.2700004578        0.125    130.2799987793    0.5199999809    4    78.3899993896        1
    6/30/15    26.5699996948    0.2300000042    0.125    125.4250030518        4    78.2399978638        1
    7/31/15    26.1000003815        0.125    121.3000030518        4    76.6999969482    0.662899971    1
    8/31/15    24.8199996948        0.125    112.7600021362    0.5199999809    4    70.6699981689        1
    9/30/15    25.2199993134    0.2300000042    0.125    110.3000030518        4    71.9400024414        1
    10/30/15    28.9200000763        0.125    119.5        4    76.3799972534    0.662899971    1
    11/30/15    29.9400005341        0.125    118.3000030518    0.5199999809    4    74.8399963379        1
    12/31/15    31.1499996185    0.2300000042    0.125    105.2600021362        4    79.4100036621        1
    1/29/16    29.1000003815        0.125    97.3399963379        4    81.6900024414    0.662899971    1
    2/29/16    29.1399993896    0.2300000042    0.125    96.6900024414    0.5199999809    4    80.2900009155        1
    3/31/16    31.7900009155        0.125    108.9899978638        4    82.3099975586        1
    4/29/16    30.75        0.125    93.7399978638        4    80.1200027466    0.6694999933    1
    5/31/16    30.2299995422        0.125    99.8600006104    0.5699999928    4    81.0400009155        1
    6/30/16    31.4799995422    0.2300000042    0.125    95.5999984741        4    84.6699981689        1
    7/29/16    31.1399993896        0.125    104.2099990845        4    85.5899963379    0.6694999933    1
    8/31/16    31.2399997711        0.125    106.0999984741    0.5699999928    4    87.3099975586        1
    9/30/16    29.6200008392    0.2300000042    0.125    113.0500030518        4    89.75        1
    10/31/16    29.1000003815        0.125    113.5400009155        4    86.8000030518    0.6694999933    1
    11/30/16    30.7600002289        0.125    110.5199966431    0.5699999928    4    82.4599990845        1
    12/30/16    31.6000003815    0.2399999946    0.125    115.8199996948        4    84.0800018311        1
    1/31/17    29.7000007629        0.125    121.3499984741        4    87.5999984741    0.6694999933    1
    2/28/17    29.8099994659    0.2399999946    0.125    136.9900054932    0.5699999928    4    91.0699996948        1
    3/31/17    29.7999992371        0.125    143.6600036621        4    89.8499984741        1
    4/28/17    28.9899997711        0.125    143.6499938965        4    87.3300018311    0.6895999908    1
    5/31/17    27.3799991608        0.125    152.7599945068    0.6299999952    4    88.0899963379        1
    6/30/17    27.0100002289    0.2399999946    0.125    144.0200042725        4    87.1500015259        1
    7/31/17    25.6100006104        0.125    148.7299957275        4    90.8199996948    0.6895999908    1
    8/31/17    24.5499992371        0.125    164    0.6299999952    4    92.2699966431        1
    9/29/17    24.1800003052    0.2399999946    0.125    154.1199951172        4    90.9800033569        1
    10/31/17    20.1599998474        0.125    169.0399932861        4    86.3399963379    0.6895999908    1
    11/30/17    18.2900009155        0.125    171.8500061035    0.6299999952    4    89.9899978638        1
    12/29/17    17.4500007629    0.1199999973    0.125    169.2299957275        4    91.8799972534        1
    1/31/18    16.1700000763        0.125    167.4299926758        4    86.3399963379    0.6895999908    1
    2/28/18    14.1099996567    0.1199999973    0.125    178.1199951172    0.6299999952    4    78.5199966431        1
    3/29/18    13.4799995422        0.125    167.7799987793        4    79.2799987793        1
    4/30/18    14.0699996948        0.125    165.2599945068        4    72.3399963379    0.7171999812    1
    5/31/18    14.0799999237        0.125    186.8699951172    0.7300000191    4    73.1699981689        1
    6/29/18    13.6099996567    0.1199999973    0.125    185.1100006104        4    78.0599975586        1
    7/31/18    13.6300001144        0.125    190.2899932861        4    80.8799972534    0.7171999812    1
    8/31/18    12.9399995804        0.125    227.6300048828    0.7300000191    4    82.9499969482        1
    9/28/18    11.2899999619    0.1199999973    0.125    225.7400054932        4    83.2300033569        1
    10/31/18    10.1000003815        0.125    218.8600006104        4    88.6800003052    0.7171999812    1
    11/30/18    7.5        0.125    178.5800018311    0.7300000191    4    94.5100021362        1
    12/31/18    7.5700001717    0.0099999998    0.125    157.7400054932        4    91.9199981689        1
    1/31/19    10.1599998474        0.125    166.4400024414        4    96.4700012207    0.7171999812    1
    2/28/19    10.3900003433    0.3972391486    0.125    173.1499938965    0.7300000191    4    98.5500030518        1
    3/29/19    9.9899997711    0.0099999998    0.125    189.9499969482        4    104.0500030518        1
    4/30/19    10.1700000763        0.125    200.6699981689        4    106.4800033569    0.7458999753    1
    5/31/19    9.4399995804        0.125    175.0700073242    0.7699999809    4    102.9100036621        1
    6/28/19    10.5    0.0099999998    0.125    197.9199981689        4    109.6500015259        1
    7/31/19    10.4499998093        0.125    213.0399932861        4    118.0400009155    0.7458999753    1
    8/30/19    8.25        0.125    208.7400054932    0.7699999809    4    120.2300033569        1
    9/30/19    8.9399995804    0.0099999998    0.125    223.9700012207        4    124.3799972534        1
    10/31/19    9.9799995422        0.125    248.7599945068        4    124.5100021362    0.7458999753    1
    11/29/19    11.2700004578        0.125    267.25    0.7699999809    4    122.0599975586        1
    12/31/19    11.1599998474    0.0099999998    0.125    293.6499938965        4    124.9000015259        1
    1/31/20    12.4499998093        0.125    309.5100097656        4    124.6200027466    0.7458999753    1
    2/28/20    10.8800001144        0.125    273.3599853516    0.7699999809    4    113.2300033569        1
    3/31/20    7.9400000572    0.0099999998    0.125    254.2899932861        4    110        1
    4/30/20    6.8000001907        0.125    293.799987793        4    117.8700027466    0.7907000184    1
    5/29/20    6.5700001717        0.125    317.9400024414    0.8199999928    4    115.9199981689        1
    6/30/20    6.8299999237    0.0099999998    0.125    364.799987793        4    119.5699996948        1
    7/31/20    6.0700001717        0.125    425.0400085449        4    131.1199951172    0.7907000184    1
    8/31/20    6.3400001526        0.125    129.0399932861    0.8199999928    1    138.3300018311        1
    9/30/20    6.2300000191    0.0099999998    0.125    115.8099975586        1    138.9900054932        1
    10/30/20    7.4200000763        0.125    108.8600006104        1    137.1000061035    0.7907000184    1
    11/30/20    10.1800003052        0.125    119.0500030518    0.2049999982    1    138.8699951172        1
    12/31/20    10.8000001907    0.0099999998    0.125    132.6900024414        1    139.1399993896        1
    1/29/21    10.6800003052        0.125    131.9600067139        1    128.2100067139    0.7907000184    1
    2/26/21    12.5399999619        0.125    121.2600021362    0.2049999982    1    123.5299987793        1
    3/31/21    13.1300001144    0.0099999998    0.125    122.1500015259        1    135.4299926758        1
    4/30/21    13.1199998856        0.125    131.4600067139        1    133.4199981689    0.8697999716    1
    5/28/21    14.0600004196        0.125    124.6100006104    0.2199999988    1    134.8500061035        1
    6/30/21    13.4600000381    0.0099999998    0.125    136.9600067139        1    134.9299926758        1
    7/30/21    12.9499998093        0.125    145.8600006104        1    142.2299957275    0.8697999716    1
    8/31/21    105.4100036621    0    1    151.8300018311    0.2199999988    1    142.3899993896        1
    9/30/21    103.0299987793    0.0799999982    1    141.5        1    139.8000030518        1
    10/29/21    104.8700027466        1    149.8000030518        1    142.9900054932    0.8697999716    1
    11/30/21    94.9899978638        1    165.3000030518    0.2199999988    1    144.5800018311        1
    12/31/21    94.4700012207    0.0799999982    1    177.5700073242        1    163.5800018311        1
ReturnData
    DATE     GE    AAPL    PG    MKT Index.
    1/31/14    -0.1092130715    -0.1139491991    -0.0528202097    -3.00%
    2/28/14    0.0220394662    0.0557290761    0.0262766384    4.62%
    3/31/14    0.0163555082    0.0197564239    0.024363817    0.45%
    4/30/14    0.0378976774    0.0947613254    0.0316718277    0.16%
    5/30/14    -0.0037257299    0.075379321    -0.0215510005    2.03%
    6/30/14    -0.0108839589    0.0272862518    -0.0276087885    2.80%
    7/31/14    -0.0439503384    0.0283262792    -0.0080023713    -2.06%
    8/29/14    0.0324691122    0.0742648796    0.0722123885    4.02%
    9/30/14    -0.0054032823    -0.0172205978    0.0075516962    -2.52%
    10/31/14    0.0073886638    0.0694890263    0.0486377406    2.12%
    11/28/14    0.0260053016    0.1003479764    0.0355693514    2.11%
    12/31/14    -0.0380888244    -0.0746061542    0.0072719144    -0.37%
    1/30/15    -0.0561580037    0.0596116201    -0.0699782032    -2.72%
    2/27/15    0.093062534    0.0957291203    0.0099162228    5.60%
    3/31/15    -0.0464650637    -0.0318743129    -0.0381921657    -1.04%
    4/30/15    0.087548669    0.0057697188    -0.0218017231    0.88%
    5/29/15    0.0069917684    0.0441563995    -0.0141864664    1.03%
    6/30/15    -0.0173853374    -0.0379779763    -0.001915362    -1.92%
    7/31/15    -0.0178474193    -0.0334411536    -0.0112737007    1.21%
    8/31/15    -0.0502855615    -0.0684041925    -0.0818805398    -6.00%
    9/30/15    0.0250659487    -0.0220577313    0.0178113434    -3.38%
    10/30/15    0.1368960966    0.0801124174    0.0685299013    7.40%
    11/30/15    0.0346619969    -0.0057066029    -0.0203683934    0.24%
    12/31/15    0.0469753385    -0.1167902966    0.0592718986    -2.22%
    1/29/16    -0.0680760457    -0.0782235334    0.0363893498    -5.71%
    2/29/16    0.0092355373    -0.001336355    -0.0172865336    0.06%
    3/31/16    0.0870400239    0.1197461056    0.0248474857    7.06%
    4/29/16    -0.0332618095    -0.1507311456    -0.0186455368    1.17%
    5/31/16    -0.0170552006    0.0689360037    0.0114173262    1.44%
    6/30/16    0.0477972806    -0.0435964071    0.0438184548    0.29%
    7/29/16    -0.0108592622    0.0862352811    0.0185988262    3.88%
    8/31/16    0.0032061708    0.023331857    0.0198965648    0.28%
    9/30/16    -0.0455144496    0.0634481945    0.0275630515    0.30%
    10/31/16    -0.0177116499    0.0043249801    -0.0257378314    -2.16%
    11/30/16    0.0554769648    -0.0218145525    -0.0512933406    4.05%
    12/30/16    0.0345082207    0.0468407895    0.0194554322    1.88%
    1/31/17    -0.0620100612    0.0466416604    0.0486258689    2.22%
    2/28/17    0.0117155715    0.1253813058    0.0388474577    3.26%
    3/31/17    -0.0003355219    0.0475414516    -0.013486842    0.21%
    4/28/17    -0.0275574329    -0.0000696792    -0.0205820586    0.97%
    5/31/17    -0.0571381458    0.065603919    0.0086649102    0.93%
    6/30/17    -0.0047592599    -0.0589158179    -0.0107281881    0.96%
    7/31/17    -0.0532242522    0.0321803444    0.0488130919    2.03%
    8/31/17    -0.0422711009    0.1015679788    0.0158395029    0.16%
    9/29/17    -0.0053093422    -0.0621349397    -0.0140792862    2.38%
    10/31/17    -0.1818254222    0.0924038455    -0.0443914905    1.93%
    11/30/17    -0.0973459232    0.0201459895    0.0414055816    2.73%
    12/29/17    -0.040161567    -0.0153633276    0.0207848186    1.21%
    1/31/18    -0.0761820141    -0.0106934015    -0.0542350994    5.06%
    2/28/18    -0.1278052905    0.0654228445    -0.0949396211    -3.95%
    3/29/18    -0.04567667    -0.0598038629    0.009632548    -1.84%
    4/30/18    0.0428377779    -0.0151336321    -0.081743229    0.47%
    5/31/18    0.0007104959    0.1267920526    0.0114083    2.62%
    6/29/18    -0.0251721507    -0.0094629155    0.0646922546    0.54%
    7/31/18    0.0014684627    0.0275989428    0.0443171833    3.16%
    8/31/18    -0.0519499975    0.1823741635    0.0252714391    3.02%
    9/28/18    -0.1258330963    -0.0083376063    0.0033699202    0.04%
    10/31/18    -0.1113819132    -0.0309516601    0.0714814649    -7.40%
    11/30/18    -0.2976324411    -0.199316097    0.0636712834    1.85%
    12/31/18    0.0106102017    -0.1240885475    -0.027787058    -8.99%
    1/31/19    0.294265337    0.053686756    0.0557204143    8.83%
    2/28/19    0.0599054799    0.0437304723    0.0213319727    3.27%
    3/29/19    -0.0382587681    0.092602628    0.0543075176    1.30%
    4/30/19    0.0178576478    0.0549008951    0.0300662728    3.79%
    5/31/19    -0.0744862819    -0.1320872265    -0.0341023502    -6.17%
    6/28/19    0.1073712492    0.1226769636    0.0634386333    6.73%
    7/31/19    -0.004773297    0.0736170106    0.0800292317    1.19%
    8/30/19    -0.2363887598    -0.0167084129    0.0183830451    -2.03%
    9/30/19    0.081440285    0.0704226351    0.0339347707    1.60%
    10/31/19    0.1100475022    0.1049764344    0.0070174879    1.93%
    11/29/19    0.1215613242    0.0745730504    -0.019873344    3.50%
    12/31/19    -0.0089127693    0.0942040113    0.0230007222    2.85%
    1/31/20    0.1093846643    0.0526018721    0.0037232538    -0.17%
    2/28/20    -0.1347943556    -0.1213880404    -0.0958479512    -7.79%
    3/31/20    -0.3137543161    -0.0723142326    -0.0289408122    -14.17%
    4/30/20    -0.1549906422    0.1444239006    0.0757878175    12.97%
    5/29/20    -0.0344087816    0.081539254    -0.0166820633    5.37%
    6/30/20    0.0402738618    0.1374865313    0.0310016892    2.53%
    7/31/20    -0.1179660291    0.1528340775    0.0982231702    5.55%
    8/31/20    0.0435201592    0.2005679542    0.0535292509    6.84%
    9/30/20    -0.0158986071    -0.1081714857    0.0047598795    -3.51%
    10/30/20    0.1748027316    -0.0618882375    -0.0079406409    -2.02%
    11/30/20    0.3162459736    0.0912014246    0.0128275775    12.37%
    12/31/20    0.0600466082    0.1084720004    0.0019424076    4.50%
    1/29/21    -0.0111732897    -0.0055167017    -0.0756627332    -0.06%
    2/26/21    0.1605506701    -0.0828727242    -0.0371855653    2.92%
    3/31/21    0.0467374896    0.0073127919    0.0919708159    3.06%
    4/30/21    -0.0007619222    0.0734528637    -0.0084547102    4.82%
    5/28/21    0.0691961414    -0.0517498574    0.0106610613    0.71%
    6/30/21    -0.042868923    0.0945000966    0.0005929763    2.34%
    7/30/21    -0.0386265536    0.0629583003    0.0587861877    1.18%
    8/31/21    0.0173202279    0.0415621644    0.001124332    2.71%
    9/30/21    -0.0220611753    -0.0704617689    -0.0183569159    -4.22%
    10/29/21    0.0177013179    0.0570013744    0.0286264006    6.47%
    11/30/21    -0.0989499136    0.0997909604    0.0110582641    -1.84%
    12/31/21    -0.0046427924    0.0716029153    0.1234691783    3.33%
P G
    DATE     PG    (Rt -Ravg) ^2
    1/31/14    -0.0528202097    0.0039145737
    2/28/14    0.0262766384    0.0002732506
    3/31/14    0.024363817    0.0002136705
    4/30/14    0.0316718277    0.0004807268
    5/30/14    -0.0215510005    0.0009795238
    6/30/14    -0.0276087885    0.001395406
    7/31/14    -0.0080023713    0.0003150169
    8/29/14    0.0722123885    0.0039020067
    9/30/14    0.0075516962    0.0000048165
    10/31/14    0.0486377406    0.0015125407
    11/28/14    0.0355693514    0.0006668277
    12/31/14    0.0072719144    0.0000061228
    1/30/15    -0.0699782032    0.0063560035
    2/27/15    0.0099162228    0.0000000289
    3/31/15    -0.0381921657    0.0022981008
    4/30/15    -0.0218017231    0.0009952806
    5/29/15    -0.0141864664    0.0005727794
    6/30/15    -0.001915362    0.0001359954
    7/31/15    -0.0112737007    0.0004418423
    8/31/15    -0.0818805398    0.0083954859
    9/30/15    0.0178113434    0.0000650442
    10/30/15    0.0685299013    0.0034555066
    11/30/15    -0.0203683934    0.0009068974
    12/31/15    0.0592718986    0.0024527805
    1/29/16    0.0363893498    0.0007098497
    2/29/16    -0.0172865336    0.0007307765
    3/31/16    0.0248474857    0.0002280445
    4/29/16    -0.0186455368    0.0008060989
    5/31/16    0.0114173262    0.0000027922
    6/30/16    0.0438184548    0.0011609087
    7/29/16    0.0185988262    0.0000783664
    8/31/16    0.0198965648    0.000103027
    9/30/16    0.0275630515    0.0003174351
    10/31/16    -0.0257378314    0.0012591267
    11/30/16    -0.0512933406    0.0037258431
    12/30/16    0.0194554322    0.0000942664
    1/31/17    0.0486258689    0.0015116174
    2/28/17    0.0388474577    0.0008468748
    3/31/17    -0.013486842    0.0005397809
    4/28/17    -0.0205820586    0.000919812
    5/31/17    0.0086649102    0.0000011695
    6/30/17    -0.0107281881    0.0004192065
    7/31/17    0.0488130919    0.0015262108
    8/31/17    0.0158395029    0.0000371266
    9/29/17    -0.0140792862    0.0005676607
    10/31/17    -0.0443914905    0.0029309052
    11/30/17    0.0414055816    0.0010023073
    12/29/17    0.0207848186    0.0001218479
    1/31/18    -0.0542350994    0.0040936252
    2/28/18    -0.0949396211    0.0109591514
    3/29/18    0.009632548    0.0000000129
    4/30/18    -0.081743229    0.008370342
    5/31/18    0.0114083    0.0000027621
    6/29/18    0.0646922546    0.003019053
    7/31/18    0.0443171833    0.0011951429
    8/31/18    0.0252714391    0.0002410286
    9/28/18    0.0033699202    0.0000406588
    10/31/18    0.0714814649    0.0038112251
    11/30/18    0.0636712834    0.002907899
    12/31/18    -0.027787058    0.0014087563
    1/31/19    0.0557204143    0.0021136151
    2/28/19    0.0213319727    0.0001342268
    3/29/19    0.0543075176    0.0019856982
    4/30/19    0.0300662728    0.0004128995
    5/31/19    -0.0341023502    0.001922708
    6/28/19    0.0634386333    0.0028828619
    7/31/19    0.0800292317    0.0049396843
    8/30/19    0.0183830451    0.0000745926
    9/30/19    0.0339347707    0.00058508
    10/31/19    0.0070174879    0.0000074467
    11/29/19    -0.019873344    0.0008773259
    12/31/19    0.0230007222    0.0001756785
    1/31/20    0.0037232538    0.0000362776
    2/28/20    -0.0958479512    0.0111501553
    3/31/20    -0.0289408122    0.0014966961
    4/30/20    0.0757878175    0.0043614762
    5/29/20    -0.0166820633    0.0006984607
    6/30/20    0.0310016892    0.0004517897
    7/31/20    0.0982231702    0.0078281487
    8/31/20    0.0535292509    0.0019169429
    9/30/20    0.0047598795    0.0000248648
    10/30/20    -0.0079406409    0.0003128294
    11/30/20    0.0128275775    0.000009494
    12/31/20    0.0019424076    0.0000609014
    1/29/21    -0.0756627332    0.0072947105
    2/26/21    -0.0371855653    0.0022026041
    3/31/21    0.0919708159    0.0067608637
    4/30/21    -0.0084547102    0.0003312784
    5/28/21    0.0106610613    0.0000008367
    6/30/21    0.0005929763    0.0000837841
    7/30/21    0.0587861877    0.0024049062
    8/31/21    0.001124332    0.0000743391
    9/30/21    -0.0183569159    0.0007897932
    10/29/21    0.0286264006    0.0003564565
    11/30/21    0.0110582641    0.0000017211
    12/31/21    0.1234691783    0.0129328829
    SUM    0.9356490616    0.1731249713    AVERAGE(FORMULA)    0.0097463444
    AVERAGE    0.0097463444    0.0018223681
    ST. DEV        0.0426892038    ST DEV (FORMULA)    0.0426892038
PortfolioReturns
    DATE     GE    AAPL    PG    Portfolio 1            Portfolio 2            Portfolio 3            Portfolio 4            Portfolio 5            Portfolio 6            Portfolio 7            Portfolio 8            Portfolio 9            Portfolio...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here