Portfolio Management and Excel Modeling

This report explains the calculation of risk and returns of different stocks, analysis of variance and standard deviation, and practical limitations of portfolio management. It also includes the calculation of value at risk for the portfolio. The report is relevant for those interested in portfolio management and excel modeling.

PORTFOLIO
MANAGEMENT
AND
EXCEL MODELING

Contents
INTRODUCTION...........................................................................................................................3
MAIN BODY...................................................................................................................................3
1.Estimation of Weekly and Annual Return of Each Share:..................................................3
2.Calculation of Standard Deviation of Annual Returns:.......................................................3
3.Production of Matrix of Correlations between Weekly returns of Each Share:..................3
4.Creation of Spreadsheet Model to Identify Efficient Portfolio and derivation of Efficient
Frontier Chart. Explanation on the relationship between risk and return and how
diversification reduces risk:....................................................................................................4
5.Discussion of Practical limitation to portfolio management and models itself:..................5
6.Based on the volatility calculate 95 % VAR of the Portfolio and also comment upon it:. .6
CONCLUSION................................................................................................................................7
REFERENCES................................................................................................................................8

INTRODUCTION
Portfolio management is vital for an investor as their funds are being invested in multiple
stocks and if not managed properly then they will lose the sums that are being invested because
the fluctuations in the market are volatile. Therefore, the investor needs to protect themselves
from such changes with the help of hedging their portfolio (Araujo, Storopoli, and Rabechini Jr,
2021). This report shall consist of the stock of Five different companies whose returns on weekly
basis are being given in the spreadsheet. The weekly and annual returns are being calculated
accordingly and after that, the variance and standard deviation are being analyzed so that the risk
involved in the portfolio can be judged and analysed. Further this report also showcases the
practical limitation of portfolio management and also showcase the volatility at 95 % value at
risk of the portfolio.
MAIN BODY
1.Estimation of Weekly and Annual Return of Each Share:
The annual return on the stock will be calculated with the help of returns on the stock at the
end of each trading day. The returns so arrived are being calculated by taking the difference
between the stock. The annual returns are being calculated with the help of taking the average of
all the returns that are arrived on daily and weekly basis. The weekly and annual return
associated with the different companies are being explained below:
(Covered in Excel)
2.Calculation of Standard Deviation of Annual Returns:
Standard deviation is considered to be the risk that has been attached with all the securities
that are being listed on the stock exchange. Standard deviation shows the deviated value or the
range of value from the original one so that investors have an idea that how much the value is
fluctuated in respect of the particular security (Brasil, Salerno, and Gomes, 2021).
(Covered in Excel)
3.Production of Matrix of Correlations between Weekly returns of Each Share:
The correlation shows the relationship between the two securities with the change in the
market occurs. The relation can be positive or negative depending upon the security and the
market fluctuations. The matrix of correlations of each share is explained below:

RTO.L SSE.L WTB.L AHT.L STJ.L SMT.L BA. L ANTO.L BATS.L RR. L
RTO.L 1
SSE.L 0.567624 1
WTB.L -0.60165 -0.55552 1
AHT.L 0.812883 0.876572 -0.47642 1
STJ.L 0.405514 0.919832 -0.21171 0.849434 1
SMT.L 0.913992 0.78496 -0.69677 0.941061 0.649833 1
BA. L -0.60512 0.219013 0.493969 -0.07775 0.456162 -0.37429 1
ANTO.L 0.816235 0.888533 -0.67518 0.966639 0.780793 0.976216 -0.17683 1
BATS.L -0.83668 -0.11106 0.211697 -0.55108 -0.08134 -0.65386 0.720901 -0.4951 1
RR. L -0.93605 -0.62289 0.83929 -0.76919 -0.37463 -0.93387 0.626033
-
0.85952 0.67206 1
4.Creation of Spreadsheet Model to Identify Efficient Portfolio and derivation of Efficient
Frontier Chart. Explanation on the relationship between risk and return and how
diversification reduces risk:
An efficient portfolio, additionally referred to as an ‘most efficient portfolio’, is one that
gives that excellent predicted go back on a given stage of chance, or alternatively, the minimal
chance for a given predicted go back. A portfolio is a variety of funding products. If, given a
selected stage of chance, the predicted returns aren't met, or if the chance required to acquire that
predicted stage of go back is simply too high, its miles known as an ‘inefficient portfolio’. On
the other hand, the Efficient frontier chart is a fixed of funding portfolios which can be
anticipated to offer the best returns at a given stage of threat (Hakanen and Jähi, 2021).
The efficient portfolio of the companies can be known by the level of risk and the highest
expected return of the security. From the analysis of the portfolio annual risk and returns, it can
be estimated that the highest annual return generated is for the security of British Tobacco
company listed as BATS.L in the stock exchange. Although the minimum risk is of BAE
systems with 1264.225.

