Scribd Logo
Upload

Welcome to Scribd!

  • 6 views

    C4 - Excel Application Solution

    Uploaded by

    Yasmine Rhissassi

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    C4 - Excel Application Solution

    Uploaded by

    Yasmine Rhissassi
    6 views14 pages

    Original Title

    C4_Excel Application Solution

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    6 views14 pages

    C4 - Excel Application Solution

    Uploaded by

    Yasmine Rhissassi

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 14

    Start Date 12/31/2014

    End
    StepDate1: Download monthly
    12/31/2019 net asset values for the

    NORWACT FP Equity EURF50I FP Equity

    AVIVA ACTION AFER ACTIONS


    EUROPE ISR EURO ISR

    Dates TOT_RETURN_INDEX_NTOT_RETURN_INDEX
    12/31/2019 91.23 131976.89
    11/29/2019 89.03 129711.03
    10/31/2019 86.17 126133.44
    9/30/2019 84.91 125773.87
    8/30/2019 81.58 120573.76
    8/31/2015 69.64 106811.7
    7/31/2015 75.9 116503.5
    6/30/2015 72.97 111512.4
    5/29/2015 76.45 115979.4
    4/30/2015 75.62 114378.1
    3/31/2015 75.25 115736.8
    2/27/2015 73.61 112508.3
    1/30/2015 67.78 103953
    12/31/2014 63.35 96966.39
    asset values for the seven SR funds and for the STOXX Europe 600 index

    AGFOPII FP Equity NSMCRET FP Equity AXAEUNC FP Equity BNPETHE FP Equity

    BNP PARIBAS
    ALLIANZ VALEURS ALM ACTIONS ZONE AXA EURO VALEURS
    ACTIONS EUROPE
    DURABLES EURO ISR RESPONSABLES
    RESPONSABLE

    TOT_RETURN_INDEX TOT_RETURN_INDEX TOT_RETURN_INDEX TOT_RETURN_INDEX


    271.88 20.59 55.88 269.9272
    267.27 20.34 55.45 266.0995
    260.8 19.68 53.7 260.8013
    258.7 19.54 53.04 259.5687
    249.4 18.69 51.18 249.4805
    201.6 15.64 42.92 234.146
    219.34 17.01 47.01 256.9655
    209.62 16.25 44.98 247.0943
    218.37 16.94 46.62 259.5044
    215.42 16.87 46.29 254.1727
    216.39 16.84 46.69 254.7313
    211.71 16.28 45.31 249.2575
    196.21 15.05 42.06 231.597
    182.13 14.23 39.11 217.45
    urope 600 index from Bloomberg ( 31/12/2014 to 31/12/2019)

    CMVALTH FP Equity SXXP Index

    CM-CIC OBJECTIF STOXX Europe 600


    ENVIRONNEMENT index

    TOT_RETURN_INDEX TOT_RETURN_INDEX_NET_DVDS
    10.55 495.2691
    10.5 484.884
    10.25 471.4332
    10.15 466.5119
    9.84 449.7881
    8.98 372.5402
    9.8 405.9147
    9.31 390.1952
    9.73 408.3089
    9.69 401.1678
    9.69 400.5563
    9.46 393.5156
    8.85 367.7668
    8.35 342.859
    Step 2 : Compute monthly returns for the seven SR funds and for the ST

    AVIVA ACTION AFER ACTIONS ALLIANZ VALEURS


    EUROPE ISR EURO ISR DURABLES
    Month
    1 2.47% 1.75% 1.72%
    2 3.32% 2.84% 2.48%
    3 1.48% 0.29% 0.81%
    4 4.08% 4.31% 3.73%
    5 -2.02% -2.11% -0.73%
    6 1.28% 0.27% 0.60%
    7 4.98% 5.45% 5.77%
    55 -4.55% -3.85% -4.01%
    56 1.10% 1.40% 1.37%
    57 0.49% -1.17% -0.45%
    58 2.23% 2.87% 2.21%
    59 8.60% 8.23% 7.90%
    60 6.99% 7.21% 7.73%
    Step 3: Compute the expected return, volatility and beta for the

    E(Ri) 0.69% 0.60% 0.75%


    Volatility 4.05% 4.09% 3.93%
    Beta 1.10 1.11 1.06

    Rf 0.30%

    Step 4 : Assume that the risk-free rate is 0.3%. Compute the Shar

    Sharpe Ratio 9.65% 7.27% 11.35%


    Treynor Ratio 0.36% 0.27% 0.42%
    Jensen's Alpha -0.03% -0.12% 0.04%
    nds and for the STOXX Europe 600 index

    AXA EURO BNP PARIBAS


    ALM ACTIONS CM-CIC OBJECTIF
    VALEURS ACTIONS EUROPE
    ZONE EURO ISR ENVIRONNEMENT
    RESPONSABLES RESPONSABLE
    1.23% 0.78% 1.44% 0.48%
    3.35% 3.26% 2.03% 2.44%
    0.72% 1.24% 0.47% 0.99%
    4.55% 3.63% 4.04% 3.15%
    -1.89% -0.58% -1.52% -1.30%
    -0.26% -0.39% 0.66% 0.00%
    5.88% 5.58% 4.85% 5.73%
    -4.07% -3.52% -4.78% -4.32%
    0.41% 0.71% 2.10% 0.41%
    0.18% -0.86% -0.22% 0.00%
    3.44% 3.05% 2.20% 2.43%
    8.17% 7.73% 7.63% 6.89%
    5.76% 7.54% 6.51% 5.99%
    and beta for the seven SR funds and for the STOXX Europe 600 index

    0.70% 0.68% 0.43% 0.46%

    4.04% 4.00% 3.73% 3.64%

    1.09 1.08 1.02 0.99

    ompute the Sharpe ratio, the Treynor ratio and the Jensen’s alpha for each fund

    9.85% 9.39% 3.47% 4.28% <--- (I65-$C$68)/I66


    0.37% 0.35% 0.13% 0.16% <--- (I65-$C$68)/I67
    -0.01% -0.03% -0.26% -0.22% <--- I65-($C$68+I67*($K$65-$C
    STOXX
    Europe 600
    Index
    2.14%
    2.85%
    1.05%
    3.72%
    -1.32%
    0.35%
    4.51%
    -4.44%
    1.78%
    0.15%
    1.79%
    7.00%
    7.26%
    0 index

    0.68% <--MOYENNE(K3:K62)

    3.58% <-- ECARTYPE.STANDARD(K3:K62)

    1.00 <-- COVARIANCE.STANDARD(K3:K62;$K$3:$K$62)/VAR.S($K$3:$K$62)

    lpha for each fund

    <--- (I65-$C$68)/I66
    <--- (I65-$C$68)/I67
    <--- I65-($C$68+I67*($K$65-$C$68))

    You might also like