Problem #1 AAPLPGVVVB MktSMBHMLRF Aug-2010'-5.5%-1.7%-4.5%-6.6% 201008-4.8%-3.0%-2.0%0.0% Sep-2010'16.7%0.5%8.7%12.0% 2010099.6%3.9%-3.1%0.0% Oct-2010'6.1%6.0%4.5%4.1%...

1 answer below »
In need of help for Finance homework, know how to use excel and have good understanding about security market


Problem #1 AAPLPGVVVBMktSMBHMLRF Aug-2010'-5.5%-1.7%-4.5%-6.6%201008-4.8%-3.0%-2.0%0.0% Sep-2010'16.7%0.5%8.7%12.0%2010099.6%3.9%-3.1%0.0% Oct-2010'6.1%6.0%4.5%4.1%2010103.9%1.1%-2.6%0.0% Nov-2010'3.4%-3.2%0.1%3.2%2010110.6%3.7%-0.9%0.0% Dec-2010'3.7%5.3%6.3%6.6%2010126.8%0.7%3.8%0.0% Jan-2011'5.2%-1.9%2.9%1.9%2011012.0%-2.5%0.8%0.0% Feb-2011'4.1%0.6%3.4%5.5%2011023.5%1.5%1.1%0.0% Mar-2011'-1.3%-2.3%-0.3%2.3%2011030.5%2.6%-1.6%0.0% Apr-2011'0.5%5.4%3.4%2.9%2011042.9%-0.3%-2.5%0.0% May-2011'-0.7%4.1%-1.0%-2.0%201105-1.3%-0.7%-2.1%0.0% Jun-2011'-3.5%-5.1%-2.2%-2.1%201106-1.8%-0.2%-0.3%0.0% Jul-2011'16.3%-3.3%-1.6%-3.6%201107-2.4%-1.4%-1.2%0.0% Aug-2011'-1.4%4.4%-5.7%-8.3%201108-6.0%-3.1%-2.5%0.0% Sep-2011'-0.9%-0.8%-7.8%-11.0%201109-7.6%-3.5%-1.4%0.0% Oct-2011'6.2%1.3%11.6%15.3%20111011.4%3.4%-0.2%0.0% Nov-2011'-5.6%1.7%-0.4%-0.4%201111-0.3%-0.2%-0.3%0.0% Dec-2011'6.0%3.3%0.4%-1.1%2011120.7%-0.7%1.8%0.0% Jan-2012'12.7%-5.5%5.5%8.4%2012015.1%2.2%-1.1%0.0% Feb-2012'18.8%8.1%4.2%3.3%2012024.4%-1.8%0.1%0.0% Mar-2012'10.5%-0.6%2.9%2.3%2012033.1%-0.6%0.9%0.0% Apr-2012'-2.6%-5.3%-0.2%-1.0%201204-0.9%-0.5%-0.5%0.0% May-2012'-1.1%-1.3%-6.2%-6.7%201205-6.2%0.0%-0.6%0.0% Jun-2012'1.1%-1.7%3.3%4.6%2012063.9%0.8%0.4%0.0% Jul-2012'4.6%5.4%1.6%-1.0%2012070.8%-2.6%-0.3%0.0% Aug-2012'8.9%5.0%2.5%3.6%2012082.6%0.4%1.3%0.0% Sep-2012'0.7%3.2%2.0%2.8%2012092.7%0.5%1.5%0.0% Oct-2012'-10.8%-0.2%-1.3%-1.4%201210-1.8%-1.2%3.8%0.0% Nov-2012'-1.7%1.7%0.6%1.3%2012110.8%0.6%-1.0%0.0% Dec-2012'-8.7%-2.8%0.3%1.2%2012121.2%1.5%3.6%0.0% Jan-2013'-14.4%10.7%6.0%8.3%2013015.6%0.4%1.0%0.0% Feb-2013'-3.1%2.2%1.4%1.3%2013021.3%-0.5%0.0%0.0% Mar-2013'0.9%1.2%3.2%4.6%2013034.0%0.8%-0.3%0.0% Apr-2013'0.0%-0.4%2.5%0.3%2013041.6%-2.4%0.6%0.0% May-2013'1.6%0.7%2.2%3.6%2013052.8%1.7%2.6%0.0% Jun-2013'-11.2%0.3%-1.8%-1.1%201306-1.2%1.2%-0.2%0.0% Jul-2013'14.1%4.3%5.9%6.8%2013075.7%1.9%0.6%0.0% Aug-2013'7.7%-2.3%-2.9%-3.2%201308-2.7%0.3%-2.8%0.0% Sep-2013'-1.5%-3.0%2.9%5.8%2013093.8%2.9%-1.2%0.0% Oct-2013'9.6%6.8%4.9%3.2%2013104.2%-1.5%1.1%0.0% Nov-2013'6.4%5.1%2.8%2.7%2013113.1%1.2%0.2%0.0% Dec-2013'1.5%-3.3%2.1%1.2%2013122.8%-0.5%-0.3%0.0% Jan-2014'-10.8%-5.9%-2.8%-0.8%201401-3.3%0.9%-2.1%0.0% Feb-2014'5.1%3.4%4.6%5.1%2014024.7%0.3%-0.4%0.0% Mar-2014'2.6%2.5%0.3%-0.2%2014030.4%-1.9%5.1%0.0% Apr-2014'9.9%2.4%1.0%-2.4%201404-0.2%-4.2%1.1%0.0% May-2014'7.3%-1.4%2.4%1.3%2014052.1%-1.9%-0.3%0.0% Jun-2014'3.3%-2.7%1.7%5.0%2014062.6%3.1%-0.7%0.0% Jul-2014'2.9%-1.6%-1.0%-5.0%201407-2.0%-4.2%0.0%0.0% Aug-2014'7.2%8.3%4.0%5.1%2014084.2%0.4%-0.6%0.0% Sep-2014'-1.2%0.8%-2.0%-5.3%201409-2.0%-3.8%-1.2%0.0% Oct-2014'7.2%4.2%2.8%4.5%2014102.5%4.2%-1.7%0.0% Nov-2014'10.1%4.4%2.8%1.1%2014112.6%-2.1%-3.0%0.0% Dec-2014'-6.8%0.7%-0.8%-0.2%201412-0.1%2.5%2.1%0.0% Jan-2015'6.1%-7.5%0.8%-0.7%201501-3.1%-0.6%-3.5%0.0% Feb-2015'9.6%1.7%2.4%5.8%2015026.1%0.5%-1.8%0.0% Mar-2015'-2.8%-3.7%-1.8%1.5%201503-1.1%3.0%-0.5%0.0% Apr-2015'0.6%-3.0%1.9%-2.0%2015040.6%-3.0%1.9%0.0% May-2015'4.1%-0.6%0.6%2.0%2015051.4%0.9%-1.4%0.0% Jun-2015'-3.3%-0.2%-2.4%-0.8%201506-1.5%2.8%-0.8%0.0% Jul-2015'-3.3%-2.0%2.6%-0.3%2015071.5%-4.2%-4.1%0.0% Aug-2015'-6.9%-7.1%-9.9%-5.8%201508-6.0%0.5%2.7%0.0% Sep-2015'-1.9%1.8%3.1%-5.4%201509-3.1%-2.6%0.5%0.0% Oct-2015'8.3%6.2%6.6%6.6%2015107.8%-2.0%-0.1%0.0% Nov-2015'-1.0%-1.1%0.3%1.8%2015110.6%3.6%-0.5%0.0% Dec-2015'-10.6%6.1%-2.3%-4.7%201512-2.2%-2.8%-2.6%0.0% Jan-2016'-7.5%2.9%-4.9%-7.0%201601-5.8%-3.4%2.1%0.0% Feb-2016'-0.7%-0.9%-0.1%0.8%201602-0.1%0.8%-0.5%0.0% Mar-2016'13.3%2.5%6.4%8.2%2016037.0%0.9%1.2%0.0% Apr-2016'-14.0%-2.7%0.9%2.1%2016040.9%0.7%3.3%0.0% May-2016'6.5%2.0%1.7%1.9%2016051.8%-0.3%-1.8%0.0% Jun-2016'-3.7%4.5%-0.2%0.1%201606-0.0%0.7%-1.5%0.0% Jul-2016'9.0%1.1%4.2%5.3%2016074.0%2.6%-1.1%0.0% Aug-2016'1.8%2.8%0.2%0.7%2016080.5%1.2%3.3%0.0% Sep-2016'7.1%2.8%-0.5%-0.1%2016090.3%2.0%-1.5%0.0% Oct-2016'0.4%-3.3%-1.3%-3.6%201610-2.0%-4.4%4.2%0.0% Nov-2016'-2.7%-4.3%3.7%8.6%2016114.9%5.5%8.3%0.0% Dec-2016'5.3%2.0%1.4%1.3%2016121.9%0.1%3.6%0.0% Jan-2017'4.8%4.2%2.5%2.1%2017012.0%-1.0%-2.8%0.0% Feb-2017'12.9%4.8%4.0%2.3%2017023.6%-2.0%-1.8%0.0% Mar-2017'5.3%-1.3%-0.4%-0.5%2017030.2%1.2%-3.2%0.0% Apr-2017'-0.0%-2.8%1.6%1.2%2017041.1%0.7%-1.9%0.1% May-2017'6.3%1.6%1.4%-1.1%2017051.1%-2.5%-3.8%0.1% Jun-2017'-5.3%-1.1%0.2%2.0%2017060.8%2.2%1.4%0.1% Jul-2017'3.3%4.2%2.5%1.4%2017071.9%-1.4%-0.3%0.1% Aug-2017'10.3%2.4%0.3%-0.9%2017080.3%-1.7%-2.2%0.1% Sep-2017'-5.7%-1.4%1.5%4.0%2017092.6%4.6%3.0%0.1% Oct-2017'9.7%-5.1%2.9%2.0%2017102.3%-1.9%-0.1%0.1% Nov-2017'1.7%5.0%3.0%3.1%2017113.2%-0.7%-0.1%0.1% Dec-2017'-1.2%2.1%0.8%-0.1%2017121.2%-1.3%0.1%0.1% Jan-2018'-1.1%-6.0%6.2%3.2%2018015.7%-3.0%-1.4%0.1% Feb-2018'6.4%-8.4%-3.7%-4.0%201802-3.5%0.3%-1.2%0.1% Mar-2018'-5.4%1.0%-2.9%0.8%201803-2.2%4.0%-0.1%0.1% Apr-2018'-1.5%-8.8%0.7%0.7%2018040.4%1.1%0.5%0.1% May-2018'13.1%2.1%2.4%5.1%2018052.8%5.2%-3.2%0.1% Jun-2018'-0.6%6.7%0.2%0.4%2018060.6%1.2%-2.4%0.1% Jul-2018'2.8%3.6%4.1%2.1%2018073.4%-2.2%0.4%0.2% Aug-2018'19.6%3.5%3.3%4.5%2018083.6%1.2%-4.1%0.2% Sep-2018'-0.5%0.3%0.0%-1.9%2018090.2%-2.4%-1.3%0.2% Oct-2018'-3.0%6.5%-6.5%-9.8%201810-7.5%-4.8%3.4%0.2% Nov-2018'-18.4%7.5%1.9%2.2%2018111.9%-0.8%0.3%0.2% Dec-2018'-11.4%-2.7%-9.3%-11.6%201812-9.4%-2.6%-1.5%0.2% Jan-2019'5.5%4.9%8.7%12.3%2019018.6%3.0%-0.6%0.2% Feb-2019'4.0%3.0%3.3%4.9%2019023.6%2.1%-2.8%0.2% Mar-2019'10.2%5.6%1.3%-1.2%2019031.3%-3.1%-4.1%0.2% Apr-2019'5.6%2.3%4.6%3.9%2019044.2%-1.7%1.9%0.2% May-2019'-12.8%-2.7%-6.3%-7.2%201905-6.7%-1.2%-2.4%0.2% Jun-2019'13.5%6.5%6.5%6.6%2019067.1%0.3%-1.1%0.2% Jul-2019'7.6%7.7%2.0%1.5%2019071.4%-2.1%0.1%0.2% Aug-2019'-2.0%2.5%-1.8%-4.0%201908-2.4%-2.4%-5.0%0.2% Sep-2019'7.7%3.5%1.5%1.1%2019091.6%-0.9%6.7%0.2% Problem #3 DateHGLB NAVHGLB Price 2/15/1915.0815.05 2/19/1915.215.1074 2/20/1915.1715.19 2/21/1915.1414.12 2/22/1915.0513.15 2/25/1915.0112.74 2/26/1914.8913.3 2/27/1914.9213.48 2/28/1914.913.43 3/1/1915.0613.46 3/4/1914.8913.1551 3/5/1914.8612.85 3/6/1914.6812.78 3/7/1914.6612.95 3/8/1914.6613.16 3/11/1914.7713 3/12/1914.8812.95 3/13/1914.9412.98 3/14/1914.9112.85 3/15/1914.9112.83 3/18/1915.1112.9 3/19/1915.1212.92 3/20/1915.0912.94 3/21/1915.212.8499 3/22/1915.0312.7601 3/25/1914.9712.92 3/26/1915.0212.99 3/27/1914.9213 3/28/1914.9412.92 3/29/1915.1212.98 4/1/1915.1812.95 4/2/1915.2913.02 4/3/1915.2512.98 4/4/1915.213.2 4/5/1915.2313.13 4/8/1915.2312.9 4/9/1915.0812.97 4/10/1915.1612.95 4/11/1915.1212.88 4/12/1915.2812.95 4/15/1915.2512.92 4/16/1915.1812.7 4/17/1915.1312.65 4/18/1915.0212.51 4/22/1915.0112.53 4/23/1915.0112.52 4/24/1914.9512.41 4/25/1914.9512.43 4/26/1914.9212.43 4/29/1914.9712.3 4/30/1914.9412.3385 5/1/1914.8812.22 5/2/1914.7512.36 5/3/1914.8412.27 5/6/1914.7912.11 5/7/1914.7812.22 5/8/1914.6112.01 5/9/1914.4712.22 5/10/1914.5311.9 5/13/1914.4311.96 5/14/1914.4612.09 5/15/1914.4512.11 5/16/1914.4312 5/17/1914.3411.92 5/20/1914.412.02 5/21/1914.6311.86 5/22/1914.3811.78 5/23/1914.2211.7 5/24/1914.1911.6 5/28/1914.1111.32 5/29/1913.9211.23 5/30/1913.7311 5/31/1913.7510.92 6/3/1913.911.08 6/4/1914.0411 6/5/1913.9711.24 6/6/1913.9611.39 6/7/1913.8811.24 6/10/1913.8811.28 6/11/1914.0511.06 6/12/1914.1111.13 6/13/1914.1411.08 6/14/1914.0811.02 6/17/1913.9111.11 6/18/191411.15 6/19/1914.0511.03 6/20/1914.1811.23 6/21/1914.1911.1 6/24/1914.0810.98 6/25/1914.0211.03 6/26/1913.8910.91 6/27/1913.8811.24 6/28/1914.0911.53 7/1/1913.9911.5 7/2/1913.9711.7 7/3/1913.9911.37 7/5/1914.0111.37 7/8/1913.9211.37 7/9/1913.8711.41 7/10/1913.9411.39 7/11/1913.9911.39 7/12/1914.0811.65 7/15/1914.0611.29 7/16/1913.9311.14 7/17/1913.9911.17 7/18/1914.0611.11 7/19/1914.0311.09 7/22/1914.0210.91 7/23/1913.7610.93 7/24/1913.6910.91 7/25/1913.6110.84 7/26/1913.4910.87 7/29/1913.4810.75 7/30/1913.6410.83 7/31/1913.5410.82 8/1/1913.6710.77 8/2/1913.6810.71 8/5/1913.8110.82 8/6/1913.6810.7 8/7/1913.6610.82 8/8/1913.8810.81 8/9/1913.7810.88 8/12/1913.3910.57 8/13/1913.0510.2 8/14/1913.2210.26 8/15/1913.2510.25 8/16/1913.2910.32 8/19/191310.38 8/20/1913.1910.53 8/21/1913.2410.51 8/22/1913.1310.35 8/23/1913.2510.2 8/26/1912.9810.01 8/27/1912.8910.07 8/28/1912.8710.01 8/29/1912.8510.1 8/30/1912.889.95 9/3/1912.979.96 9/4/1913.0410.05 9/5/1913.1210.01 9/6/1913.0310.06 9/9/1913.1410.0539 9/10/1913.1710.21 9/11/1913.2910.41 9/12/1913.2310.43 9/13/1913.2310.5 9/16/1913.4210.44 9/17/1913.3710.36 9/18/1913.3510.68 9/19/1913.2810.5 9/20/1913.3610.43 9/23/1913.2710.29 9/24/1913.3110.32 9/25/1913.2710.32 9/26/1913.1610.28 9/27/1913.2410.25 9/30/1913.1310.05 10/1/1913.1310.01 10/2/1913.1110.02 10/3/1913.1110.04 10/4/1913.1710.03 10/7/1913.169.97 10/8/1913.2710.02 10/9/1913.1510.02 10/10/1913.1310.07 10/11/1913.1210.09 10/14/1913.0710.18 10/15/1912.999.92 10/16/1912.879.84 10/17/1912.879.8 10/18/1912.829.66 10/21/1912.849.66 10/22/1912.899.72 10/23/1912.699.6 10/24/1912.679.57 10/25/1912.699.52 10/28/1912.529.43 10/29/1912.469.46 10/30/1912.449.45 10/31/1912.269.58 11/1/1912.489.58 11/4/1912.569.51 11/5/1912.259.45 11/6/1912.039.4 11/7/1912.039.11 11/8/1911.979.24 11/11/1911.919.2 11/12/1911.888.98 11/13/1911.88.98 11/14/1911.838.81 11/15/1911.868.82 Using any computer file you did not create originally is expressly prohibited and will result in zero credit. Googling for answers is expressly prohibited. All work submitted is required to be the original work product of the group members. Failure to follow instructions will lead to a reduced grade. You can find the data file named DATA-SET.xlsx containing data you will need for this problem set. Problem 1: The Single Index Model and APT [23 points] See the worksheet named “Problem #1” for returns data that will help you complete this problem. A. [6 points] Compute the excess return for each stock and fund (AAPL, PG, VV, and VB) and for the market by subtracting the risk-free return. Note that all returns provided are monthly. Use those excess returns to estimate the market model (single index model) for each of the 4 investments. Present the alpha, beta, and residual variance for each of the 4 investments. B. [3 points] Using your estimate from part A, estimate the alpha and beta of the following portfolio: 25% AAPL, 25% PG, 25% VV, and 25% VB. C. [4 points] Is the residual variance greater for the individual stocks (AAPL, PG) or for the funds (VV and VB)? Why do you think that is the case? D. [4 points] Plot the 4 investments on the security market line. E. [4 points] Estimate the Fama-Macbeth 3-factor model and report the coefficients, t-statistics, and the R2. F. [2 points] Interpret the regression coefficients on the market and size factors for VV and VB. Problem 2: Comparing performance [7 points] Two investment professionals are comparing their return performance. The first professional managed portfolios with an average return of 10% and the second professional managed portfolios with a 12% rate of return. The beta of the first portfolio was 0.8 while the beta of the second was 1.1. The risk-free rate of return was 2% and the expected market return is 8%. A. [5 points] Which manager was a better selector of individual stocks, and why? B. [2 points] Plot both of the portfolios on the security market line. Problem 3: Closed End Funds [10 points] See the worksheet named “Problem #3” for the prices and NAV of a recently IPO-ed closed end fund
data-set-bsrvaxft.xlsx requirement-data-set-zlho34mu.docx
Answered Same DayNov 27, 2021

