Nov 07

Surplus at risk – spreadsheet

by David Harper, CFA, FRM, CIPM

FRM |

sar_xls

The EditGrid spreadsheet below contains two sheets. Both calculate surplus at risk (SaR). The first is the worksheet I reviewed in an earlier screencast (based on the approach used in question 37 of the 2008 FRM sample exam). The second sheet replicates the approach used in Jorion Chapter 7.

The first approach to SaR

This is easier if you see that this approach is identical to the calculation of diversified portfolio VaR only, instead of two assets, we are long pension assets and short pension liabilities. Don’t forget the formula for variance(a – b) = variance (a) + variance(b) - (2)cov(a,b) such that the variance of the surplus is given by:

  • variance (surplus) = assets^2*asset variance + liabilities^2*liabilities variance - (2)(asset)(liability)(assets volatility)(liabilities volatility)(correlation)

Then we apply the typical “absolute VaR” calculation:

  • Surplus at risk (SaR) = –(expected surplus) + (volatility of surplus)*(normal deviate)

The second approach to SaR

The second sheet show’s Jorion’s approach. The difference here is that we are given the volatility of surplus directly.

Spreadsheet:

Comments

  1. skcd 13 Nov 2008

    Why is there a difference in final approach. Var = - mean(*del_t) + sigma*Z(alpha)*sqrt(del_t).
    In Jorion433, the Sar does’nt take into account -50 or the expected increase in surplus.
    Also, the (68) negative value is i dont know what?

Leave a Comment