当前位置:天才代写 > 作业代写 > Financial Modelling代写 财务建模代写

Financial Modelling代写 财务建模代写

2022-10-21 11:04 星期五 所属: 作业代写 浏览:167

Financial Modelling代写

Financial Modelling 2018-19 Project

Portfolio Optimization

Financial Modelling代写 In our project, we aim to perform a portfolio optimization analysis based on a portfolio of thirty UK stocks and solve seven tasks.

Group members:

Introduction  Financial Modelling代写

In our project, we aim to perform a portfolio optimization analysis based on a portfolio of thirty UK stocks and solve seven tasks. In the provided dataset, we have 10 years (Dec. 2007 – Dec. 2017) of monthly price and market capitalization data for each of the stocks as well as prices for the FTSE100 market index and proxies for the risk-free rates over various sub-periods of the sample.

In this report, we offer the clear detailed working process and explanation for efficient frontier generation, portfolio optimization, and out-of-sample risk-adjusted performance analysis. We mainly incorporate Excel Solver and several Excel functions to achieve our results. These tools are extremely effective and next we will present our solution on the seven tasks

Task 1

(1)As required, firstly we calculate return, then we generate variance-covariance matrix for the 30 stocks as shown in table 1.1 (a snapshot of 30*30matrix).

  • Our Workings andfindings
Financial Modelling代写
Financial Modelling代写

Table 1.1

(2)Then we set up initial weight for each stock as 1/30, and we calculate portfolio statistics using both portfolio return series and Variance-covariance matrix as shown in table2.

Table 1.2

(3)Use Solver to search for the minimum variance portfolio. The calculated weights are shown in table3.

MAN GROUP 0.06691
AVEVA GROUP 0.05715
BARCLAYS 0.07092
SKY -0.00598
BT GROUP 0.07878
CARNIVAL 0.07491
CLARKSON 0.06324
COBHAM 0.00904
GLAXOSMITHKLINE 0.09425
GREGGS 0.08846
HISCOX DI 0.11716
IMI  Financial Modelling代写 -0.04639
INTERTEK GROUP 0.17510
LAIRD -0.01576
LLOYDS BANKING GROUP 0.01236
LOOKERS 0.00323
MARKS & SPENCER GROUP -0.01161
MITIE GROUP 0.02741
NATIONAL EXPRESS GP. -0.04591
NATIONAL GRID 0.30425
NEXT 0.01086
OLD MUTUAL LIMITED (LON) -0.16427
SAINSBURY J 0.12681
SERCO GROUP 0.00261
SHIRE 0.06211
SIG -0.05674
UNILEVER (UK) -0.00701
UNITE GROUP -0.04049
VESUVIUS -0.03559
WILLIAM HILL -0.01579

Table 1.3

(4)Set up the target standard deviation and use Solver to maximize and minimize return for portfolios, then plot return against variance and get the efficient frontier graph as shown in graph 1.1.

Graph 1.1

(5)Calculate 30 stocks return and variance and then plot them in the same efficient frontier graph as shown in graph 1.2

Graph 1.2

As we can see, the mean-variance frontier generates a much better risk-adjusted return and this shows that firm specific risks of these 30 stocks tend to offset each other and expected returns are not sacrificed. Based on what we have learnt, the unsystematic risks fade away when we add more and more risky asset into our portfolio: the benefits of diversification.

Financial Modelling代写
Financial Modelling代写

Task 2

  • Our workings and findings

(1)Paste price data and risk-free rate data into a new worksheet and calculate returns for 30 stocks. Then we set up initial weight for each stock as 1/30 and create a portfolio column with MMULT function

(2)Create Variance-covariance matrix using MMULT function on the returnseries

(3)Calculate portfolio statistics using both portfolio return seriesand Variance-covariance matrix to double check the result as shown in table 2.1.

Table 2.1

(4)Use Solver to search for the maximum Sharpe ratio portfolio and the weights for each stock are shown in table 2.2 But we do not believe that the composition of the market portfolio that we have found is a desirable or practical one as an investment. The reasons are we need to short 15 stocks, and the short-selling will be very costly since we need to put the money in margin account and we may have margin call. Further, we need to monitor and manage 30 stocks, and this will be very time-consuming and hard.

