financial markets
angei112
StockSpreadsheet.xlsxSheet 1
| Examining Risk and Return Characteristics of a Two Asset Portfolio | |||||
| Profile of Individual Assets | |||||
| Asset | Expected Return | Risk | |||
| 1 | 15% | 10% | |||
| 2 | 30% | 15% | |||
| Correltion coefficient of returns between the assets = | 1 | ||||
| Weights | Portfolio | ||||
| Portfolio # | Asset 1 | Asset 2 | Standard Deviation | Expected Return | |
| 1 | 100% | 0% | 10.00% | 15.00% | |
| 2 | 90% | 10% | 10.50% | 16.50% | |
| 3 | 80% | 20% | 11.00% | 18.00% | |
| 4 | 70% | 30% | 11.50% | 19.50% | |
| 5 | 60% | 40% | 12.00% | 21.00% | |
| 6 | 50% | 50% | 12.50% | 22.50% | |
| 7 | 40% | 60% | 13.00% | 24.00% | |
| 8 | 30% | 70% | 13.50% | 25.50% | |
| 9 | 20% | 80% | 14.00% | 27.00% | |
| 10 | 10% | 90% | 14.50% | 28.50% | |
| 11 | 0% | 100% | 15.00% | 30.00% | <=B25*$B$7+C25*$B$8 |
| =SQRT(B25^2*$C$7^2+C25^2*$C$8^2+2*$F$10*B25*C25*$C$7*$C$8) | |||||
Two Asset Portfolio
Expected Return 0.1 0.105 0.11 0.115 0.12000000000000001 0.125 0.13 0.13499999999999998 0.14000000000000001 0.14500000000000002 0.15 0.15 0.16500000000000001 0.18 0.19500000000000001 0.21 0.22499999999999998 0.24 0.255 0.27 0.28500000000000003 0.3Risk (Standard Deviation) %
Return %
Sheet 2
| Quarterly Return of Selected Funds during the Past Five Years | Summary of Return and Risk | Variance-Covariance Matrix | |||||||||||||||||||||||||||||||||||||
| Fund # | MF1 | MF2 | MF3 | MF4 | MF5 | MF6 | MF7 | MF8 | MF9 | MF10 | Fund # | MF1 | MF2 | MF3 | MF4 | MF5 | MF6 | MF7 | MF8 | MF9 | MF10 | ||||||||||||||||||
| Quarter | ICICI Pru Focused Bluechip Eqty (G) | Quantum Long-Term Equity (G) | DSP-BR Micro Cap Fund - RP (G) | SBI Emerging Busi (G) | Reliance Equity Oppor - RP (G) | ICICI Pru Exp&Other Services-RP (G) | HDFC Balanced Fund (G) | HDFC Prudence Fund (G) | Birla Sun Life GSec - LTF (G) | R* Shares Golld ETF | Fund Name | ICICI Pru Focused Bluechip Eqty (G) | Quantum Long-Term Equity (G) | DSP-BR Micro Cap Fund - RP (G) | SBI Emerging Busi (G) | Reliance Equity Oppor - RP (G) | ICICI Pru Exp&Other Services-RP (G) | HDFC Balanced Fund (G) | HDFC Prudence Fund (G) | Birla Sun Life GSec - LTF (G) | R* Shares Golld ETF | Formula in previous column | ICICI Pru Focused Bluechip Eqty (G) | Quantum Long-Term Equity (G) | DSP-BR Micro Cap Fund - RP (G) | SBI Emerging Busi (G) | Reliance Equity Oppor - RP (G) | ICICI Pru Exp&Other Services-RP (G) | HDFC Balanced Fund (G) | HDFC Prudence Fund (G) | Birla Sun Life GSec - LTF (G) | R* Shares Golld ETF | Formula in previous column | ||||||
| 2009 | Q1 | 2.90% | 0.10% | -13.70% | -14.70% | -6.40% | -11.40% | -1.00% | -4.20% | 7.40% | 10.40% | Average Return per Quarter | 5.19% | 5.27% | 6.59% | 5.44% | 5.76% | 5.93% | 5.03% | 4.74% | 2.21% | 3.08% | < =AVERAGE(M6:M25) | ICICI Pru Focused Bluechip Eqty (G) | 4.61% | 5.15% | 7.33% | 6.62% | 5.84% | 5.28% | 4.00% | 5.19% | 0.18% | -0.87% | < =COVAR($D:$D,M:M)*4 | ||||
| 2009 | Q2 | 41.60% | 44.30% | 66.00% | 66.20% | 47.50% | 49.10% | 37.60% | 47.00% | 4.70% | -4.10% | Annual Return | 22.43% | 22.78% | 29.08% | 23.60% | 25.08% | 25.89% | 21.69% | 20.33% | 9.14% | 12.90% | < =(1+AA6)^4-1 | Quantum Long-Term Equity (G) | 5.15% | 5.89% | 8.48% | 7.46% | 6.74% | 5.98% | 4.59% | 5.93% | 0.21% | -0.82% | < =COVAR($E:$E,M:M)*4 | ||||
| 2009 | Q3 | 19.20% | 23.40% | 30.50% | 15.70% | 27.40% | 19.80% | 14.10% | 18.20% | 1.80% | 7.70% | Variance per Quarter | 1.15% | 1.47% | 3.51% | 2.85% | 2.03% | 1.84% | 1.00% | 1.59% | 0.07% | 0.64% | < =VARP(M6:M25) | DSP-BR Micro Cap Fund - RP (G) | 7.33% | 8.48% | 14.06% | 11.83% | 10.24% | 9.04% | 7.31% | 9.11% | 0.20% | -1.44% | < =COVAR($F:$F,M:M)*4 | ||||
| 2009 | Q4 | -0.20% | -0.20% | 14.10% | -0.20% | -0.20% | 5.60% | 8.50% | -0.20% | 1.70% | -0.20% | Annual Variance of Returns | 4.61% | 5.89% | 14.06% | 11.42% | 8.11% | 7.35% | 4.00% | 6.37% | 0.28% | 2.56% | < =4*AA8 | SBI Emerging Busi (G) | 6.62% | 7.46% | 11.83% | 11.42% | 8.93% | 7.85% | 6.22% | 8.13% | 0.23% | -1.47% | < =COVAR($G:$G,M:M)*4 | ||||
| 2010 | Q1 | 3.40% | 1.90% | 9.20% | 4.60% | 5.40% | 1.80% | 4.00% | 4.10% | 1.20% | -2.80% | Srandard Deviation of Returns | 21.47% | 24.26% | 37.49% | 33.79% | 28.48% | 27.11% | 20.01% | 25.25% | 5.32% | 16.01% | < =SQRT(AA9) | Reliance Equity Oppor - RP (G) | 5.84% | 6.74% | 10.24% | 8.93% | 8.11% | 6.90% | 5.46% | 7.04% | 0.16% | -1.09% | < =COVAR($H:$H,M:M)*4 | ||||
| 2010 | Q2 | 2.90% | 6.10% | 9.40% | 0.20% | 5.00% | 0.40% | 6.70% | 6.60% | 5.70% | 14.20% | ICICI Pru Exp&Other Services-RP (G) | 5.28% | 5.98% | 9.04% | 7.85% | 6.90% | 7.35% | 4.79% | 5.99% | -0.23% | -1.12% | < =COVAR($I:$I,M:M)*4 | ||||||||||||||||
| 2010 | Q3 | 15.40% | 16.20% | 14.20% | 19.10% | 18.30% | 14.30% | 9.90% | 12.50% | 1.40% | 1.50% | HDFC Balanced Fund (G) | 4.00% | 4.59% | 7.31% | 6.22% | 5.46% | 4.79% | 4.00% | 4.93% | 0.17% | -0.81% | < =COVAR($J:$J,M:M)*4 | ||||||||||||||||
| 2010 | Q4 | 1.20% | 0.20% | -4.40% | 0.10% | -2.60% | -1.90% | 0.60% | -0.70% | 1.10% | 7.30% | Annual | HDFC Prudence Fund (G) | 5.19% | 5.93% | 9.11% | 8.13% | 7.04% | 5.99% | 4.93% | 6.37% | 0.27% | -1.14% | < =COVAR($K:$K,M:M)*4 | |||||||||||||||
| 2011 | Q1 | -2.60% | -5.80% | -14.70% | -9.00% | -7.30% | -4.90% | -3.10% | -3.20% | 1.20% | 1.30% | Fund | Return | Weights | Birla Sun Life GSec - LTF (G) | 0.18% | 0.21% | 0.20% | 0.23% | 0.16% | -0.23% | 0.17% | 0.27% | 0.28% | -0.23% | < =COVAR($L:$L,M:M)*4 | |||||||||||||
| 2011 | Q2 | -1.00% | -2.50% | 1.50% | 4.90% | 2.60% | -2.70% | 4.60% | 1.60% | 1.10% | 4.90% | ICICI Pru Focused Bluechip Eqty (G) | 22.43% | 20.00% | R* Shares Golld ETF | -0.87% | -0.82% | -1.44% | -1.47% | -1.09% | -1.12% | -0.81% | -1.14% | -0.23% | 2.56% | < =COVAR($M:$M,M:M)*4 | |||||||||||||
| 2011 | Q3 | -9.40% | -8.70% | -5.20% | -1.40% | -8.90% | -10.70% | -5.10% | -6.80% | 1.40% | 19.20% | Quantum Long-Term Equity (G) | 22.78% | 0.00% | |||||||||||||||||||||||||
| 2011 | Q4 | -2.40% | -4.00% | -13.50% | -8.10% | -8.50% | -7.30% | -6.60% | -7.50% | 3.20% | 2.00% | DSP-BR Micro Cap Fund - RP (G) | 29.08% | 0.00% | |||||||||||||||||||||||||
| 2012 | Q1 | 12.70% | 17.10% | 20.00% | 14.60% | 21.10% | 17.80% | 15.50% | 16.00% | 2.00% | 3.80% | SBI Emerging Busi (G) | 23.60% | 0.00% | |||||||||||||||||||||||||
| 2012 | Q2 | -0.60% | -1.90% | 0.40% | 3.10% | 1.40% | -3.20% | -0.80% | -1.10% | 3.40% | 3.50% | Reliance Equity Oppor - RP (G) | 25.08% | 0.00% | |||||||||||||||||||||||||
| 2012 | Q3 | 8.10% | 8.60% | 9.60% | 10.20% | 10.70% | 9.30% | 6.20% | 6.50% | 2.00% | 5.20% | ICICI Pru Exp&Other Services-RP (G) | 25.89% | 20.00% | |||||||||||||||||||||||||
| 2012 | Q4 | 4.40% | 3.80% | 4.50% | 13.00% | 5.40% | 5.20% | 2.50% | 5.10% | 2.40% | -2.30% | HDFC Balanced Fund (G) | 21.69% | 20.00% | |||||||||||||||||||||||||
| 2013 | Q1 | -4.90% | -3.90% | -15.80% | -12.50% | -7.90% | 1.90% | -5.80% | -8.60% | 2.60% | -2.80% | HDFC Prudence Fund (G) | 20.33% | 0.00% | |||||||||||||||||||||||||
| 2013 | Q2 | 1.80% | -0.60% | -0.70% | -1.70% | -3.10% | 2.10% | -0.30% | -0.40% | 5.20% | -15.60% | Birla Sun Life GSec - LTF (G) | 9.14% | 20.00% | |||||||||||||||||||||||||
| 2013 | Q3 | 1.30% | -1.10% | -4.50% | -8.40% | -4.30% | 17.00% | -2.30% | -7.30% | -6.60% | 16.00% | R* Shares Golld ETF | 12.90% | 20.00% | |||||||||||||||||||||||||
| 2013 | Q4 | 10.00% | 12.30% | 24.90% | 13.10% | 19.50% | 16.30% | 15.40% | 17.10% | 1.30% | -7.60% | ||||||||||||||||||||||||||||
| Total weights | 100.00% | < =SUM(R15:R24) | |||||||||||||||||||||||||||||||||||||
| Return | 18.41% | < =MMULT(TRANSPOSE(R15:R24),Q15:Q24) | |||||||||||||||||||||||||||||||||||||
| Variance | 1.64% | < =MMULT(MMULT(TRANSPOSE(R15:R24),AD6:AM15),R15:R24) | |||||||||||||||||||||||||||||||||||||
| Standard Deviation | 12.82% | < =SQRT(S29) | |||||||||||||||||||||||||||||||||||||
| Sharpe Ratio | 0.8119799101 | < =(S28-0.08)/S30 | (assuming risk free rate is 8%) | ||||||||||||||||||||||||||||||||||||
| To maximise Sharpe Ratio, Solver can be used. | |||||||||||||||||||||||||||||||||||||||
| The market portfolio has following characteristics | |||||||||||||||||||||||||||||||||||||||
| Expected Return = | 18.41% | ||||||||||||||||||||||||||||||||||||||
| Standard Deviation = | 12.82% | ||||||||||||||||||||||||||||||||||||||
| Final Portfolio | |||||||||||||||||||||||||||||||||||||||
| Weights | |||||||||||||||||||||||||||||||||||||||
| Asset | Annual Return | Market Portfolio | Final Portfolio | ||||||||||||||||||||||||||||||||||||
| ICICI Pru Focused Bluechip Eqty (G) | 22.43% | 20.00% | 15.60% | ||||||||||||||||||||||||||||||||||||
| Quantum Long-Term Equity (G) | 22.78% | 0.00% | 0.00% | ||||||||||||||||||||||||||||||||||||
| DSP-BR Micro Cap Fund - RP (G) | 29.08% | 0.00% | 0.00% | ||||||||||||||||||||||||||||||||||||
| SBI Emerging Busi (G) | 23.60% | 0.00% | 0.00% | ||||||||||||||||||||||||||||||||||||
| Reliance Equity Oppor - RP (G) | 25.08% | 0.00% | 0.00% | ||||||||||||||||||||||||||||||||||||
| ICICI Pru Exp&Other Services-RP (G) | 25.89% | 20.00% | 15.60% | ||||||||||||||||||||||||||||||||||||
| HDFC Balanced Fund (G) | 21.69% | 20.00% | 15.60% | ||||||||||||||||||||||||||||||||||||
| HDFC Prudence Fund (G) | 20.33% | 0.00% | 0.00% | ||||||||||||||||||||||||||||||||||||
| Birla Sun Life GSec - LTF (G) | 9.14% | 20.00% | 15.60% | ||||||||||||||||||||||||||||||||||||
| R* Shares Golld ETF | 12.90% | 20.00% | 15.60% | ||||||||||||||||||||||||||||||||||||
| Risk Free Asset | 8.00% | 22.00% | |||||||||||||||||||||||||||||||||||||
| Total | 100.00% |
