Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

AXIS Assignment #5 – Model and Reconcile Simplified VA Products with Guarantees

Requirements:

Part 1:

• I have provided a very simple VA basic policy cell without guarantees (“No Guarantee Cell”).

– Reconcile the following lines in the Calendar Year report (for Jan 2010 to Dec 2012 period): YTD retained earning, inv inc on CF and Reserve, inv inc adjustment.

– The cell I have provided is a general account policy. Go into the investment account, and change it to a separate account. Reconcile the same three CY report lines for the same time period.

Part 2:

• Code in the base cell for a simplified VA with GMAB and GMDB product (see product specs below), please submit the single “filtered” cell in a backup data zip file.

• Reconciliation/Calculation of AXIS results in Excel:

1- Look at Main illustration monthly report, reconcile the “Average Death Benefit” column.

2- Look at Calendar Year report:

a. Reconcile Net benefit-death row, only the 2016 and 2017 values. (Hint: you will need to use the average death benefit and fund value period end columns in the monthly main illustration report. Further hint: you should compare the average death benefit with average fund value.)

b. Reconcile the GMAB benefit at maturity (rounded to integer).

Part 3:

• I have provided a very simple VA with GMWB cell (“GMWB”). Please reconcile the GMWB benefit in the PY report (“Scheduled Withdrawal”). You will need to use information from the illustration (monthly) report and policy year report.

Final products:

(1) Single filtered cell (Part 2) in a backup data zip file

(2) Excel worksheets for reconciliation (three tabs, one for each Part).

Please only show information that need to be reconciled and/or relevant for reconciliation in your Excel sheets. Make your work products reader-friendly!!!

VA with GMAB+GMDB Base Cell (for Part 2):

Set Valuation date:

In the Dataset Parameters – Period and Dates tab, set the Valuation date as December 2009.

Set up a new future scenario:

Shape: 20 quarters and then annual. Scenario Format is “Fixed market only AE PAR”. On the Equity tab, put 7% for all rows in Column 1 (equity growth rate), except for 2015 and 2027. For these two years, put (-25%). Leave Column 2 (equity dividend rate) blank.

In the Dataset Parameters - Scenario tab, set the “Current future scenario” as the new scenario you just set up; and set “Future scenario start date” as “1 month since valuation date” (which should be January 2010).

Processing section:

- Pricing issue year: 2010, Pricing issue month: 1

- Volumes table – Inforce date is December 2009. New issues all in January 2010 and are worth $1 million in premium.

-  Timing of issue: Policy month coincides with financial month

Product Features:

o It is an “accumulation phase only” annuity.

o The policy is issued to 50 year olds. Accumulation Term is 15 years. Age mix by annualized premium. (you can put 1 unit in the age distribution table)

Product Features – Accumulation Phase:

o Set up an investment account: It is a separate account. Credited interest rate is linked to the scenario you just set up: 100% of equity total return rate.

o Dumping premium of $1000 in the first policy year.

o Commission is 2% dumping premium.

o The GMAB guarantee ensures the maturity value is the maximum of fund value and 100% guaranteed fund for maturity.

o The GMDB guarantee ensures the death benefit is the maximum of fund value and 100% guaranteed fund for death benefit.

o Set up a guarantee fund for maturity. The guarantee fund will grow 6% annually.

o Set up another guarantee fund for death benefits. The guarantee fund will grow 6% annually.

Pricing Assumptions (Projection assumptions):

o Decrement method: convert to multiple decrement rates monthly – UDD

Mortality

o The mortality rate is 100 per 1000.

Lapses

o 5%.

Interest Rate

o Credited interest rate in the investment account plus 50 basis points.

Expense

o $10 per policy per year.

Other:

For simplicity, ignore reserves and required surplus.