Actuarial pricing, capital modelling and reserving

Pricing Squad

Issue 37 -- September 2020

Welcome back to Pricing Squad

Pricing Squad is the newsletter for fellow pricing practitioners and actuaries in general insurance.

Today I am sharing VBA code for calculating exposure in different layers in property insurance. Also linking an interesting article on how not to program statistical models.

MBBEFD "Swiss Re" curves

If you insure a property worth x, how much of the expected cost falls within y% of x?

MBBEFD is a curve family which gives a useful answer. Published by Stefan Bernegger in the 70s, it solves an endless list of problems from pricing and reserving to layering and determining large loss loads in property insurance. It is still widely used in the London Market as a primary tool or as a benchmark.

MBBEFD curves can be parametrised with a single number c or with a couple (b,g). In both scenarios G(y) = expected capped loss / expected uncapped loss. The cap y is expressed as fraction of total sum insured. By definition, G(0)=0 and G(1)=1.

A low c = 1.5 is typical for personal lines. Large Lloyd's-type risks need c=5 or more. Below are two examples of G(y).

You can also estimate the best c from you own claims data.

Here is VBA code you can use calculate G(y) and E(G()) if you simply copy-paste this code into a VBA module.

' MBBEFD so called "Swiss Re" curves. Implementation as in Stefan Bernegger,
' The Swiss Re Exposure Curves and the MBBEFD Distribution Class.
' The software is provided 'as is' and we take not responsibility for the results of
' your work if it is completed using this software.

' This function returns G(x) which is the expected capped loss / expected total loss.
' x is a number between 0% and 100% and it represents capping at the %-age of sum insured
' c is class of business parameter:
' 1.5 Y1 Swiss Re (Gasser) Y1 Personal lines (CHF 200k - 400k in today's money)
' 2.0 Y2 Swiss Re (Gasser) Y2 Small commercial (CHF 400k - 1m)
' 3.0 Y3 Swiss Re (Gasser) Y3 Medium commercial (CHF 1m - 2m)
' 4.0 Y4 Swiss Re (Gasser) Y4 Industrial (over CHF 2m)
' 5.0 L Lloyd's
Public Function Mbbefd_G(x As Double, c As Double) As Double
Mbbefd_G = Mbbefd_G_bg(x, b_c(c), g_c(c))
End Function

' The same but two-parameter version with b and g
Public Function Mbbefd_G_bg(x As Double, b As Double, g As Double) As Double
If g = 1 Or b = 0 Then
Mbbefd_G_bg = x
ElseIf b = 1 And g > 1 Then
Mbbefd_G_bg = Log(1 + (g - 1) * x) / Log(g)
ElseIf b * g = 1 And g > 1 Then
Mbbefd_G_bg = (1 - b ^ x) / (1 - b)
ElseIf b > 0 And b <> 1 And b * g <> 1 And g > 1 Then
Mbbefd_G_bg = Log(((g - 1) * b + (1 - g * b) * (b ^ x)) / (1 - b)) / Log(g * b)
End If
End Function

' Expected value of Swiss Re excess curve
Public Function MbbefdEX(c As Double) As Double
Dim b as Double, g as Double
b = b_c(c)
g = g_c(c)
If g = 1 Or b = 0 Then
MbbefdEX = 1
ElseIf b = 1 And g > 1 Then
MbbefdEX = Log(g) / (g - 1)
ElseIf b * g = 1 And g > 1 Then
MbbefdEX = (b - 1) / Log(b)
ElseIf b > 0 And b <> 1 And b * g <> 1 And g > 1 Then
MbbefdEX = Log(b * g) * (1 - b) / (Log(b) * (1 - g * b))
End If
End Function

' auxiliary function b(c)
Private Function b_c(c As Double) As Double
b_c = Exp(3.1 - 0.15 * (1 + c) * c)
End Function

' auxiliary function g(c)
Private Function g_c(c As Double) As Double
g_c = Exp((0.78 + 0.12 * c) * c)
End Function

Downing Street Home Insurance Price Index

After a short period of panic in early summer, home insurance price for 10 Downing Street has dropped 8% this month back to the six-month average.

Code review

Make sure your models never are like this. Especially if someone uses them as a pretext to paralyse a country: Code Review of Ferguson’s Model.

This is just the implementation layer. You can only imagine the quality of assumptions and algos!

Copyright © 2020 Jan Iwanik, All rights reserved. You are receiving this email because you subscribed to updates from We publish data and analysis for informational and educational purposes only. You can unsubscribe from this list by emailing us.