富邦人壽富貴長紅(PAA)分紅終身壽險(台幣躉繳)IRR分析(死亡IRR最高30%) #富貴都是韭菜幫的 #香港保單集合

# -*- 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
    資料的年度不一致,還可以正確合併
04:
    修正未滿期時的總報酬率計算錯誤
05:
    模組化,刪除已經註解的保誠資料,__file__擷取當前py檔路徑
    輸出的xlsx檔名,可以跟py檔 主檔名相同
05_1:
    原始資料為xlsx檔,看不到列數,無法正確skiprows, skipfooter
"""
import numpy_financial as npf
import numpy as np
import pandas as pd
import os

# =============================================================================
# folder   = r"P:\Python\financial\富邦\PAA-富貴長紅"
# basename = "PAA-富貴長紅專案試算表V1.4-1120825-保經代_45歲男.xlsx"
# fpath = os.path.join( folder, basename)
# dfRaw = pd.read_excel(fpath,sheet_name="列印頁",skiprows=16)
# =============================================================================


# =============================================================================
# survival_insurance:list=[109,   326,  651,    1085,  1627,
#                          2277,  3075, 3873,   4671,  5469,
#                          6267,  7065, 7863,   8661,  9459,
#                          10257, 11055, 11853, 12651, 13449]
# =============================================================================
#若無還本金設計: 
survival_insurance:list=[0]*15
ary_survival_insurance = np.array(survival_insurance)

cash_value:list=[526000, 578474, 631951, 686409, 741683,
                 843063, 863730, 884950, 906209, 927718,
                 949839, 972193, 994984,1018115, 1041588]
                 # 78695, 81076, 83524, 86052, 88651] 
ary_cash_value = np.array(cash_value)

cash_value = list( ary_cash_value + ary_survival_insurance)
# cash_value:list=[5244+109,  12125+326, 19265+651, 26726+1085, 34475+1627,
#                  56483+2277, 59969+3075, 61913+3873, 63889+4671, 65840+5469,
#                  67809+6267, 69854+7065, 71968+7863, 74147+8661, 76387+9459,
#                  78695+10257, 81076+11055, 83524+11853, 86052+12651, 88651+13449]
##上述寫法,cash_value加一次,death_insurance又要再加一次,因此使用np.array

#富邦人壽美年紅旺PPC #1~20年末解約金(上),死亡保額(下)
death_insurance:list=[1000000, 1012474, 1025251, 1046509, 1068583,
                      1136163, 1124830, 1139750, 1154609, 1169718,
                      1185439, 1201493, 1217884, 1234615, 1251688]
                      # 100742,103147,105618,108168,110790]
ary_death_insurance = np.array(death_insurance)
death_insurance = list(ary_death_insurance + ary_survival_insurance)


capital_protection = 6 #6年末保本
discount = (0)/100 
# 2% 花旗,星展,華南 
# 樂天10萬回饋2500(2.5%)
# 遠銀百萬保費3.5%
pay:float = -769135* (1-discount) #-60570  #-59358.6
n_years = 1

def get_increasing_rate(capital_protection=capital_protection,
                        cash_value=cash_value,fmt_str=True):
    
    increasing_rate = [np.nan]*capital_protection 
    #從保本才開始算解約金增加率
    
    for i in range(capital_protection, len(cash_value)):
        increase = cash_value[i]/cash_value[i-1]-1
        increasing_rate.append(increase)
    if fmt_str == True:
        increasing_rate = [f"{ele:.4%}" for ele in increasing_rate]
    return increasing_rate #list

increasing_rate = \
get_increasing_rate(capital_protection=capital_protection,
                    cash_value=cash_value)


def get_lisIRR_totReturn(pay=pay, discount=discount,
                         n_years=n_years,
                         cash_value=cash_value, start=1,
                         fmt_str=True):

    lisIRR = []
    totReturn = []
    
    for i, value in enumerate(cash_value, start-1):
        if i < n_years: #i=0,1,2
            lis_pay=[pay]*(i+1)
            cash_flow = lis_pay+[value]
            tot = sum(cash_flow)/abs(sum( lis_pay ) )
        else:
            lis_pay=[pay]*n_years
            lis0 = [0]*(i-n_years+1)
            cash_flow = lis_pay+lis0+[value]
            tot = sum(cash_flow)/abs(sum( lis_pay ))
        print(f"第{i+1:02d}年末cashflow:",cash_flow)
        irr = npf.irr(cash_flow)
        lisIRR.append(irr)
        totReturn.append(tot)
    
    if fmt_str == True:
        lisIRR = [f"{ele:.4%}" for ele in lisIRR]
        totReturn = [f"{ele:.4%}" for ele in totReturn]
        
    return lisIRR,totReturn #list
    #return的縮排不要在for迴圈之內,不然lisIRR只會有一個元素

lisIRR,totReturn = get_lisIRR_totReturn(pay=pay,discount=discount,
                         n_years=n_years,
                         cash_value=cash_value,start=1)
# print("lisIRR:",lisIRR)

lisIRR_death,totReturn_death = \
get_lisIRR_totReturn(pay=pay,discount=discount,
                     n_years=n_years,
                     cash_value=death_insurance,start=1)

# =============================================================================
##lisIRR_death 要重複使用function get_lisIRR_totReturn
# 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 ,"死亡總報酬率":totReturn_death, 
     "年": list(range(1,len(death_insurance)+1))}
dfIRR_death = pd.DataFrame(dic).set_index("年")
#以"年"當index,以免cash_value跟death_insurance資料不一致
#這樣合併資料還可以正確
  

# 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,
    }

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)

# 獲取當前腳本的文件路徑
current_script_path = __file__
#'p:\\python\\financial\\富邦\\pfa-美富紅運30歲6年\\irr_05_富邦人壽美富紅運外幣分紅終身壽險6年.py'

# 提取主檔名
#main_filename = os.path.basename(current_script_path).split('.')[0]
main_filename = os.path.splitext(os.path.basename(current_script_path))[0]
"""
os.path.basename(current_script_path)
Out[21]: 'irr_05_富邦人壽美富紅運外幣分紅終身壽險6年.py'

os.path.splitext(os.path.basename(current_script_path))
Out[20]: ('irr_05_富邦人壽美富紅運外幣分紅終身壽險6年', '.py')
"""

# 打印主檔名
print("\npy主檔名:", main_filename)

xlsx_path = "\\".join( [exFolder,f"{main_filename}.xlsx"] )
df_concat.to_excel(xlsx_path)
print(f"\nxlsx檔已經輸出到:\n{xlsx_path}")