6 Stock Portfolio Analysis
Introduction
In this model, I calculated Minimum Variance, Efficient Frontier, & Distribution of Returns of a 6-stock Portfolio.
(Disclaimer: This is only for Educational Purpose and does not construe to any kind of trading advice)
-
Stocks - Havells, RIL, Berger Paints, Sun Pharma, ITC and TATA Motors
-
From Date - 20th May 2020
-
To Date - 22nd January 2021
-
Type - Closing Prices
Steps
-
Calculate Average Return & SD
-
Calculate Excess Returns
-
Create Variance-Covariance Matrix
-
Create Correlation Matrix
-
Calculate Annual SD & Expected Returns
-
Calculate of Min. Variance Portfolio (Solver)
-
Calculate Returns & Weights for various SDs (Solver)
-
Plot Efficient Frontier
-
Calculate Portfolio Returns as a whole
-
Frequency Distribution Table & Plot
Tools / Techniques
-
Normal Excel Functions
-
Tables
-
Solver
-
Power Query
-
Dynamic Arrays
Simulation Details
-
Number of Iterations 5,000
-
Cashflows: 10 Years
-
Fixed Discount Rate of 12%
Analysis
During the last 6 months, stock market has seen many ups and downs. The impact of COVID-19 on the economy was huge.
Auto-sector has faced lot of difficulties due to introduction of BS-VI norms and COVID-19 left the stock of BS-IV vehicles unsold during the last quarter of FY 2019-20. Companies like TATA Motors operate internationally. Their subsidiary Jaguar has also faced lot of difficulties in UK due to Brexit. Auto sector started its recovery starting from Sep-Oct timeframe. These are some of the of the reasons for the volatility of TATA Motors stock which increased the overall risk of the portfolio.
Pharma sector and FMCG Sectors on the other hand have done reasonably well during the pandemic. Although Oil Sector has taken a big hit because of lockdown restrictions, RIL stock has done well, thanks to Jio Telecom and Retail divisions. The arrival of FDI into RIL subsidiaries and partnerships with various foreign firms has boosted investor's confidence. This is the reason for good performance of RIL stock.
Paint Industry has taken a big hit during the initial lockdown but recovered fast around September Quarter because of the Infrastructure Projects taken up by State and Central Governments across India. This can be seen in the financial and stock performance of Berger paints. Electrical Industry also had similar outlook which can be observed in the sales & stock performance.