Answer To: Problem #1 AAPLPGVVVB MktSMBHMLRF Aug-2010'-5.5%-1.7%-4.5%-6.6%...

Kushal answered on Nov 29 2021
134 Votes
AAPL
    SUMMARY OUTPUT
    Regression Statistics                                            Alpha
    Multiple R    0.5644367596                                        Beta    1.1543133449
    R Square    0.3185888556                                        Residualk Variance    0.6814111444
    Adjusted R Square    0.2993036345
    Standard Error    0.0618740946
    Observations    110
    ANOVA
        df    SS    MS    F    Significance F
    Regression    3    0.1897338969    0.0632446323    16.5198446241    0.0000000071
    Residual    106    0.4058107795    0.0038284036
    Total    109    0.5955446765
        Coefficients    Standard Error    t Stat    P-value    Lower 95%    Upper 95%    Lower 95.0%    Upper 95.0%
    Intercept    0.0052361365    0.0062839715    0.8332527431    0.406575823    -0.0072224482    0.0176947212    -0.0072224482    0.0176947212
    SMB    -0.51923345    0.2810048854    -1.8477737472    0.0674233168    -1.0763529663    0.0378860663    -1.0763529663    0.0378860663
    HML    -0.7503552899    0.2613431498    -2.8711496382    0.0049392769    -1.268493503    -0.2322170768    -1.268493503    -0.2322170768
    Rm-Rf    1.1543133449    0.176755596    6.5305618091    0.0000000023    0.8038781842    1.5047485055    0.8038781842    1.5047485055
