Modul1
unknown
vbscript
2 years ago
104 kB
9
Indexable
Sub Read_Data()
file$ = Path_inf(test_NUM(0))
Erase NP, LL, GP_0, P6, H6, PA6, P16, P26, V6, VV, CV, SIA, SIB, Pay_AN, SIA_RC, SIA_PUA, SIA_RC_PUA, PU, RPU_CV, AXP6, AXP, Pay_HB, A_HB, Pay_Accident
For MF = 1 To 2
If MF = 1 Then SEX$ = "M" Else SEX$ = "F"
For NN = 1 To 20
If NN_inf(test_NUM(0), NN) <> 0 Then
Open file$ + "\P-" + SEX$ & NN For Input As #1
Open file$ + "\V-" + SEX$ & NN For Input As #2
Open file$ + "\SIA-" + SEX$ & NN For Input As #3
Open file$ + "\PAY-" + SEX$ & NN For Input As #4
Open file$ + "\SIC-" + SEX$ & NN For Input As #5
Open file$ + "\U-" + SEX$ & NN For Input As #6
Open file$ + "\AXP-" + SEX$ & NN For Input As #7
On Error Resume Next
If Index_HB_inf(test_NUM(0)) = "Y" Then Open file$ + "\HB-" + SEX$ & NN For Input As #8
If Index_HB_inf(test_NUM(0)) = "Y" Then Open file$ + "\VL-" + SEX$ & NN For Input As #9
If Index_HB_inf(test_NUM(0)) = "Y" Then Open file$ + "\AXPL-" + SEX$ & NN For Input As #10
Do Until EOF(1): Input #1, XX: Input #1, SP, LS, SG, NP(MF, NN, XX), LL(MF, NN, XX), GP_0(MF, NN, XX), h, Pa, P1, P2, SP6, P6(MF, NN, XX), H6(MF, NN, XX), PA6(MF, NN, XX), P16(MF, NN, XX), P26(MF, NN, XX), SPX, SP6X, V_DEF0: Loop
Do Until EOF(2): Input #2, XX, t: Input #2, SV6, SV, V6(MF, NN, XX, t), VV(MF, NN, XX, t), CV(MF, NN, XX, t): Loop
Do Until EOF(3): Input #3, XX, t: Input #3, SIA(MF, NN, XX, t), SIB(MF, NN, XX, t): Loop
Do Until EOF(4): Input #4, XX, t: Input #4, Pay_AN(MF, NN, XX, t): Loop
If Data_inf(test_NUM(0)) = 1 Then
Do Until EOF(5): Input #5, XX, t: Input #5, SIA_RC(MF, NN, XX, t), SIB(MF, NN, XX, t): Loop
ElseIf Data_inf(test_NUM(0)) = 2 Then
Do Until EOF(5): Input #5, XX, t: Input #5, SIA_RC(MF, NN, XX, t), SIB(MF, NN, XX, t), SIA_RC_PUA(MF, NN, XX, t), SIA_PUA(MF, CC, AGE, t): Loop
Else
Do Until EOF(5): Input #5, XX, t: Input #5, SIA_RC(MF, NN, XX, t), SIA_RC_PUA(MF, NN, XX, t), SIA_PUA(MF, CC, AGE, t): Loop
End If
Do Until EOF(6): Input #6, XX, t: Input #6, PU(MF, NN, XX, t), RPU_CV(MF, NN, XX, t), ET_YEAR, ET_DAY, ET_AN: Loop
Do Until EOF(7): Input #7, XX, t: Input #7, AXP6(MF, NN, XX, t), AXP(MF, NN, XX, t): Loop
If Index_HB_inf(test_NUM(0)) = "Y" Then Do Until EOF(8): Input #8, XX, t: Input #8, Pay_HB(MF, NN, XX, t), A_HB(MF, NN, XX, t), Pay_Accident(MF, NN, XX, t): Loop: Close #8
If Index_HB_inf(test_NUM(0)) = "Y" Then Do Until EOF(9): Input #9, XX, t: Input #9, SVL6, SVL, VL6(MF, NN, XX, t), VL(MF, NN, XX, t): Loop: Close #9
If Index_HB_inf(test_NUM(0)) = "Y" Then Do Until EOF(10): Input #10, XX, t: Input #10, AXP6(MF, NN, XX, t), AXP(MF, NN, XX, t): Loop: Close #10
Close #1, #2, #3, #4, #5, #6, #7
End If
Next NN
Next MF
End Sub
Sub Assumption()
test_type = Sheets("Setup").Cells(7, 3)
Erase Px, Qs, Qx, Qw, Exp_Var, Exp_Fix, Exp_CSMVar, Exp_CSMFix, EXP_test_Var2, EXP_test_Var3, EXP_test_Fix1, EXP_test_Fix2, TPR
MF = test_MF(0): CH = test_CH(0): AGE = test_Age(0): NN = test_NN(0): num = test_NUM(0)
Px(0) = 1: Qs(1) = 1
WW = DUR_inf(num) - AGE
For t = 1 To WW
'死亡率
If Index_小額_inf(num) = "Y" And Sheets("Setup").Cells(7, 3) <> 2 And AGE + t <= 85 And t <= 20 Then
Q1 = Sheets("Assumption").Cells(5, 5) * 1.1
Else
Q1 = Sheets("Assumption").Cells(5, 5)
End If
Q3 = Sheets("Assumption").Cells(6, 5)
If test_type = 3 Then Q2 = Range("Qx_Q2")(MF, min(t, 10)) Else Q2 = Range("Qx_Q2")(MF + 2, min(t, 10))
If Index_小額_inf(num) = "Y" And Sheets("Setup").Cells(7, 3) <> 2 And AGE + t > 85 Then Q2 = 1
If index_BEY(0) = 1 Then '損益兩平投報率無死亡
Qx(t) = 0
Else
If Name_inf(num) = "中國人壽友保障終身壽險" Then
Qx(t) = Range("TSO_友保障")(AGE + t, MF) * Q1 * Q2 * (1 - Q3) ^ t * (1 + Mortality_Sen(0))
Else
Qx(t) = Range("TSO_2021")(AGE + t, MF) * Q1 * Q2 * (1 - Q3) ^ t * (1 + Mortality_Sen(0))
End If
End If
Px(t) = Px(t - 1) * (1 - Qx(t))
'脫退率
Select Case NN
Case 1: cex = 1
Case 2, 3: cex = 5
Case 4: cex = 9
Case 5, 6: If CUR_inf(num) < 2 Then cex = 13 Else cex = 17
Case Else: cex = 21
End Select
If Index_小額_inf(num) = "Y" Then cex = 21 '小額脫退採長年期
If test_type = 1 Or test_type = 4 Then
cex = cex
ElseIf test_type = 2 Then
If PT2_inf(num) <> "還本型保險" Then cex = cex + 1 Else cex = cex + 2
Else
cex = cex + 3
End If
If t < WW Then Qw(t) = Range("Qw")(cex, min(t, 11)) Else Qw(t) = Range("Qw")(cex, 12)
If t = Lapse_year_Sen(0) Then
Qw(t) = Lapse_rate_Sen(0)
Else
If Lapse_rate_Sen(0) = 1 And Sheets("Total Lapse").Cells(4, 9) = "皆為零" Then
Qw(t) = 0
Else
Qw(t) = Qw(t) * (1 + Lapse_Sen(0))
End If
End If
If index_BEY(0) = 1 Then Qw(t) = 0 '
'費用
If test_type = 4 Then test_type2 = 1 Else test_type2 = test_type
If CUR_inf(num) = 1 Then ex_rate = 1 Else ex_rate = Sheets("Assumption").Cells(69, 3) '匯率
exp_var1 = Sheets("Assumption").Cells(72 + Int(test_type2 / 3) * 2 + min(NN, 2), 3 + CH) '通路費用
If NN > 1 And CUR_inf(num) = 1 Then '收費成本
exp_var2 = Sheets("Assumption").Cells(82 + Int(test_type2 / 3) * 2, 3 + CH)
Else
If t <= NN Then exp_fix1 = Sheets("Assumption").Cells(81 + Int(test_type2 / 3) * 2, 4 + Int(CUR_inf(num) / 2) * 5) Else exp_fix1 = 0
End If
If CH = 5 Then R_EC = Sheets("Assumption").Cells(114, 5) Else R_EC = 1 '經常性費用
If PT1_inf(num) = "傳統型" Then cex = 0 Else cex = 2
exp_var3 = Sheets("Assumption").Cells(88 + min(NN, 10) + Int(test_type2 / 3) * 10, 4 + min(t, 2) + cex) * R_EC
exp_fix2 = Sheets("Assumption").Cells(108 + min(NN, 2) + Int(test_type2 / 3) * 2, 4 + min(t, 2) + cex) * R_EC
Exp_Var(t) = (exp_var1 + exp_var2 + exp_var3) * (1 + Exp_Sen(0))
Exp_Fix(t) = (exp_fix1 + exp_fix2) / ex_rate * (1 + Exp_Sen(0))
If t = 1 Then
exp_csmvar3 = Sheets("Assumption").Cells(88 + min(NN, 10) + Int(test_type2 / 3) * 10, 4 + min(t, 2) + cex) * R_EC
exp_csmfix2 = Sheets("Assumption").Cells(108 + min(NN, 2) + Int(test_type2 / 3) * 2, 4 + min(t, 2) + cex) * R_E
exp_RAvar3 = 0
exp_RAfix2 = 0
Else
exp_csmvar3 = Sheets("Assumption").Cells(88 + min(NN, 10) + Int(test_type2 / 3) * 10, 4 + min(t, 2) + cex) * R_EC * 0.37
exp_csmfix2 = Sheets("Assumption").Cells(108 + min(NN, 2) + Int(test_type2 / 3) * 2, 4 + min(t, 2) + cex) * R_E * 0.37
exp_RAvar3 = Sheets("Assumption").Cells(88 + min(NN, 10) + Int(test_type2 / 3) * 10, 4 + min(t, 2) + cex) * R_EC * 0.37
exp_RAfix2 = Sheets("Assumption").Cells(108 + min(NN, 2) + Int(test_type2 / 3) * 2, 4 + min(t, 2) + cex) * R_E * 0.37
Exp_CSMVar(t) = (exp_var1 + exp_var2 * 0.37 + exp_csmvar3) * (1 + Exp_Sen(0))
Exp_CSMFix(t) = (exp_fix1 * 0.37 + exp_csmfix2) / ex_rate * (1 + Exp_Sen(0))
Exp_RAVar(t) = (exp_var1 * 0 + exp_var2 * 0.37 + exp_RAvar3) * (1 + Exp_Sen(0))
Exp_RAFix(t) = (exp_fix1 * 0.37 + exp_RAfix2) / ex_rate * (1 + Exp_Sen(0))
End If
If Name_inf(num) = "中國人壽全民小額終身壽險" Or Name_inf(test_NUM(0)) = "中國人壽公教小額終身壽險" Then
Exp_Var(t) = Exp_Var(t) * 0.7
Exp_Fix(t) = Exp_Fix(t) * 0.7
End If
If t = 1 And Name_inf(num) = "中國人壽醫生守富美元利率變動型終身壽險－定期給付型" Then Exp_Var(t) = Exp_Var(t) + 0.07 'VIP成本
'費用適足性檢測
If NN > 1 And CUR_inf(num) = 1 Then exp_var2 = Sheets("Assumption").Cells(157, 4)
If NN = 1 Or CUR_inf(num) <> 1 Then
If t <= NN Then exp_fix1 = Sheets("Assumption").Cells(156, 3 + CUR_inf(num)) Else exp_fix1 = 0
End If
exp_var3 = Sheets("Assumption").Cells(161 + min(NN, 10), 3 + min(t, 2) + cex)
exp_fix2 = Sheets("Assumption").Cells(171 + min(NN, 2), 3 + min(t, 2) + cex)
EXP_test_Var2(t) = (exp_var2) * (1 + Exp_Sen(0))
EXP_test_Var3(t) = (exp_var3) * (1 + Exp_Sen(0))
EXP_test_Fix1(t) = exp_fix1 / ex_rate * (1 + Exp_Sen(0))
EXP_test_Fix2(t) = exp_fix2 / ex_rate * (1 + Exp_Sen(0))
Next t
For t = 1 To 110
'利率
If index_BEY(0) = 1 Then
TPR(t) = BEY_Invest(0)
Else
If test_type = 4 Then
TPR(t) = Range("TPR2")(Int(CUR_inf(num) / 2) + 1, min(t, 20)) + Interest_Sen(0)
Else
TPR(t) = Range("TPR")(Int(CUR_inf(num) / 2) + 1, min(t, 20)) + Interest_Sen(0)
End If
End If
Next t
End Sub
Sub Commission()
CH = test_CH(0): NN = test_NN(0): num = test_NUM(0)
Erase COMM_rate, COMM_rate_exp1, COMM_rate_exp2, COMM_rate_exp3, COMM_rate_exp4, COMM_rate_exp5, COMM_rate_test, COMM_CSM
For t = 1 To NN
'佣金率
COMM1 = COMM1_inf(num, NN, CH, t)
'獎%P、獎%C
COMM2 = COMM2_inf(num, NN, CH, t)
COMM3 = COMM3_inf(num, NN, CH, t)
'續年度服務津貼、繼續率獎金
COMM4 = COMM4_inf(num, NN, CH, t)
If Index_繼續率_inf(num, NN, CH) = "Y" Then
Select Case NN
Case 1 To 5: R = 0
Case 6 To 7: R = Sheets("Assumption").Cells(199, 4)
Case 8 To 9: R = Sheets("Assumption").Cells(199, 5)
Case 10 To 12: R = Sheets("Assumption").Cells(199, 6)
Case 13 To 14: R = Sheets("Assumption").Cells(199, 7)
Case 15 To 17: R = Sheets("Assumption").Cells(199, 8)
Case 18 To 19: R = Sheets("Assumption").Cells(199, 9)
Case 20: R = Sheets("Assumption").Cells(199, 10)
End Select
If t = 2 Then
COMM5 = Sheets("Assumption").Cells(196, 5) * R
ElseIf t = 3 Then
COMM5 = Sheets("Assumption").Cells(196, 6) * R
Else
COMM5 = 0
End If
End If
'年終、營運
If t = 1 And NN >= 6 And Index_年終_inf(num, NN, CH) = "Y" Then COMM6 = Sheets("Assumption").Cells(204, 3) Else COMM6 = 0
If t = 1 And NN >= 6 And Index_營運_inf(num, NN, CH) = "Y" Then COMM7 = Sheets("Assumption").Cells(205, 3) Else COMM7 = 0
'勞保成本
COMM8 = Sheets("Assumption").Cells(210, 3 + min(t, 11))
'OR
Dim COMM_OR(120)
'If t = 1 Then COMM_OR(t) = 2.23 Else COMM_OR(t) = 1.24
If t = 1 Then COMM_OR(t) = 2.21 Else COMM_OR(t) = 1.16
'佣金(Profit Test)
If CH = 1 Then
COMM_rate(t) = COMM1 * COMM_OR(t) + COMM2 + COMM1 * (COMM3 + COMM8)
ElseIf CH = 2 Then
COMM_rate(t) = COMM1 * (1 + COMM3 + COMM6 + COMM7) + COMM2 + COMM4 + COMM5
Else
COMM_rate(t) = COMM1 * (1 + COMM3) + COMM2
End If
'佣金(費差)
COMM_rate_exp1(t) = COMM1
COMM_rate_exp2(t) = COMM2
COMM_rate_exp3(t) = COMM3 * COMM1
If CH = 1 Then
If t = 1 Then COMM_rate_exp4(t) = COMM1 * 1.02 Else COMM_rate_exp4(t) = COMM1 * 0.02
ElseIf CH = 2 Then
COMM_rate_exp4(t) = COMM4 + COMM5
COMM_rate_exp5(t) = (COMM6 + COMM7) * COMM1
End If
'佣金(CSM)
If CH = 1 Then
If t = 1 Then COMM_CSM(t) = COMM1 * COMM_OR(t) + COMM2 + COMM1 * (COMM3 + COMM8) Else COMM_CSM(t) = COMM1 * (COMM_OR(t) - 1) * 0.37 + COMM1 + COMM2 * 0.37 + COMM1 * (COMM3 + COMM8) * 0.37
ElseIf CH = 2 Then
If t = 1 Then COMM_CSM(t) = COMM1 * (1 + COMM3 + COMM6 + COMM7) + COMM2 + COMM4 + COMM5 Else COMM_CSM(t) = COMM1 * (COMM3 + COMM6 + COMM7) * 0.37 + COMM1 + COMM2 * 0.37 + COMM4 * 0.37 + COMM5 * 0.37
Else
If t = 1 Then COMM_CSM(t) = COMM1 * (1 + COMM3) + COMM2 Else COMM_CSM(t) = COMM1 * COMM3 * 0.37 + COMM1 + COMM2 * 0.37
End If
'佣金(RA)
If CH = 1 Then
If t = 1 Then COMM_RA(t) = 0 Else COMM_RA(t) = COMM1 * (COMM_OR(t) - 1) * 0.37 + COMM1 + COMM2 * 0.37 + COMM1 * (COMM3 + COMM8) * 0.37
ElseIf CH = 2 Then
If t = 1 Then COMM_RA(t) = 0 Else COMM_RA(t) = COMM1 * (COMM3 + COMM6 + COMM7) * 0.37 + COMM1 + COMM2 * 0.37 + COMM4 * 0.37 + COMM5 * 0.37
Else
If t = 1 Then COMM_RA(t) = 0 Else COMM_RA(t) = COMM1 * COMM3 * 0.37 + COMM1 + COMM2 * 0.37
End If
'佣金(Commission test)
If CH = 1 Then
COMM_rate_test(t) = COMM_rate(t) - COMM1 * COMM8 '無勞保成本
Else
COMM_rate_test(t) = COMM_rate(t)
End If
Next t
End Sub
Sub CSM_Test_Total()
Clear_CSM
Clear_Cashflow_CSM
Erase Out_table_CSM, AEFF, GE_table, Profit_RBC_table, disGE_CSM_table
Calculate
test_CSM(0) = 1
Dim MP As Range
If Sheets("Setup").Range("X7") = "" Then Set MP = Sheets("Setup").Range("X6:AD6") Else Set MP = Sheets("Setup").Range("X6", Sheets("Setup").Range("AD6").End(xlDown))
Read_Inf
For i = 1 To MP.Rows.Count
If MP(i, 2).Interior.Color <> 14277081 Then
test_NUM(0) = MP(i, 1): test_NN(0) = MP(i, 3): test_Age(0) = MP(i, 4)
test_MF(0) = MP(i, 5): test_CH(0) = MP(i, 7): test_dis(0) = MP(i, 13)
If CAL_inf(test_NUM(0)) = "保額" Then test_SA(0) = MP(i, 6) Else test_GP(0) = MP(i, 6)
If test_NUM(0) <> MP(i - 1, 1) Then Read_Data
Read_CSM
CSM_Test
If Sheets("Setup").Cells(8, 3) = 1 Then test_Weight(0) = 1 Else test_Weight(0) = MP(i, 8) * Sheets("Setup").Cells(9, 8) / GE(1)
If Sheets("Setup").Cells(14, 8) = "Y" Then
Cashflow_CSM
If test_MF(0) = 1 Then MF = "M" Else MF = "F"
Select Case test_CH(0)
Case 1: CH = "AG": Case 2: CH = "BR": Case 3: CH = "BK": Case 4: CH = "FS": Case 5: CH = "EC"
End Select
If Sheets("Cashflow(CSM)").Cells(2, 5) = "" Or Sheets("Cashflow(CSM)").Cells(2, 5) = test_Age(0) Then Sheets("Cashflow(CSM)").Cells(2, 5) = test_Age(0) Else Sheets("Cashflow(CSM)").Cells(2, 5) = "Total"
If Sheets("Cashflow(CSM)").Cells(2, 7) = "" Or Sheets("Cashflow(CSM)").Cells(2, 7) = test_NN(0) Then Sheets("Cashflow(CSM)").Cells(2, 7) = test_NN(0) Else Sheets("Cashflow(CSM)").Cells(2, 7) = "Total"
If Sheets("Cashflow(CSM)").Cells(2, 11) = "" Or Sheets("Cashflow(CSM)").Cells(2, 11) = test_GP(0) * 10000 Then Sheets("Cashflow(CSM)").Cells(2, 11) = test_GP(0) * 10000 Else Sheets("Cashflow(CSM)").Cells(2, 11) = "Total"
If Sheets("Cashflow(CSM)").Cells(2, 13) = "" Or Sheets("Cashflow(CSM)").Cells(2, 13) = MF Then Sheets("Cashflow(CSM)").Cells(2, 13) = MF Else Sheets("Cashflow(CSM)").Cells(2, 13) = "Total"
If Sheets("Cashflow(CSM)").Cells(2, 15) = "" Or Sheets("Cashflow(CSM)").Cells(2, 15) = CH Then Sheets("Cashflow(CSM)").Cells(2, 15) = CH Else Sheets("Cashflow(CSM)").Cells(2, 15) = "Total"
If Sheets("CSM").Cells(4, 3) = "" Or Sheets("CSM").Cells(4, 3) = test_NN(0) Then Sheets("CSM").Cells(4, 3) = test_NN(0) Else Sheets("CSM").Cells(4, 3) = "Total"
End If
WW = DUR_inf(test_NUM(0)) - test_Age(0)
MP(i, 12) = -(BEL(0) + RA(0)) / disCF(GE(), WW, , DIS_CSM(), 1)
End If
Next i
If Sheets("Setup").Cells(14, 8) = "Y" Then Summary_Total(0) = 1
Cashflow_CSM
Summary_Total(0) = 0
Erase test_CSM
End Sub
Sub Read_Inf()
Calculate
'基本資料
Dim inf As Range
If Sheets("data").Range("F7") = "" Then Set inf = Sheets("data").Range("F6:F6") Else Set inf = Sheets("data").Range("F6", Sheets("data").Range("F6").End(xlDown))
For i = 1 To inf.Rows.Count
num = inf(i, 2)
Name_inf(num) = inf(i, 3)
DIR_inf(num) = inf(i, 4)
CUR_inf(num) = inf(i, 5)
PT1_inf(num) = inf(i, 6)
PT2_inf(num) = inf(i, 7)
DUR_inf(num) = inf(i, 8)
Index_WP_inf(num) = inf(i, 9)
Index_HB_inf(num) = inf(i, 10)
Index_RGE_inf(num) = inf(i, 11)
Index_小額_inf(num) = inf(i, 12)
CAL_inf(num) = inf(i, 13)
Path_inf(num) = inf(i, 14)
Data_inf(num) = inf(i, 15)
Data2_inf(num) = inf(i, 16)
S2_max_inf(num) = inf(i, 17)
S2_min_inf(num) = inf(i, 18)
Dir_min_inf(num) = inf(i, 19)
Rein_inf(num) = inf(i, 20)
Retention_inf(num) = inf(i, 21)
Rein_TSO(num) = inf(i, 22)
Rein_rate(num, 1) = inf(i, 23)
Rein_rate(num, 2) = inf(i, 24)
Rein_rate(num, 3) = inf(i, 25)
Rein_P(num) = inf(i, 119) '新增利潤率
Rein_E(num) = inf(i, 120) '新增費用率
Rein_B1(num) = inf(i, 121) '新增B1
Rein_B2(num) = inf(i, 122) '新增B2
Rein_B3(num) = inf(i, 123) '新增B3
Rein_B4(num) = inf(i, 124) '新增B4
Next i
X1 = "AG": X2 = "AH"
If Sheets("data").Range(X2 & 7) = "" Then Set inf = Sheets("data").Range(X1 & 6) Else Set inf = Sheets("data").Range(X1 & 6, Sheets("data").Range(X2 & 6).End(xlDown))
For i = 1 To inf.Rows.Count
num = inf(i, 1)
NN = inf(i, 3)
NN_inf(num, NN) = inf(i, 4)
dis_num1 = inf(i, 5)
dis_num2_inf(num, NN, dis_num1) = inf(i, 6)
AIR_inf(num, NN) = inf(i, 7)
VI6_inf(num, NN) = 1 / (1 + inf(i, 8))
dis1_inf(num, NN, dis_num1) = inf(i, 9)
dis_SA_inf(num, NN, dis_num1) = inf(i, 10)
dis2_inf(num, NN, dis_num1) = inf(i, 11)
Next i
'Commission
X1 = "BB": X2 = "BC"
If Sheets("data").Range(X2 & 7) = "" Then Set inf = Sheets("data").Range(X1 & 6) Else Set inf = Sheets("data").Range(X1 & 6, Sheets("data").Range(X2 & 6).End(xlDown))
For i = 1 To inf.Rows.Count
num = inf(i, 1)
CH = inf(i, 3)
NN = inf(i, 4)
Index_年終_inf(num, NN, CH) = inf(i, 5)
Index_營運_inf(num, NN, CH) = inf(i, 6)
Index_繼續率_inf(num, NN, CH) = inf(i, 7)
For t = 1 To NN
'佣金率
COMM1_inf(num, NN, CH, t) = inf(i, 7 + min(t, 11))
'續年度
COMM4_inf(num, NN, CH, t) = inf(i, 18 + min(t, 11))
'獎%P、獎%C
If t = 1 Then
COMM2_inf(num, NN, CH, t) = inf(i, 30)
COMM3_inf(num, NN, CH, t) = inf(i, 33)
ElseIf t <= 5 Then
COMM2_inf(num, NN, CH, t) = inf(i, 31)
COMM3_inf(num, NN, CH, t) = inf(i, 34)
Else
COMM2_inf(num, NN, CH, t) = inf(i, 32)
COMM3_inf(num, NN, CH, t) = inf(i, 35)
End If
Next t
Next i
'CVrate
X1 = "CU": X2 = "CV"
If Sheets("data").Range(X2 & 7) = "" Then Set inf = Sheets("data").Range(X1 & 6) Else Set inf = Sheets("data").Range(X1 & 6, Sheets("data").Range(X2 & 6).End(xlDown))
For i = 1 To inf.Rows.Count
num = inf(i, 1)
NN = inf(i, 3)
For t = 1 To 120
CVrate_inf(num, NN, t) = inf(i, 3 + min(t, 20))
Rein_BM(num, t) = inf(i, 3 + min(t, 30))
Next t
Next i
End Sub
Sub Read_CSM()
'Erase Dir_CSM, Dir_報部
'CSM 1000投報率、折現率
'Read_Inf
CSM_month = Sheets("Setup").Cells(16, 4)
Workbook1 = ThisWorkbook.Name
If Sheets("Setup").Cells(17, 4) = "商發" Then file$ = "\\tpact01\Production2\PD\商發公用\1.傳統型商品科(暫存區)\Product\CSM_1000組情境\NEW\" Else file$ = "\\tpact01\Production2\PD\商發公用\1.傳統型商品科(暫存區)\Product\CSM_1000組情境\精算\"
If Sheets("Setup").Cells(14, 4) = "HW" Then
Workbook2 = "2.千組投報率及折現率_HW_" & CSM_month & "(值)" & ".xlsx"
Workbooks.Open FileName:=file$ & CSM_month & "\2.千組投報率及折現率_HW_" & CSM_month & "(值)" & ".xlsx"
Windows(Workbook2).Activate
For Scenario = 1 To 1000
For t = 1 To 110
INV_1000(1, Scenario, t) = Sheets("投報率_台幣利變壽").Cells(1 + Scenario, 1 + t) '台幣
INV_1000(2, Scenario, t) = Sheets("投報率_美元利變壽").Cells(1 + Scenario, 1 + t) '美元
DIS_1000(1, Scenario, t) = Sheets("折現率_台幣利變壽").Cells(1 + Scenario, 1 + t) '台幣
DIS_1000(2, Scenario, t) = Sheets("折現率_美元利變壽").Cells(1 + Scenario, 1 + t) '美元
'********************************** 112.11.24調整宣告利率 **********************************
If t = 1 Then Dir(0) = DIR_inf(test_NUM(0)): Dir_CSM(Scenario, t) = Dir(0)
Rein_W(CUR_inf(test_NUM(0)), Scenario, t) = Rein_B1(test_NUM(0)) * (INV_1000(CUR_inf(test_NUM(0)), Scenario, t) - INV_1000(CUR_inf(test_NUM(0)), Scenario, t - 1)) + Rein_B2(test_NUM(0)) * (Dir_CSM(Scenario, t) - Rein_BM(test_NUM(0), t))
Rein_ACC_W(CUR_inf(test_NUM(0)), Scenario, t) = Rein_ACC_W(CUR_inf(test_NUM(0)), Scenario, t - 1) + Rein_W(CUR_inf(test_NUM(0)), Scenario, t)
Dir_CSM(Scenario, t + 1) = Application.max(0, INV_1000(CUR_inf(test_NUM(0)), Scenario, t) - Rein_P(test_NUM(0)) - Rein_E(test_NUM(0)) - Rein_W(CUR_inf(test_NUM(0)), Scenario, t))
'*******************************************************************************************
' A_INV(t) = A_INV(t) + INV_1000(CUR_inf(test_NUM(0)), Scenario, t) / ss
' A_DIS_CSM(t) = A_DIS_CSM(t) + DIS_1000(CUR_inf(test_NUM(0)), Scenario, t) / ss
' A_DIR(t) = A_DIR(t) + Dir_CSM(Scenario, t) / ss
Next t
Next Scenario
ActiveWorkbook.Close (False)
Windows(Workbook1).Activate
End If
Workbook2 = "1.無風險利率產出_" & CSM_month & "(值)" & ".xlsx"
Workbooks.Open FileName:=file$ & CSM_month & "\1.無風險利率產出_" & CSM_month & "(值)" & ".xlsx"
Windows(Workbook2).Activate
For t = 1 To 110
DIS_CSM_CUR(1, t) = Sheets("各幣別無風險利率").Cells(4 + t, 3) '台幣
DIS_CSM_CUR(2, t) = Sheets("各幣別無風險利率").Cells(4 + t, 6) '美元
Next t
ActiveWorkbook.Close (False)
Windows(Workbook1).Activate
End Sub
Sub Profit_Test_Total()
Erase NP_dis, GE_dis, VD_dis, Cost_dis, RBC_dis, Profit_dis, Profit_Tax_dis, Profit_RBC_dis, AProfit_RBC_dis, AProfit_Tax_dis, Dead_P_dis, INT_P_dis, EXP_P_dis, Lapse_P_dis, Other_P_RBC_dis
Erase NP_NN, GE_NN, VD_NN, Cost_NN, RBC_NN, Profit_NN, Profit_Tax_NN, Profit_RBC_NN, AProfit_RBC_NN, AProfit_Tax_NN, Dead_P_NN, INT_P_NN, EXP_P_NN, Lapse_P_NN, Other_P_RBC_NN
Erase NP_Total, GE_Total, VD_Total, Cost_Total, RBC_Total, Profit_Total, Profit_Tax_Total, Profit_RBC_Total, AProfit_RBC_Total, AProfit_Tax_Total, Dead_P_Total, INT_P_Total, EXP_P_Total, Lapse_P_Total, Other_P_RBC_Total
Erase GE_競品, RBC_競品, Profit_Tax_競品
Erase Out_table
Clear_Summary
Clear_Cashflow
Clear_費用適足性檢測
Sheets("Summary").Rows("4:1283").Hidden = True
Sheets("Summary").Rows("1288:1372").Hidden = True
Sheets("Summary").Rows("1378:1402").Hidden = True
Sheets("Summary").Rows("1405:1679").Hidden = True
Calculate
Read_Inf
If Sheets("Setup").Range("X7") = "" Then Set MP = Sheets("Setup").Range("X6:AD6") Else Set MP = Sheets("Setup").Range("X6", Sheets("Setup").Range("AD6").End(xlDown))
For i = 1 To MP.Rows.Count
test_NUM(0) = MP(i, 1): test_NN(0) = MP(i, 3): test_Age(0) = MP(i, 4)
test_MF(0) = MP(i, 5): test_CH(0) = MP(i, 7): test_dis(0) = MP(i, 13)
If CAL_inf(test_NUM(0)) = "保額" Then test_SA(0) = MP(i, 6) Else test_GP(0) = MP(i, 6)
If test_NUM(0) <> MP(i - 1, 1) Then Read_Data
If MP(i, 2).Interior.Color <> 14277081 Then
Profit_Test
費差
WW = DUR_inf(test_NUM(0)) - test_Age(0)
MP(i, 9) = disCF(Profit_RBC(), WW, Range("RDR"), , 0) / GE(1)
MP(i, 10) = disCF(Profit_RBC(), WW, , TPR(), 0) / disCF(GE(), WW, , TPR(), 1)
MP(i, 11) = EXP_diff(0) / GE_EXP_Sum(0)
If Sheets("Setup").Cells(6, 4) = "Key Age" And (Sheets("Setup").Cells(5, 8) = "Y" Or Sheets("Setup").Cells(6, 8) = "Y") Then
If Sheets("Setup").Cells(7, 3) = 1 Then
Sheets("Summary").Cells(1, 1) = Name_inf(test_NUM(0)) & " 利潤測試(情境測試假設)"
ElseIf Sheets("Setup").Cells(7, 3) = 2 Then
Sheets("Summary").Cells(1, 1) = Name_inf(test_NUM(0)) & " 利潤測試(標準假設)"
ElseIf Sheets("Setup").Cells(7, 3) = 3 Then
Sheets("Summary").Cells(1, 1) = Name_inf(test_NUM(0)) & " 利潤測試(最佳估計)"
ElseIf Sheets("Setup").Cells(7, 3) = 4 Then
Sheets("Summary").Cells(1, 1) = Name_inf(test_NUM(0)) & " 利潤測試(V1假設)"
End If
If Sheets("Setup").Cells(8, 3) = 1 Then test_Weight(0) = 1 Else test_Weight(0) = MP(i, 8) * Sheets("Setup").Cells(9, 8) / GE(1)
If MP(i, 4) Mod 10 = 0 Then
test_X(0) = MP(i, 4) / 10 + 1: Summary
End If
If i = 1 Or MP(i, 4) < MP(i - 1, 4) Then
test_X(0) = 11: Summary
End If
If MP(i, 4) > MP(i + 1, 4) Then
test_X(0) = 12: Summary
End If
If Sheets("Setup").Cells(6, 8) = "Y" Then
Cashflow
If test_MF(0) = 1 Then MF = "M" Else MF = "F"
Select Case test_CH(0)
Case 1: CH = "AG": Case 2: CH = "BR": Case 3: CH = "BK": Case 4: CH = "FS": Case 5: CH = "EC"
End Select
If Sheets("Cashflow").Cells(2, 3) = "" Or Sheets("Cashflow").Cells(2, 3) = AIR_inf(test_NUM(0), test_NN(0)) Then Sheets("Cashflow").Cells(2, 3) = AIR_inf(test_NUM(0), test_NN(0)) Else Sheets("Cashflow").Cells(2, 5) = "Total"
If Sheets("Cashflow").Cells(2, 5) = "" Or Sheets("Cashflow").Cells(2, 5) = test_Age(0) Then Sheets("Cashflow").Cells(2, 5) = test_Age(0) Else Sheets("Cashflow").Cells(2, 5) = "Total"
If Sheets("Cashflow").Cells(2, 7) = "" Or Sheets("Cashflow").Cells(2, 7) = test_NN(0) Then Sheets("Cashflow").Cells(2, 7) = test_NN(0) Else Sheets("Cashflow").Cells(2, 7) = "Total"
If CAL_inf(test_NUM(0)) = "保額" Then
Sheets("Cashflow").Cells(2, 10) = "保額="
If Sheets("Cashflow").Cells(2, 11) = "" Or Sheets("Cashflow").Cells(2, 11) = test_SA(0) * 10000 Then Sheets("Cashflow").Cells(2, 11) = test_SA(0) * 10000 Else Sheets("Cashflow").Cells(2, 11) = "Total"
Else
Sheets("Cashflow").Cells(2, 10) = "保費="
If Sheets("Cashflow").Cells(2, 11) = "" Or Sheets("Cashflow").Cells(2, 11) = test_GP(0) * 10000 Then Sheets("Cashflow").Cells(2, 11) = test_GP(0) * 10000 Else Sheets("Cashflow").Cells(2, 11) = "Total"
End If
If Sheets("Cashflow").Cells(2, 13) = "" Or Sheets("Cashflow").Cells(2, 13) = MF Then Sheets("Cashflow").Cells(2, 13) = MF Else Sheets("Cashflow").Cells(2, 13) = "Total"
If Sheets("Cashflow").Cells(2, 15) = "" Or Sheets("Cashflow").Cells(2, 15) = CH Then Sheets("Cashflow").Cells(2, 15) = CH Else Sheets("Cashflow").Cells(2, 15) = "Total"
If Sheets("費用適足性檢測").Cells(7, 2) = "" Or Sheets("費用適足性檢測").Cells(7, 2) = test_NN(0) Then Sheets("費用適足性檢測").Cells(7, 2) = test_NN(0) Else Sheets("費用適足性檢測").Cells(7, 2) = "Total"
If Sheets("費用適足性檢測").Cells(7, 3) = "" Or Sheets("費用適足性檢測").Cells(7, 3) = CH Then Sheets("費用適足性檢測").Cells(7, 3) = CH Else Sheets("費用適足性檢測").Cells(7, 3) = "Total"
If Sheets("Setup").Cells(7, 3) = 1 Then
Sheets("Cashflow").Cells(1, 1) = Name_inf(test_NUM(0)) & " 利潤測試(情境測試假設)"
ElseIf Sheets("Setup").Cells(7, 3) = 2 Then
Sheets("Cashflow").Cells(1, 1) = Name_inf(test_NUM(0)) & " 利潤測試(標準假設)"
ElseIf Sheets("Setup").Cells(7, 3) = 3 Then
Sheets("Cashflow").Cells(1, 1) = Name_inf(test_NUM(0)) & " 利潤測試(最佳估計)"
ElseIf Sheets("Setup").Cells(7, 3) = 4 Then
Sheets("Cashflow").Cells(1, 1) = Name_inf(test_NUM(0)) & " 利潤測試(V1假設)"
End If
End If
End If
End If
Next i
If Sheets("Setup").Cells(5, 8) = "Y" Then
Summary_Total(0) = 1
Summary
Summary_Total(0) = 0
End If
If Sheets("Setup").Cells(6, 8) = "Y" Then
Summary_Total(0) = 1
Cashflow
Summary_Total(0) = 0
End If
End Sub
Sub Profit_Test()
Erase EFF, GP, Loading, GE, GE_0, COMM, COMM_1, COMM_2, EXP, CV_cost, P_DB, P_WP, P_HB, P_AD, P_SB, P_MB, APUA, V, d_V, V_DEF, VD, d_VD, Tax, Tax_1, RBC, d_RBC, Invest, Profit, Profit_Tax, AProfit_Tax, Profit_RBC, AProfit_RBC, Asset, In_SA, Net_CF, Cost, FYS, Dead_P, INT_P, EXP_P, Lapse_P, Other_P, Other_P_RBC, Out, BER_CSM, D, D_HW, MCD, MCD2, Dir
Erase Retention, ReinGP_rate, ReinGP, ReinDead, ReinSA
Application.ScreenUpdating = False
Assumption
Commission
Calculate
MF = test_MF(0): CH = test_CH(0): AGE = test_Age(0): NN = test_NN(0): num = test_NUM(0): dis_num1 = test_dis(0)
AIR = AIR_inf(num, NN)
VI = 1 / (1 + AIR)
VI6 = VI6_inf(num, NN)
WW = DUR_inf(num) - AGE
EFF(0) = 1
If Data2_inf(num) = "Y" Then 取位 = 0 Else 取位 = 30
If Name_inf(num) = "中國人壽友保障終身壽險" Then TSO = "TSO_友保障" Else TSO = "TSO_2021"
If CAL_inf(num) = "保額" Then SA = test_SA(0) Else SA = test_GP(0) / GP_0(MF, NN, AGE) * 10000
For t = 1 To WW
GP(0) = Round(GP_0(MF, NN, AGE) * (1 - dis1_inf(num, NN, dis_num1) - dis2_inf(num, NN, dis_num1)), 取位)
Loading(0) = 1 - NP(MF, NN, AGE) / GP(0)
If t <= NN Then GE(t) = GP(0) * EFF(t - 1) * SA: GE_0(t) = GP_0(MF, NN, AGE) * EFF(t - 1) * SA Else GE(t) = 0
COMM(t) = GE(t) * COMM_rate(t)
COMM_1(t) = GE(t) * COMM_rate_exp1(t)
COMM_2(t) = COMM(t) - COMM_1(t)
EXP(t) = Exp_Fix(t) * EFF(t - 1) + Exp_Var(t) * GE(t)
'死亡給付
If t = WW Then
P_DB(t) = EFF(t - 1) * Qx(t) * (SA + APUA(t)) * (max(Pay_AN(MF, NN, AGE, t), SIA(MF, NN, AGE, t) * 10000) - A_HB(MF, NN, AGE, t) * 10000 * (1 + Morbidity_Sen(0)))
Else
P_DB(t) = EFF(t - 1) * Qx(t) * (SA * SIA_RC(MF, NN, AGE, t) * 10000 + APUA(t) * SIA_RC_PUA(MF, NN, AGE, t) * 10000)
End If
If t <= NN And AGE + t > 16 Then
P_DB(t) = P_DB(t) + EFF(t - 1) * Qx(t) * GP_0(MF, NN, AGE) * 0.5 * SA * RGE
End If
'豁免
If Index_WP_inf(num) = "Y" Then
Pay_WP = 0
'Px0 = Px(t - 1)
For k = t To NN - 1
Pay_WP = Pay_WP + Px(k) * VI ^ k
Next k
P_WP(t) = Pay_WP * Range("Q_26")(AGE + t, MF) * GE(t)
End If
'健康、傷害
P_HB(t) = EFF(t - 1) * SA * 1 * Pay_HB(MF, NN, AGE, t) * (1 + Morbidity_Sen(0))
P_AD(t) = EFF(t - 1) * (SA + APUA(t)) * 10000 * Pay_Accident(MF, NN, AGE, t)
'解約
If Lapse_rate_Sen(0) = 1 And Sheets("Total Lapse").Cells(3, 9) = "次年度期初" Then
CV(MF, NN, AGE, t) = Int(VV(MF, NN, AGE, t) * CVrate_inf(num, NN, t + 1) + 0.5)
End If
CV_cost(t) = EFF(t - 1) * (1 - Qx(t)) * Qw(t) * (SA * (CV(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t) - A_HB(MF, NN, AGE, t) * 10000 * (1 + Morbidity_Sen(0))) + APUA(t) * (AXP(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)))
'生存金
If t < WW Then P_SB(t) = EFF(t - 1) * (1 - Qx(t)) * (1 - Qw(t)) * (SA + APUA(t)) * (Pay_AN(MF, NN, AGE, t))
'滿期金
If t = WW Then
P_MB(t) = EFF(t - 1) * (1 - Qx(t)) * (1 - Qw(t)) * (SA + APUA(t)) * (Pay_AN(MF, NN, AGE, t) - A_HB(MF, NN, AGE, t) * 10000 * (1 + Morbidity_Sen(0)))
Else
P_MB(t) = 0
End If
'回饋金
If Index_HB_inf(test_NUM(0)) = "Y" Then VVV(MF, NN, AGE, t) = VL(MF, NN, AGE, t) Else VVV(MF, NN, AGE, t) = VV(MF, NN, AGE, t)
'判斷PUA
If PT1_inf(num) = "利變型" Then
If Sheets("Setup").Cells(11, 4) = 1 Then
If t < WW Then D(t) = EFF(t - 1) * (1 - Qx(t)) * SA * (VVV(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)) * max(Dir_CSM(Scenario, t) - AIR, 0) Else D(t) = EFF(t - 1) * (1 - Qx(t)) * SA * Pay_AN(MF, NN, AGE, t) * max(Dir_CSM(Scenario, t) - AIR, 0)
If Sheets("Setup").Cells(14, 4) = "HW" And test_CSM(0) = 1 Then 'HW
' For Scenario = 1 To 1000
If t < WW Then
D_HW(Scenario, t) = EFF(t - 1) * (1 - Qx(t)) * SA * (VVV(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)) * max(Dir_CSM(Scenario, t) - AIR, 0)
Else
D_HW(Scenario, t) = EFF(t - 1) * (1 - Qx(t)) * SA * Pay_AN(MF, NN, AGE, t) * max(Dir_CSM(Scenario, t) - AIR, 0)
End If
' Next Scenario
End If
Else
If Sheets("Setup").Cells(11, 4) = 0 Then
If t < WW Then
PUA = (SA * (VVV(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)) + APUA(t) * (AXP(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t))) * max(Dir_CSM(Scenario, t) - AIR, 0) / AXP(MF, NN, AGE, t)
APUA(t + 1) = APUA(t) + PUA
Else
D(t) = EFF(t - 1) * (1 - Qx(t)) * (SA + APUA(t)) * (Pay_AN(MF, NN, AGE, t)) * max(Dir(t) - AIR, 0)
End If
End If
End If
End If
'***********
' If Index_HB_inf(test_NUM(0)) = "Y" Then VVV(MF, NN, AGE, t) = VL(MF, NN, AGE, t) Else VVV(MF, NN, AGE, t) = VV(MF, NN, AGE, t)
' If PT1_inf(num) = "利變型" Then
' If t = 1 Or (Sheets("Setup").Cells(7, 4) = "1. 定價假設" And Sheets("Setup").Cells(7, 5) = "(拉平)") Or Sheets("Setup").Cells(7, 4) = "3. 最佳估計" Then
' Dir(0) = DIR_inf(num)
' Dir(t) = DIR_inf(num)
' Else
' MCD(t) = Range("bond")(CUR_inf(num), min(t, 20))
' If CUR_inf(num) = 1 Then ratio1 = 0.65 Else ratio1 = 0.94
' MCD2(t) = (TPR(t) - MCD(t) * (1 - ratio1)) / ratio1
' If Interest_Sen(0) > 0 Then maxS2_1 = 0.005: maxS2_2 = 0
' If Interest_Sen(0) < 0 Then maxS2_1 = 0: maxS2_2 = -0.0015
' If Interest_Sen(0) = -0.005 Then maxS2_1 = -0.0015: maxS2_2 = 0
' If Interest_Sen(0) = 0.005 Then maxS2_1 = 0.002: maxS2_2 = 0
' S1 = MCD2(t) - MCD(t)
' If Sheets("Setup").Cells(7, 4) = "4. V1假設" Then S2 = min(max((TPR(t) - Dir(0)) * 0, 0.0165 + maxS2_2), 0.025 + maxS2_1) Else S2 = min(max(TPR(t) - Dir(0), S2_min_inf(num) + maxS2_2), S2_max_inf(num) + maxS2_1) '0.01
' If Interest_Sen(0) < 0 Then minR = 0 Else: If Dir_min_inf(num) = "宣告利率" Then minR = Dir(0) Else minR = Dir_min_inf(num)
' Dir(t) = max(minR, min(MCD(t) + S1, TPR(t) - S2))
' End If
' If PT1_inf(num) = "利變型" Then
' If Sheets("Setup").Cells(7, 4) <> "4. V1假設" Then
' If t < WW Then D(t) = EFF(t - 1) * (1 - Qx(t)) * SA * (VVV(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)) * max(Dir(t) - AIR, 0) Else D(t) = EFF(t - 1) * (1 - Qx(t)) * SA * Pay_AN(MF, NN, AGE, t) * max(Dir(t) - AIR, 0)
' If Sheets("Setup").Cells(14, 4) = "HW" And test_CSM(0) = 1 Then 'HW
' For Scenario = 1 To 1000
' If t < WW Then
' D_HW(Scenario, t) = EFF(t - 1) * (1 - Qx(t)) * SA * (VVV(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)) * max(Dir_CSM(Scenario, t) - AIR, 0)
' Else
' D_HW(Scenario, t) = EFF(t - 1) * (1 - Qx(t)) * SA * Pay_AN(MF, NN, AGE, t) * max(Dir_CSM(Scenario, t) - AIR, 0)
' End If
' Next Scenario
' End If
' ElseIf Sheets("Setup").Cells(7, 4) = "4. V1假設" Then
' If t < WW Then
' PUA = (SA * (VVV(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)) + APUA(t) * (AXPL(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t))) * max(Dir(t) - AIR, 0) / AXPL(MF, NN, AGE, t)
' APUA(t + 1) = APUA(t) + PUA
' Else
' D(t) = EFF(t - 1) * (1 - Qx(t)) * (SA + APUA(t)) * (Pay_AN(MF, NN, AGE, t)) * max(Dir(t) - AIR, 0)
' End If
' End If
' End If
' End If
'再保
If Sheets("Setup").Cells(10, 4) = "Y" And test_CSM(0) <> 1 Then
If Rein_inf(num) = 1 Then
If t = 1 Then
If CUR_inf(num) = 1 Then ex_rate = 1 Else ex_rate = Sheets("Assumption").Cells(69, 3) '匯率
Rein_Surplus_Ratio = max(SA * (SIB(MF, NN, AGE, t) * 10000 - V6(MF, NN, AGE, t) - Pay_AN(MF, NN, AGE, t) - Pay_AN(MF, NN, AGE, t)) - Retention_inf(num) * 10000 / ex_rate, 0) / (SA * (SIB(MF, NN, AGE, t) * 10000 - V6(MF, NN, AGE, t) - Pay_AN(MF, NN, AGE, t) - Pay_AN(MF, NN, AGE, t)))
End If
ReinSA(t) = max(SA * (SIB(MF, NN, AGE, t) * 10000 - V6(MF, NN, AGE, t) - Pay_AN(MF, NN, AGE, t) - Pay_AN(MF, NN, AGE, t)), 0) * Rein_Surplus_Ratio
ElseIf Rein_inf(num) = 2 Then
ReinSA(t) = max(SA * (SIB(MF, NN, AGE, t) * 10000 - V6(MF, NN, AGE, t) - Pay_AN(MF, NN, AGE, t) - Pay_AN(MF, NN, AGE, t)), 0) * (1 - Retention_inf(num))
End If
ReinGP_rate(t) = Range("TSO_" & Rein_TSO(num))(AGE + t, MF) * Rein_rate(num, min(t, 3))
ReinGP(t) = EFF(t - 1) * ReinGP_rate(t) * ReinSA(t)
ReinDead(t) = EFF(t - 1) * Qx(t) * ReinSA(t)
End If
'責任準備金
If t = WW Then
V(t) = 0
Else
V(t) = Round(EFF(t - 1) * (1 - Qx(t)) * (1 - Qw(t)) * (SA * V6(MF, NN, AGE, t) + APUA(t) * AXP(MF, NN, AGE, t)), 取位)
End If
d_V(t) = V(t) - V(t - 1)
'保費不足準備金
If NN > 1 And t <= NN And P26(MF, NN, AGE) > GP(0) Then
If P6(MF, NN, AGE) > H6(MF, NN, AGE) * PA6(MF, NN, AGE) Then '20修
Vdef_AN1 = 0
For k = 1 To min(NN, 20) - t
tPx6 = 1
For KT = 1 To k - 1
tPx6 = tPx6 * (1 - Range(TSO)(AGE + t + KT, MF))
Next KT
Vdef_AN1 = Vdef_AN1 + tPx6 * VI6 ^ (k - 1)
Next k
Vdef_AN2 = 0 'a6(x+t:n-20)
For k = Application.max(t, 21) To NN
tPx6 = 1
For KT = 1 To k - 1
tPx6 = tPx6 * (1 - Range(TSO)(AGE + t + KT, MF))
Next KT
Vdef_AN2 = Vdef_AN2 + tPx6 * VI6 ^ (k - 1)
Next k
V_DEF(t) = (P26(MF, NN, AGE) - GP(0)) * Vdef_AN1 + (P6(MF, NN, AGE) - GP(0)) * Vdef_AN2
Else 'FPT制
Vdef_AN1 = 0 'a6(x+t:n-t)
For k = 1 To NN - t
tPx6 = 1
For KT = 1 To k - 1
tPx6 = tPx6 * (1 - Range(TSO)(AGE + t + KT, MF))
Next KT
Vdef_AN1 = Vdef_AN1 + tPx6 * VI6 ^ (k - 1)
Next k
V_DEF(t) = (P26(MF, NN, AGE) - GP(0)) * Vdef_AN1
End If
If t = 1 Then '第1年度初保費不足準備金(T=0)
DEF0 = P16(MF, NN, AGE) - GP(0): If DEF0 < 0 Then DEF0 = 0
V_DEF(0) = V_DEF(t) * (1 - Range(TSO)(AGE + 1, MF)) * VI6 + DEF0
End If
Else
V_DEF(t) = 0
End If
VD(0) = EFF(0) * SA * V_DEF(0)
VD(t) = EFF(t - 1) * (1 - Qx(t)) * (1 - Qw(t)) * SA * V_DEF(t)
If t = 1 Then d_VD(t) = VD(t) Else d_VD(t) = VD(t) - VD(t - 1)
'稅
If GE(t) = 0 Then Tax1 = 0 Else Tax1 = (GE(t) - CV_cost(t) - P_SB(t) - P_MB(t) - d_V(t)) * Range("Tax")(1)
Tax2 = GE(t) * Range("Tax")(3)
Tax(t) = max(Tax1, 0) + Tax2
TaxCSM(t) = max(Tax1, 0) + Tax2 * 0.37
'投資收益
Invest(t) = (GE(t) + Asset(t - 1) - EXP(t) - COMM(t) - Tax(t) - RBC(t - 1) - ReinGP(t) - 0.5 * (P_DB(t) + P_WP(t) + P_HB(t) + P_AD(t) - ReinDead(t))) * TPR(t)
'稅前利潤
Profit(t) _
= GE(t) + Invest(t) - EXP(t) - Tax(t) - COMM(t) _
- P_DB(t) - P_HB(t) - P_AD(t) - P_WP(t) - CV_cost(t) - P_SB(t) - P_MB(t) - D(t) _
- ReinGP(t) + ReinDead(t) - d_V(t) - d_VD(t)
'稅後利潤
Profit_Tax(t) = Profit(t) * (1 - Range("Tax")(2))
AProfit_Tax(t) = AProfit_Tax(t - 1) * (1 + TPR(t)) + Profit_Tax(t)
'有效保額
If t < WW Then In_SA(t) = EFF(t - 1) * (1 - Qx(t)) * (1 - Qw(t)) * (SA * SIB(MF, NN, AGE, t) * 10000 + APUA(t) * SIA_PUA(MF, NN, AGE, t) * 10000) Else In_SA(t) = 0
'RBC
Dim C3(120)
C1o = Range("RBC_C1")(CUR_inf(num) + 0, min(t, 11))
C1s = Range("RBC_C1")(CUR_inf(num) + 2, min(t, 11))
C1c = Range("RBC_C1")(CUR_inf(num) + 4, min(t, 11))
If PT2_inf(num) = "終身死亡險" Then
C2 = Range("RBC_C2")(1)
ElseIf PT2_inf(num) = "定期死亡險" Then
C2 = Range("RBC_C2")(2)
ElseIf PT2_inf(num) = "養老險" Then
C2 = Range("RBC_C2")(3)
ElseIf PT2_inf(num) = "還本型保險" Then
C2 = Range("RBC_C2")(4)
End If
C4_P = Range("RBC_C4")(1)
C4_V = Range("RBC_C4")(2)
C1o = C1o * (V(t) + VD(t))
C1s = C1s * (V(t) + VD(t))
C1c = C1c * (V(t) + VD(t))
C2 = C2 * max(In_SA(t) - (V(t) + P_SB(t)), 0)
C3(t) = ((SA * VV(MF, NN, AGE, t) + APUA(t) * AXP(MF, NN, AGE, t)) * EFF(t - 1) * (1 - Qx(t)) * (1 - Qw(t))) * max(max(Dir(t), AIR) - TPR(t), 0) + 0.5 * C3(t - 1) '計算RBC使用之保單價值準備金不含當年度生存金
C4 = C4_P * GE(t) + C4_V * (V(t) + VD(t))
RBC(t) = 2 * 0.5 * (C0 + C4 + ((C1o + C3(t)) ^ 2 + C1c ^ 2 + C1s ^ 2 + C2 ^ 2) ^ 0.5)
d_RBC(t) = RBC(t) - RBC(t - 1)
'所得稅
Tax_1(t) = (Profit(t) + RBC(t - 1) * TPR(t)) * Range("Tax")(2)
'稅後利潤(股東紅利分配)
Profit_RBC(t) = (Profit(t) + RBC(t - 1) * TPR(t)) * (1 - Range("Tax")(2)) - d_RBC(t)
AProfit_RBC(t) = AProfit_RBC(t - 1) * (1 + TPR(t)) + Profit_RBC(t)
'淨現金流
Net_CF(t) _
= GE(t) + Invest(t) - EXP(t) - Tax(t) - Tax_1(t) - COMM(t) _
- P_DB(t) - P_HB(t) - P_AD(t) - P_WP(t) - CV_cost(t) - P_SB(t) - P_MB(t) - D(t) - ReinGP(t) + ReinDead(t) _
- Profit_RBC(t)
'期末資產
Asset(t) = Asset(t - 1) + Net_CF(t) + RBC(t - 1) * TPR(t)
'第一年總成本、FYS(Monthly Report)
If t = 1 Then
Cost(1) = (EXP(1) + COMM(1) _
+ P_DB(1) - ReinDead(t) + P_SB(1) + P_MB(1) + P_WP(1) + P_HB(1) + P_AD(1) + CV_cost(1) + D(1) _
+ d_V(1) + d_VD(1) + Tax(1) + ReinGP(t))
FYS(1) = -(GE(1) - COMM(1) - EXP(1) - Tax(1) - SA * (V6(MF, NN, AGE, t) + V_DEF(t)))
End If
On Error Resume Next
'死差益
Dead_P(t) = EFF(t - 1) * (P_DB(t) / (EFF(t - 1) * Qx(t)) - (SA * (V6(MF, NN, AGE, t - 1) + V6(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)) / 2 + APUA(t) * (AXP(MF, NN, AGE, t - 1) + AXP(MF, NN, AGE, t) + Pay_AN(MF, NN, AGE, t)) / 2)) * (Range(TSO)(AGE + t, MF) * 1.1 - Qx(t)) * (1 + TPR(t) / 2) * (1 - Range("Tax")(2))
'利差益
INT_P(t) = (GE(t) + Asset(t - 1)) * (TPR(t) - max(AIR, Dir(t))) * (1 - Range("Tax")(2))
'費差益
If t = 1 Then P6_EXP = P16(MF, NN, AGE) Else P6_EXP = P26(MF, NN, AGE)
EXP_P(t) = (max(GE(t) - EFF(t - 1) * P6_EXP * SA, 0) - EXP(t) - COMM(t) - Tax(t)) * (1 - Range("Tax")(2))
'解約益
Lapse_P(t) = EFF(t - 1) * (1 - Qx(t)) * Qw(t) * SA * (V6(MF, NN, AGE, t) - CV(MF, NN, AGE, t)) * (1 - Range("Tax")(2))
'其他損益
Other_P(t) = Profit_Tax(t) - Dead_P(t) - INT_P(t) - EXP_P(t) - Lapse_P(t)
Other_P_RBC(t) = Profit_RBC(t) - Dead_P(t) - INT_P(t) - EXP_P(t) - Lapse_P(t)
EFF(t) = EFF(t - 1) * (1 - Qx(t)) * (1 - Qw(t))
Next t
If Sheets("Setup").Cells(6, 8) = "Y" Then
For t = 0 To WW
Out(2, t) = EFF(t)
Out(3, t) = GE(t)
Out(4, t) = Invest(t)
Out(5, t) = COMM(t)
Out(6, t) = EXP(t)
Out(7, t) = P_DB(t)
Out(8, t) = CV_cost(t)
Out(9, t) = P_SB(t)
Out(10, t) = P_MB(t)
Out(11, t) = Tax(t)
Out(12, t) = d_V(t)
Out(13, t) = d_VD(t)
Out(14, t) = Profit(t)
Out(15, t) = Tax_1(t)
Out(16, t) = Profit_Tax(t)
Out(17, t) = AProfit_Tax(t)
Out(18, t) = d_RBC(t)
Out(19, t) = Profit_RBC(t)
Out(20, t) = AProfit_RBC(t)
Out(21, t) = Net_CF(t)
Out(22, t) = Asset(t)
Out(23, t) = V(t)
Out(24, t) = VD(t)
Out(25, t) = RBC(t)
Out(26, t) = D(t)
Out(27, t) = P_WP(t)
Out(28, t) = In_SA(t)
Out(29, t) = P_AD(t)
Out(30, t) = P_HB(t)
Out(31, t) = ReinGP(t)
Out(32, t) = ReinDead(t)
Out(33, t) = TPR(t)
Out(34, t) = Dir(t)
Out(35, t) = Dead_P(t)
Out(36, t) = INT_P(t)
Out(37, t) = EXP_P(t)
Out(38, t) = Lapse_P(t)
Out(39, t) = Other_P_RBC(t)
Next t
End If
End Sub
Sub Summary()
dis_num1 = test_dis(0)
dis_num2 = dis_num2_inf(test_NUM(0), test_NN(0), test_dis(0))
If Summary_Total(0) <> 1 Then
discount = dis1_inf(test_NUM(0), test_NN(0), test_dis(0)) + dis2_inf(test_NUM(0), test_NN(0), test_dis(0))
cex = (test_CH(0) - 1) * 256 + 5 + (dis_num2 - 1) * 16
Sheets("Summary").Cells(cex, 1) = test_NN(0)
Sheets("Summary").Cells(cex, 5) = discount
Sheets("Summary").Cells(cex, 6) = CAL_inf(test_NUM(0)) & "="
If CAL_inf(test_NUM(0)) = "保額" Then Sheets("Summary").Cells(cex, 7) = test_SA(0) Else Sheets("Summary").Cells(cex, 7) = test_GP(0)
If Sheets("Summary").Rows(cex - 1 & ":" & cex + 2).Hidden = True Then Sheets("Summary").Rows(cex - 1 & ":" & cex + 2).Hidden = False
cex = (test_CH(0) - 1) * 256 + 7 + (dis_num2 - 1) * 16 + test_X(0)
CEY = (test_MF(0) - 1) * 17
WW = DUR_inf(test_NUM(0)) - test_Age(0)
Sheets("summary").Cells(cex, 1) = test_Age(0)
Sheets("summary").Cells(cex, 2 + CEY) = VD(0) / GE(1) '第一年初保費不足
Sheets("summary").Cells(cex, 3 + CEY) = Cost(1) / GE(1) '第一年總成本
Sheets("summary").Cells(cex, 4 + CEY) = RBC(1) / GE(1) '第一年風險資本
Sheets("summary").Cells(cex, 5 + CEY) = Loading(0) 'loading
Sheets("summary").Cells(cex, 6 + CEY) = BEY(AProfit_RBC(), WW) 'BEY(after RBC)
Sheets("summary").Cells(cex, 7 + CEY) = BEY(AProfit_Tax(), WW) 'BEY(after tax)
Sheets("summary").Cells(cex, 8 + CEY) = IRR_value(Profit_RBC(), WW, 20) 'IRR(20年)
Sheets("summary").Cells(cex, 9 + CEY) = -Profit(1) / GE(1) 'FY_Strain
If Name_inf(test_NUM(0)) = "中國人壽多美鑫旺美元利率變動型終身壽險－定期給付型" Then Sheets("summary").Cells(cex, 9 + CEY) = Sheets("summary").Cells(cex, 9 + CEY) - 0.02
Sheets("summary").Cells(cex, 10 + CEY) = disCF(Profit_RBC(), WW, Range("RDR"), , 0) / GE(1) 'V0NB
Sheets("summary").Cells(cex, 11 + CEY) = (disCF(Profit_RBC(), WW, Range("RDR"), , 0) * (1 + Range("RDR")) - Profit_RBC(1)) / GE(1) 'V1NB
Sheets("summary").Cells(cex, 12 + CEY) = disCF(Dead_P(), WW, , TPR(), 0) / disCF(GE(), WW, , TPR(), 1) '死差益
Sheets("summary").Cells(cex, 13 + CEY) = disCF(INT_P(), WW, , TPR(), 0) / disCF(GE(), WW, , TPR(), 1) '利差益
Sheets("summary").Cells(cex, 14 + CEY) = disCF(EXP_P(), WW, , TPR(), 0) / disCF(GE(), WW, , TPR(), 1) '費差益
Sheets("summary").Cells(cex, 15 + CEY) = disCF(Lapse_P(), WW, , TPR(), 0) / disCF(GE(), WW, , TPR(), 1) '解約益
Sheets("summary").Cells(cex, 16 + CEY) = disCF(Other_P_RBC(), WW, , TPR(), 0) / disCF(GE(), WW, , TPR(), 1) '其他益
Sheets("summary").Cells(cex, 17 + CEY) = RORAC(Profit_Tax(), RBC(), WW, Range("RDR")) 'RORAC
Sheets("summary").Cells(cex, 18 + CEY) = disCF(Profit_RBC(), WW, , TPR(), 0) / disCF(GE(), WW, , TPR(), 1) 'Profit Margin
If Sheets("Summary").Rows(cex).Hidden = True Then Sheets("Summary").Rows(cex).Hidden = False
'競品
If test_MF(0) = 1 Then MF = "M" Else MF = "F"
If test_Age(0) & MF = Sheets("Summary").Cells(1404, 2) Then
cex = 1406 + (NN_inf(test_NUM(0), test_NN(0)) - 1) * 10 + (test_CH(0) - 1) * 50
Sheets("Summary").Cells(cex, test_dis(0) + 1) = test_NN(0)
Sheets("Summary").Cells(cex + 1, test_dis(0) + 1) = BEY(AProfit_Tax(), WW)
Sheets("Summary").Cells(cex + 2, test_dis(0) + 1) = -Profit(1) / GE(1)
If Name_inf(test_NUM(0)) = "中國人壽多美鑫旺美元利率變動型終身壽險－定期給付型" Then Sheets("Summary").Cells(cex + 2, test_dis(0) + 1) = Sheets("Summary").Cells(cex + 2, test_dis(0) + 1) - 0.02
Sheets("Summary").Cells(cex + 3, test_dis(0) + 1) = disCF(Profit_RBC(), WW, Range("RDR"), , 0) / GE(1)
Sheets("Summary").Cells(cex + 4, test_dis(0) + 1) = (disCF(Profit_RBC(), WW, Range("RDR"), , 0) * (1 + Range("RDR")) - Profit_RBC(1)) / GE(1)
Sheets("Summary").Cells(cex + 5, test_dis(0) + 1) = disCF(Profit_RBC(), WW, , TPR(), 0) / disCF(GE(), WW, , TPR(), 1)
Sheets("Summary").Cells(cex + 6, test_dis(0) + 1) = EXP_diff(0) / GE_EXP_Sum(0)
If Sheets("Summary").Rows(cex & ":" & cex + 9).Hidden = True Then Sheets("Summary").Rows(cex & ":" & cex + 9).Hidden = False
If Sheets("Summary").Rows(1405 + (test_CH(0) - 1) * 50).Hidden = True Then Sheets("Summary").Rows(1405 + (test_CH(0) - 1) * 50).Hidden = False
End If
If test_X(0) <= 10 Or (test_X(0) > 10 And test_Age(0) Mod 10 <> 0) Then
VD_dis(test_CH(0), dis_num2, 0) = VD_dis(test_CH(0), dis_num2, 0) + VD(0) * test_Weight(0): VD_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), 0) = VD_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), 0) + VD(0) * test_Weight(0): VD_Total(test_CH(0), 0) = VD_Total(test_CH(0), 0) + VD(0) * test_Weight(0): VD_Total(0, 0) = VD_Total(0, 0) + VD(0) * test_Weight(0)
For t = 1 To WW
Cost_dis(test_CH(0), dis_num2, t) = Cost_dis(test_CH(0), dis_num2, t) + Cost(t) * test_Weight(0): Cost_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = Cost_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + Cost(t) * test_Weight(0): Cost_Total(test_CH(0), t) = Cost_Total(test_CH(0), t) + Cost(t) * test_Weight(0): Cost_Total(0, t) = Cost_Total(0, t) + Cost(t) * test_Weight(0)
RBC_dis(test_CH(0), dis_num2, t) = RBC_dis(test_CH(0), dis_num2, t) + RBC(t) * test_Weight(0): RBC_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = RBC_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + RBC(t) * test_Weight(0): RBC_Total(test_CH(0), t) = RBC_Total(test_CH(0), t) + RBC(t) * test_Weight(0): RBC_Total(0, t) = RBC_Total(0, t) + RBC(t) * test_Weight(0)
NP_dis(test_CH(0), dis_num2, 0) = NP_dis(test_CH(0), dis_num2, 0) + GE(t) * (1 - Loading(0)) * test_Weight(0): NP_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), 0) = NP_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), 0) + GE(t) * (1 - Loading(0)) * test_Weight(0): NP_Total(test_CH(0), 0) = NP_Total(test_CH(0), 0) + GE(t) * (1 - Loading(0)) * test_Weight(0): NP_Total(0, 0) = NP_Total(0, 0) + GE(t) * (1 - Loading(0)) * test_Weight(0)
GE_dis(test_CH(0), dis_num2, 0) = GE_dis(test_CH(0), dis_num2, 0) + GE(t) * test_Weight(0): GE_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), 0) = GE_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), 0) + GE(t) * test_Weight(0): GE_Total(test_CH(0), 0) = GE_Total(test_CH(0), 0) + GE(t) * test_Weight(0): GE_Total(0, 0) = GE_Total(0, 0) + GE(t) * test_Weight(0)
GE_dis(test_CH(0), dis_num2, t) = GE_dis(test_CH(0), dis_num2, t) + GE(t) * test_Weight(0): GE_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = GE_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + GE(t) * test_Weight(0): GE_Total(test_CH(0), t) = GE_Total(test_CH(0), t) + GE(t) * test_Weight(0): GE_Total(0, t) = GE_Total(0, t) + GE(t) * test_Weight(0)
Profit_dis(test_CH(0), dis_num2, t) = Profit_dis(test_CH(0), dis_num2, t) + Profit(t) * test_Weight(0): Profit_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = Profit_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + Profit(t) * test_Weight(0): Profit_Total(test_CH(0), t) = Profit_Total(test_CH(0), t) + Profit(t) * test_Weight(0): Profit_Total(0, t) = Profit_Total(0, t) + Profit(t) * test_Weight(0)
Profit_Tax_dis(test_CH(0), dis_num2, t) = Profit_Tax_dis(test_CH(0), dis_num2, t) + Profit_Tax(t) * test_Weight(0): Profit_Tax_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = Profit_Tax_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + Profit_Tax(t) * test_Weight(0): Profit_Tax_Total(test_CH(0), t) = Profit_Tax_Total(test_CH(0), t) + Profit_Tax(t) * test_Weight(0): Profit_Tax_Total(0, t) = Profit_Tax_Total(0, t) + Profit_Tax(t) * test_Weight(0)
Profit_RBC_dis(test_CH(0), dis_num2, t) = Profit_RBC_dis(test_CH(0), dis_num2, t) + Profit_RBC(t) * test_Weight(0): Profit_RBC_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = Profit_RBC_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + Profit_RBC(t) * test_Weight(0): Profit_RBC_Total(test_CH(0), t) = Profit_RBC_Total(test_CH(0), t) + Profit_RBC(t) * test_Weight(0): Profit_RBC_Total(0, t) = Profit_RBC_Total(0, t) + Profit_RBC(t) * test_Weight(0)
AProfit_RBC_dis(test_CH(0), dis_num2, t) = AProfit_RBC_dis(test_CH(0), dis_num2, t) + AProfit_RBC(t) * test_Weight(0): AProfit_RBC_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = AProfit_RBC_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + AProfit_RBC(t) * test_Weight(0): AProfit_RBC_Total(test_CH(0), t) = AProfit_RBC_Total(test_CH(0), t) + AProfit_RBC(t) * test_Weight(0): AProfit_RBC_Total(0, t) = AProfit_RBC_Total(0, t) + AProfit_RBC(t) * test_Weight(0)
AProfit_Tax_dis(test_CH(0), dis_num2, t) = AProfit_Tax_dis(test_CH(0), dis_num2, t) + AProfit_Tax(t) * test_Weight(0): AProfit_Tax_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = AProfit_Tax_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + AProfit_Tax(t) * test_Weight(0): AProfit_Tax_Total(test_CH(0), t) = AProfit_Tax_Total(test_CH(0), t) + AProfit_Tax(t) * test_Weight(0): AProfit_Tax_Total(0, t) = AProfit_Tax_Total(0, t) + AProfit_Tax(t) * test_Weight(0)
Dead_P_dis(test_CH(0), dis_num2, t) = Dead_P_dis(test_CH(0), dis_num2, t) + Dead_P(t) * test_Weight(0): Dead_P_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = Dead_P_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + Dead_P(t) * test_Weight(0): Dead_P_Total(test_CH(0), t) = Dead_P_Total(test_CH(0), t) + Dead_P(t) * test_Weight(0): Dead_P_Total(0, t) = Dead_P_Total(0, t) + Dead_P(t) * test_Weight(0)
INT_P_dis(test_CH(0), dis_num2, t) = INT_P_dis(test_CH(0), dis_num2, t) + INT_P(t) * test_Weight(0): INT_P_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = INT_P_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + INT_P(t) * test_Weight(0): INT_P_Total(test_CH(0), t) = INT_P_Total(test_CH(0), t) + INT_P(t) * test_Weight(0): INT_P_Total(0, t) = INT_P_Total(0, t) + INT_P(t) * test_Weight(0)
EXP_P_dis(test_CH(0), dis_num2, t) = EXP_P_dis(test_CH(0), dis_num2, t) + EXP_P(t) * test_Weight(0): EXP_P_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = EXP_P_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + EXP_P(t) * test_Weight(0): EXP_P_Total(test_CH(0), t) = EXP_P_Total(test_CH(0), t) + EXP_P(t) * test_Weight(0): EXP_P_Total(0, t) = EXP_P_Total(0, t) + EXP_P(t) * test_Weight(0)
Lapse_P_dis(test_CH(0), dis_num2, t) = Lapse_P_dis(test_CH(0), dis_num2, t) + Lapse_P(t) * test_Weight(0): Lapse_P_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = Lapse_P_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + Lapse_P(t) * test_Weight(0): Lapse_P_Total(test_CH(0), t) = Lapse_P_Total(test_CH(0), t) + Lapse_P(t) * test_Weight(0): Lapse_P_Total(0, t) = Lapse_P_Total(0, t) + Lapse_P(t) * test_Weight(0)
Other_P_RBC_dis(test_CH(0), dis_num2, t) = Other_P_RBC_dis(test_CH(0), dis_num2, t) + Other_P_RBC(t) * test_Weight(0): Other_P_RBC_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = Other_P_RBC_NN(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + Other_P_RBC(t) * test_Weight(0): Other_P_RBC_Total(test_CH(0), t) = Other_P_RBC_Total(test_CH(0), t) + Other_P_RBC(t) * test_Weight(0): Other_P_RBC_Total(0, t) = Other_P_RBC_Total(0, t) + Other_P_RBC(t) * test_Weight(0)
If test_MF(0) = 1 Then MF = "M" Else MF = "F"
If test_Age(0) & MF = Sheets("Summary").Cells(1404, 2) Then
GE_競品(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = GE_競品(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + GE(t) * test_Weight(0)
RBC_競品(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = RBC_競品(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + RBC(t) * test_Weight(0)
Profit_Tax_競品(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) = Profit_Tax_競品(test_CH(0), NN_inf(test_NUM(0), test_NN(0)), t) + Profit_Tax(t) * test_Weight(0)
End If
Next t
End If
End If
WW = DUR_inf(test_NUM(0))
Select Case test_CH(0)
Case 1: channel = "(AG)"
Case 2: channel = "(BR)"
Case 3: channel = "(BK)"
Case 4: channel = "(FS)"
Case 5: channel = "(EC)"
End Select
cex = 1287 + dis_num2 + 16 * (test_CH(0) - 1)
Sheets("summary").Cells(cex, 1) = test_NN(0) & channel
cex = 1377 + NN_inf(test_NUM(0), test_NN(0)) + 5 * (test_CH(0) - 1)
Sheets("summary").Cells(cex, 1) = test_NN(0) & channel
If Summary_Total(0) = 1 Then
For CH = 1 To 5
For dis_num = 1 To 16
If GE_dis(CH, dis_num, 1) <> 0 Then
cex = 1287 + dis_num + 16 * (CH - 1)
Sheets("summary").Cells(cex, 2) = VD_dis(CH, dis_num, 0) / GE_dis(CH, dis_num, 1) '第一年初保費不足
Sheets("summary").Cells(cex, 3) = Cost_dis(CH, dis_num, 1) / GE_dis(CH, dis_num, 1) '第一年總成本
Sheets("summary").Cells(cex, 4) = RBC_dis(CH, dis_num, 1) / GE_dis(CH, dis_num, 1) '第一年風險資本
Sheets("summary").Cells(cex, 5) = 1 - NP_dis(CH, dis_num, 0) / GE_dis(CH, dis_num, 0) 'loading
Sheets("summary").Cells(cex, 6) = BEY(AProfit_RBC_dis(), WW, CH, dis_num) 'BEY(after RBC)
Sheets("summary").Cells(cex, 7) = BEY(AProfit_Tax_dis(), WW, CH, dis_num) 'BEY(after tax)
Sheets("summary").Cells(cex, 8) = IRR_value(Profit_RBC_dis(), WW, 20, CH, dis_num) 'IRR(20年)
Sheets("summary").Cells(cex, 9) = -Profit_dis(CH, dis_num, 1) / GE_dis(CH, dis_num, 1) 'FY_Strain
If Name_inf(test_NUM(0)) = "中國人壽多美鑫旺美元利率變動型終身壽險－定期給付型" Then Sheets("summary").Cells(cex, 9) = Sheets("summary").Cells(cex, 9) - 0.02
Sheets("summary").Cells(cex, 10) = disCF(Profit_RBC_dis(), WW, Range("RDR"), , 0, , CH, dis_num) / GE_dis(CH, dis_num, 1) 'V0NB
Sheets("summary").Cells(cex, 11) = (disCF(Profit_RBC_dis(), WW, Range("RDR"), , 0, , CH, dis_num) * (1 + Range("RDR")) - Profit_RBC_dis(CH, dis_num, 1)) / GE_dis(CH, dis_num, 1) 'V1NB
Sheets("summary").Cells(cex, 12) = disCF(Dead_P_dis(), WW, , TPR(), 0, , CH, dis_num) / disCF(GE_dis(), WW, , TPR(), 1, , CH, dis_num) '死差益
Sheets("summary").Cells(cex, 13) = disCF(INT_P_dis(), WW, , TPR(), 0, , CH, dis_num) / disCF(GE_dis(), WW, , TPR(), 1, , CH, dis_num) '利差益
Sheets("summary").Cells(cex, 14) = disCF(EXP_P_dis(), WW, , TPR(), 0, , CH, dis_num) / disCF(GE_dis(), WW, , TPR(), 1, , CH, dis_num) '費差益
Sheets("summary").Cells(cex, 15) = disCF(Lapse_P_dis(), WW, , TPR(), 0, , CH, dis_num) / disCF(GE_dis(), WW, , TPR(), 1, , CH, dis_num) '解約益
Sheets("summary").Cells(cex, 16) = disCF(Other_P_RBC_dis(), WW, , TPR(), 0, , CH, dis_num) / disCF(GE_dis(), WW, , TPR(), 1, , CH, dis_num) '其他益
Sheets("summary").Cells(cex, 17) = RORAC(Profit_Tax_dis(), RBC_dis(), WW, Range("RDR"), CH, dis_num) 'RORAC
Sheets("summary").Cells(cex, 18) = disCF(Profit_RBC_dis(), WW, , TPR(), 0, , CH, dis_num) / disCF(GE_dis(), WW, , TPR(), 1, , CH, dis_num) 'Profit Margin
Sheets("summary").Rows(cex).Hidden = False
End If
Next dis_num
For NN_num = 1 To 5
If GE_NN(CH, NN_num, 1) <> 0 Then
cex = 1377 + NN_num + 5 * (CH - 1)
Sheets("summary").Cells(cex, 2) = VD_NN(CH, NN_num, 0) / GE_NN(CH, NN_num, 1) '第一年初保費不足
Sheets("summary").Cells(cex, 3) = Cost_NN(CH, NN_num, 1) / GE_NN(CH, NN_num, 1) '第一年總成本
Sheets("summary").Cells(cex, 4) = RBC_NN(CH, NN_num, 1) / GE_NN(CH, NN_num, 1) '第一年風險資本
Sheets("summary").Cells(cex, 5) = 1 - NP_NN(CH, NN_num, 0) / GE_NN(CH, NN_num, 0) 'loading
Sheets("summary").Cells(cex, 6) = BEY(AProfit_RBC_NN(), WW, CH, NN_num) 'BEY(after RBC)
Sheets("summary").Cells(cex, 7) = BEY(AProfit_Tax_NN(), WW, CH, NN_num) 'BEY(after tax)
Sheets("summary").Cells(cex, 8) = IRR_value(Profit_RBC_NN(), WW, 20, CH, NN_num) 'IRR(20年)
Sheets("summary").Cells(cex, 9) = -Profit_NN(CH, NN_num, 1) / GE_NN(CH, NN_num, 1) 'FY_Strain
If Name_inf(test_NUM(0)) = "中國人壽多美鑫旺美元利率變動型終身壽險－定期給付型" Then Sheets("summary").Cells(cex, 9) = Sheets("summary").Cells(cex, 9) - 0.02
Sheets("summary").Cells(cex, 10) = disCF(Profit_RBC_NN(), WW, Range("RDR"), , 0, , CH, NN_num) / GE_NN(CH, NN_num, 1) 'V0NB
Sheets("summary").Cells(cex, 11) = (disCF(Profit_RBC_NN(), WW, Range("RDR"), , 0, , CH, NN_num) * (1 + Range("RDR")) - Profit_RBC_NN(CH, NN_num, 1)) / GE_NN(CH, NN_num, 1) 'V1NB
Sheets("summary").Cells(cex, 12) = disCF(Dead_P_NN(), WW, , TPR(), 0, , CH, NN_num) / disCF(GE_NN(), WW, , TPR(), 1, , CH, NN_num) '死差益
Sheets("summary").Cells(cex, 13) = disCF(INT_P_NN(), WW, , TPR(), 0, , CH, NN_num) / disCF(GE_NN(), WW, , TPR(), 1, , CH, NN_num) '利差益
Sheets("summary").Cells(cex, 14) = disCF(EXP_P_NN(), WW, , TPR(), 0, , CH, NN_num) / disCF(GE_NN(), WW, , TPR(), 1, , CH, NN_num) '費差益
Sheets("summary").Cells(cex, 15) = disCF(Lapse_P_NN(), WW, , TPR(), 0, , CH, NN_num) / disCF(GE_NN(), WW, , TPR(), 1, , CH, NN_num) '解約益
Sheets("summary").Cells(cex, 16) = disCF(Other_P_RBC_NN(), WW, , TPR(), 0, , CH, NN_num) / disCF(GE_NN(), WW, , TPR(), 1, , CH, NN_num) '其他益
Sheets("summary").Cells(cex, 17) = RORAC(Profit_Tax_NN(), RBC_NN(), WW, Range("RDR"), CH, NN_num) 'RORAC
Sheets("summary").Cells(cex, 18) = disCF(Profit_RBC_NN(), WW, , TPR(), 0, , CH, NN_num) / disCF(GE_NN(), WW, , TPR(), 1, , CH, NN_num) 'Profit Margin
Sheets("summary").Rows(cex).Hidden = False
If GE_競品(CH, NN_num, 1) <> 0 Then
cex = 1406 + (NN_num - 1) * 10 + (CH - 1) * 50
Sheets("Summary").Cells(cex + 7, 2) = RORAC(Profit_Tax_競品(), RBC_競品(), WW, Range("RDR"), CH, NN_num)
Sheets("Summary").Cells(cex + 8, 2) = RBC_競品(CH, NN_num, 1) / GE_競品(CH, NN_num, 1)
End If
End If
Next NN_num
If GE_Total(CH, 1) <> 0 Then
cex = 1367 + CH
Sheets("summary").Cells(cex, 2) = VD_Total(CH, 0) / GE_Total(CH, 1) '第一年初保費不足
Sheets("summary").Cells(cex, 3) = Cost_Total(CH, 1) / GE_Total(CH, 1) '第一年總成本
Sheets("summary").Cells(cex, 4) = RBC_Total(CH, 1) / GE_Total(CH, 1) '第一年風險資本
Sheets("summary").Cells(cex, 5) = 1 - NP_Total(CH, 0) / GE_Total(CH, 0) 'loading
Sheets("summary").Cells(cex, 6) = BEY(AProfit_RBC_Total(), WW, CH) 'BEY(after RBC)
Sheets("summary").Cells(cex, 7) = BEY(AProfit_Tax_Total(), WW, CH) 'BEY(after tax)
Sheets("summary").Cells(cex, 8) = IRR_value(Profit_RBC_Total(), WW, 20, CH) 'IRR(20年)
Sheets("summary").Cells(cex, 9) = -Profit_Total(CH, 1) / GE_Total(CH, 1) 'FY_Strain
If Name_inf(test_NUM(0)) = "中國人壽多美鑫旺美元利率變動型終身壽險－定期給付型" Then Sheets("summary").Cells(cex, 9) = Sheets("summary").Cells(cex, 9) - 0.02
Sheets("summary").Cells(cex, 10) = disCF(Profit_RBC_Total(), WW, Range("RDR"), , 0, , CH) / GE_Total(CH, 1) 'V0NB
Sheets("summary").Cells(cex, 11) = (disCF(Profit_RBC_Total(), WW, Range("RDR"), , 0, , CH) * (1 + Range("RDR")) - Profit_RBC_Total(CH, 1)) / GE_Total(CH, 1) 'V1NB
Sheets("summary").Cells(cex, 12) = disCF(Dead_P_Total(), WW, , TPR(), 0, , CH) / disCF(GE_Total(), WW, , TPR(), 1, , CH) '死差益
Sheets("summary").Cells(cex, 13) = disCF(INT_P_Total(), WW, , TPR(), 0, , CH) / disCF(GE_Total(), WW, , TPR(), 1, , CH) '利差益
Sheets("summary").Cells(cex, 14) = disCF(EXP_P_Total(), WW, , TPR(), 0, , CH) / disCF(GE_Total(), WW, , TPR(), 1, , CH) '費差益
Sheets("summary").Cells(cex, 15) = disCF(Lapse_P_Total(), WW, , TPR(), 0, , CH) / disCF(GE_Total(), WW, , TPR(), 1, , CH) '解約益
Sheets("summary").Cells(cex, 16) = disCF(Other_P_RBC_Total(), WW, , TPR(), 0, , CH) / disCF(GE_Total(), WW, , TPR(), 1, , CH) '其他益
Sheets("summary").Cells(cex, 17) = RORAC(Profit_Tax_Total(), RBC_Total(), WW, Range("RDR"), CH) 'RORAC
Sheets("summary").Cells(cex, 18) = disCF(Profit_RBC_Total(), WW, , TPR(), 0, , CH) / disCF(GE_Total(), WW, , TPR(), 1, , CH) 'Profit Margin
Sheets("summary").Rows(cex).Hidden = False
End If
Next CH
If GE_Total(0, 1) <> 0 Then
cex = 1373
Sheets("summary").Cells(cex, 2) = VD_Total(0, 0) / GE_Total(0, 1) '第一年初保費不足
Sheets("summary").Cells(cex, 3) = Cost_Total(0, 1) / GE_Total(0, 1) '第一年總成本
Sheets("summary").Cells(cex, 4) = RBC_Total(0, 1) / GE_Total(0, 1) '第一年風險資本
Sheets("summary").Cells(cex, 5) = 1 - NP_Total(0, 0) / GE_Total(0, 0) 'loading
Sheets("summary").Cells(cex, 6) = BEY(AProfit_RBC_Total(), WW, 0) 'BEY(after RBC)
Sheets("summary").Cells(cex, 7) = BEY(AProfit_Tax_Total(), WW, 0) 'BEY(after tax)
Sheets("summary").Cells(cex, 8) = IRR_value(Profit_RBC_Total(), WW, 20, 0) 'IRR(20年)
Sheets("summary").Cells(cex, 9) = -Profit_Total(0, 1) / GE_Total(0, 1) 'FY_Strain
If Name_inf(test_NUM(0)) = "中國人壽多美鑫旺美元利率變動型終身壽險－定期給付型" Then Sheets("summary").Cells(cex, 9) = Sheets("summary").Cells(cex, 9) - 0.02
Sheets("summary").Cells(cex, 10) = disCF(Profit_RBC_Total(), WW, Range("RDR"), , 0, , 0) / GE_Total(0, 1) 'V0NB
Sheets("summary").Cells(cex, 11) = (disCF(Profit_RBC_Total(), WW, Range("RDR"), , 0, , 0) * (1 + Range("RDR")) - Profit_RBC_Total(0, 1)) / GE_Total(0, 1) 'V1NB
Sheets("summary").Cells(cex, 12) = disCF(Dead_P_Total(), WW, , TPR(), 0, , 0) / disCF(GE_Total(), WW, , TPR(), 1, , 0) '死差益
Sheets("summary").Cells(cex, 13) = disCF(INT_P_Total(), WW, , TPR(), 0, , 0) / disCF(GE_Total(), WW, , TPR(), 1, , 0) '利差益
Sheets("summary").Cells(cex, 14) = disCF(EXP_P_Total(), WW, , TPR(), 0, , 0) / disCF(GE_Total(), WW, , TPR(), 1, , 0) '費差益
Sheets("summary").Cells(cex, 15) = disCF(Lapse_P_Total(), WW, , TPR(), 0, , 0) / disCF(GE_Total(), WW, , TPR(), 1, , 0) '解約益
Sheets("summary").Cells(cex, 16) = disCF(Other_P_RBC_Total(), WW, , TPR(), 0, , 0) / disCF(GE_Total(), WW, , TPR(), 1, , 0) '其他益
Sheets("summary").Cells(cex, 17) = RORAC(Profit_Tax_Total(), RBC_Total(), WW, Range("RDR"), 0) 'RORAC
Sheets("summary").Cells(cex, 18) = disCF(Profit_RBC_Total(), WW, , TPR(), 0, , 0) / disCF(GE_Total(), WW, , TPR(), 1, , 0) 'Profit Margin
End If
End If
End Sub
Sub Cashflow()
If Summary_Total(0) <> 1 Then
WW = DUR_inf(test_NUM(0)) - test_Age(0)
For t = 0 To WW
For i = 2 To 39
Select Case i
Case 33, 34
Out_table(i, t) = Out(i, t)
Case Else
Out_table(i, t) = Out_table(i, t) + Out(i, t) * test_Weight(0)
End Select
Next i
For i = 42 To 56
Out_table(i, t) = Out_table(i, t) + Out(i, t) * test_Weight(0)
Next i
Next t
Else
WW = DUR_inf(test_NUM(0))
For t = 1 To WW
If t = 1 Then Sheets("cashflow").Cells(6, 2) = Out_table(2, 0): Sheets("cashflow").Cells(6, 24) = Out_table(24, 0): Sheets("cashflow").Cells(2, 9) = Out_table(3, t)
For i = 2 To 39
Sheets("cashflow").Cells(6 + t, i) = Out_table(i, t)
Next i
If t = 1 Then Sheets("費用適足性檢測").Cells(13, 2) = Out_table(42, 1)
For i = 42 To 55
Sheets("費用適足性檢測").Cells(13 + t, i - 40) = Out_table(i, t)
Next i
total_gp0 = total_gp0 + Out_table(43, t)
total_gp1 = total_gp1 + Out_table(44, t)
total_comm1 = total_comm1 + Out_table(45, t): total_comm2 = total_comm2 + Out_table(46, t): total_comm3 = total_comm3 + Out_table(47, t): total_comm4 = total_comm4 + Out_table(48, t): total_comm5 = total_comm5 + Out_table(49, t)
total_cost1 = total_cost1 + Out_table(50, t): total_cost2 = total_cost2 + Out_table(51, t): total_cost3 = total_cost3 + Out_table(52, t): total_cost4 = total_cost4 + Out_table(53, t)
total_stab = total_stab + Out_table(54, t)
total_oper = total_oper + Out_table(55, t)
total_np = total_np + Out_table(56, t)
On Error Resume Next
Sheets("費用適足性檢測").Cells(13 + t, 16) = (Out_table(45, t) + Out_table(46, t) + Out_table(47, t) + Out_table(48, t) + Out_table(49, t)) / Out_table(43, t)
Sheets("費用適足性檢測").Cells(13 + t, 17) = Out_table(50, t) / Out_table(43, t)
Sheets("費用適足性檢測").Cells(13 + t, 18) = Out_table(51, t) / Out_table(43, t)
Sheets("費用適足性檢測").Cells(13 + t, 19) = Out_table(52, t) / Out_table(43, t)
Sheets("費用適足性檢測").Cells(13 + t, 20) = Out_table(53, t) / Out_table(43, t)
Sheets("費用適足性檢測").Cells(13 + t, 21) = Out_table(54, t) / Out_table(43, t)
Sheets("費用適足性檢測").Cells(13 + t, 22) = Out_table(55, t) / Out_table(43, t)
Next t
If CUR_inf(test_NUM(0)) = 2 Then
Sheets("費用適足性檢測").Cells(7, 1) = Mid(Name_inf(test_NUM(0)), 5, Application.Find("美元", Name_inf(test_NUM(0))) - 5)
Else
If PT1_inf(test_NUM(0)) = "利變型" Then
Sheets("費用適足性檢測").Cells(7, 1) = Mid(Name_inf(test_NUM(0)), 5, Application.Find("利率", Name_inf(test_NUM(0))) - 5)
Else
Sheets("費用適足性檢測").Cells(7, 1) = Mid(Name_inf(test_NUM(0)), 5, Application.Find("終身", Name_inf(test_NUM(0))) - 5)
End If
End If
Sheets("費用適足性檢測").Cells(7, 5) = total_comm1 / total_gp1
Sheets("費用適足性檢測").Cells(7, 5) = total_comm1 / total_gp1
Sheets("費用適足性檢測").Cells(7, 6) = total_comm2 / total_gp1
Sheets("費用適足性檢測").Cells(7, 7) = total_comm3 / total_gp1
Sheets("費用適足性檢測").Cells(7, 8) = total_comm4 / total_gp1
Sheets("費用適足性檢測").Cells(7, 9) = total_comm5 / total_gp1
Sheets("費用適足性檢測").Cells(7, 10) = (total_comm1 + total_comm2 + total_comm3 + total_comm4 + total_comm5) / total_gp1
Sheets("費用適足性檢測").Cells(7, 11) = total_cost1 / total_gp1
Sheets("費用適足性檢測").Cells(7, 12) = total_cost2 / total_gp1
Sheets("費用適足性檢測").Cells(7, 13) = total_cost3 / total_gp1
Sheets("費用適足性檢測").Cells(7, 14) = total_cost4 / total_gp1
Sheets("費用適足性檢測").Cells(7, 15) = (total_cost1 + total_cost2 + total_cost3 + total_cost4) / total_gp1
Sheets("費用適足性檢測").Cells(7, 16) = total_stab / total_gp1
Sheets("費用適足性檢測").Cells(7, 17) = total_oper / total_gp1
Sheets("費用適足性檢測").Cells(7, 18) = total_np / total_gp1 - total_np / total_gp0
Sheets("費用適足性檢測").Cells(7, 19) = 1 - total_np / total_gp0
Sheets("費用適足性檢測").Cells(7, 20) = Sheets("費用適足性檢測").Cells(7, 19) - Sheets("費用適足性檢測").Cells(7, 10) - Sheets("費用適足性檢測").Cells(7, 15) - Sheets("費用適足性檢測").Cells(7, 16) - Sheets("費用適足性檢測").Cells(7, 17) - Sheets("費用適足性檢測").Cells(7, 18)
Sheets("費用適足性檢測").Cells(7, 21) = (Sheets("費用適足性檢測").Cells(7, 10) + Sheets("費用適足性檢測").Cells(7, 15) + Sheets("費用適足性檢測").Cells(7, 16) + Sheets("費用適足性檢測").Cells(7, 17)) / (Sheets("費用適足性檢測").Cells(7, 19) - Sheets("費用適足性檢測").Cells(7, 18))
End If
End Sub
Public Sub CSM_Test()
Erase GUR_B, EXP_ALL, G_NCF, D_CSM, dis_ADCF_scenario, dis_ADCF_CSM, BEL, dis_NADCF_CSM, RA, dis_G_NCF_CSM, A_INV, A_DIS_CSM, A_DIR, Out_A
WW = DUR_inf(test_NUM(0)) - test_Age(0)
Assumption
Commission
' If Sheets("Setup").Cells(15, 8) = "Total" Then
' S_S = 1: S_E = 1000: ss = 1000
' Else
' S_S = Sheets("Setup").Cells(15, 8): S_E = Sheets("Setup").Cells(15, 8): ss = 1
' End If
'
'' For t = 1 To WW
'' '宣告利率
'' If PT1_inf(test_NUM(0)) = "利變型" Then
'' If CUR_inf(test_NUM(0)) < 2 Then
'' spread_L = 0.01
'' spread_U = 0.025
'' Else
'' spread_L = 0.0125
'' spread_U = 0.025
'' End If
''
'' For Scenario = S_S To S_E
'' If t = 1 Then Dir(0) = DIR_inf(test_NUM(0)): Dir_CSM(Scenario, t) = Dir(0)
'' Dir_CSM(Scenario, t + 1) = max(min(INV_1000(CUR_inf(test_NUM(0)), Scenario, t) - spread_L, Dir(0)), INV_1000(CUR_inf(test_NUM(0)), Scenario, t) - spread_U)
'' A_INV(t) = A_INV(t) + INV_1000(CUR_inf(test_NUM(0)), Scenario, t) / ss
'' A_DIS_CSM(t) = A_DIS_CSM(t) + DIS_1000(CUR_inf(test_NUM(0)), Scenario, t) / ss
'' A_DIR(t) = A_DIR(t) + Dir_CSM(Scenario, t) / ss
'' Next Scenario
'' End If
'' DIS_CSM(t) = DIS_CSM_CUR(CUR_inf(test_NUM(0)), t)
'' Next t
'
'
''1121121 修改宣告利率公式
'
' For t = 1 To WW
' '宣告利率
' If PT1_inf(test_NUM(0)) = "利變型" Then
'
' For Scenario = S_S To S_E
' If t = 1 Then Dir(0) = DIR_inf(test_NUM(0)): Dir_CSM(Scenario, t) = Dir(0)
' Rein_W(CUR_inf(test_NUM(0)), Scenario, t) = Rein_B1(test_NUM(0)) * (INV_1000(CUR_inf(test_NUM(0)), Scenario, t) - INV_1000(CUR_inf(test_NUM(0)), Scenario, t - 1)) + Rein_B2(test_NUM(0)) * (Dir_CSM(Scenario, t) - Rein_BM(test_NUM(0), t))
' Rein_ACC_W(CUR_inf(test_NUM(0)), Scenario, t) = Rein_ACC_W(CUR_inf(test_NUM(0)), Scenario, t - 1) + Rein_W(CUR_inf(test_NUM(0)), Scenario, t)
' Dir_CSM(Scenario, t + 1) = max(0, INV_1000(CUR_inf(test_NUM(0)), Scenario, t) - Rein_P(test_NUM(0)) - Rein_E(test_NUM(0)) - Rein_W(CUR_inf(test_NUM(0)), Scenario, t))
' A_INV(t) = A_INV(t) + INV_1000(CUR_inf(test_NUM(0)), Scenario, t) / ss
' A_DIS_CSM(t) = A_DIS_CSM(t) + DIS_1000(CUR_inf(test_NUM(0)), Scenario, t) / ss
' A_DIR(t) = A_DIR(t) + Dir_CSM(Scenario, t) / ss
'
' Next Scenario
' End If
' DIS_CSM(t) = DIS_CSM_CUR(CUR_inf(test_NUM(0)), t)
' Next t
'***************************************** 11/24 進度 *************************************
For Scenario = 1 To 1
Profit_Test
For t = 1 To WW
'保證給付、現金流
GUR_B(t) = P_DB(t) + CV_cost(t) + P_SB(t) + P_MB(t) + P_WP(t) + P_AD(t) + P_HB(t)
'COMM(T) = GE(T) * COMM_rate(T)
'COMM_1(T) = GE(T) * COMM_rate_exp1(T)
'COMM_2(T) = COMM(T) - COMM_1(T)
' EXP(T) = Exp_Fix(T) * EFF(T - 1) + Exp_Var(T) * GE(T)
' EXP_ALL(T) = COMM(T) + Tax(T) + EXP(T) * 0.52
' G_NCF(T) = GUR_B(T) + EXP_ALL(T)
COMM(t) = GE(t) * COMM_CSM(t)
COMM_1(t) = GE(t) * COMM_rate_exp1(t)
COMM_2(t) = COMM(t) - COMM_1(t)
EXP(t) = Exp_CSMFix(t) * EFF(t - 1) + Exp_CSMVar(t) * GE(t)
EXP_ALL(t) = COMM(t) + TaxCSM(t) + EXP(t) + 0.0004 * (P_DB(t) + P_WP(t) + P_AD(t) + P_HB(t))
G_NCF(t) = GUR_B(t) + EXP_ALL(t)
G_RA(t) = GE(t) * COMM_RA(t) + Exp_RAFix(t) * EFF(t - 1) + Exp_RAVar(t) * GE(t) + 0.0004 * (P_DB(t) + P_WP(t) + P_AD(t) + P_HB(t))
'回饋金
' For Scenario = S_S To S_E
' D_CSM(t) = D_CSM(t) + D_HW(Scenario, t) / ss
' Next Scenario
Next t
'BEL
For t = WW To 1 Step -1
' For Scenario = S_S To S_E
dis_ADCF_scenario(Scenario) = (dis_ADCF_scenario(Scenario) + D_HW(Scenario, t)) / (1 + DIS_1000(CUR_inf(test_NUM(0)), Scenario, t))
' dis_ADCF_CSM(t - 1) = dis_ADCF_CSM(t - 1) + dis_ADCF_scenario(Scenario) / ss
dis_ADCF_CSM(t - 1) = dis_ADCF_CSM(t - 1) + dis_ADCF_scenario(Scenario)
' Next Scenario
dis_NADCF_CSM(t - 1) = disCF(G_NCF(), WW, , DIS_CSM(), 0, t) - disCF(GE(), WW, , DIS_CSM(), 1, t)
dis_NADCF_RA(t - 1) = disCF(G_RA(), WW, , DIS_CSM(), 0, t) - disCF(GE(), WW, , DIS_CSM(), 1, t)
BEL(t - 1) = dis_NADCF_CSM(t - 1) + dis_ADCF_CSM(t - 1)
BEL_RA(t - 1) = dis_NADCF_RA(t - 1) + dis_ADCF_CSM(t - 1)
Next t
'RA
For t = 1 To WW
RA(t - 1) = (disCF(G_NCF(), WW, , DIS_CSM(), 0, t) + dis_ADCF_CSM(t - 1)) * 0.0092
Next t
If Sheets("Setup").Cells(14, 8) = "Y" Then
For t = 0 To WW
Out_A(2, t) = EFF(t)
Out_A(3, t) = GE(t)
Out_A(4, t) = GUR_B(t)
Out_A(5, t) = EXP_ALL(t)
Out_A(6, t) = G_NCF(t)
Out_A(7, t) = D_CSM(t)
Out_A(8, t) = dis_NADCF_CSM(t)
Out_A(9, t) = dis_ADCF_CSM(t)
Out_A(10, t) = BEL(t)
Out_A(11, t) = RA(t)
Out_A(20, t) = D_CSM(t) - D(t)
Out_A(24, t) = A_INV(t)
Out_A(25, t) = A_DIS_CSM(t)
Out_A(26, t) = A_DIR(t)
Out_A(27, t) = COMM(t)
Out_A(28, t) = EXP(t)
Out_A(29, t) = P_DB(t)
Out_A(30, t) = CV_cost(t)
Out_A(31, t) = P_SB(t)
Out_A(32, t) = P_MB(t)
Out_A(33, t) = P_WP(t) + P_AD(t) + P_HB(t)
Out_A(34, t) = Tax(t)
Out_A(35, t) = Invest(t)
Out_A(36, t) = In_SA(t)
Out_A(37, t) = Asset(t)
Out_A(38, t) = d_V(t)
Out_A(39, t) = D(t)
Next t
End If
Next Scenario
End Sub
Sub Cashflow_CSM()
Dim VI(120)
Erase VI
If Summary_Total(0) <> 1 Then
WW = DUR_inf(test_NUM(0)) - test_Age(0)
For t = 0 To WW
For i = 2 To 39
Select Case i
Case 24, 25, 26
Out_table_CSM(i, t) = Out_A(i, t)
Case Else
Out_table_CSM(i, t) = Out_table_CSM(i, t) + Out_A(i, t) * test_Weight(0)
End Select
Next i
GE_table(t) = GE_table(t) + GE(t) * test_Weight(0)
Profit_RBC_table(t) = Profit_RBC_table(t) + Profit_RBC(t) * test_Weight(0)
Next t
Else
WW = DUR_inf(test_NUM(0))
'攤銷
Out_table_CSM(13, 0) = max(-(Out_table_CSM(10, 0) + Out_table_CSM(11, 0)), 0)
For t = 1 To WW
AEFF(WW + 1 - t) = AEFF(WW + 2 - t) + Out_table_CSM(2, WW + 1 - t)
Next t
VI(0) = 1
For t = 1 To WW
If Sheets("Setup").Cells(17, 4) = "商發" Then
VI(t) = 1
ElseIf Sheets("Setup").Cells(17, 4) = "精算" Then
VI(t) = VI(t - 1) / (1 + DIS_CSM(t))
End If
Next t
For t = 1 To WW
AEFF(WW + 1 - t) = AEFF(WW + 2 - t) + Out_table_CSM(2, WW + 1 - t) * VI(WW + 1 - t)
Next t
For t = 1 To WW
If AEFF(t) <> 0 Then Out_table_CSM(15, t) = Out_table_CSM(13, t - 1) * (1 + DIS_CSM(t)) * Out_table_CSM(2, t) * VI(t) / AEFF(t)
Out_table_CSM(13, t) = Out_table_CSM(13, t - 1) * (1 + DIS_CSM(t)) - Out_table_CSM(15, t)
Out_table_CSM(14, t) = Out_table_CSM(13, t - 1) * DIS_CSM(t)
Out_table_CSM(12, t) = Out_table_CSM(11, t - 1) - Out_table_CSM(11, t)
Out_table_CSM(16, t) = (Out_table_CSM(10, t - 1) + Out_table_CSM(3, t)) * DIS_CSM(t)
Out_table_CSM(17, t) = Out_table_CSM(8, t) - Out_table_CSM(8, t - 1) + Out_table_CSM(9, t) - Out_table_CSM(9, t - 1) + Out_table_CSM(4, t) + Out_table_CSM(5, t) + Out_table_CSM(7, t) - Out_table_CSM(3, t) - Out_table_CSM(16, t)
Out_table_CSM(18, t) = Out_table_CSM(13, t - 1) * DIS_CSM(t)
Out_table_CSM(19, t) = Out_table_CSM(16, t) + Out_table_CSM(17, t) + Out_table_CSM(18, t)
Out_table_CSM(21, t) = Out_table_CSM(35, t) - Out_table_CSM(19, t) + Out_table_CSM(20, t)
Out_table_CSM(22, t) = -Out_table_CSM(28, t) * (1 - 0.52)
Out_table_CSM(23, t) = Out_table_CSM(12, t) + Out_table_CSM(15, t) + Out_table_CSM(21, t) + Out_table_CSM(22, t)
Next t
For t = 1 To WW
Out_table_CSM(48, t) = Out_table_CSM(15, t)
Out_table_CSM(49, t) = Out_table_CSM(12, t)
If Out_table_CSM(36, t) <> 0 Then Out_table_CSM(50, t) = Out_table_CSM(29, t) * max(Out_table_CSM(36, t) - Out_table_CSM(37, t), 0) / Out_table_CSM(36, t)
Out_table_CSM(51, t) = Out_table_CSM(27, t) + Out_table_CSM(28, t) * 0.52 + Out_table_CSM(34, t) - Out_table_CSM(27, t) * max(2 - t, 0)
If AEFF(t) <> 0 Then
If t = 1 Then IACF_COMM = Out_table_CSM(27, t) Else IACF_COMM = IACF_COMM - Out_table_CSM(52, t - 1)
Out_table_CSM(52, t) = IACF_COMM * Out_table_CSM(2, t) * VI(t) / AEFF(t)
End If
Out_table_CSM(47, t) = Out_table_CSM(48, t) + Out_table_CSM(49, t) + Out_table_CSM(50, t) + Out_table_CSM(51, t) + Out_table_CSM(52, t)
Out_table_CSM(54, t) = Out_table_CSM(50, t) + Out_table_CSM(51, t) + Out_table_CSM(52, t)
Out_table_CSM(55, t) = 0
Out_table_CSM(56, t) = Out_table_CSM(50, t)
Out_table_CSM(57, t) = Out_table_CSM(51, t)
Out_table_CSM(58, t) = Out_table_CSM(52, t)
Out_table_CSM(60, t) = Out_table_CSM(47, t) - Out_table_CSM(54, t)
Out_table_CSM(62, t) = Out_table_CSM(35, t)
Out_table_CSM(65, t) = Out_table_CSM(16, t) + Out_table_CSM(17, t)
Out_table_CSM(66, t) = 0
Out_table_CSM(67, t) = Out_table_CSM(18, t)
Out_table_CSM(68, t) = 0
Out_table_CSM(69, t) = -Out_table_CSM(20, t)
Out_table_CSM(64, t) = Out_table_CSM(65, t) + Out_table_CSM(66, t) + Out_table_CSM(67, t) + Out_table_CSM(68, t) + Out_table_CSM(69, t)
Out_table_CSM(70, t) = Out_table_CSM(62, t) - Out_table_CSM(64, t)
Out_table_CSM(72, t) = -Out_table_CSM(22, t)
Out_table_CSM(74, t) = Out_table_CSM(60, t) + Out_table_CSM(70, t) - Out_table_CSM(72, t)
Out_table_CSM(77, t) = Out_table_CSM(3, t) + Out_table_CSM(35, t)
Out_table_CSM(78, t) = Out_table_CSM(3, t)
Out_table_CSM(79, t) = Out_table_CSM(35, t)
Out_table_CSM(82, t) = Out_table_CSM(27, t)
Out_table_CSM(83, t) = Out_table_CSM(28, t) + Out_table_CSM(34, t)
Out_table_CSM(84, t) = Out_table_CSM(29, t)
Out_table_CSM(85, t) = Out_table_CSM(30, t)
Out_table_CSM(86, t) = Out_table_CSM(33, t)
Out_table_CSM(87, t) = Out_table_CSM(31, t) + Out_table_CSM(32, t)
Out_table_CSM(88, t) = Out_table_CSM(39, t)
Out_table_CSM(89, t) = Out_table_CSM(38, t)
Out_table_CSM(81, t) = Out_table_CSM(82, t) + Out_table_CSM(83, t) + Out_table_CSM(84, t) + Out_table_CSM(85, t) + Out_table_CSM(86, t) + Out_table_CSM(87, t) + Out_table_CSM(88, t) + Out_table_CSM(89, t)
Out_table_CSM(91, t) = Out_table_CSM(77, t) - Out_table_CSM(81, t)
Next t
For t = 0 To WW
If t = 1 Then Sheets("Cashflow(CSM)").Cells(2, 9) = Out_table_CSM(3, t)
For i = 2 To 39
If t = 0 Then
Select Case i
Case 2, 8, 9, 10, 11, 13
Sheets("cashflow(CSM)").Cells(6 + t, i) = Out_table_CSM(i, t)
End Select
Else
Sheets("cashflow(CSM)").Cells(6 + t, i) = Out_table_CSM(i, t)
End If
Next i
Next t
For t = 1 To WW
For i = 7 To 51
Select Case i
Case 13, 19, 21, 23, 31, 33, 35, 36, 40, 50
Case Else
Sheets("CSM").Cells(i, 2 + t) = Out_table_CSM(i + 40, t)
End Select
Next i
Next t
If Sheets("Setup").Cells(7, 3) = 1 Then
Sheets("CSM").Cells(1, 1) = Name_inf(test_NUM(0)) & " CSM測試(情境測試假設)"
Sheets("Cashflow(CSM)").Cells(1, 1) = Name_inf(test_NUM(0)) & " CSM測試(情境測試假設)"
ElseIf Sheets("Setup").Cells(7, 3) = 2 Then
Sheets("CSM").Cells(1, 1) = Name_inf(test_NUM(0)) & " CSM測試(標準假設)"
Sheets("Cashflow(CSM)").Cells(1, 1) = Name_inf(test_NUM(0)) & " CSM測試(標準假設)"
ElseIf Sheets("Setup").Cells(7, 3) = 3 Then
Sheets("CSM").Cells(1, 1) = Name_inf(test_NUM(0)) & " CSM測試(最佳估計)"
Sheets("Cashflow(CSM)").Cells(1, 1) = Name_inf(test_NUM(0)) & " CSM測試(最佳估計)"
Else
Sheets("CSM").Cells(1, 1) = Name_inf(test_NUM(0)) & " CSM測試"
Sheets("Cashflow(CSM)").Cells(1, 1) = Name_inf(test_NUM(0)) & " CSM測試"
End If
If CUR_inf(test_NUM(0)) = 1 Then Sheets("CSM").Cells(4, 1) = "台幣" Else Sheets("CSM").Cells(4, 1) = "美元"
If PT1_inf(test_NUM(0)) = "利變型" Then Sheets("CSM").Cells(4, 1) = Sheets("CSM").Cells(4, 1) & "利變壽" Else Sheets("CSM").Cells(4, 1) = Sheets("CSM").Cells(4, 1) & "傳統"
disGE_CSM_table(0) = disCF(GE_table(), WW, , DIS_CSM(), 1)
If CUR_inf(test_NUM(0)) = 2 Then
Sheets("CSM").Cells(4, 4) = Mid(Name_inf(test_NUM(0)), 5, Application.Find("美元", Name_inf(test_NUM(0))) - 5)
Else
If PT1_inf(test_NUM(0)) = "利變型" Then
Sheets("CSM").Cells(4, 4) = Mid(Name_inf(test_NUM(0)), 5, Application.Find("利率", Name_inf(test_NUM(0))) - 5)
Else
Sheets("CSM").Cells(4, 4) = Mid(Name_inf(test_NUM(0)), 5, Application.Find("終身", Name_inf(test_NUM(0))) - 5)
End If
End If
Sheets("CSM").Cells(4, 5) = (Out_table_CSM(8, 0) + disGE_CSM_table(0)) / disGE_CSM_table(0)
Sheets("CSM").Cells(4, 6) = Out_table_CSM(9, 0) / disGE_CSM_table(0)
Sheets("CSM").Cells(4, 7) = Out_table_CSM(11, 0) / disGE_CSM_table(0)
Sheets("CSM").Cells(4, 8) = Out_table_CSM(13, 0) / disGE_CSM_table(0)
Sheets("CSM").Cells(4, 9) = Out_table_CSM(13, 0) / GE_table(1)
Sheets("CSM").Cells(4, 10) = disCF(Profit_RBC_table(), WW, Range("RDR"), , 0) / GE_table(1)
Sheets("CSM").Cells(4, 11) = disCF(Profit_RBC_table(), WW, , TPR(), 0) / disCF(GE_table(), WW, , TPR(), 1)
Sheets("CSM").Cells(74, 3) = Sheets("CSM").Cells(4, 3)
Sheets("CSM").Cells(75, 3) = GE_table(1)
Sheets("CSM").Cells(76, 3) = Out_table_CSM(8, 0)
Sheets("CSM").Cells(77, 3) = Out_table_CSM(9, 0)
Sheets("CSM").Cells(78, 3) = Out_table_CSM(11, 0)
Sheets("CSM").Cells(79, 3) = Out_table_CSM(8, 0) + Out_table_CSM(9, 0) + Out_table_CSM(11, 0)
Sheets("CSM").Cells(80, 3) = -(Out_table_CSM(8, 0) + Out_table_CSM(9, 0) + Out_table_CSM(11, 0))
Sheets("CSM").Cells(81, 3) = disGE_CSM_table(0)
Sheets("CSM").Cells(82, 3) = -(Out_table_CSM(8, 0) + Out_table_CSM(9, 0) + Out_table_CSM(11, 0)) / disGE_CSM_table(0)
For t = 1 To 20
If t <= 10 Then cex = 86: CEY = 2 Else cex = 92: CEY = -9
If t = 1 Then Sheets("CSM").Cells(cex, CEY) = Out_table_CSM(13, 0) / Out_table_CSM(3, 1)
On Error Resume Next
Sheets("CSM").Cells(cex, CEY + t) = Out_table_CSM(13, t) / Out_table_CSM(3, 1)
Sheets("CSM").Cells(cex + 1, CEY + t) = Out_table_CSM(2, t) * VI(t) / AEFF(t)
Sheets("CSM").Cells(cex + 2, CEY + t) = Out_table_CSM(15, t) / Out_table_CSM(3, 1)
Next t
End If
End Sub
Sub 費差()
Erase GE0_EXP, GE_EXP, COMM_EXP1, COMM_EXP2, COMM_EXP3, COMM_EXP4, COMM_EXP5, COST_EXP1, COST_EXP2, COST_EXP3, COST_EXP4, Stab_EXP, Oper_EXP, GE_EXP_Sum, EXP_diff
NN = test_NN(0)
For t = 1 To NN
GE0_EXP(t) = GE_0(1)
GE_EXP(t) = GE(1)
COMM_EXP1(t) = GE(1) * COMM_rate_exp1(t)
COMM_EXP2(t) = GE(1) * COMM_rate_exp2(t)
COMM_EXP3(t) = GE(1) * COMM_rate_exp3(t)
COMM_EXP4(t) = GE(1) * COMM_rate_exp4(t)
COMM_EXP5(t) = GE(1) * COMM_rate_exp5(t)
COST_EXP1(t) = 0
COST_EXP2(t) = GE(1) * EXP_test_Var3(t)
COST_EXP3(t) = EXP_test_Fix2(t)
COST_EXP4(t) = GE(1) * EXP_test_Var2(t) + EXP_test_Fix1(t)
Stab_EXP(t) = GE(1) * Range("Tax")(3)
Oper_EXP(t) = GE(1) * 0.00007
GE_EXP_Sum(0) = GE_EXP_Sum(0) + GE_EXP(t)
EXP_diff(0) = EXP_diff(0) + GE_EXP(t) * Loading(0) - COMM_EXP1(t) - COMM_EXP2(t) - COMM_EXP3(t) - COMM_EXP4(t) - COMM_EXP5(t) - COST_EXP1(t) - COST_EXP2(t) - COST_EXP3(t) - COST_EXP4(t) - Stab_EXP(t) - Oper_EXP(t)
Next t
If Sheets("Setup").Cells(6, 8) = "Y" Then
For t = 1 To NN
Out(42, t) = EFF(0)
Out(43, t) = GE0_EXP(t)
Out(44, t) = GE_EXP(t)
Out(45, t) = COMM_EXP1(t)
Out(46, t) = COMM_EXP2(t)
Out(47, t) = COMM_EXP3(t)
Out(48, t) = COMM_EXP4(t)
Out(49, t) = COMM_EXP5(t)
Out(50, t) = COST_EXP1(t)
Out(51, t) = COST_EXP2(t)
Out(52, t) = COST_EXP3(t)
Out(53, t) = COST_EXP4(t)
Out(54, t) = Stab_EXP(t)
Out(55, t) = Oper_EXP(t)
Out(56, t) = GE_EXP(t) * (1 - Loading(0))
Next t
End If
End Sub
Editor is loading...
Leave a Comment