In: Finance
calculate the 1% monthly VaR in dollars, Using the historical method.
Suppose you invested $15,000 in the stock of XYZ company in early 2020. You have compiled the monthly returns on this stock during the period of 2015-2019, as given below.
2015 |
2016 |
2017 |
2018 |
2019 |
-0.0214 |
-0.0347 |
-0.1824 |
-0.0723 |
-0.1017 |
-0.0106 |
-0.0566 |
-0.0070 |
-0.1021 |
0.0264 |
0.0262 |
0.0158 |
0.0010 |
0.1114 |
0.1344 |
-0.1196 |
0.0862 |
-0.0648 |
0.2257 |
0.0786 |
-0.0313 |
0.0675 |
0.2378 |
-0.0043 |
-0.1772 |
-0.0362 |
0.0609 |
-0.0512 |
0.1867 |
-0.0953 |
-0.1137 |
-0.0203 |
0.1229 |
-0.0255 |
0.0978 |
0.0401 |
0.0100 |
-0.1156 |
0.1831 |
-0.1110 |
0.0129 |
-0.0230 |
-0.2416 |
-0.0360 |
0.1020 |
0.0652 |
0.1087 |
-0.2591 |
-0.0531 |
0.1099 |
0.1196 |
-0.1980 |
-0.0844 |
-0.0228 |
-0.0816 |
-0.0789 |
-0.0012 |
-0.0833 |
0.0170 |
0.0250 |
Steps to Calculate VAR using Historical Method
a)Have all the 60 monthly return values under a column (Returns 2015-2019)
b) Multiply the return with your invested Capital (Returns 2015-2019 * Invested Capital (15000))
c)In another column ( Returns 2015-2019 in asending order ( R ) )paste the values of (Returns 2015-2019 * Invested Capital (15000)) and then sort it in ascending order
d) To calculate P&L you need to subtract your invested Capital 15000 from values under column (Returns 2015-2019 in asending order ( R )
e) Next to calculate 1% VaR in excel you can use the percentile formula in excel. =PERCENTILE(K2:K61, 1%) here K2 :K61 is our P&L ( R-15000) column . This will calculate 1% VaR as -3731.625
Returns 2015-2019 | Returns 2015-2019 * Invested Capital (15000) | Returns 2015-2019 in asending order ( R ) | P&L ( R-15000) | VaR | 1 Month H-S Valuation | |
-0.0214 | 14679 | 11113.5 | -3886.5 | 1.0000% | PERCENTILE(K2:K61,L2) | |
-0.0106 | 14841 | 11376 | -3624 | -3731.6250 | ||
0.0262 | 15393 | 12030 | -2970 | |||
-0.1196 | 13206 | 12264 | -2736 | |||
-0.0313 | 14530.5 | 12342 | -2658 | |||
-0.0362 | 14457 | 13206 | -1794 | |||
-0.1137 | 13294.5 | 13266 | -1734 | |||
0.0401 | 15601.5 | 13294.5 | -1705.5 | |||
0.0129 | 15193.5 | 13335 | -1665 | |||
0.0652 | 15978 | 13468.5 | -1531.5 | |||
0.1196 | 16794 | 13474.5 | -1525.5 | |||
-0.0789 | 13816.5 | 13570.5 | -1429.5 | |||
-0.0347 | 14479.5 | 13734 | -1266 | |||
-0.0566 | 14151 | 13750.5 | -1249.5 | |||
0.0158 | 15237 | 13776 | -1224 | |||
0.0862 | 16293 | 13816.5 | -1183.5 | |||
0.0675 | 16012.5 | 13915.5 | -1084.5 | |||
0.0609 | 15913.5 | 14028 | -972 | |||
-0.0203 | 14695.5 | 14151 | -849 | |||
0.01 | 15150 | 14203.5 | -796.5 | |||
-0.023 | 14655 | 14232 | -768 | |||
0.1087 | 16630.5 | 14457 | -543 | |||
-0.198 | 12030 | 14460 | -540 | |||
-0.0012 | 14982 | 14479.5 | -520.5 | |||
-0.1824 | 12264 | 14530.5 | -469.5 | |||
-0.007 | 14895 | 14617.5 | -382.5 | |||
0.001 | 15015 | 14655 | -345 | |||
-0.0648 | 14028 | 14658 | -342 | |||
0.2378 | 18567 | 14679 | -321 | |||
-0.0512 | 14232 | 14695.5 | -304.5 | |||
0.1229 | 16843.5 | 14841 | -159 | |||
-0.1156 | 13266 | 14895 | -105 | |||
-0.2416 | 11376 | 14935.5 | -64.5 | |||
-0.2591 | 11113.5 | 14982 | -18 | |||
-0.0844 | 13734 | 15015 | 15 | |||
-0.0833 | 13750.5 | 15150 | 150 | |||
-0.0723 | 13915.5 | 15193.5 | 193.5 | |||
-0.1021 | 13468.5 | 15237 | 237 | |||
0.1114 | 16671 | 15255 | 255 | |||
0.2257 | 18385.5 | 15375 | 375 | |||
-0.0043 | 14935.5 | 15393 | 393 | |||
0.1867 | 17800.5 | 15396 | 396 | |||
-0.0255 | 14617.5 | 15601.5 | 601.5 | |||
0.1831 | 17746.5 | 15913.5 | 913.5 | |||
-0.036 | 14460 | 15978 | 978 | |||
-0.0531 | 14203.5 | 16012.5 | 1012.5 | |||
-0.0228 | 14658 | 16179 | 1179 | |||
0.017 | 15255 | 16293 | 1293 | |||
-0.1017 | 13474.5 | 16467 | 1467 | |||
0.0264 | 15396 | 16530 | 1530 | |||
0.1344 | 17016 | 16630.5 | 1630.5 | |||
0.0786 | 16179 | 16648.5 | 1648.5 | |||
-0.1772 | 12342 | 16671 | 1671 | |||
-0.0953 | 13570.5 | 16794 | 1794 | |||
0.0978 | 16467 | 16843.5 | 1843.5 | |||
-0.111 | 13335 | 17016 | 2016 | |||
0.102 | 16530 | 17746.5 | 2746.5 | |||
0.1099 | 16648.5 | 17800.5 | 2800.5 | |||
-0.0816 | 13776 | 18385.5 | 3385.5 | |||
0.025 | 15375 | 18567 | 3567 |