PG
    SUMMARY OUTPUT
    Regression Statistics                                        Beta    Alpha
    Multiple R    0.4267600264                                        Beta    0.4790769664
    R Square    0.1821241201                                        Residualk Variance    0.8178758799
    Adjusted R Square    0.1589766896
    Standard Error    0.0363953321
    Observations    110
    ANOVA
        df    SS    MS    F    Significance F
    Regression    3    0.0312663587    0.0104221196    7.8680058953    0.0000865604
    Residual    106    0.1404097414    0.0013246202
    Total    109    0.1716761
        Coefficients    Standard Error    t Stat    P-value    Lower 95%    Upper 95%    Lower 95.0%    Upper 95.0%
    Intercept    0.0035865438    0.0036963326    0.9702979139    0.3341068672    -0.003741795    0.0109148826    -0.003741795    0.0109148826
    SMB    -0.5209183889    0.1652915684    -3.1515121667    0.0021120155    -0.8486249997    -0.193211778    -0.8486249997    -0.193211778
    HML    -0.0783101248    0.153726221    -0.5094129306    0.6115216983    -0.3830873096    0.2264670601    -0.3830873096    0.2264670601
    Rm-Rf    0.4790769664    0.1039704689    4.6078176971    0.0000113889    0.2729453995    0.6852085333    0.2729453995    0.6852085333
