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

Each student will download the past five years of monthly price data for 3 stocks of his/her choice and S&P 500 Index ETF (SPY) from Yahoo! Finance (https://finance.yahoo.com/). Student will use...

1 answer below »
Each student will download the past five years of monthly price data for 3 stocks of his/her choice and S&P 500 Index ETF (SPY) from Yahoo! Finance (https://finance.yahoo.com/). Student will use Excel to compute monthly returns for both stocks and S&P 500 Index. Students will then use Excel to estimate means and standard deviations for each stock’s returns, as well as a covariance matrix across all returns. Students will also use Excel to estimate Security Characteristic Line (SCL) and the beta coefficient by using a regression analysis. Students will then compute the expected return on a stock, implied by the beta coefficient under the single factor specification of the Capital Asset Pricing Model. Students will turn in their calculations, including spreadsheets showing the raw stock prices, calculations, and relevant graphs.In the project, students will derive an optimal portfolio using the optimization technique using Excel solver. 3 companies areSynopsys (SNPS), Visa (V) and Walmart (WMT). the period is 5 years from april to april. It should be excel file. Risk free rate monthly should be 2%/12 =0.17%
Answered 1 days After Apr 18, 2023

Solution

Mayuri answered on Apr 19 2023
28 Votes
Sheet1
    Date    SNPS    V    WMT    S&P 500        average retruns                            SNPS    V    WMT    S&P 500                    excess returns after risk free rate
    4/1/18    85.510002    122.755051    80.87999    2648.05        AR SNPS    AR V    AR WMT    AR S&P 500            MEAN    0.0285783998    0.0130882908    0.0116907929    0.0089558449                        SNPS    V    WMT    S&P 500
    5/1/18    88.07    126.470207    75.467262    2705.27        2.9937995%    3.0264791%    -6.6922956%    2.1608353%            ST DEV    0.0834636595    0.0684221431    0.0526622791    0.0533133791                        -13.67287%    -13.64019%    -23.35896%    -14.50583%            SNPS    V    WMT
    6/1/18    85.57    128.349884    78.804138    2718.37        -2.8386511%    1.4862607%    4.4216206%    0.4842400%            yearly return    0.4023253943    0.1688736013    0.1496709871    0.1129250727    risk free return            2        -19.50532%    -15.18041%    -12.24505%    -16.18243%        Beta    1.1794387216    0.9618898862    0.4889682811
    7/1/18    89.43    132.507126    82.097977    2816.29        4.5109267%    3.2389916%    4.1797792%    3.6021586%                                Risk free rate monthly            0.1666666667        -12.15574%    -13.42768%    -12.48689%    -13.06451%
    8/1/18    102.139999    142.342896    88.198067    2901.52        14.2122319%    7.4228234%    7.4302562%    3.0263219%                coveriance matrix                market risk premium            -0.1577108218        -2.45443%    -9.24384%    -9.23641%    -13.64034%
    9/1/18    98.610001    145.66246    86.905762    2913.98        -3.4560388%    2.3320897%    -1.4652305%    0.4294301%                SNPS    V    WMT    S&P 500                        -20.12271%    -14.33458%    -18.13190%    -16.23724%
    10/1/18    89.529999    133.783539    92.800644    2711.74        -9.2079930%    -8.1551012%    6.7830738%    -6.9403359%            SNPS    0.0069661825    0.0027738512    0.0013561598    0.003352338                        -25.87466%    -24.82177%    -9.88359%    -23.60700%
    11/1/18    91.940002    137.529648    90.366806    2760.17        2.6918385%    2.8001270%    -2.6226521%    1.7859382%            V    0.0027738512    0.0046815897    0.0012766614    0.0027339954                        -13.97483%    -13.86654%    -19.28932%    -14.88073%
    12/1/18    84.239998    128.277771    86.202438    2506.85        -8.3750314%    -6.7271873%    -4.6082939%    -9.1776956%            WMT    0.0013561598    0.0012766614    0.0027733156    0.0013898026                        -25.04170%    -23.39385%    -21.27496%    -25.84436%
    1/1/19    93.349998    131.262573    89.166519    2704.1        10.8143402%    2.3268271%    3.4385118%    7.8684405%            S&P 500    0.003352338    0.0027339954    0.0013898026    0.0028423164                        -5.85233%    -14.33984%    -13.22815%    -8.79823%
    2/1/19    101.68    144.008652    92.106766    2784.49        8.9234089%    9.7103681%    3.2974787%    2.9728930%                                                    -7.74326%    -6.95630%    -13.36919%    -13.69377%        monthly returns        portfolio weightage
    3/1/19    115.150002    152.119705    90.748283    2834.4        13.2474449%    5.6323373%    -1.4749003%    1.7924288%                                                    -3.41922%    -11.03433%    -18.14157%    -14.87424%        SNPS    2.8578%    SNPS    0.6467947829
    4/1/19    121.080002    160.144974    96.203934    2945.83        5.1498045%    5.2756275%    6.0118504%    3.9313435%                                                    -11.51686%    -11.39104%    -10.65482%    -12.73532%        V    1.3088%    V    0
    5/1/19    116.440002    157.125748    94.894272    2752.06        -3.8321770%    -1.8853080%    -1.3613393%    -6.5777726%            SNPS    V    WMT                                -20.49884%    -18.55197%    -18.02801%    -23.24444%        WMT    1.1691%    WMT    0.3532052232
    6/1/19    128.690002    169.287292    103.90937    2941.76        10.5204395%    7.7400071%    9.5001498%    6.8930183%        E returns    -0.2256892398    0.1848417129    1.0772802165                                -6.14623%    -8.92666%    -7.16652%    -9.77365%                total    1.0000000061
    7/1/19    132.759995    173.628006    103.805931    2980.38        3.1626334%    2.5641110%    -0.0995473%    1.3128195%                                                    -13.50403%    -14.10256%    -16.76621%    -15.35385%
    8/1/19    141.809998    176.378738    107.454819    2926.46        6.8168148%    1.5842675%    3.5151055%    -1.8091653%                coveriamce matrix of three stocks                                    -9.84985%    -15.08240%    -13.15156%    -18.47583%
    9/1/19    137.25    168.027206    112.160995    2976.74        -3.2155688%    -4.7349993%    4.3796789%    1.7181168%                        SNPS    V    WMT                    -19.88224%    -21.40167%    -12.28699%    -14.94855%
    10/1/19    135.75    174.718613    110.818985    3037.56        -1.0928962%    3.9823355%    -1.1965033%    2.0431747%                    SNPS    0.0069661825    0.0027738512    0.0013561598                    -17.75956%    -12.68433%    -17.86317%    -14.62349%            mothly return
    11/1/19    141.039993    180.237808    112.548447    3140.98        3.8968641%    3.1589050%    1.5606189%    3.4047064%                    V    0.0027738512    0.0046815897    0.0012766614                    -12.76980%    -13.50776%    -15.10605%    -13.26196%        return    2.2614%    27.1363%
    12/1/19    139.199997    183.85672    112.312172    3230.78        -1.3045917%    2.0078540%    -0.2099318%    2.8589803%                    WMT    0.0013561598    0.0012766614    0.0027733156                    -17.97126%    -14.65881%    -16.87660%    -13.80769%        variance     0.003879872
    1/1/20    147.509995    194.688507    108.686455    3225.52        5.9698263%    5.8914284%    -3.2282494%    -0.1628090%                                                    -10.69684%    -10.77524%    -19.89492%    -16.82948%        st dev    6.2289%    21.5774%
    2/1/20    137.929993    177.848831    102.221664    2954.22        -6.4944765%    -8.6495481%    -5.9481110%    -8.4110469%                                                    -23.16114%    -25.31621%    -22.61478%    -25.07771%
    3/1/20    128.789993    157.881287    107.86055    2584.59        -6.6265500%    -11.2272563%    5.5163316%    -12.5119321%                                                    -23.29322%    -27.89392%    -11.15034%    -29.17860%        risk free rate        2%
    4/1/20    157.119995    175.127548    115.899147    2912.43        21.9970522%    10.9235625%    7.4527684%    12.6844103%                                                    5.33039%    -5.74310%    -9.21390%    -3.98226%
    5/1/20    180.910004    191.315506    118.29245    3044.31        15.1412995%    9.2435246%    2.0649876%    4.5281775%                                                    -1.52537%    -7.42314%    -14.60168%    -12.13849%        sharp...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download