# Pricing Squad

## 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 www.iwanik.co.uk. We publish data and analysis for informational and educational purposes only. You can unsubscribe from this list by emailing us.