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.