Summary Case Study

This is the pack of case study including the R Markdown code and the analysis results.
The pack can be accessed easily via a HTML link: https://rpubs.com/xiumei_wang/1118935.

Load library needed

library(tidyverse)  # A Collection of R packages designed for data science.
library(data.table) # An enhanced version of data.frame that allows for faster data operations
library(janitor)    # Examining and cleaning dirty data
library(lubridate)  # Dealing with dates and times
library(tictoc)     # Measuring execution time
tic()               # Start measuring execution time

Create R Project

knitr::include_graphics(
  "C:\\Users\\bewad\\OneDrive\\Desktop\\case study\\case study - folder structure.png"
  )

A R Project (Rproj) is a specific directory that RStudio recognizes as a project home. There are a lot of advantages to manage a project with “Rproj”.

Work with Raw Data

Load the raw data

# Import the data
table1 <- read.csv("C:\\Users\\bewad\\OneDrive\\Desktop\\case study\\01_raw_data\\table1.csv", sep = ";") |> 
  janitor::clean_names()

table2 <- read.csv("C:\\Users\\bewad\\OneDrive\\Desktop\\case study\\01_raw_data\\table2.csv", sep = ";") |> 
  janitor::clean_names()

table3 <- read.csv("C:\\Users\\bewad\\OneDrive\\Desktop\\case study\\01_raw_data\\table3.csv", sep = ";") |> 
  janitor::clean_names()

table4 <- read.csv("C:\\Users\\bewad\\OneDrive\\Desktop\\case study\\01_raw_data\\table4.csv", sep = ";") |> 
  janitor::clean_names()

Reply Questions from Case Study

Q1.

In table 1, see Client’s projected premium and claims Swiss Re (SR) will receive and pay. Using the spot yields from 30JUN2023 in Table 2, in code output the present value of the cashflows

# Merge the data
cf <- merge(x = table1, y = table2, by = "date", all.x = TRUE)

# Calc the present value by discounting the interest rate
# No interst rate from 30JUN2023 in table2, hence, used rate from 30JUN2024
pv <- cf |> 
  mutate(pvp = (1 / (1 + 0.05476)) ^ year_fraction_30_360 * sr_premium, 
         pvc = (1 / (1 + 0.05476)) ^ year_fraction_30_360 * sr_claims)

# sum the present value
pv |> 
  summarise(sum_pvp = sum(pvp, na.rm = TRUE),
            sum_pvc = sum(pvc, na.rm = TRUE))
##   sum_pvp sum_pvc
## 1  862753       0

Q2:

Show the impact of a +/-100bps interest rate stress

# Calculated the increase/decrease impact
pv_q2 <- pv |> 
  mutate(
    inc_1perc_imp_pvp = (1 / (1 + 0.05476 * 1.01)) ^ year_fraction_30_360 * sr_premium - pvp, 
    dec_1perc_imp_pvp = (1 / (1 + 0.05476 * 0.99)) ^ year_fraction_30_360 * sr_premium - pvp, 
    inc_1perc_imp_pvc = (1 / (1 + 0.05476 * 1.01)) ^ year_fraction_30_360 * sr_claims - pvc,
    dec_1perc_imp_pvc = (1 / (1 + 0.05476 * 0.99)) ^ year_fraction_30_360 * sr_claims - pvc
    )

# sum the present value
pv_q2 |> 
  summarise(sum_inc_1perc_imp_pvp = sum(inc_1perc_imp_pvp, na.rm = TRUE),
            sum_dec_1perc_imp_pvp = sum(dec_1perc_imp_pvp, na.rm = TRUE),
            sum_inc_1perc_imp_pvc = sum(inc_1perc_imp_pvc, na.rm = TRUE),
            sum_dec_1perc_imp_pvc = sum(dec_1perc_imp_pvc, na.rm = TRUE)
            )
##   sum_inc_1perc_imp_pvp sum_dec_1perc_imp_pvp sum_inc_1perc_imp_pvc
## 1             -2740.522              2753.846                     0
##   sum_dec_1perc_imp_pvc
## 1                     0

