Help center

Excel Portfolio Uploader ​

This page describes how to populate the Excel Portfolio Uploader Template available to download from the Templates page under the ClariNet LS Help menu.

This page relates to version 1.74 of the Template. Subsequent changes in the Template may not be reflected below.

Once you have populated the Template with data, the import wizard either uploads new Cases or amends data points in existing Cases based on Case Reference. If the Case Reference already exists in your ClariNet LS account, the existing case will be modified, otherwise a new Case will be created with that Case Reference.

A sample template section is shown below; the MyPortfolio tab contains all sections that can be uploaded into ClariNet LS, the Readme tab contains a link to this help page.

The sections below provide key information about the template including how to customize it and use it to successfully populate ClariNet LS with data when uploaded. Also explained are some important points regarding validation and the handling of duplicates.

Customizing and populating the Template

  • Successful creation of a new Case requires that certain data fields are populated in the Template.
  • Section headings have a dark blue background.
  • Required fields are shown with a light blue background. Optional fields are shown with a white background and can be omitted.
  • If you are creating a new Case, you can completely omit sections with no required fields (e.g., Registered Owner, Servicing Carrier); if a section has a required field, you must retain the section heading and the required field(s) as a minimum.
  • If you are updating existing Cases, you can delete any or all of the sections except those containing the fields you want to add or modify.
  • Within each section, you can also delete individual fields which are not required. The only field that must feature in every Template is the Case Reference, defined in row 1, column B, C, D etc.
  • When preparing a "reduced" Template for use, you should consider what information is required for ClariNet LS to identify the Cases and Case objects you wish to add or amend. For example, to prepare a Template to upload new VOCs, you would delete all sections in the Template other than Case Reference and Policy Verification. Within Case Reference, you need to retain only the Case Reference and within Policy Verification, you need to retain only the required fields plus any other fields you wish to upload.
VOC : An abbreviation for Verification of Coverage
  • Your populated template must be saved as an Excel workbook (.XLSX extension) with the populated template as the front worksheet. Macro-enabled workbooks (.XLSM extension) will not import.
It is important that you do not change ANY of the text entries in column A of the Template, including section headings as the import wizard uses the format and content of these data field labels to store the data in ClariNet LS. The colors have no importance so you can change these if you wish. For fields with a defined list of permitted values, ensure you enter values exactly as documented – i.e., set Life Policy Type to ‘UniversalLife’, not ‘Universallife’ or ‘Universal Life’. Excel formatting (such as decimal places or percentages) is ignored and the full underlying value is imported. Fields which are described as Boolean can have values set to TRUE or 1 and to FALSE or 0 .

Example: Using the Excel Portfolio Uploader to update existing Cases.

Download an example (attached below) of just updating a single field.

portfoliov1.5update1.xlsx
You MUST precede the field that you want to update with the correct section name (e.g. in this case ‘Policy’).

In other words, if you want to update the Face Amount, you need at least three fields:

  • Case Reference (the Case Reference of an existing Case)
  • Policy (an empty header row with no data)
  • Face Amount (with the data you want to update)

The main template shows all fields and the section names, but you can delete the ones you are not updating.

Consider that if you’re updating things that can appear multiple times in a case (e.g. insured) you would also need their identifier to ensure you’re updating the right one.

In this specific example, you need to enter sufficient information so that the import wizard can determine the Case, the Insured and the LE Report which needs to be adjusted. Your uploader can be cut down to only these required fields:

The Case Reference identifies the Case, the Date of Birth and Gender identify the Primary Insured. Each LE Report is identified by the Underwriter, Report Date, Smoking Status and Mean LE50. While the Created Date is required by the import wizard, if the LE Report contained in an Underwriters Report block otherwise matches an existing LE Report for the Insured in ClariNet LS, the Created Date will not be changed.

The example above changes the LE Report blend for Valuation only. If we want to change the LE Report blend for Actual to Expected, we would add a row to each Underwriters Report block and enter "Weighting Name" in column A in each block, then enter "A2E" as the value for Weighting Name.

Using the Template to populate CRM fields

Within the ClariNet LS application the CRM symbol indicates that a data field is a CRM field and clicking the symbol opens the Customer Relationship Manager in a separate window.

CRM: A dataset within ClariNet LS which includes contact information for Companies and People connected with a Client's Cases. The CRM is controlled by the Client.

The CRM manages data relating to Individuals and Organizations and can be thought of as an "address book" for storing such information. You can use the Template to create or edit CRM entities and attach those to Case objects. In each instance, the CRM data fields and their permitted values are identical. Within the template there are sections that can be used to assign CRM entities to these fields in the Case tab, see CRM sections.

Understanding validation rules for adding or editing CRM entities

ClariNet LS follows specific rules to determine if you are creating a new CRM entity or editing an existing one.

