保誠人壽愛美鑫(rlplu28)外幣終生壽險（分紅保單）IRR分析(0歲女),註冊保誠人壽網路投保會員,輸入親友推薦序號(WCGXNV) 加贈100元悠遊金,最高可領1000元;

# -*- coding: utf-8 -*-
"""
Created on Tue May 23 18:57:45 2023
@author: SavingKing
03: 
    j,k合為同一個迴圈(死亡IRR),且使用enumerate
    df使用"年"當index,以免cash_value,death_insurance
    資料的年度不一致,還可以正確合併
"""
import numpy_financial as npf
import numpy as np
import pandas as pd
import os
#保誠愛美鑫:
cash_value:list=[606,1818,3182,4545,5909,
                 7878,9242,11514,13332,15908,
                 24240,40543,42075,43643,45246]  
#0歲女 #1~15年末解約金(上),死亡保額(下)
death_insurance:list=[19219]*15
rate=210/100 #30歲以下 
#180/100 if 31~40歲
#160/100 if 41~50歲
cash_valueXrate:list = list( np.array(cash_value) * rate )
capital_protection =12
increasing_rate = [np.nan]*capital_protection #12年末保本
#從保本才開始算解約金增加率
for i in range(capital_protection, len(cash_value)):
    increase = cash_value[i]/cash_value[i-1]-1
    increasing_rate.append(increase)
"""
len(increasing_rate)
Out[24]: 15
"""
discount = (0)/100 #花旗,星展,華南 
# 樂天10萬回饋2500(2.5%)
# 遠銀百萬保費3.5%
pay:float = -3000* (1-discount) #-60570  #-59358.6
n_years = 10
lisIRR_death = []
for i, value in enumerate(death_insurance, 0):
    if i < n_years: #i=0,1,2
        cash_flow = [pay]*(i+1)+[death_insurance[i]]
    else:
        lis0 = [0]*(i-n_years+1)
        cash_flow = [pay]*n_years+lis0+[value]
    print(f"第{i+1:02d}年末死亡cashflow:",cash_flow)
    irr = npf.irr(cash_flow)
    lisIRR_death.append(irr)
for m,irr in enumerate(lisIRR_death,1):
    print(f"第{m:02d}年末({m+40}歲)死亡IRR:\t{irr:.4%}")
lisIRR_death = [f"{irr:.4%}" for irr in lisIRR_death]
dic={"死亡IRR":lisIRR_death , "年": list(range(1,len(death_insurance)+1))}
dfIRR_death = pd.DataFrame(dic).set_index("年")
#以"年"當index,以免cash_value跟death_insurance資料不一致
#這樣合併資料還可以正確
lisIRR = []
totReturn = []
for i, value in enumerate(cash_value, 0):
    if i < n_years: #i=0,1,2
        cash_flow = [pay]*(i+1)+[cash_value[i]]
    else:
        lis0 = [0]*(i-n_years+1)
        cash_flow = [pay]*n_years+lis0+[value]
    print(f"第{i+1:02d}年末cashflow:",cash_flow)
    irr = npf.irr(cash_flow)
    lisIRR.append(irr)
    tot = sum(cash_flow)/abs(sum([pay]*n_years))
    totReturn.append(tot)
lisIRRx2 = [] #0歲女其實是*2.1倍
for i, value in enumerate(cash_valueXrate, 0):
    if i < n_years: #i=0,1,2
        # cash_flow = [pay]*(i+1)+[cash_valueXrate[i]]
        cash_flow = [pay]*(i+1)+[value]
    else:
        lis0 = [0]*(i-n_years+1)
        cash_flow = [pay]*n_years+lis0+[value]
    print(f"第{i+1:02d}年末cashflow X rate:",cash_flow)
    irr = npf.irr(cash_flow)
    lisIRRx2.append(irr)
    
lisIRR = [f"{irr:.4%}" for irr in lisIRR]
totReturn = [f"{ele:.4%}" for ele in totReturn]
increasing_rate = [f"{ele:.4%}" for ele in increasing_rate]
# lisIRR_death = [f"{ele:.4%}" for ele in lisIRR_death]
lisIRRx2 =  [f"{irr:.4%}" for irr in lisIRRx2]
dic = {
    "年"           : list(range(1, len(cash_value)+1)),
    "IRR"          : lisIRR,
    "總報酬率"      : totReturn,
    "增加率"        : increasing_rate,
    "解約金x2.1_IRR": lisIRRx2
    # "死亡IRR"     : lisIRR_death
    }
df = pd.DataFrame(dic).set_index("年")
df_concat = pd.concat([df,dfIRR_death],axis=1)
print("\n",df_concat)
cwd = os.getcwd()
exFolder = os.path.join(cwd,"export")
if not os.path.exists(exFolder):
    os.makedirs(exFolder)
xlsx_path = "\\".join( [exFolder,"rlplu28_irr.xlsx"] )
df_concat.to_excel(xlsx_path)