How to complete manual replacement business calculations

These are prepared in advance of the SMT Meetings. You will need to refer back to the previous meetings report as a starting point for your calculations. As a business, we are interested in the products being replaced, the amount of our new business that is replacement business, and a general eye on trends.

The FMA requires us to specifically report on the replacement ratios of the following risk products:

• Whole of life               

• Term life                     

• Accidental death        

• Trauma                       

• TPD                

• DI                               

• Funeral                       

• Other 

Part 1 – Risk

1. Check Cancellations Spreadsheet:

• Open the cancellations spreadsheet found in the shared drive

• Filter the spread sheet to find all cancellations received since the date on the previous replacement business report (note, this is often a few days before the SMT meeting)

1. Filter by Replacements:

• Apply a filter on Column M – Replacement Business? to display cancellations that are replacements. This includes both internal and external replacements.

1. Check Each File:

• For each policy identified in the cancellations spreadsheet, go to the corresponding client files.

• Identify the specific covers that are either being cancelled or altered as part of the replacement business.

• You must count each cover individually, for example if there is two Lives assured and they both have Life Cover being cancelled that is a count of 2 in the Life category. If one Life assured has IP and MRI which are both being cancelled that is a count of 2 in the DI category.

• You also need to record the number of policies that are being cancelled or altered as part of the replacement. You could record this data in the following way as part of your working: Client surname, Pol Number (1x Life, 2x trauma etc)

1. Count Products by Categories:

• Calculate the total number of replacements for each product in their categories: Whole of life, Term life, Accidental death, Trauma, TPD, DI, Funeral, Other.

• Note, these categories are the same as required by the FMA report to be submitted after July 1st each year https://www.fma.govt.nz/assets/Compliance/FAP-regulatory-returns-questions-Class-1.pdf (under Part 2, question 8c)

1. Calculate Percentages:

• After calculating the number of replacements for each product type, calculate the percentage of cancelled or altered products in comparison to the total number of replacements for that time period.

• (Number of replacement products in the category / Total replacements) * 100

• The total in the percentage column should add to 100

1. Calculate overall replacement ratio

• Use Mo to determine the number of policies submitted since the previous report and calculate the percentage replacement business

• (Number of replacement policies / Number of policies submitted) * 100

1. Update the Year-to-Date calculations:

• Open the previous replacement business report, which contains cumulative data from 1st July 2023

• Add the newly calculated numbers for each product type

• Update the percentages in the YTD table based on the latest counts. This is done using the same formula as above

Part 2 – KiwiSaver

1. KiwiSaver Analysis:

• Download whole business reports from the KiwiSaver providers (Generate, Milford, and NZFunds currently). This will contain all movements under MTG agencies.

• Filter the reports to contain only clients who have joined the provider since the date of the previous replacement business report.

1. Check for Same Joining Dates:

• Compare the date joined in KiwiSaver with the date joined with the provider for each client.

• If the dates match, it is not replacement business.

• This can be done as an excel formula with 0 showing the dates are the same
  =IF(A1=B1, 0, 1)

1. VLOOKUP for Internal Movements:

• Employ the VLOOKUP function to search for any movements or changes between KiwiSaver provider performed by our advisers.

• Cross-reference the data between the KiwiSaver reports from the providers to identify any shifts.

• You should use the following excel formula
  =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

1. Calculate overall replacement ratio

• Using the same formulas as in the Risk area (number of replacements/number of new members)*100, calculate the replacement ratio for each provider, as well as the overall KiwiSaver replacement ratio

Additional notes:

Trends to keep an eye out for:

• An adviser completing a lot of replacement business (a high percentage of their submitted business)

• An adviser completing a lot of internal replacements (policies they had written previously)

• A sudden or large increase in the percentage of replacement policies

• A sudden or large increase in the percentage of a particular product type

At times, the SMT may require more information on the above trends, or may request additional reporting. This would be in addition to the above reporting.

The categories in Mo on the Cancellations Tab directly reflect the FMA reporting requirements for risk. KiwiSaver Calculations will still need to be completed manually.

Any new KiwiSaver Providers that advisers begin to write with will need to be included in future replacement calculations.