# Modul1

unknown
vbscript
7 months ago
104 kB
5
Indexable
Never
```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))

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

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

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

'Erase Dir_CSM, Dir_報部

'CSM 1000投報率、折現率

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

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)), 取位)
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(30, t) = P_HB(t)
Out(31, t) = ReinGP(t)
Out(33, t) = TPR(t)
Out(34, t) = Dir(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, 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)
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, 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, 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()

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
''            Else
''            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))
'        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)
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(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)