Modul1

mail@pastecode.io avatar
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))

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


Leave a Comment