BT Presentation 6.f - Calculation of Unexpected Loss

[notjusttp]

Hi David,

I have a practical difficulty while calculating Unexpected Loss of Portfolio as suggested by you in BT Presentation 6.f and Excel 6.f.4

When i take square of UL of Exposure 1 ($ 178,511) in an excel sheet i get the answer as 31,866,177,121 while in texas calculator i get it as 3.186618 10.

How can i tackle this issue in exam as the suare of this term is an important input for Calculation of Unexpected Loss of Portfolio .

Thanks and Best rgds
Amit

 

[notjusttp]

Hi David,

Can you kindly respond on this query. This concept is quite a testable one and i am still grappling with the answer.

Rgds
Amit

 

[David Harper CFA FRM]

Hi Amit,

(aplogies for letting previous query slip by)
FYI, excel spreadsheet is here @ http://www.bionicturtle.com/premium/spreadsheet/6.e.4_portfolio_ul_ong/

Okay, so trying with my calculator given assumptions:
default correlation = 3%
AE = 8.25 MM
PD = 0.15%
LGD = 50%
StdDev (LGD) = 25%

UL is given by SQRT[(EDF*variance(LGD) + LGD^2 * variance(EDF)] * AE
... need variance of EDF, since its a bernoulli, variance = 0.0015*(1-0.0015) =~ 0.0015 [STO 1; store in 1]

with calculator:
0.0015 * .25^2 + (.5^2 * RCL1) =~ 0.0005
SQRT() =~ 0.0216
* 8250000 =~ 178,510

similar on exposure #2 should produce $159,916

okay, then for 2-asset portfolio UL, given by:
SQRT[UL#1^2 + UL#2 + (2)(UL#1)(UL#2)(correlation)]

178,510^2 (calculator gives 3.18 * 10^10 ... okay, i see your point!! Don't do anything, it's just scientific notation for the large number, you can keep going! ... this just means ADD 10 zeros to the 3. Your calculator and spreadsheet are giving the same answer)
+ 159916^2 (another huge number, ignore...)
+ (2*178510*159916*.03)
= 5.9xx * 10^10
SQRT() = ~242,000

...so if you just ignore the big numbers along the way, you should be fine
... I agree with this is technically testable, but IMO the individual component UL has low testability (it's harder than the portfolio UL calculation, if given the individual ULs)

David