VaR and ES in Excel

hello everyone in the previous videos we worked manually with this data set to calculate the normal and historical war and expected shortfall now we are going to replicate the same thing in Excel so this is the data of prices there are just 30 observations so we are going to have 29 returns the results based on this analysis as I told you in the previous video should not be used for any analytical purposes this is just a demonstration of the calculation let us calculate the returns the periodic returns first so in this cell we select this cell here this is p1 we need to say - p0 this is the p 0 we close brackets and divide it by p 0 we are not taking the log returns we're just taking simple periodic returns so this is the first periodic return we want to find out the periodic returns for all periods so we can select this right hand side corner of the cell and drag the formula down for all the cell's so we have periodic returns for all the periods now for doing the normal distribution bar we need mean return so we can calculate the mean return by using the average function we are going to provide this array because this is the array for which we want the average after the selection is done we close the brackets and hit enter so this is our mean return point 0 4 or 4% we also need the standard deviation of returns so we are using the STD to compute the standard deviation so inside the bracket we are providing the array again close the bracket hit enter and we should have our standard deviation here 0.32 now we want to find the 10% var so we are looking at the 10th percentile bottom 10% cases how we do it is by using the norm in function so let us do it here the quality sign n o RM inv norm in start bracket and then Excel is asking us to provide the parameters the first one is the probability which in our case we have decided to look look at the 10th percentile so the probability is point 1 0 comma we need to supply the mean which is this comma we need to supply the standard deviation which is this closed brackets and hit enter this is the 10% var we could say with 90 percent confidence that our loss will not exceed 36 percent we could do the same thing and calculate the 5% var which is the normal industry standard so once again norm in this time the probability is point 0 5 because we are doing a 5% war the mean is this the standard deviation is this closed brackets hit enter and this is the 5% var which is minus 0.4 8 or 48 percent so we could say with 95% confidence that our maximum loss will be 48 percent now let us move over to historical war for that first of all we need the number of observations in the data set this is a small data set and we know that there are 29 observations but if the data set is large you could use the count command or with the count function Co UN T start bracket and give the array so we are selecting the array and Excel will return us the number of observations 29 for the 10% VAR we need to look at the bottom 10% cases so 10% of 29 is 2.9 so we want to look at the 2.93 turn to point 9th return is going to lie between the third return and the second return so then let us locate the bottom third return first we have not sorted our series but we don't need to really do it we could tell Excel to give us the bottom third value from this series by using the small command or the small function so we type in small and we provide the array so we have told Excel to look for the smallest value in this array we also need to tell Excel which smallest value we are interested in the third value from the bottom so we type in 3 close brackets and hit enter so this is the bottom third value minus 0.3 one we could do the same thing to find out the bottom second return because we won't Excel to look at the second return from the bottom we type in a two closed brackets and hit enter so the bottom second return is minus 0.35 in this cell we can find the bottom 2.93 turn by interpolating and that would be our 10% historical voir let us interpolate the quality sign we are starting with the bottom second return and we need to add on 0.9 of the difference between the third and the second return so in the next bracket we are going to find the difference between the third return which is this and the second return which is this and we are going to multiply this with 0.9 hit enter and what we get is minus 0.32 this is the 10% historical var this means that we are 90% sure that our maximum loss would be 32% this is a little different from the normal distribution var this tells us that probably the return distribution is not strictly normal we can do the same thing to find the 5% historical var let us repeat so we are looking at the bottom 5% of the cases now 5% of 29 is one point four five and one point four five lies between the first return and the second return so we need to locate one point four fifth return for which we need to notice we need to note down the bottom second return this is going to be small our array and to this is the bottom second return and then we are going to find out the bottom first return which is going to be the least return or the lowest return in the series select the array and we want the first value from the bottom so we type in a 1 close brackets and hit enter this is the bottom first return minus 0.41 we can now interpolate to find the bottom one point 4/5 return we start with the second return we start with the first return and then we add the difference between the second return and the first return and multiply 0.45 hit enter this is two point two seven some things seems to be not correct here which is that I omitted the dot here I multiplied with 45 we should say 0.45 so we have minus 0.38 here this is the 5% historical var if you compare it with the normal war at 5% you see that there is a difference a substantial difference this tells us that the normal that the return distribution is not normal we can find out the characteristics of the distribution very quickly in Excel we can go to the data tab here and we'll go to this analysis section and click on data analysis but for this to be active the data analysis add-in needs to be installed on your machine if it is not you can go to the Excel button here go to Excel options click add-ins and down here click go this pop-up will tell you all the add-ins available and also which ones are installed you can see here that the analysis toolpak is already installed on this machine if it is not installed on your machine you need to check these two boxes and click OK Excel will take some time and install the analysis toolpak on to your machine since it is already installed on this machine I'm going to hit cancel once you see the data analysis tab here you click on it and we can look at some descriptive statistics of our risk-return distribution so we select descriptive statistics and click OK we provide the input range which is our return series it has already been selected here we want our output to be returned in cell d1 which is this cell here and we want summary statistics we click OK here we see some summary statistics about our return distribution the mean is point 0 4 the standard deviation is 0.3 188 for a normal distribution the excess kurtosis should be 0 but we see that in this one we have a negative kurtosis this means that the left tail does not have many values skewness of a normal distribution we know it has to be equal to 0 but this return distribution is slightly positively skewed so we have deviated from normality a little bit and that is why we see a difference between the normal distribution bar and the historical war since the kurtosis is negative we are looking at basically a platykurtic distribution which is flatter than normal and does not have too many values in the tail that is why when we computed the historical war at the bottom 5% we found the loss to be just 38% negative whereas under the assumption of normality it would have been 48 percent negative

Channel: Friendly Finance with Chandra S. Bhatnagar

Views: 116,090

Rating: 4.8709679 out of 5

Keywords: VaR, Exepcted shortfall, Conditional tail expectation, excel

Id: ykCEWHRmrlI

Length: 12min 34sec (754 seconds)

Published: Mon Jan 30 2012