﻿ Financial Modelling代写 财务建模代写 – 天才代写

Financial Modelling代写 财务建模代写

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

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

（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

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.1751 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.

• 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).

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.

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.

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.

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
Table6.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