VV
    SUMMARY OUTPUT
    Regression Statistics                                            Alpha
    Multiple R    0.9653093377                                        Beta    0.9888222819
    R Square    0.9318221175                                        Residualk Variance    0.0681778825
    Adjusted R Square    0.9298925548
    Standard Error    0.0094764665
    Observations    110
    ANOVA
        df    SS    MS    F    Significance F
    Regression    3    0.1301032776    0.0433677592    482.9188043385    1.21818436091789E-61
    Residual    106    0.0095191623    0.0000898034
    Total    109    0.1396224398
        Coefficients    Standard Error    t Stat    P-value    Lower 95%    Upper 95%    Lower 95.0%    Upper 95.0%
    Intercept    -0.0001293966    0.0009624358    -0.1344469576    0.8933041021    -0.0020375192    0.0017787261    -0.0020375192    0.0017787261
    SMB    -0.1830896171    0.0430379371    -4.2541448184    0.0000453972    -0.2684165134    -0.0977627209    -0.2684165134    -0.0977627209
    HML    -0.0394235198    0.0400265996    -0.9849330241    0.3268998088    -0.1187801466    0.039933107    -0.1187801466    0.039933107
    Rm-Rf    0.9888222819    0.027071402    36.5264525639    7.07357727810297E-62    0.9351505944    1.0424939694    0.9351505944    1.0424939694
