Markovitz efficient portfolio
You're considering to invest in two or more assets and the historical data reveal that the return from each asset has fluctuated over time. You want to reduce variability, or risk, by spreading your investment over the these assests (diversification) and in our case we are going to choose stocks.
From the historical data, we calculate an expected return (average return), the variance of the rate of return, and the covariance of the return between the different assets. Variance is a measure of the fluctuation in the return - the higher the variance, the riskier the investment. The covariance is a measure of the correlation of return fluctuations of one stock with the fluctuations of another. High covariance indicates that an increase in one stock's return is likely to correspond to an increase in the other. A low covariance means the return rates are relatively independent and a negative covariance means that an increase in one stock's return is likely to correspond to a decrease in the other.
Ok! now how does this help me - So where can we use this Markovitz efficient portfolio theroy?
Say you have a target return of 15%. What percentages of your funds should you invest in each of the assets to achieve this target and minimise the variance (or risk) of the portfolio? As an additional safety feature, you decide to invest no more than 75% in any single asset.The objective is to determine the percent to invest in each asset while minimising risk of the entire portfolio. -- this is what I am going to explore, have target returns and then choose how much each asset should be purchased to get that return.
The idea is, to minimize risk and achieve the target return, OR have a target risk and then get the maximum return.
How can we minimize risk - hah if you have met many mutul fund agents - then u got the answer, its diversification. Diversification till a point is good, after which it is over done.
20 stocks is good diversification, 30+ will be over diversification.
Now that we know what this theroy is all about, lets look at the Maths and Excel.
Lets begin with a 2 stock portfolio and then move to a multi stock portfolio.
Formula to calculate the returns for the portfolio (2 stocks) Rp = W1 * r1 + W2 * r2
Rp = portfolio return. W1, W2 = weights of the stocks in the portfolio. r1 and r2 is the return of the stocks.
Variance of a portfolio is given by
sigma (p) = is the standard deviation (SD) of portfolio.
W1 and W2 = the weights of the stocks.
sigma(1) = is the SD of stock 1 and sigma (2) is the SD of stock 2
Sigma (12) = is the covariance of stock 1 and stock 2
How to get the raw data
which is to get the daily prices of the stocks which we are prensent in our portfolio. We can use nse or the bse website. (http://www.nseindia.com/)
In the NSE website - go to the equity tab, Market information, Historical data - then security wise price and volume data. Then give in the company - My choice is Reliance communication ( i own this stock ) and the next stock will be CRISIL ( I want a job here) , wanted to choose Radha Madhav (but this is not listed on NSE)
I picked the date range from 1 Jan 2007 to 10 Aug 2007. Once the results are out, scroll down and then click on the "download file in csv format" - copy the data and paste them in a excel sheet. When you paste, it should allow you to choose to split the columns using a comma separator.
So our stocks - RCOM and CRISIL - get the data, paste them in the excel sheet, RCOM in sheet2 and CRISIL in sheet3
Phew this was only ground work.
Now we need to calculate the daily returns or weekly returns, in this example I have taken daily returns
How to calculate daily or weekly or monthly returns
Return = 100 * LN (Price at time t / Price at time t - 1) OR you can use (end - begin / end) - the general formula to calculate percentage increase or decrease.
LN is the excel function for log to the base e
Price at time t = for daily return will be price today, for weekly will be this weeks price - Friday closing
Price at time t-1 = for daily return will be yesterdays price, for weekly will be last weeks price.
This is shown in the excel workbook - Column L. Will be uploading the workbook soon.
The next steps would be to take the avg of returns, std dev and then carry on.
Right now i got to sleep. My lazy ass did do some work.
Thanks Ms Sharma, atleast ur comment made me work :D
