A DYNAMIC SPREADSHEET MODEL
FOR DETERMINING THE PORTFOLIO FRONTIER FOR BSE30 STOCKS
Anupam Mitra
Symbiosis Institute of Business Management, Bengaluru,
Symbiosis International University, India
E-mail: anupammitra13@gmail.com
Punneet Khanna
Symbiosis Institute of Business Management, Bengaluru,
Symbiosis International University, India
E-mail: puneet.khanna14@sibm.edu.in
Submission: 02/09/2013
Accept: 17/09/2013
ABSTRACT
Introductory investments courses revolve around Harry
Markowitz’s modern portfolio theory and William Sharpe’s Index for the
performance measurement of those portfolios. This paper presents a simplified
perspective of Markowitz’s contributions to Modern Portfolio Theory. It is to
see the effect of duration of historical data on the risk and return of the
portfolio and to see the applicability of risk-reward logic. The empirical
results also show that short selling may increase the risk of the portfolio
when the investor is instability preferred.
Keywords: Markowitz Portfolio
Theory, Sharpe Ratio, BSE, Stocks, Indi
1. INTRODUCTION
Individuals are concerned with both
the expected return and the risk of the assets that might be included in their
portfolios. Investors try to diversify their portfolio as much as possible so
that if one asset of the portfolio performs badly it gets compensated by other
good performing asset.
Three themes of portfolio theory,
all centering on risk have been looked. The first was the basic tenet that
investors avoid risk and demand a reward for engaging in the risky investments.
The second theme allowed us to quantify investors’ personal trade-offs between
portfolio risk and expected return. Finally, the third fundamental principle is
that the risk of an asset separate from the portfolio of which it is a part
cannot be evaluated; i.e., the proper way to measure the risk of an individual
asset is to assess its impact on the volatility of the entire portfolio of
investments.
2. SELECTION OF
STOCKS (EQUITY SHARES)
Indian equities represent less than
1% of the total market capitalization in the world as compared to US equities
which represent around 50% of world equities. There are mainly three broad
approaches that are employed for the selection of equity shares: technical analysis, fundamental analysis and
random selection.
Technical analysis looks at price
behavior and volume data to determine whether the share will move up or down or
remain trendless. Fundamental analysis focuses on fundamental factors like the
earnings level, growth prospects, and risk exposure to establish the intrinsic
value of a share. The random selection approach is based on the premise that
the market is efficient and securities are properly priced.
Because the research work has been
restricted to stocks only so it would be better to have a look at the SWOT
analysis of the Equity shares.
3. SWOT ANALYSIS
OF THE EQUITY SHARES
3.1.
Strengths:
·
Long term growth by capital
appreciation
·
High rate of return
·
Has aggressive growth
·
Provides ownership of the
company in which you made an investment
·
Sharing of profit that the
company made with the share holders
·
Suitable for risk seekers
3.2.
Weakness:
·
Very risky
·
Volatile rate of return
·
No security of investment
·
Security transaction tax
needs to be paid every time you transact
·
Suitable for risk seekers
·
Dividends received on
shares depend on profit made by the company. If company makes no profit then no
dividend will be given
·
Share prices are subject to
market
3.3.
Opportunity:
·
It gives the shareholders
the right to vote in the company’s decisions
·
The market may be volatile
due to many factors, but returns are generated by equity shares that have
earning potential i.e. P/E ratio. Therefore any investor investing in equity in
a systematic manner over a long period of time can easily expect double digit
return
·
Returns generated by equity
investment outperform all other investment avenues in the long run
3.4.
Threats:
·
Investors prefer fixed
income securities when markets are down
·
There is more security in
other investment avenues with stable rate of returns
3.5.
Decisions to be Made
Every investor that invests its
money in the market has to make three main decisions. First is the capital
allocation decision, it the choice of the proportion the overall portfolio to
place in safe but low-return money market securities versus risky but higher-return
securities like stocks. Second decision that an investor has to take is the
asset allocation decision, which describes the distribution of risky
investments across broad asset classes like stocks, bonds, real estate, etc.
Finally the investor has to take the security selection decision, which
describes the choice of which particular securities to hold within each asset
class.
While making the asset allocation
decision, we will not look into all the asset classes and keep our research
narrow to stock market. Selecting appropriate investment vehicles for an
investor’s needs must bear in mind the ‘horses for courses’ system so well
accepted by horse race followers: a horse that consistently performs well on
one course may consistently underperform on the other race courses.
3.6.
Optimal Risky Portfolio via
Diversification
First the optimal risky portfolio
will be constructed and then it will be seen that how diversification can
reduce the variability/risk of portfolio returns. The work will be started by
making capital allocation decision that excludes the risk-free assets. Then the
choice will be given to the investor to choose between risk-free assets and the
optimal portfolio of risky assets.
Intuitively smart investors knew the
benefit of diversification which is reflected in the traditional adage “Do not
put all your eggs in one basket”.
Empirical studies suggested that the
bulk of the benefit of diversification, in the form of risk reduction, is
achieved by forming a portfolio of about ten securities.
Different portfolios have been
designed for different investors keeping their risk appetite in mind like:
·
Aggressive Portfolio: The type of portfolio constructed for an
aggressive investor would include majority of those stocks that have high risk
and which will give high return.
·
Moderate Portfolio:
This type of portfolio would see a striking balance of equities in terms of
risk and return.
·
Defensive Portfolio: This type of portfolio will have a stable
return with minimum risk involved with most of the investments in less risky
securities.
·
Ideal portfolio has been designed as per our analysis where the
investors can have high returns for the same amount of risk.
3.7.
Relevant Literature
Reviewed
·
Harry Markowitz (1952), in his
research paper “Portfolio Selection“found that the returns from securities are
too inter correlated and diversification cannot eliminate all variance. The
portfolio with maximum expected return is not necessarily the one with minimum
variance.
·
Charles Bram Cadsby (1986), in
his research paper “Performance Hypothesis Testing with the Sharpe and Treynor
Measure “propose that asymptotic test statistics are designed to determine
whether apparent differences in portfolio performance are statistically
significant. Such statistics are potentially useful in that they provide a
rigorous method of differentiating between consistently superior or inferior
performance and the luck of the draw.
·
Pitabas Mohanty (2006), in his
research paper “A Dynamic Spreadsheet Model for Determining the Portfolio
Frontier “found that the tangent portfolio has the maximum slope (Sharpe
Index), one can directly obtain the portfolio weights of the tangent portfolio
by maximizing the slope of the line joining the portfolio frontier and the risk
free rate of return.
·
Valeriy Zakamulin (2011), in
his research paper “Sharpe (Ratio) Thinking about the Investment opportunity
Set and CAPM Relationship“ found that the changes in the characteristics of
individual risky assets that preserve the Sharpe ratios and the correlation
matrix do not change the investment opportunity set (CML).
·
Myles E. Mangram (2013), in
his research paper “A Simplified Perspective of the Markowitz Portfolio Theory“
found the impact on portfolio diversification by the number of securities, he
found that diversification cannot eliminate all risk i.e. it cannot eliminate
systematic risk but unsystematic risk can be eliminated to a large extent by
diversification.
4. OBJECTIVES
The commonly stated investment goals are as follows:-
·
To develop an efficient frontier
based on BSE30 stocks for last 5 years data.
·
To find the returns that
various securities would give and risk associated with them.
·
To allow a user to select a
set of securities into his portfolio and look at the change in portfolio risk
and portfolio return.
·
To find out the weightages of
securities which are there in the portfolio in order to invest in those
securities.
·
To construct Capital Market
Line (CML) in order to get more returns than that of efficient frontier if
risk-free securities are included in the portfolio.
·
To create different portfolios
for different types of investors based on their risk tolerance levels.
·
To give recommendations to
prospective investors about investment in mutual funds or portfolio of stocks.
5. ASSUMPTIONS
To achieve the above objectives few
assumptions are made about the market like:
a) Investors
are rational (they seek to maximize returns while minimizing risk),
b) Investors
are only willing to accept higher amounts of risk if they are compensated by
higher expected returns,
c) Investors
timely receive all pertinent information related to their investment decision,
d) Investors
can borrow or lend an unlimited amount of capital at a risk free rate of
interest,
e) Markets
are perfectly efficient,
f) Markets
do not include transaction costs or taxes,
g) It
is possible to select securities whose individual performance is independent of
other portfolio investments.
6. DATA SOURCE
& METHODOLOGY
Data has been collected mainly from
the secondary sources like
·
From various books and other
journals
·
From internet
The purpose is to predict future
performance of stocks based on the past data and accordingly inform prospective
investors. The data collected for the purpose of analysis was secondary in
nature and is taken from relevant websites like BSE and NSE.
For the purpose of construction of
portfolio, we have analyzed the BSE 30 stocks that are currently being traded
on Bombay Stock Exchange.
The methodology included the
detailed analysis of BSE 30 stocks. It was as follows:
·
Finding out the risk and
return associated with each stock.
·
Calculating the portfolio risk
and portfolio return for the purpose of creating a portfolio.
·
Following the portfolio
theories specifically Markowitz Theory for the construction of portfolio.
·
Selecting the most efficient
portfolio.
·
Evaluating the performance of
portfolio.
7. ANALYSIS OF
DATA
Since in depth technical as well as
fundamental analysis has not been done, the main focus of the paper is the
construction of portfolio of the stocks that the investor has selected based on
its perception about the market.
Till now we have talked about the
historical data that has been used for future prediction of market. But the
question is how many years of past data should be used for regression 5, 10,
20, or 50 years? And which data to take: yearly, monthly, weekly or daily?
Many researchers feel that data
taken should be as long as possible. Some others feel that it should not be too
long as market conditions change very frequently. And whether to take yearly,
monthly or daily data, well every option has its own pros and cons. For the
purpose of research daily data of past 5 years has been taken and an option has
been given to investor to decide how many years of data it wants to analyze.
7.1.
Risk and Return Measurement
After the
collection of data the next important thing was to look at the return the stock
has given in the past as well as the volatility of the stock. To measure the
return of the stocks the arithmetic average daily return of the stock has been
calculated. The volatility or the risk associated with the stock is measured
with the help of standard deviation of its daily returns.
7.2.
Construction of portfolio
Portfolio risk and portfolio return
has been calculated for the construction of portfolio and the portfolio frontier
has been drawn which comprised of both the efficient frontier as well as
non-efficient frontier. By looking at the efficient frontier investor would
decide how much risk it is willing to take to get a return based on its degree
of risk aversion. “Higher the risk an investor will take greater the returns he
will get”.
Then the risk-free security has been
included into the risky portfolio that we have constructed and the change in
optimal portfolio from efficient frontier to capital market line was observed.
7.3.
Portfolio Performance
Evaluation
It was found out from the previous
studies that the average portfolio return was not a straightforward measure to
evaluate the portfolio performance. There were some risk-adjusted performance
measures like Sharpe’s measure, Treynor’s measure, Jensen’s measure,
information ratio, M2 measure, etc. The performance of the portfolio has been
measured by using the Sharpe ratio as it takes into account the total risk of a
portfolio instead of only systematic risk.
From the above formula it can be
seen that Sharpe Index reflects the excess return earned on a portfolio per
unit of its total risk (standard deviation).
One can try various combinations of
securities to find the portfolio which will give the maximum return to the
investors at the minimum possible risk.
Seven different portfolios with
different years of data with all the BSE 30 securities selected into the
portfolio have been constructed.
Table 1: Market
return, risk and Sharpe Index for past 5 years is shown in the table below
PORTFOLIOS |
MARKET RETURN |
MARKET RISK |
SHARPE INDEX |
Portfolio with a data of
last 1 year |
1.852 |
5.172 |
0.351 |
Portfolio with a data of
last 2 year |
0.683 |
2.784 |
0.233 |
Portfolio with a data of
last 3 year |
3.715 |
19.907 |
0.184 |
Portfolio with a data of
last 4 year |
1.208 |
7.638 |
0.153 |
Portfolio with a data of
last 5 year |
0.602 |
4.872 |
0.116 |
(Market
Risk, Market Return)
Different portfolios are created by
taking last 1 year data and by taking some of the BSE30 stocks into the
portfolio. The weightages of different stocks at the point where ‘CML’ touches
the graph of ‘Selected Portfolio’ are shown in the tables
PORTFOLIO
1:
BHEL |
-59.2% |
ICICI Bank |
107.2% |
Tata Steel |
-207.7% |
Bharti Airtel |
8.0% |
Infosys |
15.5% |
SBI |
-8.9% |
Cipla Ltd |
65.1% |
Jaiprakash Associates |
17.0% |
Tata Motors |
-9.1% |
Hindalco Industries |
-25.5% |
Mahindra & Mahindra |
89.0% |
Reliance Industries |
23.0% |
HUL |
83.2% |
L & T |
91.9% |
Wipro |
-37.1% |
HDFC |
53.6% |
NTPC |
-53.7% |
Coal India |
-52.3% |
PORTFOLIO
2:
BHEL |
-63.43% |
Infosys |
19.55% |
SBI |
11.46% |
Bharti Airtel |
13.00% |
Jaiprakash Associates |
23.58% |
Tata Motors |
03.62% |
Cipla Ltd |
69.46% |
L & T |
124.99% |
Tata Steel |
-213.24% |
Hindalco Industries |
-25.39% |
Mahindra & Mahindra |
112.91% |
Wipro |
-45.90% |
HUL |
91.36% |
NTPC |
-60.41% |
Coal India |
-67.50% |
HDFC |
76.45% |
Reliance Industries |
29.46% |
|
|
PORTFOLIO
3:
BHEL |
-59.15% |
ICICI Bank |
109.03% |
SBI |
-10.39% |
Bharti Airtel |
07.49% |
Jaiprakash Associates |
16.12% |
Tata Motors |
-08.88% |
Cipla Ltd |
65.07% |
L & T |
95.23% |
Tata Steel |
-207.14% |
Hindalco Industries |
-26.80% |
Mahindra & Mahindra |
89.28% |
Wipro |
-27.11% |
HUL |
82.21% |
NTPC |
-51.69% |
Coal India |
-49.76% |
HDFC |
54.04% |
Reliance Industries |
22.45% |
|
|
PORTFOLIO
4:
Bharti Airtel |
-2.66% |
ICICI Bank |
67.43% |
SBI |
-3.42% |
Cipla Ltd |
76.06% |
Infosys |
2.29% |
Sterlite Industries |
-17.63% |
DLF |
48.76% |
ITC |
92.27% |
TCS |
24.12% |
Hero Motocorp |
-167.21% |
L & T |
45.46% |
Tata Motors |
-28.51% |
Hindalco Industries |
-104.49% |
Mahindra & Mahindra |
110.73% |
Tata Power |
-69.78% |
HUL |
41.23% |
NTPC |
-87.08% |
Wipro |
-48.05% |
HDFC |
27.97% |
ONGC |
14.04% |
Bajaj Auto |
78.46% |
PORTFOLIO
5:
BHEL |
-69.84% |
ICICI Bank |
43.79% |
NTPC |
-83.28% |
Bharti Airtel |
-15.47% |
Infosys |
-17.10% |
Reliance Industries |
-6.81% |
Cipla Ltd |
70.62% |
ITC |
143.29% |
SBI |
-33.55% |
DLF |
52.22% |
L & T |
70.61% |
Tata Power |
-109.06% |
Hero Motocorp |
-198.94% |
Mahindra & Mahindra |
136.56% |
|
|
HDFC |
56.48% |
Maruti Suzuki |
60.49% |
|
|
PORTFOLIO
6:
Cipla Ltd |
95.12% |
Jaiprakash Associates |
-02.85% |
Reliance Industries |
15.73% |
Hero Motocorp |
-238.51% |
L & T |
82.15% |
SBI |
-39.13% |
ICICI Bank |
66.84% |
Mahindra & Mahindra |
136.16% |
Tata Power |
-127.91% |
Infosys |
-16.83% |
Maruti Suzuki |
66.13% |
|
|
ITC |
158.96% |
NTPC |
-95.87% |
|
|
PORTFOLIO
7:
Bharti Airtel |
13.31% |
Jindal Steel & Power |
-108.02% |
ONGC |
37.73% |
Cipla Ltd |
83.92% |
L & T |
83.61% |
Reliance Industries |
17.29% |
DLF |
49.49% |
Mahindra & Mahindra |
152.96% |
Tata Motors |
-16.57% |
Hero Motocorp |
-154.63% |
Maruti Suzuki |
64.24% |
Bajaj Auto |
55.74% |
Hindalco Industries |
-98.93% |
NTPC |
-80.16% |
|
|
Some stocks have negative weightage
which because these stocks have given negative returns in the past hence it is
better to short sell these stocks then to hold them in the portfolio.
7.4.
Portfolio Analysis:
Table
2 shows the consolidated statement of all seven Portfolios and classify these
portfolios for different types of investors:-
Table
2: consolidated statement of all seven Portfolios
PORTFOLIO |
PORTFOLIO RISK |
PORTFOLIO RETURN |
SHARPE INDEX |
SYMBOLS |
Portfolio 1 |
31.165336 |
7.777 |
0.2484982 |
Δ |
Portfolio 2 |
10.89088 |
2.613 |
0.2369585 |
β |
Portfolio 3 |
54.344176 |
13.524 |
0.2482718 |
Δ |
Portfolio 4 |
4.790723 |
1.274537 |
0.2593093 |
α |
Portfolio 5 |
15.31653 |
3.573 |
0.2311697 |
γ |
Portfolio 6 |
18.255772 |
3.986 |
0.2165773 |
Ө |
Portfolio 7 |
14.69419 |
3.721509 |
0.2510687 |
γ |
α Alpha (lowest risk), preferred by defensive
investors
β Beta
(little to moderate risk), preferred by risk
averse investors
γ Gamma
(moderate risk), preferred by moderate
risk taker investors
Ө Theta
(moderate to high risk), preferred by risk
seeker investors
Δ Delta
(highest risk), preferred by aggressive investors
(Portfolio Risk, Portfolio Return)
·
As seen in the table the risk
and reward logic is applicable, more the risk a person takes more the return he
will get.
·
If Portfolio 5 and Portfolio 7
are observed very carefully it would be realized that Portfolio 5 is an
inefficient portfolio because in portfolio 7 one can have more returns with
less risk as compared to Portfolio 5. So it could be said that a person
investing in Portfolio 5 will be a fool.
·
Other portfolios can also be
inefficient if one can construct another portfolio of different securities
which will give same or more returns with same or less risk. Because of time
constraint all possible permutations and combinations have not been tried.
8. LIMITATIONS OF
THE STUDY
·
Before beginning the research
it was assumed that markets are perfectly efficient, but in actual they are
not.
·
Investors cannot borrow or
lend an unlimited amount of capital at a risk free rate of interest.
·
The detailed analysis of
stocks has been done only for BSE 30 stocks.
·
Not much of technical and
fundamental analysis has been done; main focus was on creating the portfolio of
selected securities.
·
The study was confined only to
the past 5 year’s data i.e. from May 2008 to April 2013.
·
Short selling is not allowed
in India but it has not been taken into consideration.
·
Transaction cost, brokerage
fees and other overheads have been ignored.
In
today’s globalized world, where trade is interconnected, the performance of
stocks in US and Europe also has an impact on the stock performances around the
world, including India. Such eventualities cannot be predicted in advance.
9. FINDINGS
·
If the portfolio return and
risk for the last five years are observed very carefully it will be found that
the amount of risk one has to take to get the same amount of return is
increasing as more data is being taken into consideration.
·
It is found that for 1 year
data one has to take 3 times risk to get a return whereas for a time period of
2 years one has to take 4 times risk to get the same amount of returns and for
3 years data one has to take around 5.4 times risk and for 4 years’ time
duration one has to take around 6.3 times risk and if the data will be taken
for last 5 years it will be observed that one has to take around 8 times risk
to get the same amount of return.
·
First and the foremost
important thing that one should always keep in mind before investing in the
market is that never invest in the inefficient portfolio and always invest in
the efficient portfolio. It will be more
clear from the graph shown below.
·
It is noticed that if the risk
free lending and borrowing rates are equal, the optimum risky portfolio is
obtained by drawing a tangent to the portfolio frontier from the risk free rate
of return. Since the tangent portfolio has the maximum slope (Sharpe Index),
one can directly obtain the portfolio weights of the tangent portfolio by
maximizing the slope of the line joining a portfolio in the portfolio frontier
and the risk free rate of return.
·
Another
most important learning from the project is that an investor will always invest
at point A if he wants to increase his returns more than the returns of
efficient frontier by investing some of the money in the risk free security.
It
was realized that the changes in the characteristics of individual risky assets
that preserve the Sharpe ratios and the correlation matrix do not change the
investment opportunity set (CML).
10. CONCLUSION
It is
observed that Point A represents the portfolio of stocks that the investor will
hold even if he invests some amount of money in risk free security. An investor
irrespective of his risk tolerance would never choose any other point on the
efficient frontier except A.
If the investor has a fair degree of risk aversion he
might choose a point between Rf and A and invest some of its money in risk-free
security and rest in stocks as per the weightage at point A.
And if the investor is less risk averse then he might
choose a point closer to A or even beyond A. In this case investor will borrow
some money at the risk-free rate and invest in stocks as per the weightage at
point A.
The benefit of doing this is that an investor will get
more returns for the same amount of risk taken if he invests at any point on
CML rather than investing at any point on efficient frontier because CML lies
above the efficient frontier.
REFERENCES
BODIE,
Z.; MARCUS, A. J.; KANE, A. (1995). Investments.
Mc-Graw Hill International Edition.
CADSBY,
C. B. (1986). Performance Hypothesis Testing with the Sharpe and Treynor
Measures. The Journal of Finance. v.
41, n. 5, p. 1175-1176.
COWDELL,
J.; BILLINGS, M. (2001). Investment
Management. Financial World Publishing.
KAPLAN,
P. D.; SAVAGE, S. (2009). The New
Efficient Frontier Asset Allocation for the 21st Century. Morningstar
Direct.
MANGRAM,
M. E. (2013). A Simplified Perspective of the Markowitz Portfolio Theory. Global Journal of Business Research. v.
7, n. 1, p. 59-70.
MARKOWITZ,
H. (1952). Portfolio Selection. The
Journal of Finance. v. 7, n. 1, p. 77-91.
Mohanty,
P. (2009). A Dynamic Spreadsheet Model
for Determining the Portfolio Frontier.
REILY,
F. K.; BROWN, K. C. (2005). Investment
analysis and Portfolio Management. Cengage Learning.
ROSS,
S. A.; WESTERFIELD, R. W.; & JAFFE,
J. (2009). Corporate Finance.
Mc-Graw Hill.
ZAKAMULIN,
V. (2011). Sharpe (Ratio) Thinking about the Investment opportunity Set and
CAPM Relationship. Economics Research
International.