VG
    SUMMARY OUTPUT
    Regression Statistics                                            Alpha
    Multiple R    0.9840487441                                        Beta    1.0497366501
    R Square    0.9683519308                                        Residualk Variance    0.0316480692
    Adjusted R Square    0.9674562307
    Standard Error    0.0082424749
    Observations    110
    ANOVA
        df    SS    MS    F    Significance F
    Regression    3    0.2203469987    0.0734489996    1081.1118151884    2.68656444753089E-79
    Residual    106    0.0072014697    0.0000679384
    Total    109    0.2275484684
        Coefficients    Standard Error    t Stat    P-value    Lower 95%    Upper 95%    Lower 95.0%    Upper 95.0%
    Intercept    -0.0005172122    0.0008371109    -0.6178538965    0.5379968358    -0.002176866    0.0011424415    -0.002176866    0.0011424415
    SMB    0.5708762694    0.0374336908    15.2503335062    1.72844416724694E-28    0.4966603364    0.6450922023    0.4966603364    0.6450922023
    HML    0.0246980303    0.0348144789    0.7094183536    0.4796224496    -0.0443250603    0.093721121    -0.0443250603    0.093721121
    Rm-Rf    1.0497366501    0.0235462609    44.5818831576    1.71312046487304E-70    1.0030538978    1.0964194023    1.0030538978    1.0964194023
