On-leveling using parallelogram method in Excel

Professional Actuarial Topics Property/Casualty
1

I posted this in r/actuary too, but does anybody have any tips/tricks on using Excel to automate/formulize on-leveling premium using the parallelogram method?

I’m working on a new line that only has unadjusted premium data, and manually figuring out areas of triangles/parallelograms/trapezoids seems like there’s too much room for human error.

2

We had rate files that did this at my previous company.

The complication depends on how complicated your rate history is, and whether or not anniversary dates were used.

You could theoretically devote a tab to calculate the areas specifically, and have some sort of lookup sumproduct.

I’m sure you can do it.

3

I have exhibits that match the Werner and Modlin automated in Excel. Please pm me if interested.

3 Likes
4

In the last rate indication template I built that required parallelogram on-leveling, I created a VBA function, with inputs of “StartOfAccidentPeriod”, “EndOfAccidentPeriod”, “RateChangeDate”, and “AveragePolicyLength” to return portion of the accident period earned “before” a particular change.

Before coding the VBA, I sketched out all the possible cases:

  • Rate change was implemented early enough that no policies written before the effective date would have had exposure during the accident period
  • Rate change was implemented before the start of the accident period, but some pre-change policies still earned during the accident period…but no pre-change policies still in force at and of accident period
  • Rate change was implemented before the start of the accident period, but some pre-change policies still earned during the accident period…and some pre-change policies still in force at and of accident period (e.g. if you’re building quarterly OLFs but have annual policy terms)
  • Rate change was implemented after the start of the accident period, but all policies written before the change were fully earned before the end of the accident period (e.g. 6 month policy terms but calculating OLFs for 12-month periods)
  • Rate change was implemented after the start of the accident period but before the end of the accident period, and there were still policies in force from before the change at the end of the accident period
  • Rate change was implemented after the end of the accident period.

…and the formula for each case is pretty simple. Use an if/then/elseif structure…

With that function, it becomes possible to build a table, where generations of rates are listed as rows, and accident periods are listed as columns, showing the array of these “earned before” percentages.

You can then have a second table calculating on-level factors for each generation of rates.

With those two tables, you can calculate on level factors for each accident period.

5

Hmm interesting, thank you for the detailed response.