For Individuals, ClariNet LS checks first if the Client Reference AND the Firstname AND the Lastname already exist. If there is an exact match, the following data in the CRM will be overwritten by the values found in the Excel sheet:

  • TaxID
  • Do Not Contact
  • Title
  • Job Title
  • Middle Name
  • Role

For Organizations, ClariNet LS checks if the exact Organization Name already exists (it does not use Client Reference). If there is an exact match, the following data in the CRM will be overwritten by the values found in the Excel sheet:

  • TaxID
  • Do Not Contact
  • Role
If the Role is blank in the Template but the Organization already exists in CRM, the Role for that Organization in CRM will be changed to ‘Other’.

Before being added, Contact Details are checked as follow to avoid creating duplicate entries:

  • Numbers: the exact number, as entered (ignores Description). So "(866) 487-7484" is different to "866 487-7484" and "8664877484".
  • Email: the exact email address (ignores Description)
  • Url: the exact web address (ignores Description)
  • Address: the Addressee, First Line, Second Line, City, Zip Code, State and Country are all exactly identical.

This behaviour has side effects when adding or editing a large number of Cases, for example when using a file generated from another system. The first Case in the file might add an Organization, with a phone number Description set to "Customer Service Phone". Another Case later on in the file has the same Organization (exact same Name), with the same phone number, but this time, the Description is set to "Payments Phone". After importing, on the Servicing tab for the first Case, the "Customer Service Phone" field will show "123456789 (Customer Service Phone)", as per the Excel file. For the other Case though, on the Servicing tab, the "Payments Phone" field will show "123456789 (Customer Service Phone)". This might be confusing, but it is expected. When importing the second Case, the phone number already existed (added for the first case with Description "Customer Service Phone"), so it wasn't added again, as expected. But since the Description for that second Case was set to "Payments Phone", ClariNet LS assigned the existing phone number with Description "Customer Service Phone" to the Servicing Setting "Payment Phone" field, as expected.

Understanding how duplicates are handled by the Import Wizard

The Import Wizard handles potential duplicate entries as follows:

The following objects will be added to the Case without checking for duplicates:

  • Comments
  • Illustrations
  • Insured Verifications
  • Policy Verifications
  • Premium Schedules

The following are checked using these criteria:

  • Health Care Providers: if these match an existing CRM entity (using the matching rules set out above), the existing CRM entity will be assigned as a Health Care Provider for the relevant Insured
  • Health Status Contacts: as for Health Care Providers
  • ICD9 Impairments: if these exactly match an existing ICD9 Impairment for the Insured, no duplicate is added
  • LE Reports: If all of Underwriter, LE Report Date, LE50 Mean, Smoking Status and Mortality Factor match, no duplicate is added

The following are edited if the exact Case Reference exists, with all fields being overwritten with data from the Excel importer:

  • Case
  • Insured
  • Policy

The following sections describe the template fields for Case Reference, Policy, Primary Insured and Secondary Insured, whether the values are mandatory or optional and their permitted values.

Case Reference

Case Reference Required. Used as the key to determine whether this is a new or existing Case.

Case Origination Optional.

Channel Optional.

Settlement Type Optional.

Country of Ownership Optional.

State of Ownership Optional.

Business Unit Optional. If populated, the data is copied across into the Business Unit field on the Case tab of the Case Summary.

Pricing Analyst Optional.

Loan Collateral Optional.

Agent Reference Optional. Enter the agent reference.

Broker Reference Optional.

Provider Reference Optional.

Investor Reference Optional. Enter the investor reference.

Policy

Policy Number is required and is copied across to the Policy tab.

Carrier Reference is required and is a field which describes the name of the Carrier (in this case, the Carrier which has issued or which is responsible for payments under the Policy). This can either be the Carrier name or the NAIC number for the Carrier. Because ClariNet LS populates this field with information from a data file supplied by AMBest (so that it can track rating changes) which relies on the NAIC numbers, if you enter the Carrier name the import wizard will offer you options to select the equivalent Carrier from the AMBest list. Using the NAIC number is preferable, where it is available, as it avoids having to match the Carriers during the import process.