The Market Portfolio (M)
MAN GROUP 0.1313832
AVEVA GROUP 0.2085357
BARCLAYS -0.1108155
SKY -0.1383923
BT GROUP 0.1557715
CARNIVAL 0.2185837
CLARKSON 0.0269352
COBHAM -0.2845411
GLAXOSMITHKLINE -0.0331599
GREGGS 0.1826932
HISCOX DI 0.6554072
IMI 0.2024278
INTERTEK GROUP 0.6231719
LAIRD -0.0656085
LLOYDS BANKING GROUP  -0.1508879
LOOKERS 0.1295702
MARKS & SPENCER GROUP -0.3237494
MITIE GROUP -0.2175324
NATIONAL EXPRESS GP.  -0.0585352
NATIONAL GRID 0.1941891
NEXT   Financial Modelling代写 0.4516032
OLD MUTUAL LIMITED (LON) -0.1970358
SAINSBURY J -0.1675147
SERCO GROUP -0.2786533
SHIRE 0.1528195
SIG -0.1094252
UNILEVER (UK) -0.2214737
UNITE GROUP 0.0392485
VESUVIUS -0.0208471
WILLIAM HILL 0.0058319

Table 2.2

(5)Set up the different weight for risk-free asset and calculate weight for risky asset, and portfolio return and variance. Then we plot investor portfolio return against variance and standard deviation, and add them on the efficient frontier graph as shown in graph 2.1 and 2.2. As we can see, based on two fund separation theorem, the introduction of risk-free asset changes the optimal portfolio holdings for investors from red line to blue line (capital marketline).

Financial Modelling代写
Financial Modelling代写

Task 3

Our workings and findings

(1)We repeat steps from task 1 to task 2, but restricting short selling. The composition of market portfolio is shown in table 3.1. As we can see this portfolio only has long position so we do not need to short any stocks. Further, we only need to invest in 8 stocks, so our management and transaction costs will be lower than previous portfolio, which has 30 positions to invest and manage. In conclusion, the portfolio restricting short selling is the more desirable or practical portfolio as an investment.

The Market Portfolio (M)
MAN GROUP 0.0000000
AVEVA GROUP 0.0149952
BARCLAYS 0.0000000
SKY 0.0000000
BT GROUP 0.0000000
CARNIVAL 0.0000000
CLARKSON 0.0716133
COBHAM 0.0000000
GLAXOSMITHKLINE 0.0000000
GREGGS 0.1461971
HISCOX DI 0.2997616
IMI 0.0000000
INTERTEK GROUP 0.2643230
LAIRD 0.0000000
LLOYDS BANKING GROUP  0.0000000
LOOKERS 0.0000000
MARKS & SPENCER GROUP 0.0000000
MITIE GROUP 0.0000000
NATIONAL EXPRESS GP.  0.0000000
NATIONAL GRID 0.0705641
NEXT 0.0879743
OLD MUTUAL LIMITED (LON) 0.0000000
SAINSBURY J 0.0000000
SERCO GROUP 0.0000000
SHIRE 0.0445714
SIG 0.0000000
UNILEVER (UK) 0.0000000
UNITE GROUP 0.0000000
VESUVIUS 0.0000000
WILLIAM HILL 0.0000000

Table 3.1

(2)We also plot the capital market line and efficient frontier as shown ingraph 3.1 and 3.2.

Task 4  Financial Modelling代写

Our workings and findings

(1)We Divide the sample in first half (Jan. 2008-Dec. 2012, “in-sample period”) and second half Jan. 2013-Dec. 2017, “out-of-sampleperiod”).

(2)Then we construct equally-weighted portfolios andmarket capitalization-weighted portfolios of the 30 stocks for both sub-sample periods.

(3)We compare the risk-adjusted performance of these four portfolios using Sharpe ratio as shown in table1 4.1

Sharpe Ratio Difference Jan. 2008-Dec. 2012 Jan. 2013-Dec. 2017 Market Weighted Portfolio
Jan. 2008-Dec. 2012 0.040 -0.144
Jan. 2013-Dec. 2017 0.251 0.067
Equal Weighted Portfolio

Table 4.1

