Mohit: - It is very easy to calculate Beta by using SAS. You just need to extract the data of the returns of a security and the returns of the market. Then, it will automatically calculate the Beta, without any headache at all.
Sanjana: - What is Beta?
Arindam: - Beta is just a mathematical notation, which gives the result of the covariance of a security in respect to the market divided by the variance of the market. If the value of Beta is equal to or more than 1, then it is a very aggressive security, and if it is less that 1, then it is not so risky. But, if there is no risk, there is no gain at all.
Pratibha: - But all of us don’t know SAS like Mohit. So, it is not so easy for us to calculate the Beta value. We can’t just be dependent on ‘PROWESS’ or ‘BLOOMBERG’ database to know the exact Beta value of a security.
Mohit: - No, I think that you people can also calculate the Beta value by using MS-Excel. Just take the data of any security and the Nifty for say 180 days. Now, do the entries in the excel sheet.
Sanjana: - babah, the data of 180 days ?
Arindam: - Yeah, of course. It is all about trend analysis. If you don’t take a data for six months, your predicted calculation has no value at all. But, yes, in MS-Excel, it takes some time to calculate Beta. It is very easy to calculate it on SAS 9.1.3. Now, Mohit, after entering those data in the excel sheet, you are going to find out the daily returns right?
Mohit: - Hmm…you are right. Just deduct the previous day closing price from the current price and find out the percentage of return. In an excel sheet, you just need to write its formula for once in a row and then drag it to the bottom. Every respective row will automatically calculate the percentage of daily returns. So, we have the daily returns of a security, denoted by Ri and of the Nifty, denoted by Rm.
Pratibha: - Now we need to find out the mean value of Ri and Rm respectively. Say, the means are R and M respectively. So, the summation of the product of (Ri-R) and (Rm-M) divided by 179 will give us the covariance of the security with respect to Nifty. It is denoted by COV(Ri, Rm).
Sanjana: - Wait, just hang on. Why have you divided that by 179?
Pratibha: - See, in the excel sheet, when you are calculating the daily market returns and the security returns, the first row remains vacant. It starts from the second row only. For example, if your previous closing value and current closing value are in A2 and B2 respectively, then you will get the result of their returns in the C3 row, but not in the C2 row. That is why, we are dividing the summation of product (Ri-R)*(Rm-M) by N-1, that is 180-1=179.
Arindam: - We will also divide the summation of (Rm-M)2 by N-1, that is by 179.
Sanjana: - Why we are calculating the summation of (Rm-M)2 ?
Mohit: - Very simple. We are just finding out the variance of the Nifty for those 179 days, denoted by Sm2.
Arindam: - Just divide COV(Ri, Rm) by Sm2, you will get the value of Beta. So, you don’t need to depend on any database or any broker to tell you about the Beta value of a security. You can calculate it on our own, before investing money on it. It is not that an investor always abides by the Beta value of a security before investing money on a security. He also considers the P/E ratio and the last quarter balance sheets also.
Mohit: - But, still as an investor, you can just have a rough idea about the nature of the security by calculating the Beta value. Then, you can apply this Beta value in CAPM model also to find out your expected returns.
Sanjana: - Wow, I was not aware that before investing money on a security, you need to do these kinds of calculations. My father and elder brother are brokers of Stock Exchange. Since childhood, I believed that investing on share market is just like gambling, but it is not so.
Pratibha: - Finance is a subject of probabilistic economics with a blend of socio-politics only. Anyway, today, we have learned how to “CALCULATE BETA VALUE USING MS-EXCEL.” Thanks to Arindam and Mohit to teach us about that.
Sanjana: - What is Beta?
Arindam: - Beta is just a mathematical notation, which gives the result of the covariance of a security in respect to the market divided by the variance of the market. If the value of Beta is equal to or more than 1, then it is a very aggressive security, and if it is less that 1, then it is not so risky. But, if there is no risk, there is no gain at all.
Pratibha: - But all of us don’t know SAS like Mohit. So, it is not so easy for us to calculate the Beta value. We can’t just be dependent on ‘PROWESS’ or ‘BLOOMBERG’ database to know the exact Beta value of a security.
Mohit: - No, I think that you people can also calculate the Beta value by using MS-Excel. Just take the data of any security and the Nifty for say 180 days. Now, do the entries in the excel sheet.
Sanjana: - babah, the data of 180 days ?
Arindam: - Yeah, of course. It is all about trend analysis. If you don’t take a data for six months, your predicted calculation has no value at all. But, yes, in MS-Excel, it takes some time to calculate Beta. It is very easy to calculate it on SAS 9.1.3. Now, Mohit, after entering those data in the excel sheet, you are going to find out the daily returns right?
Mohit: - Hmm…you are right. Just deduct the previous day closing price from the current price and find out the percentage of return. In an excel sheet, you just need to write its formula for once in a row and then drag it to the bottom. Every respective row will automatically calculate the percentage of daily returns. So, we have the daily returns of a security, denoted by Ri and of the Nifty, denoted by Rm.
Pratibha: - Now we need to find out the mean value of Ri and Rm respectively. Say, the means are R and M respectively. So, the summation of the product of (Ri-R) and (Rm-M) divided by 179 will give us the covariance of the security with respect to Nifty. It is denoted by COV(Ri, Rm).
Sanjana: - Wait, just hang on. Why have you divided that by 179?
Pratibha: - See, in the excel sheet, when you are calculating the daily market returns and the security returns, the first row remains vacant. It starts from the second row only. For example, if your previous closing value and current closing value are in A2 and B2 respectively, then you will get the result of their returns in the C3 row, but not in the C2 row. That is why, we are dividing the summation of product (Ri-R)*(Rm-M) by N-1, that is 180-1=179.
Arindam: - We will also divide the summation of (Rm-M)2 by N-1, that is by 179.
Sanjana: - Why we are calculating the summation of (Rm-M)2 ?
Mohit: - Very simple. We are just finding out the variance of the Nifty for those 179 days, denoted by Sm2.
Arindam: - Just divide COV(Ri, Rm) by Sm2, you will get the value of Beta. So, you don’t need to depend on any database or any broker to tell you about the Beta value of a security. You can calculate it on our own, before investing money on it. It is not that an investor always abides by the Beta value of a security before investing money on a security. He also considers the P/E ratio and the last quarter balance sheets also.
Mohit: - But, still as an investor, you can just have a rough idea about the nature of the security by calculating the Beta value. Then, you can apply this Beta value in CAPM model also to find out your expected returns.
Sanjana: - Wow, I was not aware that before investing money on a security, you need to do these kinds of calculations. My father and elder brother are brokers of Stock Exchange. Since childhood, I believed that investing on share market is just like gambling, but it is not so.
Pratibha: - Finance is a subject of probabilistic economics with a blend of socio-politics only. Anyway, today, we have learned how to “CALCULATE BETA VALUE USING MS-EXCEL.” Thanks to Arindam and Mohit to teach us about that.