Issuing Carrier: this is a free-text field which does not change when the AMBest data changes. It is intended for the name of the Carrier which issued the Policy (as opposed to the "holding" or "servicing carrier".

Face Amount is required and is the face amount or specified amount of the Policy. This is copied across to the Policy tab.

Maturity Age is required. This is the attained age of the Insured at which monthly deductions on the policy cease. Permitted values are any positive integer between 90 and 125. This is copied across to the Policy tab.

Deduction End Date

Coverage End Date

Extended DB Rider is required. This is the attained age of the Insured at which point any extended death benefit rider expires. Permitted values are "None" or any positive integer between 95 and 125. If a numerical value is specified, it must be greater than the Maturity Age. The value is copied across to the Policy tab.

Policy Date is required. This value is copied across to the Policy tab.

Issue Date is optional. If populated, this value is copied across to the Policy Issue Date on the Policy tab.

Issue State is an optional field. Where populated, it must follow the ISO format, i.e., US-XX.

Life Policy Type is required. It is the type of Policy. Permitted values are "UniversalLife", "WholeLife", "TermLife" and "Endowment". This is copied across to the Policy tab.

Death Benefit Type is optional and is only relevant for Cases with Life Policy Type set to “UniversalLife”. Permitted values are “FacePlusAccountValue”, “FacePlusSurrenderValue”, “Level” and “ReturnOfPremium”.

Return Type is optional and is only relevant for Cases with Life Policy Type set to “UniversalLife”. Permitted values are “Variable” and “NonVariable”.

Participating is optional and is relevant for Cases with a participation element (typically Whole Life). Permitted values are "NonParticipating" and "Participating".

ROP Maximum NDB is optional and is relevant for for Cases with a Death Benefit Type of "ReturnOfPremium".

Dividend Option is optional. Permitted values are "AppliedTowardsPremium", "OnDeposit" and "PaidUpAdditions".

Non-Forfeiture is optional and is relevant for Cases with a partcipation element, Permitted values are "AutomaticPremiumLoan", "ExtendedTerm" and "PaidUp".

Coverage Type is optional. Permitted values are “Group” and “Individual”.

Age Basis is required. This is copied across to the Policy tab.

Age Basis : The basis by reference to which the Insured's Attained Age is calculated; either ALB (Age Last Birthday) or ANB/ANBm (Age Nearest Birthday). ANB is the most common basis for Policies issued after 1995. ANB calculates the exact number of days from the reference date to the previous and next birthday and uses whichever is closest on that basis to determine age. For ANBm, if we are more than 6 months after the last birthday, use the next birthday as the age, otherwise use the previous one.

Lapse on CSV is required. It is a Boolean field which specifies the basis on which the Policy goes into grace. If set to "TRUE" or "1", the Policy goes into grace when the cash surrender value declines below zero. If set to "FALSE" or "0", the Policy goes into grace when the account value declines below zero. This is copied across to the Lapse Basis field in the Policy tab.

Joint Life Payout Type is required IF the Policy has more than one Insured. It describes the timing of payment of the death benefit – either on the death of the first Insured to die, or the second Insured to die. Permitted values are "FirstToDie" and "SecondToDie". It is copied across to the Policy tab.

Policy Product is optional. If specified, the value creates a new Policy Product for the Carrier (which can then be edited via the Policy Product Library). If an exact match for the value entered is associated with the Carrier in the Policy Product Library, that Policy Product is linked to the Policy.

The next seven fields are default values, displayed on the Policy tab, which will be used when creating a new Illustration for the Policy.

Percent of Premium Charge is optional. This is the administrative charge made by the Carrier on each premium payment. It must be expressed as a percentage.

POPC Above Target is optional. This is the administrative charge made by the Carrier on each premium payment received in excess of a specified target. It must be expressed as a percentage.

Per Unit Charge is optional. This is a US Dollar amount charged by the Carrier per unit ($1,000) in face/specified amount.

Per Unit Charge Frequency is required. Permitted values are “Annual”, “Monthly”, “Quarterly” and “SemiAnnual”. This reflects the payment frequency for the Per Unit Charge.

Per Policy Charge is optional. This is a US Dollar amount charged by the Carrier for each Policy.

Per Policy Charge Frequency is required. Permitted values are "Monthly", "Quarterly", "SemiAnnual" and "Annual".

Current NGCR is optional. This is the current non-guaranteed crediting rate associated with the Policy.

The next three fields are used for Policies which are convertible (typically into Universal Life Policies).

  • Convertible is optional. Permitted values are "Convertible" and "NonConvertible".
  • Conversion Expiry Date is an optionaldate field. It is a date on which the conversion option expires.
  • Conversion Premium is an optional US Dollar amount. It represents the premium due for exercise of the conversion option.

The next four fields are used for Universal Life Policies which have been converted from another Life Policy Type.

  • IsConverted is optional. It is a Boolean field which specifies whether the Policy has been converted.
  • Conversion Date is optional. It is the date on which the Policy was converted from another Life Policy Type.
  • Issue Date Converted Policy is optional. It is the Policy Issue Date associated with the original Policy (i.e., the Policy which was converted).
  • Policy Date Converted Policy is optional. It is the Policy Date associated with the original Policy.

The next two fields are relevant for Term Policies only.

  • Renewable is an optional Boolean field.
  • Renewal Term is optional and is a number of years for which the Policy can be renewed after issue.

The next three fields are relevant for Group Policies.

Group Number is an optional alphanumeric field relevant for group Policies.

Certificate Number is an optional alphanumeric field relevant for group Policies.

Owner Type is optional. It describes the original owner of the Policy (i.e., prior to a secondary sale). Permitted values are “Corporate”, “Individual” and “Trust”.

The next fields are all optional and set values on the Policy tab.

  • Previously Viaticated is Boolean and describeswhether the Policy has been sold in a secondary market transaction previously.
  • Foreclosed is Boolean and describes whether the Policy was sold or transferred as a result of foreclosure proceedings, e.g., on a premium finance loan.
  • Premium Financed is Boolean and describes whether the Policy has been or is the subject of a premium finance arrangement.
  • Bankruptcy is Boolean and describes whether the Policy has been sold or transferred out of bankruptcy proceedings against the original Owner.
  • Grace Period is the number of days in the POlicy's grace period (typically between 45 and 61).
  • Country of Ownership is the country in which the original Owner is resident. Permitted values are the ISO list of countries in full, e.g., "United States", "United Kingdom". This value is copied across to the Country of Ownership field on the Case Summary tab.
  • State of Ownership describes the US State in which the original Owner is resident (where Country of Ownership is set to United States). Where populated, it must follow the ISO format, i.e., US-XX. This value is copied across to the Country of Ownership field on the Case Summary tab.
  • Primary Insured Underwriting Class and Secondary Insured Underwriting Class are alphanumeric fields which have no other limits on their values. They represent the underwriting class assigned by the Carrier at issue to the Primary Insured and, if relevant, the Secondary Insured. Examples might include "Preferred", "Standard" and "Table D".
  • Primary Insured Carrier Issue Age, Secondary Insured Carrier Issue Age and Joint Equivalent Carrier Issue Age
  • Surrender Charge Type
  • Policy Settlement Date
  • Beneficial Interest
  • PFBI Risk Level Type
  • Supplementary Rider Amount
  • Supplementary Rider Expiry Age
  • Supplementary Rider PUC
  • No Lapse Guarantee
  • Net Death Benefit Information
  • Accelerate DB
  • Extended DB
  • Participating
  • COI Analysis
  • COI Increase
  • COI Increase Date
  • COI Increase Applied
  • COI Increase Applied Date
  • COI Language

The next three fields are all optional and relate to Policies which contain a no-lapse component for premium payments.

  • No-Lapse Premium Amount is the amount of premium (in dollars) required to be paid into the Policy over the period represented by the No-Lapse Premium Frequency in order to trigger/maintain the no-lapse guarantee.
  • No-Lapse Premium Duration Years is the period (expressed as the number of Policy Years since the Policy Date) for which the no-lapse guarantee is available.
  • No-Lapse Premium Frequency is the period applicable to the No-Lapse Premium Amount payment monitoring. For example, if the No-Lapse Premium Amount is set to 100,000 and the No-Lapse Premium Frequency is set to Annual, the terms of the no-lapse guarantee require that a minimum of $100,000 in gross premium be paid into the Policy during each Policy Year in order to maintain the guarantee.

Primary Insured, Secondary Insured

These sections provides key information on the Insureds. Primary Insured must be completed for all new Cases; Secondary Insured is only required for joint life Cases. Both sections also allow you to create or modify a CRM entry associated with the Insured. All fields up to and including Date of Death pass to the Insured Lives tab in the Case Summary; thereafter everything is copied across to CRM. Insured Reference and CRM Client Reference are used as keys to determine whether to create a new Insured/CRM entity or modify an existing object.

Insured Reference: Optional. A reference code of your own choosing for the Insured.

FirstName: Optional. The first name of the Insured.

MiddleName: Optional. The middle name of the Insured.

LastName: Optional. The last name of the Insured.

Suffix: Optional. The suffix of the Insured if applicable.

SSN: Optional. The Social Security Number for the Insured. Note that there is no prescribed format.

Date of Birth: This is a required field. You can enter the values in your local format (e.g., MM/DD/YYYY for the United States and Canada, DD/MM/YYYY elsewhere. Note that it is the local format where the spreadsheet is uploaded to ClariNet LS that is relevant, not the local format where the spreadsheet is built.

Date of Death: Optional. Permitted values as for Date of Birth.

Gender: This is a required field. Permitted values are "Female" and "Male".

State of Residence: Optional. Follows the same rules as to format and content as other State fields.

Country of Residence: Optional. Follows the same rules as to content as other Country fields.

Settlement Date: Optional. The first settlement date for any Policy referencing the Insured. This is relevant to LE report orders placed with ITM 21st.

Medical Record Start Date: Optional. The start date for Medical Records on the Insured.

Medical Record End Date: Optional. The end date for Medical Records on the Insured.

CRM Client Reference: A reference code of your own choosing for the CRM entity which links to the Insured. This is copied across to Client Reference in the Individual entity in CRM. If no match for an existing Individual is found on import, ClariNet LS creates a new Individual, with First Name and Last Name set as for the Insured, Tax ID set to the SSN and Client Reference set as specified in this field.

Address(es): The address blocks in this section follow the rules specified under Understanding how duplicates are handled by the Import Wizard and also Generic Fields and Permitted Values in CRM Sections.

The following sections describe the template fields that populate the CRM, whether the values are mandatory or optional and their permitted values.

CRM sections

Several sections in the Template allow you to create or edit CRM entities and attach those entities to Case objects. In each instance, the fields and their permitted values are identical:

Is Individual: Required. This field is Boolean and sets whether the CRM entry is an Organisation or an Individual.

Role: Required. This is the role of the entity, if applicable. Permitted values are "AgentAttorney", "AgentCPA", "AgentProducer", "Broker", "Carrier", "CountyOfficial", "EscrowAgent", "HealthStatusContact", "HealthcareProvider", "Investor", "MedicalUnderwriter", "Other", "Provider", "SecuritiesIntermediary", "Seller", "StateOfficial".

Client Reference: Optional. This is a reference code or number which is assigned by you.

Organisation Name: Required if the entity is an Organisation. The name of the entity is entered here.

Individual FirstName: Required if the entity is an Individual. The first name is entered here.

Individual MiddleName: Required if the entity is an Individual. The middle name is entered here.

Individual LastName: Required if the entity is an Individual. The last name is entered here.

SSN: Optional. The Social Security Number for the Insured. Note that there is no prescribed format.

Individual Job Title: Optional. If the entity is an Individual, a Job Title may be specified here.

TaxID: Optional. The tax ID of the entity. There is no prescribed form.

Do Not Contact: Optional. A TRUE/FALSE flag. The default is FALSE if not entered.

Resident in Hospice: Optional.

Phone1/Phone2/Phone3: Optional. Phone or fax numbers for the entity. There is no prescribed format and the data field is stored in ClariNet LS exactly as it is entered in the sheet. If values are entered in these fields, an entry is created under the Contact Details section in CRM with Detail Type set to "Number", and Description set to the Description entered. Note, if the Description is set to one of these special values: "Payments Phone", "Payments Fax", "Customer Service Phone" or "Customer Service Fax", then the corresponding entry is set under Servicing Carrier.

Fax: Optional. A fax number. The Description for this number will automatically be set to "Fax".

Email1/Email2: Email addresses for the entity. The format must follow username@domain.suffix. Values entered in these fields create entries under the Contact Details section with Detail Type set to "Email", Description set to "Email1" or "Email2" (as applicable) and Email set to the value specified in the sheet.

Address: This subsection creates an entry with Detail Type "Address" for the entity in the Contact Details section:

Address Type: Permitted values are "CustomerService", "Other", "Overnight", "Payments", "Physical", "Public", "Private" and "RegularMail".

Addressee: Optional. Use this field to enter information such as "ATTN", "FAO" (For the Attention Of), Department, or maybe a specific person's name in an Organization.

First Line: if an Address is specified, this is a required field. No constraints. is copied across to 1st Line in the Contact Details.

Second Line: Optional. If specified is copied across to 2nd Line in the Contact Details.

City: If an Address is specified, this is a required field. No constraints. is copied across to City in the Contact Details.

Zip Code: If an Address is specified, this is a required field. No constraints. is copied across to Zip/Postal Code in the Contact Details.

State: If Country is set to "United States" and an Address is specified, this is a required field. Permitted values are the ISO state codes, i.e., US-XX. Note that only a US state can be referenced in this field. is copied across to State in the Contact Details.

Country: If an Address is specified, this is a required field. Permitted values are the ISO list of countries in full, e.g., "United States", "United Kingdom". is copied across to Country in the Contact Details.

Agent, Broker, Provider, Investor, Irrevocable Beneficiary

These sections assign CRM entities to these fields in the Case tab. If required, they will also create CRM entities (where no matching entity is found). See Generic CRM Fields and Permitted Values for information on the fields and their permitted values.

Registered Owner

The Registered Owner section creates or modifies an entry in your CRM database and thus follows the format of the other CRM sections. For clients with the Servicing module active, it also links that entity to the relevant Case as its Registered Owner (displayed on the Servicing tab in the Case Summary). See Generic CRM Fields and Permitted Values for information on the fields and their permitted values.

Finally, there are optional sections in the Template. If you do not wish to enter any information in a section, you can delete it from the Excel file before you upload it into ClariNet LS.

Servicing Carrier

The Servicing Carrier section creates or modifies an entry in your CRM database. For clients with the Servicing module active, it also links that entity to your Case as the Servicing Carrier (displayed on the Servicing tab in the Case Summary). Rules and permitted values for the top section and the Address follow those mentioned above under Generic CRM Fields and Permitted Values. Entering data into the Payment Method subsection creates or modifies a Payment Method for the relevant Organisation in your CRM database as follows:

  • Method: If a Payment Method is specified, this is a required field. Permitted values are "Check" or "Wire". is copied across to Method in the Payment Method in CRM.
  • Description: If a Payment Method is specified, this is a required field. A descriptive label for the Payment Method. No prescribed values. is copied across to Method in the Payment Method in CRM.
  • Wire Account Name: If a Payment Method is specified as Wire, this is a required field. The account name for the wire transfer – no prescribed values. is copied across to Account Name in the Payment Method in CRM.
  • Wire Bank Name: If a Payment Method is specified as Wire, this is a required field. The bank name for the wire transfer – no prescribed values. is copied across to Bank Name in the Payment Method in CRM.
  • Wire SWIFT Code: If a Payment Method is specified as Wire this is a required field. The SWIFT code associated with the wire transfer – no prescribed values. is copied across to Swift Code in the Payment Method in CRM.
  • Wire Routing Number: If a Payment Method is specified as Wire, this is a required field. The routing number associated with the wire transfer – no prescribed values. is copied across to ABA/Routing Number in the Payment Method in CRM.
  • Wire Account Number: If a Payment Method is specified as Wire, this is a required field. The bank account number associated with the wire transfer – no prescribed values. is copied across to Account Number in the Payment Method in CRM.
  • Check Payee: If a Payment Method is specified as Check, this is a required field. The payee for the relevant check (e.g. for premium payments). No prescribed value. is copied across to Payee in the Payment Method in CRM.
  • Reference Information: Any reference information associated with the Payment Method. Optional.

Underwriters Report

This entire section is optional and can be deleted but note that it is not possible to value a Case or manipulate it within a Portfolio unless there is at least one life expectancy report created for each surviving Insured, so it is best to include this information in the initial upload wherever possible.

The Underwriters Report subsection can appear multiple times for each Insured (so you can upload several LE Reports simultaneously). There are several fields which are required in each subsection.

  • Underwriter: The underwriter which produced the LE Report. Permitted values are "AUS", "AVS", "EMSI", "FASANO", "GLOBAL_LIFE", "ISC", "TWENTY_FIRST", "PREDICTIVE_RESOURCES", "LSI" and "ELEVATION" or "UserDefined". The latter is used to distinguish a user-defined LE from LE Reports produced by independent underwriters.
  • Created Date: The date on which the LE Report was created in ClariNet LS. Whilst this is a required field, it can be set to any date which is the same as or later than the Report Date. The same format rules apply as for other date fields.
  • Mortality Factor: The mortality factor specified in the LE Report, if any. Note that this should be expressed as an integer – not a decimal, not a percentage.
  • Report Date: The date of the LE Report, as specified by the Underwriter. This is the date which is used to determine select periods and other functions related to the generation of survival curves. The same format rules apply as for other date fields.
  • Smoking Status: The smoking status for the Insured, specified by the Underwriter in the LE Report. Permitted values are "Aggregate", "NonSmoker" and "Smoker". "Aggregate" is a smoking status only used by Fasano Associates.
  • Mean LE50: The number of months identified as the mean life expectancy of the Insured in the LE Report. This is expressed as an integer. Note that this is a required field.
  • Median LE50: The number of months identified as the median life expectancy of the Insured in the LE Report. Not all reports state the median LE50, so this field is optional.
  • LE85: The number of months identified as the 85% point on the Insured's mortality curve by the underwriter.
  • Medical Records From: The start date of the medical records reference in the LE Report.
  • Medical Records To: The end date of the medical records referenced in the LE Report.
  • Underwriting Type: The underwriting methodology used to generate the LE Report. This is a free text field, e.g., Clinical Assessment, Longevity Update.
  • Weighting: The weighting you wish to apply to the LE Report in your Valuation Settings for the Underwriter Report. Note that the sum of all Weighting for all Underwriter Reports for a particular Insured must equal 100.
  • Weighting Name: This specifies whether the Weighting you have set in the field above is to be used for Portfolio Valuation/Monte Carlo or for Actual to Expected analysis. If "A2E" is specified in this field, the Weighting will be applied to the A2E settings on the Valuation Settings tab. Blank or any other value will cause the Weighting to be applied to the Valuation settings.
  • Weighting Mortality Specifier: The factor in the LE Report which is used to determine the blending of multiple LE Reports. Permitted values are "LE50ActuarialMean", "LE50Median" and "MortalityFactor". The default value if none is specified is "LE50ActuarialMean". This is copied across to the Valuation Settings for the Underwriter Report.
  • Weighting Use Improvement: Specify whether Mortality Improvement is to be used in calculating the blended LE for the Insured. Only relevant where the Weighting Mortality Specifier is set to "MortalityFactor". This is copied across to the Valuation Settings for the Underwriter Report.
  • Weighting Actuarial Table Name: The Base Mortality Table used in determining the blended LE of the Insured where the Valuation Template has "Age LEs Independently" checked. Permitted values (which pass to the Valuation Settings for the Underwriter Report) are:
    • 2015
    • 2014 PRELIM
    • 2008 PRIMARY (this is the default value if none is specified)
    • 2001 PRIMARY
    • 2008 RR100
    • 2008 RR110
    • 2008 RR120
    • 2008 RR125
    • 2008 RR130
    • 2008 RR140
    • 2008 RR150
    • 2008 RR160
    • 2008 RR70
    • 2008 RR75
    • 2008 RR80
    • 2008 RR90
  • Weighting Actuarial Table Age Basis: The Age Basis for the Base Mortality Table. This is copied across to the Valuation Settings for the Underwriter Report. Permitted values are "ALB" and "ANB".

Age Basis: The basis by reference to which the Insured's Attained Age is calculated; either ALB (Age Last Birthday) or ANB/ANBm (Age Nearest Birthday). ANB is the most common basis for Policies issued after 1995. ANB calculates the exact number of days from the reference date to the previous and next birthday and uses whichever is closest on that basis to determine age. For ANBm, if we are more than 6 months after the last birthday, use the next birthday as the age, otherwise use the previous one.

If you wish to create an Underwriters Report which is user-defined (i.e., not associated with any of the regular medical underwriters), you can set the Underwriter to "UserDefined". In this instance, you can also add a User-defined Description:

If you add a User-defined Description, this can be used to reference the Underwriters Report within a LE Selection and Blending Rule.

Health Status Contact

The Health Status Contact subsection can appear multiple times for each Insured, allowing you to upload several Health Status Contacts simultaneously. This subsection allows you to create or modify a CRM entity – either an Individual or an Organisation – and link that entity to an Insured as a Health Status Contact. This is copied across to the Servicing section on the Insured Lives tab.

  • Relation to Insured: A descriptive field with no prescribed values. This is copied across to the Relationship field for the Health Status Contact.

All other fields in this subsection are manipulated in the same way as the equivalent fields outlined under Understanding validation rules for adding or editing CRM entities above.

Healthcare Provider

The Healthcare Provider subsection can appear multiple times for each Insured, allowing you to upload several Healthcare Providers simultaneously. This subsection allows you to create or modify a CRM entity – either an Individual or an Organisation – and link that entity to an Insured as a Healthcare Provider. This is copied across to the Servicing section on the Insured Lives tab.

  • Specialization: A descriptive field with no prescribed values. is copied across to the same field for the CRM entity.

All other fields in this subsection are manipulated in the same way as the equivalent fields outlined under Understanding validation rules for adding or editing CRM entities above.

Insured Verification

The Insured Verification section can also appear multiple times for each Insured. It allows you to add data for different Insured Verification Types and to link that data to new or existing CRM entities.

  • Insured Verification Date: Required. The date associated with the Insured Verification.
  • Insured Verification Type: Required. The type of Insured Verification. Options are:
    • ContactDetails
    • Disability
    • Employment
    • HealthStatus
  • Contact Result: Required if the Insured Verification Type is set to ContactDetails. The disposition of the the contact with the Insured. Options are:
    • Attempted
    • Confirmed
  • Follow-Up: A date for follow-up with the Counterparty.
  • Counter Party: The name of the Counter Party.
  • Last Contact With Insured Value: last contact with insured, specified as a number.
  • Last Contact With Insured Lapse Type: last contact with insured relating to the Last Contact With Insured Value, days, weeks, months etc.
  • Notes: A free text field.
  • Medical Record From Date: A date as of which the medical record starts.
  • Medical Record To Date: A date as of which the medical record ends.
  • Change In Health: Boolean.
  • Change In Health Verification As Of Date. A date when a change in health is verified.
  • Change in Health Comment: A free text field.
  • Last Visit Date: A date of the last visit**.**
  • Employer Verification As Of Date: A date as of which the employment data is stated.
  • Employer: A free text field.
  • Works Payroll Number: A free text field.
  • Employment Status: Permitted values are:
    • Active
    • Inactive
    • Leave of Absence
    • Long Term Disability
    • Other
    • Retired
    • Short Term Disability
    • Terminated
  • Disability Verification As Of Date: A date as of which the Insured's disability status is verified.
  • Death Benefit: The amount of death benefit which is the subject of the disability claim.
  • Disability Expiry Date: The date as of which the Insured's disability status expires.
  • Proof of Disability Forms Required: Boolean.
  • Disability Forms Mailing Address: A free text field.
  • Information Provider: A free text field.
  • Address Different Explanation: A free text field allowing for notes to be made where the Counterparty has a different address on file for the Insured.

Counter Party

This is an optional subsection to the Insured Verification section. It follows the rules set out in Understanding validation rules for adding or editing CRM entities above. If a Counter Party is specified, it will link the Insured Verification object to a new or existing CRM entity.

Premium Schedule

This section is optional and can be repeated multiple times for each Case. It allows you to upload a calculated Premium Schedule for the Case.

The dates in column A must cover the full range of dates associated with all Cases that you are uploading – no date can be earlier than the earliest Start Date in the spreadsheet.

The dates in column A must follow the formatting rules for dates set out above. The values under each Case are the premium payments scheduled for that particular month and year (when importing a Premium Schedule, ClariNet LS assumes that the payment occurs on the monthiversary for the specified Policy – so the day of the month set in column A is ignored).

When entering Premium Schedules for several Cases simultaneously, it is highly likely that the different cashflows for each Case will start and finish on different dates. ClariNet LS ignores leading and trading zeros when importing a Premium Schedule.

  • Start Date: The date on which the Premium Schedule begins.
  • Pricing Date: The date on which the Premium Schedule was generated.
  • Premium Schedule Type: For Premium Schedules imported using the Excel template, this must be set to "Manual".

Policy Verification

This section is optional and can be repeated multiple times for each Case. It allows you to upload a Policy Verification for the Case. All data in this section is copied across to the Policy Verifications page for a Case.
  • Verification Date: A required date field.
  • Verification Type: The type of verification. Permitted Values are "AnnualStatement", "DisabilityPremiumWaiver", "GroupPolicyInsuranceVerification", "InitialData" and "VerificationOfCoverage".
  • Communication Type: How the information was communicated to you. Permitted values are "Unknown", "Verbal" and "Written".
  • Counterparty: An optional subsection, see note below.
  • CSR Name: A free text field for the name of the Customer Service Representative which provided the information (relevant for Verification Type = "Verbal").
  • Policy in Force: Boolean.
  • Policy in Grace: Boolean.
  • Account Information Date: The date as of which these values are stated:
    • Account Value
    • Surrender Charge
    • Cash Surrender Value
    • Face Amount
    • Loan Amount
    • Loan Interest Rate
    • Net Death Benefit
    • Death Benefit Type
    • Current Premium Mode
    • Total Premiums Paid to Date
    • Last Loan Payment Amount
    • Last Loan Payment Date
    • Last Premium Amount
    • Last Premium Date
    • Next Premium Amount
    • Next Premium Date
    • Paid Up Additions Purchased
    • Total Paid Up Additions
    • Annual Charges
    • Monthly COI
    • Annual COI
    • Next Year COI
    • Accumulated Dividends
    • Dividend Applied To Premium
    • Dividend Paid in Cash
    • COI Includes Fees Loads
    • COI Date
    • Current Crediting Rate
  • Death Benefit Type: Permitted values are "FacePlusSurrenderValue", "FacePlusAccountValue", "Level" or "ReturnOfPremium".
  • Current Premium Mode: Permitted values are as for Per Policy Charge Frequency in the Policy section.
  • COI Includes Fees Loads: Boolean.
  • COI Date: The date as of which the Current COI is stated.
  • Notes: A free text field.
  • Collateral Assignment in Effect: Boolean.
  • No Lapse Guarantee Rider: Boolean.
  • Maturity Rider Pays Past 100: Boolean.
  • If Survivorship, Primary Insured Deceased: Boolean.
  • If Survivorship, Secondary Insured Deceased: Boolean.
  • Liens Against Policy: Boolean.
  • Policy Past Contestability Period: Boolean.
  • Policy past Suicide Exclusion Period: Boolean.
  • Policy Ever Lapsed: Boolean.
  • Lapsed Policy Reinstatement Date: Date.
  • Mortality Charges: Boolean.
  • Designated Beneficiaries: A free text field.
  • Current Beneficiary Irrevocable: Boolean.
  • Irrevocable Beneficiary Assignee: A free text field.
  • Insurance Verification As Of Date: Date
  • Ported: Boolean.
  • Ported Date: Date.
  • Basic Insurance Company: A free text field.
  • Basic Policy Number:
  • Basic Premiums Paid: An optional subsection, see note below.
  • Basic Premiums Current: Boolean.
  • Basic Face Amount: A US Dollar amount.
  • Supplemental Premiums Current: Boolean.
  • Supplemental Policy Number: A free text field.
  • Supplemental Insurance Company: A free text field.
  • Supplemental Face Amount: A US Dollar amount.
  • Supplemental Premiums Paid By: An optional subsection, see note below.
  • Owner / Assignee: An optional subsection, see note below.
  • Beneficiary: An optional subsection, see note below.
  • Disability Premium Waiver
  • Premium Payment Status:
  • Premium Reimbursement Active: Boolean.
  • Next Renewal Date: Date.

Counter Party, Basic Premiums Paid By, Supplemental Premiums Paid By, Owner / Assignee, Beneficiary

These are optional subsections to the Policy Verification section.

Each follows the rules set out in Understanding validation rules for adding or editing CRM entities above. If a Counter Party is specified, it will link a Policy Verification object to a new or existing CRM entity; the other sections relate to new/existing CRM entities which can be attached as part of a Group Policy Insurance Verification.

Custom IRRs

This section is optional and can be repeated up to five times for each Case.

It allows you to specify Custom IRR values to be used when valuing a Portfolio of Cases with the Custom IRR option. The Name must match a Name set in the Custom IRR tab on your Pricing & Risk page under the Administration menu, otherwise the importer will show an error.

© 2025 ClearLife Limited. All Rights Reserved.