Q3:

Introduce an annual flat lapse rate of 6% into the model – the initial cashflows provided are already post the inclusion of a 6% lapse – there should be no impact to the present value of the cashflows as an output from step 1

# Add the lapse rate to the model
pv_q3 <- pv_q2 |> 
  mutate(
    lps_rt = 0.06
  )

Q4:

Generate the impact on the present value of cashflows of a 40% increase in lapse (40% x 6% = 8.4%)

# Calculate the lapse change impact on PV 
pv_q4 <- pv_q3 |> 
  mutate(
    inc_lps_rt = 0.084, 
    pvp_lap_inc_imp = ((1 - inc_lps_rt) * (1 / (1 + 0.05476))) ^ year_fraction_30_360 * sr_premium - pvp,
    pvc_lap_inc_imp = ((1 - inc_lps_rt) * (1 / (1 + 0.05476))) ^ year_fraction_30_360 * sr_claims - pvc,
  )

# sum the present value
pv_q4 |> 
  summarise(pvp_lap_inc_imp = sum(pvp_lap_inc_imp, na.rm = TRUE),
            pvc_lap_inc_imp = sum(pvc_lap_inc_imp, na.rm = TRUE))
##   pvp_lap_inc_imp pvc_lap_inc_imp
## 1       -333162.2               0

Q5:

The Client issues debt with a yield of 6%, the associated risk-free rate is 4% for the same maturity. Introduce a risk premium for any credit risk into the model. This will change the output from step 1

pv_q5 <- pv_q4 |> 
  mutate(
    debt_rt = 0.06
  )

Q6:

The Client informs you that the projected premium they provided in step 1 is driven by SR receiving a 1% charge of the policyholders current account value at the end of that year. You are told the account value will follow 50% of S&P 500 equity index and 50% of US Treasury bond fund. a. Table 3 shows the Client’s past values for these funds and future projections. b. Table 4 shows a past history of performance of these 2 total return funds. c. In code, what is the impact to the present value of cashflows from a 50% fall in equities. [Optional to decide whether want to introduce a correlation parameter for equity/rates]

# Merge table 3 and table 4 to data cf
pv_q6 <- merge(x = cf, y = table3, by = "date", all.x = TRUE)
pv_q6 <- merge(x = pv_q6, y = table4, by = "date", all.x = TRUE)

# My thoughts:
# If 
#  50% fall in equity, then the account value cashflow will be also falling by 1% * 50%. 
#  Then the the present value of the policyholders current account value will be also falling depending on the discount interest rate.

Q7:

How much should SR be willing to pay to the client today to enter into this transaction?

SR should pay less than present value of the policyholders current account value at the end of the period where the tansaction is happening. Other expenses are excluded from here.

Q8:

  1. 1% higher interest rates leads to a 1% increases lapse, introduce this feature into the modelling, show the impact of a +/-100bps interest rate stress
pv_q8a <- pv_q4 |> 
  mutate(
    ir_chg_rt = 0.01, 
    lps_chg_rt = ir_chg_rt , 
    pvp_lap_inc_imp = ((1 - inc_lps_rt * (1 + lps_chg_rt)) * (1 / (1 + 0.05476 * (1 + ir_chg_rt)))) ^ year_fraction_30_360 * sr_premium - pvp,
    pvc_lap_inc_imp = ((1 - inc_lps_rt * (1 + lps_chg_rt)) * (1 / (1 + 0.05476 * (1 + ir_chg_rt)))) ^ year_fraction_30_360 * sr_claims - pvc,
  )

# sum the present value
pv_q8a |> 
  summarise(pvp_lap_inc_imp = sum(pvp_lap_inc_imp, na.rm = TRUE),
            pvc_lap_inc_imp = sum(pvc_lap_inc_imp, na.rm = TRUE))
##   pvp_lap_inc_imp pvc_lap_inc_imp
## 1       -336960.3               0
toc() # End of the execution time
## 0.3 sec elapsed

Thank you

Thank you Lisa and Rob for preparing and organizing the study case. I have thoroughly enjoyed the scripting excercise process.