VBA Historical VaR, ES, EVT with Risk Factors
2. Write a VBA Procedure that, assuming uniform historical scenario weights, calculates
– Historical VaR,
– Expected Shortfall
– and EVT Tail VaR of DFL Bank’s Trading Portfolio while at the same time…
A) Give user flexibility to choose confidence interval and time horizon (max=5 days) as number of days; as well as critical cut point required by EVT
Assume DFL Bank’s total trading portfolio consists of derivatives, stocks and bond instruments and only considers risk arising from FX, IR and EQ risk factors and reporting Currency is in USD.
The worksheet: RFSeries contains historical riskfactor scenario time series of all risk factors, including IR, EQ, FX risk factors (risk factor symbols are consistent to my class notes). In the worksheet: RFSeries, the 3rd row contains Base scenario (i.e. risk factors’ values as of today), 4th row contains the past one day historical observations, 5th row contains the past two days historical observations, etc. There are 76 historical observations in total. IR series are returns per annual.