Portfolio
    SUMMARY OUTPUT
    Regression Statistics                                            Beta    0.9179873108
    Multiple R    0.8775441563                                        Residual Variance    0.2299162537
    R Square    0.7700837463
    Adjusted R Square    0.7635766825
    Standard Error    0.0178533935
    Observations    110
    ANOVA
        df    SS    MS    F    Significance F
    Regression    3    0.1131659314    0.0377219771    118.3458118652    1.05999560261303E-33
    Residual    106    0.0337868279    0.0003187437
    Total    109    0.1469527592
        Coefficients    Standard Error    t Stat    P-value    Lower 95%    Upper 95%    Lower 95.0%    Upper 95.0%
    Intercept    0.0020440179    0.0018132017    1.127297544    0.2621620553    -0.0015508309    0.0056388667    -0.0015508309    0.0056388667
    SMB    -0.1630912967    0.0810822498    -2.0114303329    0.0468178083    -0.323844743    -0.0023378503    -0.323844743    -0.0023378503
    HML    -0.210847726    0.0754089756    -2.7960560973    0.0061433127    -0.3603533546    -0.0613420975    -0.3603533546    -0.0613420975
    Rm-Rf    0.9179873108    0.0510017517    17.9991329817    5.2176766518815E-34    0.8168713776    1.019103244    0.8168713776    1.019103244