As we can see, equal weighted portfolio outperforms market capitalization weighted portfolios in both in-sample and out-of-sample period since its Sharpe ratios are higher than later. Further, the Sharpe ratio difference even increases in out-of-sample period and this shows that equal-weighted portfolio is more stable.

Task 5

Our workings and findings

(1)We repeat steps in task 3 but this time we re-estimate the market portfolio and the minimum variance portfolio using the first half of the data only(Jan.2008-Dec.2012). The weights of both portfolios are shown below in table 5.1 and 5.2.

Minimum Variance Portfolio Weights
MAN GROUP 0.0276759
AVEVA GROUP 0.0000000
BARCLAYS 0.0000000
SKY  Financial Modelling代写 0.0000000
BT GROUP 0.0000000
CARNIVAL 0.0000000
CLARKSON 0.0000000
COBHAM 0.0000000
GLAXOSMITHKLINE 0.1599254
GREGGS 0.1618785
HISCOX DI 0.0141122
IMI 0.0000000
INTERTEK GROUP 0.0772122
LAIRD 0.0000000
LLOYDS BANKING GROUP  0.0000000
LOOKERS 0.0000000
MARKS & SPENCER GROUP 0.0000000
MITIE GROUP 0.1304072
NATIONAL EXPRESS GP.   

 

0.0000000

NATIONAL GRID 0.3232745
NEXT 0.0000000
OLD MUTUAL LIMITED (LON) 0.0000000
SAINSBURY J 0.1055140
SERCO GROUP 0.0000000
SHIRE 0.0000000
SIG 0.0000000
UNILEVER (UK) 0.0000000
UNITE GROUP 0.0000000
VESUVIUS 0.0000000
WILLIAM HILL 0.0000000

 Table 5.1

Market Portfolio Weights
MAN GROUP 0.0000000
AVEVA GROUP 0.1230718
BARCLAYS 0.0000000
SKY 0.0000000
BT GROUP 0.0000000
CARNIVAL 0.0000000
CLARKSON   Financial Modelling代写 0.0000000
COBHAM 0.0000000
GLAXOSMITHKLINE 0.0000000
GREGGS 0.0000000
HISCOX DI 0.2007059
IMI 0.0000000
INTERTEK GROUP 0.4471119
LAIRD 0.0000000
LLOYDS BANKING GROUP  0.0000000
LOOKERS 0.0000000
MARKS & SPENCER GROUP 0.0000000
MITIE GROUP 0.0000000
NATIONAL EXPRESS GP.   

 

0.0000000

NATIONAL GRID 0.0000000
NEXT 0.2291104
OLD MUTUAL LIMITED (LON) 0.0000000
SAINSBURY J 0.0000000
SERCO GROUP 0.0000000
SHIRE 0.0000000
SIG 0.0000000
UNILEVER (UK) 0.0000000
UNITE GROUP 0.0000000
VESUVIUS 0.0000000
WILLIAM HILL 0.0000000

 Table 5.2

(2)We then Use the weights of the market portfolio and the minimum variance portfolio from the first half of the data and apply those to the second half of the data (Jan. 2013-Dec.2017)

(3)We compare the risk-adjusted performance of the four portfolios using the Sharpe ratio as shown in table 5.3

Sharpe Ratio Analysis for  two periods
Minimum Variance Portfolio Mean Std Risk Free rate Risk Premium Sharpe Ratio
Jan. 2008-Dec. 2012 0.56% 0.033152915 0.11% 0.45% 0.134792068
Jan. 2013-Dec. 2017 0.96% 0.03115284 0.03% 0.93% 0.300043555
Market Portfolio Mean Std Risk Free rate Risk Premium Sharpe Ratio
Jan. 2008-Dec. 2012 0.020 0.048005271 0.11% 1.90% 0.39651647
Jan. 2013-Dec. 2017 0.012920542 0.041058505 0.03% 1.26% 0.307379471

 

Sharpe ratio difference Jan. 2008-Dec. 2012 Jan. 2013-Dec. 2017 << Minimum Variance Portfolio
Jan. 2008-Dec. 2012 0.261724401 0.096472915
Jan. 2013-Dec. 2017 0.172587402 0.007335916
Market Portfolio ^^

Table 5.3