Also, from the above efficient frontier chart it can be evaluated that the portfolio of Ashtead
Group Plc is better than all the other portfolios and is an efficient portfolio with the annual return
of 2917 and risk of 2219094. It assumes the approach of high risk high return.
A portfolio is stated to be green if there may be no different portfolio that gives better
returns for a decrease or same quantity of threat. Where portfolios are placed at the green frontier
relies upon at the investor’s diploma of threat tolerance (Klepac, Mršić, and Kopal, 2022).
5.Discussion of Practical limitation to portfolio management and models itself:
The limitations of portfolio management and model are being explained below:
Modern portfolio is an idea this is used to decide the correlation among threat and go back while
the usage of positive forms of investments. It is a totally superior mathematical version that gives
a few blessings to traders. While its miles are generally used, it does have a few capacity
drawbacks. Here are a number of the shortcomings of the contemporary-day portfolio idea.
 Assumes No Costs
Another trouble with this version is that it does now no longer think about any prices in
buying and selling. In reality, when the investor makes a change withinside the inventory
marketplace, they must pay a fee to the broking in addition to taxes to the government.
These prices can substantially affect their buying and selling and decrease their typical
returns. When something seems brilliant with a contemporary-day portfolio idea graph,
they need to decrease expectancies as it did now no longer permit something for prices.
 Assumes All Investors Have Same Credit

In order to make maximum of this work, the version has to expect that each investor can
lend and borrow the identical quantity of money. When it involves reality, traders have a
particular credit score restrict with their broking that they are able to change. Once they
attain the restrict, they're carried out buying and selling till a few margins opens again up
(Lee, Joseph, and Yu, 2022).
 Assumes Investors Have Realistic Expectations
This version assumes that traders all have a sensible expectation of funding returns.
However, in reality, that is a long way from the truth. People get overconfident and
anticipate their investments to preserve growing in value. Investors often grasp onto
investments too long, or promote them too quickly. In order to make this version work,
anybody could must time the whole lot perfectly.
 Assumes All Investors Are Risk Averse and Rational
Another massive assumption that this version makes is that each one trader is threat-
averse and are absolutely rational. In reality, many traders revel in threat and on occasion
tackle dangers that aren't wise. They do now no longer assume virtually and regularly
change primarily based totally on emotion. This version does now no longer think about
any of those elements. These elements are very essential due to the fact maximum traders
use emotion and now no longer anybody is rational.
 Assumes Investors Have no Impact on Market
Another wrong assumption this is made with this version is that each one trader are rate
takers. This way that they absolutely take the rate this is to be had withinside the
marketplace and don't have any effect on buying and selling prices. In reality, big orders
generally tend to trade the rate of securities which influences the version. Every buy and
promote order impact the marketplace, it's miles all interconnected and linked (Qin, Gu,
and Su, 2022).
6.Based on the volatility calculate 95 % VAR of the Portfolio and also comment upon it:
Value-at-risk is a statistical measure of the riskiness of economic entities or portfolios of
assets. It is described because the most greenback quantity anticipated to be misplaced over a
given time horizon, at a pre-described self-assurance level. For example, if the 95% one-month
VAR is $1 million, there may be 95% self-assurance that over the following month the portfolio
will now no longer lose extra than $1 million. VAR may be calculated the use of exclusive

