[MUSIC] And now we're going to look at the second tab in our workbook where we've done our portfolio allocations and we're going to look at the performance of the portfolio on each of the 500 trading days in our data set. And let me share my screen with you. Okay, so here is the second tab and as you can see in the upper left hand corner you have some statistical measures. You show the portfolio allocation, you show the index levels in dollars on the first day of our data set. And we also have some statistical measures there showing the minimum and maximum and mean and standard deviation and skew and kurtosis for our loss function. So now we're going to be looking at portfolio performance in terms of loss because as risk managers losses, what you're focused on when you calculate our most common measure of risk, value at risk, value at risk is a loss number. It's the maximum loss that you expect with a certain level of confidence. So just focusing first on the upper left hand corner here. So we have our allocations and as I said before, we allocated 40% or $4,000 to the S&P 500, which is the GSPC ticker symbol reallocated 30% to the Hang Seng. 10% to the Dax index in Frankfurt and then 20% to the Nikkei index. Now, what we see next to that is the price of each of those indexes at the beginning of our data set in dollars. And so we see that the Dax index. Excuse me, the S and P 500 index is at 2779, the Hang Seng index in dollars was at $3944. The Dax index is 15,279. And then the Nikkei at 205. Now the size of the index doesn't matter because we're going to allocate, our allocations based on a dollar amount of that index. So even though the Nikkei is a small index in dollar terms, it's still going to be 20% of our portfolio. Okay, now if we look to the middle of our tab, our portfolio loss, what we see are the same data that we saw before in our data set except now we've converted it from indexes to portfolio values. So when we look at our first GSPC number or S and P 500 number 3949.17. That is the value of $4,000 invested on day 0, on day 1. So in fact the S and P 500 index, if you go back and look at the data set, went down from day 0 to day 1. So we can go back and look at that right now. And what we see was going back to our data set, here we are, okay? And that's what we see. We see that the S and P 500 was 2779 on day 0. And then it was 2744. Okay so the index has gone down. So the value of the 4000 dollars that we've invested in that index also is going to go down in our portfolio. All right, going back to the portfolio lost tab. So that's what we see. We see a loss based on that decline in the index. But now we're looking at dollars in our portfolio and we repeat that same calculation. So you can see the calculation here. So basically we are pulling our data from the data tab. And then just saying we take our, this be 5 here. Oops, the B, $B$5. There is just the 4000 dollars that were allocating times the ratio of the price on day 1 to the price on day 0. Okay, so that gives us our value for the S and P 500. So that's $4000 times that ratio. And then the Hang Seng would be $3,000 times that ratio. So also the Hang Seng index on that first from day 0 to day 1 lost a little bit. The Dax index also lost a little bit because remember the allocation there was $1000, and the Hang Seng was $3,000 and the S & P was $4,000. Okay, and the Nikkei was $2,000 here. Now the Nikkei was the exceptions, the Nikkei actually went up in value on that day. This is what you're going to see a little bit. These indexes are not perfectly correlated. So generally they may move together, but sometimes you'll see one index going up and the other is going down. So you have a bit of a global diversification benefit. So once we combine all of those components of our portfolio, we come up with an overall profit of -81.3, which is our loss of 81.13 also. So our lost function is just showing the loss is a positive number. The, or as a negative profit, okay? And this is repeated all the way down for all 500 of our data points in our portfolio. And you can see here. And so for each day, what we're going to have is a return on that day. So what was our loss positive or negative? Essentially what deserved gain or loss on each day? And this is what we're going to use to construct our distribution so that we can determine what kind of a distribution we have. What is the shape of that density curve? And the way we do that is over here a little bit further to the right in our. On this worksheet and what we see are three sets here which are used to construct three separate hissed a grams. And so our first hissed a gram is our loss function. So this is our loss function. And what I've done here is in order to construct a histogram, you have to break your loss down into intervals where the loss is relative to zero. So -50 would be actually a gain of 50 in your portfolio. And then +50 would be a loss of $50 in your portfolio. And for all 500 observations, we're going to actually count up how many times the loss fell within that range. And so the actual limits of that range are, for zero would be -$25 to +$25, for -$50 here would be -$25, -75 and so on. Well, you can look at these on the spreadsheet. And so we count up how many times in our data set the losses or gains occurred in those buckets or bins. And then we calculate the relative frequency. And the relative frequency is where we get our density functions. This is where we're going to get our density function. And then the cumulative frequency, that's the same as our cumulative probability distribution. So we want to make sure that this totals up to 100% which means that all of our data points have been counted. Now if we take the loss function and make a histogram out of it, what we get is over here on the left, this loss function. So this is what we have. So this is our distribution. It looks fairly symmetric, fairly normal and so on. Now the other distributions that we have shown here are a normal distribution. And then also a T-Distribution. Now we're going to be comparing at normal distribution calculated using the normal density function and the T-distribution calculated using the kurtosis of the data. And we're going to look at histograms of the normal versus our actual data and the T-Distribution versus our actual data. That's what we see here on the left once again. So we have our loss function first, which is nice and symmetrical and looks pretty normal. Now we're going to compare that to a normal distribution. So just below that, our loss function is in blue and our normal distribution is in red. So what we see with our distribution is that our function shows a few more values right in the middle of zero. So it's little more in the middle and then a few less values than normal on the gain side. And this is true here on the losses also. So what we're seeing is that we expect fewer games and losses within the -100 to 100 range than we will with the normal. So it's actually somewhat less risky there. But then as we get to the bigger gains and losses, we see we have something here. There were lost around 250 and that's not expected in a normal distribution, so that's an unusually high and once again that is a loss of 250. And now on the game side, we actually see that the normal distribution has more big gains than we do in our data. Okay. So what we're looking at is something that's kind of pretty normal but maybe has less kurtosis than a normal distribution. So we'll look at that in just a second. And then the last thing we look at is comparing our data to a T-Distribution and here even more so we tend to see, once again that more values around zero than a T-Distribution. But a lot fewer values in the, let's see the -50 and so on. So pretty much this distribution looks to be somewhat less risky than the T-distribution, especially on the losses. So it seems to be a fairly low risk distribution. Okay, now, let's go up to the upper right hand corner where we showed our allocations to our portfolio and now let's look at the statistical measures that we have for that data. So with 500 data points which we're going to call 500 scenarios. So each day is a scenario effectively. We can see for losses that are minimum loss. So this is actually a gain. Here Is a gain of about $179. So that's the biggest gain or the minimum loss. Now the maximum loss, which is what we're more interested in this first managers was 236 here. That means on the worst day in the 500 days or two years of this portfolio, this portfolio took a loss of $236 or actually $237. Now, that's about what 2.36%, that is not a very big loss that you consider. You're looking at two years of data. And that's actually very small for this period. And this is an unusually calm period for the market where you didn't see any big selloffs or any big movements in the market particularly. And we see that when we look at their kurtosis where the data set and that's 0.8262. Now, remember that a normal kurtosis is equal to three. So this has less kurtosis than normal. So really, you're looking at something that is below normal distribution risk. Okay, sure. Okay, and that's the statistical measures that we have. Next we're going to look at the portfolio value at risk, how that's calculated? And that is in the 3rd tab. Okay, and bank this a little bit bigger again. All right, so we have the same information that we had before about our allocations and also our statistical measures. And then below that though, what I've done is created a table that calculates the value at risk for different confidence levels. And so what you would expect if you have a higher confidence level, which means your alpha is smaller. So for say the 99.99 confidence here, that means you're alpha equal to 0.01, okay. So for that the value at risk that we calculate from our data Is actually equal to 151.7. Now in calculating this, I use a function called percentile.EXC. Which is an excel function. It basically looks at all 500 data points and then takes the percent island says, what would the cut off be in those 500 data points. And I'm taking one percent of the worst losses. And then I want to see what would be my loss 99% of the time. So no greater than that. So that's what percent towel EXC does. And as you can see it looks at the entire data set. So that's the all of the returns that we have all 500 returns. And then looks at the confidence level, which is that a 24 there is the 0.99. And so what you would expect is that higher the confidence level, the bigger your value at risk because that means your tail or what's left over once you take the confidence interval has bigger losses or you have a an area that has more of the big losses. So if we go to a 98 percent confidence level, so alpha equal 2.02. So that means we're going to look at what is our loss 98% of the time. And we're down to 134 20. So there and what I've also shown is that these estimates of the value at risk, they're not precise. So once again these are statistical estimates and they have a certain error. And so what I've also shown is the standard error and how you calculate the standard error for that estimate, evaluate risk. And so I calculated that for each of the confidence levels and based on that standard error, you can actually say that you have a high and a low range and this is 151, 71 is right in the middle of that high and low range. So are low based on a actually 95% confidence level in our estimated value at risk is 1 35 45. And our high, which 1 67 97. And this is our average. Okay now so you can see that for all of the values there and this information is also graft for you over here. They make this a little bit smaller. So what we see on the right hand side of the tap is once again our loss function here. So we don't need to look at that again. And then what I've done is graph the value at risk for our portfolio for different levels of confidence. So this is 90% and then all the way up to 100% here and you can see that it goes up and the dotted lines with the high in the low interval for that confidence level. And now what we're going to do is we're going to compare back to the expected shortfall. And so you'll remember from the course expected shortfall is the measure of risk that's used by recommended by the basil committee and I say more robust measure of risk. And that measures the average loss once you're in that value at risk tail. And so expected shortfall is going to be bigger than your value at risk. And the second graph compares value at risk and expected shortfall. And as you can see expected shortfall is higher than value at risk and the difference between the two of them tends to increase and that's the third graph here. The CNN is the space between the expected shortfall and evaluate risk increases as you go to higher levels of confidence. Okay, so a good bit of information here and as I said, for the time period that we're looking at initially yeah, there's not a lot of risk here. But what you're looking at are some good ways. But measuring risk once you finally look at it and you're going to be looking at it in the second part of this project, when we look at that data set or the six months after February of 2020. Now, just as the final thing for this worksheet, we want to compare before we compare the normal density function with our density function for our data, you can also do that compare the normal value at risk to the value at risk that we get from our data. And so that's what we're going to show here. So once again here we have normal value at risk and we're going to compare that to the value at risk for our data, okay. And where you have the same kind of grass over here that we had before over here on the right. And what we see is in fact bet for lower levels of confidence value at risk, it is approximately equal to the normal value at risk. Now, as you go to higher levels of confidence, where alpha is smaller, then you see a bit of a difference between the value at risk and the normal value at risk or the value risk you get if you had a normal distribution and same thing with the expected shortfall, fairly close for lower confidence levels and a bigger difference here for higher confidence levels, okay. That's just another way of measuring things were going to come back to each of these measures in the 2nd part of the project. When we look at that new data, the six months that came after February 2020, okay.