Problem #1
                                                        Excess Returns
        AAPL    PG    VV    VB        AAPL    PG    VV    VB    Index    Market        AAPL    PG    VV    VB    Index            Mkt    RF    SMB    HML    Rm-Rf        1.A,B                                                        1.E
    Aug-2010'    -5.5%    -1.7%    -4.5%    -6.6%        94.50    98.33    95.46    93.36    95.41    95.24        -5.51%    -1.68%    -4.55%    -6.65%    -4.6%        201008    -4.8%    0.010%    -3.0%    -2.0%    -4.8%            CAGR    AAPL    PG    VV    VB    Portfolio    Mkt                                R2    0.7700837463
    Sep-2010'    16.7%    0.5%    8.7%    12.0%        110.30    98.82    103.72    104.57    104.45    104.34        16.71%    0.49%    8.65%    12.00%    9.5%        201009    9.6%    0.010%    3.9%    -3.1%    9.5%            Returns    24%    12%    14%    12%    16%    14%                                    Coefficients    Standard Error    t Stat    P-value
    Oct-2010'    6.1%    6.0%    4.5%    4.1%        117.00    104.75    108.39    108.83    109.84    108.39        6.06%    5.99%    4.48%    4.07%    5.2%        201010    3.9%    0.010%    1.1%    -2.6%    3.9%            Alpha    8%    5%    0%    -2%    4%    13%                                Intercept    0.0020440179    0.0018132017    1.127297544    0.2621620553
    Nov-2010'    3.4%    -3.2%    0.1%    3.2%        120.95    101.41    108.55    112.30    110.81    109.06        3.37%    -3.20%    0.14%    3.17%    0.9%        201011    0.6%    0.010%    3.7%    -0.9%    0.6%            beta    1.15    0.48    0.99    1.05    92%                                    SMB    -0.1630912967    0.0810822498    -2.0114303329    0.0468178083
    Dec-2010'    3.7%    5.3%    6.3%    6.6%        125.39    106.82    115.33    119.71    116.86    116.50        3.66%    5.33%    6.24%    6.60%    5.5%        201012    6.8%    0.010%    0.7%    3.8%    6.8%            Residual variance    68%    82%    7%    3%    23%                                    HML    -0.210847726    0.0754089756    -2.7960560973    0.0061433127
    Jan-2011'    5.2%    -1.9%    2.9%    1.9%        131.90    104.83    118.63    121.94    119.21    118.83        5.19%    -1.88%    2.85%    1.85%    2.0%        201101    2.0%    0.010%    -2.5%    0.8%    2.0%                                                                    Rm-Rf    0.9179873108    0.0510017517    17.9991329817    5.2176766518815E-34
    Feb-2011'    4.1%    0.6%    3.4%    5.5%        137.30    105.47    122.72    128.67    123.29    122.99        4.08%    0.60%    3.43%    5.51%    3.4%        201102    3.5%    0.010%    1.5%    1.1%    3.5%        1.C    Residual variance for the individual stocks is lesser and the portfolio is higher                                                        *Used the index return for the dependent variable
    Mar-2011'    -1.3%    -2.3%    -0.3%    2.3%        135.48    103.05    122.32    131.69    122.79    123.56        -1.34%    -2.31%    -0.34%    2.34%    -0.4%        201103    0.5%    0.010%    2.6%    -1.6%    0.5%            Siunce the portfolio would be able to explain the more variation in the market returns
    Apr-2011'    0.5%    5.4%    3.4%    2.9%        136.10    108.57    126.48    135.57    126.53    127.14        0.46%    5.36%    3.41%    2.95%    3.0%        201104    2.9%    0.000%    -0.3%    -2.5%    2.9%            On top of that more number of stocks will explain the SMB and HML variation too                                                    1.F
    May-2011'    -0.7%    4.1%    -1.0%    -2.0%        135.21    113.01    125.21    132.92    126.68    125.53        -0.66%    4.09%    -1.01%    -1.96%    0.1%        201105    -1.3%    0.000%    -0.7%    -2.1%    -1.3%                                                                    VV
    Jun-2011'    -3.5%    -5.1%    -2.2%    -2.1%        130.48    107.22    122.44    130.13    122.59    123.33        -3.50%    -5.12%    -2.21%    -2.09%    -3.2%        201106    -1.8%    0.000%    -0.2%    -0.3%    -1.8%        1.D                                                                Coefficients    Standard Error    t Stat    P-value
    Jul-2011'    16.3%    -3.3%    -1.6%    -3.6%        151.79    103.71    120.52    125.42    124.99    120.42        16.33%    -3.27%    -1.57%    -3.63%    2.0%        201107    -2.4%    0.000%    -1.4%    -1.2%    -2.4%                                                                    Intercept    -0.0001293966    0.0009624358    -0.1344469576    0.8933041021
    Aug-2011'    -1.4%    4.4%    -5.7%    -8.3%        149.59    108.29    113.65    114.94    121.53    113.22        -1.46%    4.40%    -5.70%    -8.36%    -2.8%        201108    -6.0%    0.010%    -3.1%    -2.5%    -6.0%                Beta    Return    Security                                            SMB    -0.1830896171    0.0430379371    -4.2541448184    0.0000453972
    Sep-2011'    -0.9%    -0.8%    -7.8%    -11.0%        148.23    107.44    104.84    102.32    115.33    104.63        -0.91%    -0.79%    -7.75%    -10.98%    -5.1%        201109    -7.6%    0.000%    -3.5%    -1.4%    -7.6%                0    0.180%    Risk Free                                            HML    -0.0394235198    0.0400265996    -0.9849330241    0.3268998088
    Oct-2011'    6.2%    1.3%    11.6%    15.3%        157.35    108.82    117.03    117.96    125.23    116.50        6.15%    1.28%    11.62%    15.28%    8.6%        201110    11.4%    0.000%    3.4%    -0.2%    11.4%                1    14%    Market
    Nov-2011'    -5.6%    1.7%    -0.4%    -0.4%        148.57    110.70    116.52    117.45    123.75    116.17        -5.58%    1.73%    -0.44%    -0.44%    -1.2%        201111    -0.3%    0.000%    -0.2%    -0.3%    -0.3%                1.15    24%    AAPL        AAPL                                    VG
    Dec-2011'    6.0%    3.3%    0.4%    -1.1%        157.43    114.37    116.92    116.16    126.39    117.03        5.97%    3.31%    0.35%    -1.09%    2.1%        201112    0.7%    0.000%    -0.7%    1.8%    0.7%                0.48    12%    PG        PG                                        Coefficients    Standard Error    t...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?