00:00In today's video, I will tell you about investment risk management, what is risk, how to know
00:10monthly risk or standard deviation of a stock, how to know monthly return or standard deviation,
00:18how to calculate beta, then I will tell you about trainer ratio, how to calculate it,
00:27then I will tell you about value at risk calculation and in the end I will do its graph and conclusion.
00:36So first of all, risk. Risk means how much risk is there in the money we are investing,
00:46that the purpose for which we are investing, whether that purpose will be achieved or not.
00:52For example, we have invested Rs. 100,000 in the bank and the bank says that after one year you will get Rs. 1,10,000.
01:01So we want to check whether we will get Rs. 1,10,000, it can be more or less.
01:10For example, we had invested for Rs. 1,10,000 but we get Rs. 1,05,000.
01:17So we have a risk of Rs. 5,000.
01:20Whatever we want, how much difference will it make, this is the risk.
01:26So let's go to the asset sheet for its calculation.
01:34I have already downloaded the data, I have data of 5 stocks.
01:39And this data is of Facebook, Amazon, Apple, Netflix and Google.
01:46And this data has been taken on a daily basis for the whole year 2014.
01:52And with this, we have taken the data of SBX Standard & Poor's as a benchmark.
02:00So first of all, we will calculate the daily return.
02:05For that, I select all of them and paste it here.
02:13After this, the return is obtained from the second day.
02:18The reason for this is that we have to compare with the first day.
02:22We will not get the return on the first day.
02:24So we will take the return of the second day.
02:27So we will put its formula as equal to.
02:32And today's return means today's return.
02:37We will divide it by yesterday's return and make it minus 1.
02:43So we got the return of Facebook.
02:48I will drag it, so we will get the return of all the stocks.
02:53And then I will double click it, so all the returns will come down completely.
02:59From here, we delete the last one.
03:03So we have all the returns.
03:07I will select all of them.
03:17After selecting, we will change it in percentage.
03:20And after the point in percentage, we will keep two values.
03:26So this is our daily return.
03:30After this, we will go to the calculations.
03:35In which we had monthly return and standard deviation.
03:40So we come to this task.
03:42So we will calculate the monthly return.
03:47For that, we will put the formula of the average.
03:52After this, we will go to the data.
03:56I will select all the data of Facebook and enter it.
04:00So this is our daily return.
04:04If we want to change it in monthly, then we will add a little in its formula.
04:08We will do plus 1.
04:11I will put brackets here.
04:13After this, I will take power.
04:1621 minus 1
04:21So this 21 is our working day in a month.
04:26If I enter it, then we have the monthly return.
04:30I will drag it.
04:34So we have the monthly return of all the stocks.
04:38After this, we will calculate the standard deviation.
04:42For standard deviation, we will put the formula of standard deviation.
04:48After this, we will select all the data of Facebook and enter it.
04:56So this is our daily return.
05:02If we want to change it in monthly, then we will multiply it.
05:07We will take the square root of 21.
05:12So this is our standard deviation on a monthly basis.
05:16If we drag it, then we have the monthly standard deviation.
05:21Now let's calculate the monthly return of the portfolio.
05:25If we do an equal weighted portfolio, then we have 5 stocks.
05:29So we will get 20-20% in all.
05:32So we will multiply it with 0.20.
05:42Plus we will do 0.20.
05:46And multiply it with the second stock.
05:49Plus we will do 0.20.
05:53And multiply it with the third stock.
05:57Plus again we will take 0.20.
06:01This is the weight of each stock.
06:04We will multiply it with the return of Netflix.
06:08Plus we will do 0.20.
06:17And multiply it with Google stock.
06:21So this is the return of all the stocks.
06:25And this is the return of the portfolio.
06:28Let's drag it.
06:30So this is the return of the portfolio.
06:33So this is the standard deviation of the point stock.
06:37So this is not a simple calculation.
06:40We will see it later.
06:42I am giving it blank.
06:44Now we have to calculate the beta.
06:48To calculate the beta, we have 2-3 ways.
06:56I will put the formula for beta here.
06:59I will put the formula for covariance.
07:01And the formula for NKVAR.
07:03For example, we will have this.
07:09I am putting the formula for beta.
07:11I will put the formula for covariance.
07:18We will multiply the stock.
07:22We will take the covar of the stock.
07:25SPX.
07:27And then we will divide it.
07:29We will take the bar of SPX.
07:34So we will get the beta.
07:38I am putting the formula for it.
07:41We will take the covar.
07:47And we will take the stock of Facebook.
07:56We will take the stock of Facebook.
07:59And we will compare it with the returns of Markit S&P.
08:09After selecting it, we will select it.
08:12And we will do CTRL F4.
08:14Because it is fixed for all the stocks.
08:16After that we will divide it.
08:19We will take the bar with the bar.
08:23And then the S&P.
08:25Markit is after it.
08:28So this will come.
08:31And this will also be fixed.
08:33So I fix it with F4.
08:36So it will not change anywhere.
08:39Sorry.
08:45So we got this.
08:47We corrected the formula automatically.
08:49We got the beta of Facebook.
08:53We will drag it.
08:57You can check it.
08:59We have all the stocks.
09:01Because we don't have the data of it.
09:03So I will skip it.
09:06So we have all the beta of all the stocks.
09:11If you want to see the beta,
09:15Facebook is less aggressive than Markit.
09:17If there is a 100% change in Markit,
09:20then there will be a 87% change in it.
09:24Similarly, Netflix's beta is aggressive.
09:27If there is a 1% change in Markit,
09:30then there will be a 1.10% change in it.
09:34And Amazon is almost similar to Markit.
09:38So we have the beta of Amazon and all the other stocks.
09:46After this, we will write the trainer ratio.
09:50So the formula for trainer ratio is
09:53trainer ratio
10:03I will write the formula so that it is not difficult for you to understand.
10:07So it will be
10:09monthly return
10:11minus
10:13risk free rate
10:17We will minus this.
10:21And we will divide it with beta.
10:25So we use beta in this.
10:29In the Sharpe ratio, we use standard deviation.
10:33But we use beta in this.
10:35So with beta, we get
10:37the return on the basis of the stock movement with Markit.
10:43And our calculations come.
10:47And it gets closer to the actual return.
10:50So now the risk free rate of 10 years
10:56is 0.19% monthly.
11:10So this 10 years is the risk free rate.
11:13Now we calculate the trainer ratio.
11:16So the monthly return is
11:20minus the risk free rate.
11:24And the risk free rate is the same in all.
11:28So we will fix it.
11:32And then we will divide it with beta.
11:39So this is our ratio.
11:44This is our trainer ratio.
11:48This is the trainer ratio of all stocks.
11:52So this is the trainer ratio of all stocks.
11:56And we can see that the trainer ratio of Google is the lowest.
12:00This means that the value of risk in this
12:03is the lowest.
12:05The other stocks have a high chance of variation.
12:09Now we calculate the VAR.
12:11So let me explain it to you first.
12:14If the return of the stock is a normal distribution.
12:18If the distribution is normal.
12:20So in such a situation,
12:22we have a bell-shaped curve.
12:26And the average return comes here.
12:30The average of all returns will come here.
12:33And this will be written in three parts.
12:35That we will minus the strength deviation once from the return.
12:38Minus twice and minus three times.
12:41So if we minus once,
12:43that is, if we minus the strength deviation from the return,
12:46or if we minus the strength deviation from the return,
12:50then our 68% falls in it.
12:53That is, there is a 68% chance that our return will be between these two values.
12:59That is, by minusing the strength deviation once.
13:03And if we minus the strength deviation twice,
13:07that is, by multiplying by 2 and minusing from the return,
13:12then our 95.45% will fall between these two values.
13:18That is, our return will come between these two blue values.
13:23And if we minus it three times,
13:26that is, if we minus our return three times,
13:30three multiplied by the strength deviation,
13:32then our return will fall by 99.73%.
13:36That is, our return will fall between these two values.
13:39That is, whatever return will come, whatever value will come,
13:42it will fall between them.
13:44So now we do calculations,
13:46then you will understand more easily.
13:48We go to the second sheet,
13:50in this portfolio sheet.
13:52So in this, we calculate it.
13:55More at 95%.
13:58So what we have to do is,
14:00this return is our monthly return.
14:03We have to minus 2 multiplied by the strength deviation.
14:07That is, we will minus it twice.
14:09So we have 2 times return.
14:13That is, in our return,
14:16this much percent variation can come.
14:19And if we,
14:21it can be in both plus and minus.
14:24So this much percent variation can come.
14:27And if we take it to 99%,
14:30so what we will do for that,
14:32we will take return,
14:34and from this we will minus
14:403 times the strength deviation.
14:44And we will drag it.
14:47So this will come to us.
14:49That is, the variations in our return can be like this.
14:54Now we calculate the portfolio first,
14:57and then we see how much is coming,
15:00and the return ratio.
15:02So for that, I come here,
15:04and first we will calculate the portfolio.
15:07I write the portfolio here.
15:11And for the portfolio,
15:13I copy the simple formula written here.
15:15I copy it.
15:17After copying, I will paste it here.
15:20So it will take all the values by itself.
15:23Now I drag it after this.
15:26So we have all this daily.
15:29Then the percent deviation came.
15:32Now I have already dragged it here.
15:35So we got its average.
15:38After this, if we take its average,
15:41then we get the strength deviation.
15:44We calculate the return ratio.
15:47I will drag it on the expression, so it will come.
15:50After this, we needed beta for it.
15:55So first we drag it on beta.
15:59So this came to us.
16:01Its return ratio has come.
16:03After this, we drag it.
16:08So this is coming to us.
16:11We keep it.
16:13So this has come to us.
16:16Now we did not calculate the strength deviation,
16:18so we drag the strength deviation,
16:21so we get the strength deviation.
16:23So this is the strength deviation of our portfolio.
16:27So you can see that the value at risk of all stocks is 0.23.
16:33Similarly, it also has 0.23 margin.
16:36Apple has 18 points.
16:38Netflix has 32.
16:40And Google has 18.
16:42While the strength deviation of our portfolio is 17.
16:48That means the maximum value at risk is 17%.
16:53And if we check above,
16:56then the return is also high.
16:58So this is also an advantage of the portfolio.
17:01Now let's go to diversification.
17:04Now let's see the trainer ratio through the graph.
17:08And before that, let's check the other values.
17:10So for that, we will make a graph.
17:12I will select all the values.
17:14And first we will make a trainer ratio graph.
17:17I will insert it.
17:19And we will check the recommended chart.
17:21So we got this.
17:23The trainer ratio graph.
17:25We got this graph.
17:27Let's drag it here.
17:30And a little bit from here.
17:34Because we are not looking with S&P,
17:37and we have not calculated the trainer ratio.
17:40I will finish it from here.
17:42I will press OK.
17:44So we got the trainer ratio.
17:48And after that, I will make a graph of the value at risk.
17:55For that, we will select all the values.
17:58After that, we will select them.
18:01After selecting, we will go to Insert.
18:04And we will go to Recommended Charts.
18:06So we got this chart.
18:08I will press OK.
18:10After pressing OK,
18:13I will change the name.
18:17I will change it to value at risk.
18:19Value at risk.
18:25So we got this chart.
18:27And we can see that the maximum risk is of Netflix.
18:32And it is coming here.
18:34And if we see others,
18:36the portfolio risk is coming here.
18:40We can make it better.
18:42If we draw a line here.
18:44Anyway, we have calculated the trainer ratio and value at risk.
18:51So value at risk tells us how much maximum risk we can have.
18:56So if we invest in the portfolio of Pointstar,
19:00then if the maximum loss is more than 3 times our expectation,
19:06then the maximum loss will be 17%.
19:11So this was today's video.
19:13I hope you liked it.
19:15Thank you. Allah Hafiz.
Comments