techniques. Under the parametric technique, additionally referred to as variance-covariance
technique, VAR is calculated as a feature of suggest and variance of the return’s series, assuming
ordinary distribution (Sedlmayer, 2018). With the ancient technique, VAR is decided with the
aid of using taking the returns belonging to the bottom quintile of the series (diagnosed with the
aid of using the self-assurance level) and watching the best of these returns. The Monte Carlo
technique simulates big numbers of situations for the portfolio and determines VAR with the aid
of using watching the distribution of the ensuing paths.
The value at risk is calculated for all the portfolio of the companies is 24249.88 for 5 years
of time frame. It is computed in excel by apply the formula of NOMAL.INV and by selecting the
range of Weighted return of the portfolio along by choosing the confidence interval probability
of 95%.
CONCLUSION
Portfolio management is the selection, prioritisation and manipulate of an organisation’s
programmes and projects, in step with its strategic goals and potential to deliver. The purpose is
to stability the implementation of extrude tasks and the upkeep of business-as-usual, whilst
optimising goes back on investment. In the above statement the risk and returns are being
calculated of five different stocks being listed on the stock exchange on weekly and annual basis.
After that the variance and standard deviation is calculated for each stock so that risk involved in
each of the security are being calculated above. In this report also the limitation of portfolio
management is being explained in detailed manner considering the investor point of view. At the
end of the report the suitability of the portfolio is being calculated with the help of confidence
interval at 95% value at risk.

REFERENCES
Books and Journals
Araujo, M.D., Storopoli, J. and Rabechini Jr, R., 2021. Project Portfolio Management and
Information Technology Strategic Alignment. International Journal of Innovation and
Technology Management, 18(08), p.2150042.
Brasil, V.C., Salerno, M.S., and Gomes, L.A.D.V., 2021. Boosting Radical Innovation Using
Ambidextrous Portfolio Management: To manage radical innovation effectively,
companies can build ambidextrous portfolio management systems and adopt a
multilevel organizational approach. Research-Technology Management, 64(5), pp.39-
49.
Hakanen, T. and Jähi, M., 2021. Central activities of solution portfolio
management. International Journal of Services Technology and Management, 27(1-2),
pp.104-128.
Klepac, G., Mršić, L. and Kopal, R., 2022. Advanced Portfolio Management in Big Data
Environments With Machine Learning and Advanced Analytical Techniques.
In Handbook of Research on New Investigations in Artificial Life, AI, and Machine
Learning (pp. 413-437). IGI Global.
Lee, D.K.C., Joseph, L.I.M., and Yu, W.A.N.G., 2022. Portfolio Management. World Scientific
Book Chapters, pp.901-941.
Qin, Y., Gu, F. and Su, J., 2022, March. A Novel Deep Reinforcement Learning Strategy for
Portfolio Management. In 2022 7th International Conference on Big Data Analytics
(ICBDA) (pp. 366-372). IEEE.
Sedlmayer, M., 2018. Delivering organizational strategy with portfolio management. In The
Handbook of Project Portfolio Management (pp. 9-18). Routledge.
Shah, J., Doshi, M. and Nimkar, A.V., 2021, June. Kairos: A Remunerative Framework for
Minimum Investment Portfolio Management. In 2021 International Conference on
Communication information and Computing Technology (ICCICT) (pp. 1-6). IEEE.
Simonian, J., 2021. Causal Uncertainty in Capital Markets: A Robust Noisy-Or Framework for
Portfolio Management. The Journal of Financial Data Science, 3(1), pp.43-55.
Zhou, J. and Li, X., 2021. Multi-period mean-semi-entropy portfolio management with
transaction costs and bankruptcy control. Journal of Ambient Intelligence and
Humanized Computing, 12(1), pp.705-715. Cziráki, G. and Kovács, T., 2018, April.
Order and distribution in Portfolio Management. In Proceedings of International
Academic Conferences (No. 7508660). International Institute of Social and Economic
Sciences.