As we can see, market portfolio outperforms minimum variance portfolio a lot in in-sample period, but in out-of-sample period, it only beats later a little. This shows that the maximum Sharpe ratio strategy is not stable and we need to be careful when using this strategy in long-term investment. 

Task 6  Financial Modelling代写

Our workings and findings

(1) We rank the portfolio performance based on Sharpe ratio as shown in table 6.1 and present the detailed Sharpe ratio table in table 6.2

 

Rankings
Sharpe ratio for in-sample Sharpe ratio for out-sample
Market Portfolio Market Portfolio
Minimum Variance Portfolio Equal Weighted Portfolio
Equal Weighted Portfolio Minimum Variance Portfolio
Market capitalisation-Weighted Portfolio Market capitalisation-Weighted Portfolio
Table 6.1
Sharpe Ratio Analysis for two periods
Equal Weighted Portfolio Sha rp e R a tio
Jan. 2008-Dec. 2012 0.09570
Jan. 2013-Dec. 2017 0.30669
Market capitalisation-Weighted Portfolio Sha rp e R a tio
Jan. 2008-Dec. 2012 0.05597
Jan. 2013-Dec. 2017 0.23925
Minimum Variance Portfolio Sha rp e R a tio
Jan. 2008-Dec. 2012 0.13479
Jan. 2013-Dec. 2017 0.30004
Market Portfolio Sha rp e R a tio
Jan. 2008-Dec. 2012 0.39652
Jan. 2013-Dec. 2017 0.30738

Table 6.2

As we can see, the market portfolio outperforms all other strategy in both the in-sample and out-sample period,

but it actually performs worse than pervious period (with global financial crisis), so this is a surprise to us and may show that the estimated weight for market portfolio only suits for the similar market conditions. If the market conditions change, the market portfolio can not perform as good as it was. The Sharpe ratio difference among four strategies also shrink in out-of-sample period. As Victor DeMiguel says in his paper, Optimal Versus Naive Diversification: How Inefficient is the 1/N Portfolio Strategy?, the gain from optimal diversification is more than offset by estimation error with time. We are also amazed by the performance of naive equal-weighted portfolio as we will discuss next.  Financial Modelling代写

Further, As ROGER C LARKE et al. presented in their paper, Risk Parity, Maximum Diversification, and Minimum Variance: An Analytic Perspective, from 1986 to 2012, equal-weighted portfolio outperforms market capitalization-Weighted Portfolio but underperforms Minimum Variance Portfolio in risk-adjusted basis. But in our results, we can see that equal-weighted portfolio performs better than minimum variance portfolio in resurgence period but worse than minimum variance portfolio in recession. We think this makes sense since the minimum variance portfolio limits the big downside risk but also restrains the upside returns. This is why our results are not what we have expected

Conclusion

After the long-term working on financial modeling, and analysis on different portfolio strategy (such as minimum Variance, maximum Sharpe ratio, equally-weighted and market capitalization-weighted), we have learnt how to merge theory with practice and conduct further research in empirical literature to solve our questions. We sincerely appreciate this project and will continuously reflect on our work throughout our future life.

References   Financial Modelling代写

1.Clarke, Roger G and de Silva, Harindra and Thorley, Steven, RiskParity,Maximum Diversification, and Minimum Variance: An Analytic Perspective (June 1, 2012). Journal of Portfolio Management, Vol. 39, No. 3, pp. 39-53 (Spring 2013).

Available at SSRN: https://ssrn.com/abstract=1977577 or http://dx.doi.org/10.2139/ssrn.1977577

2.DeMiguel, Victor and Garlappi, Lorenzo and Uppal, Raman, Optimal Versus Naive Diversification: How Inefficient is the 1/N Portfolio Strategy? (May 2009). The Review of Financial Studies, Vol. 22, Issue 5, pp. 1915-1953, 2009. Available at SSRN: https://ssrn.com/abstract=1376199 orhttp://dx.doi.org/hhm075

 

Financial Modelling代写
Financial Modelling代写

更多代写:新西兰代考  雅思网上考试  新西兰教育学作业代写  学期陈述代写  信息科技代写论文  考试代考

合作平台:essay代写 论文代写 写手招聘 英国留学生代写

 

天才代写-代写联系方式