def net_earning_after_tax(data_dic, input_dic, cal=False, semi=False): net_earning_bef_tax = net_earning_before_tax(data_dic, input_dic, cal, semi) if semi: df_index = net_earning_bef_tax.index.get_level_values('financial_year') tax_rate = general.fillna_monthly(input_dic['tax rate']).reindex(df_index).fillna(method='ffill')['Tax'] df_values = net_earning_bef_tax.values * (1 - tax_rate.values) result = pandas.Series(df_values, index=net_earning_bef_tax.index) else: tax_rate = general.fillna_monthly(input_dic['tax rate']).reindex(net_earning_bef_tax.index).fillna(method='ffill')['Tax'] result = net_earning_bef_tax*(1-tax_rate) result.name = 'Net Earning After Tax' return result
def hlf_amc(dic_data, input_dic, period='half_no'): aua = combined.total_aua(dic_data, input_dic) aua_margins = general.fillna_monthly( input_dic['aua margin']).reindex(index=general.month_end_series) return (aua['discretionary_aua'] * (aua_margins['hlf_amc'] / 12)).groupby( ['financial_year', period]).sum(min_count=1) #.map(general.compound_growth_rate))
def net_earning_after_tax(data_dic, input_dic, cal=False): net_earning_bef_tax = net_earning_before_tax(data_dic, input_dic, cal) tax_rate = general.fillna_monthly(input_dic['tax rate']).reindex( net_earning_bef_tax.index).fillna(method='ffill')['Tax'] result = net_earning_bef_tax * (1 - tax_rate) result.name = 'Net Earning After Tax' return result
def growth_revenues(input_dic): df = aua_frame.copy() df = df.loc[:,general.growth_revenue_cols] revenue_growth = general.fillna_monthly(input_dic['growth rate']).loc[:,general.growth_revenue_cols].reindex(index=general.semi_annual_series) revenue_growth.iloc[0,:]=0 result = df.fillna(method='ffill').multiply(((revenue_growth+1).cumprod()),axis='index') return result
def paper_statement_revenue(dic_data): df = general.fillna_monthly(dic_data['clients']).reindex(index=general.semi_annual_series) df2 = pandas.DataFrame(general.client_number_growth_semi, index=df.index, columns=df.columns) df2.iloc[0,:] = 0 result = ((df2+1).cumprod()).multiply(df,axis='index') * general.paper_client_pcent * general.paper_charge_semi result.columns=['paper_income'] return result['paper_income']
def disc_cash_flow(dic_data, input_dic, now=False, dcf_p=dcf_period, disc_rate = discount_rate): dcf_end = dcf_start_year + dcf_p - 1 if now is True: dcf_end = dcf_end -1 if general.last_result_month == 6: df1 = consolidated.annual_revenue_analysis(dic_data, input_dic).loc[dcf_start_year:dcf_end,:] df2 = consolidated.annual_costs_analysis(input_dic).loc[dcf_start_year:dcf_end,:] ebit = df1['Total revenue']+revenue.annual_revenue(dic_data, input_dic).loc[dcf_start_year:dcf_end,:]['interest_on_reserve'] + df2['Total operating costs'] + revenue.annual_revenue(dic_data, input_dic).loc[dcf_start_year:dcf_end,:]['currency_revenue'] dcf = ebit.to_frame(name='EBIT').transpose() dcf.loc['Depreciation',:] = -(costs.annual_costs(input_dic).loc[dcf_start_year:dcf_end,:]['depre_amort_financial']) dcf.loc['Capital Expenditure',:] = -(costs.annual_costs(input_dic).loc[dcf_start_year:dcf_end,:]['capital_expenditure']) if now is True: dcf.loc['EBIT',dcf_start_year-1] = consolidated.convert_report_revenue_data(False, year=dcf_start_year-1).loc['Total revenue',dcf_start_year-1] + report_reformat('revenue').loc['interest_on_reserve'] + consolidated.convert_report_costs_data(False, year=dcf_start_year-1).loc['Total operating costs',dcf_start_year-1] + report_reformat('revenue').loc['currency_revenue'] dcf.loc['Depreciation', dcf_start_year-1] = -(report_reformat('costs').loc['depre_amort_financial']) dcf.loc['Capital Expenditure', dcf_start_year-1] = -(report_reformat('costs').loc['capital_expenditure']) dcf = dcf.sort_index(axis='columns') tax_rate = general.fillna_monthly(input_dic['tax rate']).reindex(index=dcf.columns).transpose() dcf.loc['Tax',:] = dcf.loc['EBIT',:] * tax_rate.loc['Tax',:] dcf.loc['EAT',:] = dcf.loc['EBIT',:] - dcf.loc['Tax',:] dcf.loc['Free cash flow',:] = dcf.loc['EAT',:] + dcf.loc['Depreciation',:] - dcf.loc['Capital Expenditure',:] else: df1 = consolidated.annual_revenue_analysis(dic_data, input_dic, cal_year=True).loc[dcf_start_year:dcf_end,:] df2 = consolidated.annual_costs_analysis(input_dic,cal_year=True).loc[dcf_start_year:dcf_end,:] ebit = df1['Total revenue']+revenue.annual_revenue(dic_data, input_dic,cal_year=True).loc[dcf_start_year:dcf_end,:]['interest_on_reserve'] + df2['Total operating costs'] + revenue.annual_revenue(dic_data, input_dic,cal_year=True).loc[dcf_start_year:dcf_end,:]['currency_revenue'] dcf = ebit.to_frame(name='EBIT').transpose() dcf.loc['Depreciation',:] = -(costs.annual_costs(input_dic,cal_year=True).loc[dcf_start_year:dcf_end,:]['depre_amort_financial']) dcf.loc['Capital Expenditure',:] = -(costs.annual_costs(input_dic,cal_year=True).loc[dcf_start_year:dcf_end,:]['capital_expenditure']) if now is True: dcf.loc['EBIT',dcf_start_year-1] = consolidated.convert_report_revenue_data(False, year=dcf_start_year-1,cal_year=True).loc['Total revenue',dcf_start_year-1] + report_reformat('revenue',cal_year=True).loc['interest_on_reserve'] + consolidated.convert_report_costs_data(False, year=dcf_start_year-1,cal_year=True).loc['Total operating costs',dcf_start_year-1] + report_reformat('revenue',cal_year=True).loc['currency_revenue'] dcf.loc['Depreciation', dcf_start_year-1] = -(report_reformat('costs',cal_year=True).loc['depre_amort_financial']) dcf.loc['Capital Expenditure', dcf_start_year-1] = -(report_reformat('costs',cal_year=True).loc['capital_expenditure']) dcf = dcf.sort_index(axis='columns') tax_rate = general.fillna_monthly(input_dic['tax rate']).reindex(index=dcf.columns).transpose() dcf.loc['Tax',:] = dcf.loc['EBIT',:] * tax_rate.loc['Tax',:] dcf.loc['EAT',:] = dcf.loc['EBIT',:] - dcf.loc['Tax',:] dcf.loc['Free cash flow',:] = dcf.loc['EAT',:] + dcf.loc['Depreciation',:] - dcf.loc['Capital Expenditure',:] s1 = pandas.Series(1, index=dcf.columns).cumsum() s2 = pandas.Series(1+disc_rate, index=dcf.columns) discount_factors = s2 ** s1 dcf.loc['Discounted cash flow',:] = dcf.loc['Free cash flow',:] / (s2 ** s1) return dcf
def hlf_daily_fund_size(dic_data, input_dic, period=None): test = dic_data['fund size'] daily_fund_size = test.reindex(index=pandas.date_range( test.index.min(), general.month_end_series.max())) daily_fund_size = daily_fund_size.fillna(method='ffill') daily_fund_size[(daily_fund_size.index >= datetime.datetime.today())] = numpy.nan compound_rate = general.fillna_monthly( input_dic['compound growth'].reindex( index=daily_fund_size.index)).apply(general.compound_growth_rate, freq='Daily') sliced_compound_rate = compound_rate.loc[pandas.to_datetime('today'):, :] sliced_fund_size = daily_fund_size[pandas.to_datetime('today'):].fillna( method='ffill') working_compound = (1 + sliced_compound_rate).pow( (sliced_compound_rate.count(axis='columns').cumsum() - 1), axis='index') sliced_fund_size = sliced_fund_size.multiply( working_compound['compound growth rate'], axis='index') b = combined.future_nnb_distribution(dic_data, input_dic).reindex( sliced_fund_size.index) temp = b.loc[:, 'vantage_hlf_aua'] + b.loc[:, 'thirdparty_hlf_aua'] + b.loc[:, 'pms_others_aua'] + b.loc[:, 'pms_hlf_aua'] temp.name = 'nnb' temp = temp.to_frame() temp['key'] = pandas.to_numeric( temp.index.year.astype(str) + temp.index.month.astype(str)) temp = temp.fillna(method='bfill') count_map = temp.groupby('key').count() count_map.iloc[0, :] = 30 temp['count'] = temp['key'].map(count_map.to_dict()['nnb']) temp['nnb'] = temp['nnb'] / temp['count'] portion = daily_fund_size.loc[ pandas.to_datetime('today'), :] / daily_fund_size.loc[ pandas.to_datetime('today'), :].sum(min_count=1) nnb = temp['nnb'] temp2 = pandas.DataFrame(columns=portion.index, index=nnb.index) temp2.loc[:, :] = portion.values nnb = temp2.multiply(nnb, axis='index') sliced_fund_size = sliced_fund_size + nnb.cumsum(axis='index') final_fund_size = daily_fund_size.combine_first(sliced_fund_size) if period is not None: final_fund_size = general.convert_fy_quarter_half_index( final_fund_size, final_fund_size.index) if period == 'month_no': final_fund_size = final_fund_size.groupby( ['calendar_year', period]).mean() else: final_fund_size = final_fund_size.groupby( ['financial_year', period]).mean() return final_fund_size
def pms_advice_fee(dic_data, input_dic, period='half_no'): aua = combined.total_aua(dic_data, input_dic) aua_margins = general.fillna_monthly(input_dic['aua margin']).reindex(index=general.month_end_series) net_revenue = (aua['pms_aua'] * (aua_margins['pms_advice']/12)) if period=='month_no': result = net_revenue elif (period=='financial_year' or period=='calendar_year'): result = net_revenue.groupby(period).sum() else: result = net_revenue.groupby(['financial_year',period]).sum() return result#.map(general.compound_growth_rate))
def total_costs(input_dic): df = aua_frame.copy() df = df.loc[:, general.growth_costs_cols] costs = general.fillna_monthly( input_dic['growth rate']).loc[:, general.growth_costs_cols].reindex( index=general.semi_annual_series) costs.iloc[0, :] = 0 result = df.fillna(method='ffill').multiply(((costs + 1).cumprod()), axis='index') return result
def get_composite_return(dic_data): ''' Return a composite return series of HL funds, taking into account of proportion invested in the acc and inc units as well as fund size proportion ''' acc_percent = dic_data['acc unit'] / (dic_data['acc unit'] + dic_data['inc unit']) inc_percent = dic_data['inc unit'] / (dic_data['acc unit'] + dic_data['inc unit']) acc_percent = general.fillna_monthly(acc_percent).reindex( index=general.month_end_series) inc_percent = general.fillna_monthly(inc_percent).reindex( index=general.month_end_series) #fund_size = dic_data['fund size'].reindex(index=general.month_end_series) #fund_size_percent = fund_size.div(fund_size.sum(axis='columns'), axis='index') acc_bid_price = dic_data['acc price'].reindex( index=general.month_end_series) inc_bid_price = dic_data['inc price'].reindex( index=general.month_end_series) acc_bid_return = acc_bid_price / acc_bid_price.shift(1) - 1 inc_bid_return = inc_bid_price / inc_bid_price.shift(1) - 1 fund_units = general.fillna_monthly( (dic_data['acc unit'] + dic_data['inc unit'])).reindex(index=general.month_end_series) fund_units_percent = fund_units.div(fund_units.sum(axis='columns'), axis='index') composite_bid_return = acc_bid_return.where( acc_percent != 0, 0) * acc_percent + inc_bid_return.where( inc_percent != 0, 0) * inc_percent composite_bid_return = (composite_bid_return * fund_units_percent).sum(axis='columns') composite_bid_return = composite_bid_return.where( composite_bid_return.index <= pandas.to_datetime( general.last_day_prev_month)) return composite_bid_return
def future_aua(dic_data, input_dic): aua = total_historic_aua(dic_data, input_dic) compound_rate = general.fillna_monthly( input_dic['compound growth']).reindex( index=general.month_end_series).applymap( general.compound_growth_rate) sliced_compound_rate = compound_rate.loc[general.last_day_prev_month:, :] sliced_aua = aua.loc[general.last_day_prev_month:, :].fillna( method='ffill') working_compound = (1 + sliced_compound_rate).pow( (sliced_compound_rate.count(axis='columns').cumsum() - 1), axis='index') sliced_aua = sliced_aua.multiply(working_compound['compound growth rate'], axis='index') return sliced_aua.reindex(general.month_end_series)
def future_aua(dic_data, input_dic): aua = total_historic_aua(dic_data, input_dic) compound_rate = general.fillna_monthly( input_dic['compound growth']).reindex( index=general.month_end_series).applymap( general.compound_growth_rate) compound_rate[compound_rate.index <= pandas.to_datetime( general.last_day_prev_month)] = 0 sliced_compound_rate = compound_rate.loc[general.last_day_prev_month:, :] sliced_aua = aua.loc[general.last_day_prev_month:, :].fillna( method='ffill') working_compound = (sliced_compound_rate + 1).cumprod() sliced_aua = sliced_aua.multiply(working_compound['compound growth rate'], axis='index') return sliced_aua.reindex(general.month_end_series)
def hlf_amc_daily(dic_data, input_dic, period='half_no'): test = dic_data['fund size'] daily_fund_size = test.reindex(index=pandas.date_range(test.index.min(),general.month_end_series.max())) daily_fund_size = daily_fund_size.fillna(method='ffill') daily_fund_size[(daily_fund_size.index>=datetime.datetime.today())] = numpy.nan compound_rate = general.fillna_monthly(input_dic['compound growth'].reindex(index=daily_fund_size.index)).apply(general.compound_growth_rate,freq='Daily') sliced_compound_rate = compound_rate.loc[pandas.to_datetime('today'):,:] sliced_fund_size = daily_fund_size[pandas.to_datetime('today'):].fillna(method='ffill') working_compound= (1+sliced_compound_rate).pow((sliced_compound_rate.count(axis='columns').cumsum()-1),axis='index') sliced_fund_size = sliced_fund_size.multiply(working_compound['compound growth rate'], axis='index') b = combined.future_nnb_distribution(dic_data, input_dic).reindex(sliced_fund_size.index) temp = b.loc[:,'vantage_hlf_aua'] + b.loc[:,'thirdparty_hlf_aua'] + b.loc[:,'pms_others_aua'] + b.loc[:,'pms_hlf_aua'] temp.name='nnb' temp = temp.to_frame() temp['key'] = pandas.to_numeric(temp.index.year.astype(str) + temp.index.month.astype(str)) temp = temp.fillna(method='bfill') count_map = temp.groupby('key').count() count_map.iloc[0,:] = 30 temp['count'] = temp['key'].map(count_map.to_dict()['nnb']) temp['nnb'] = temp['nnb'] / temp['count'] portion = daily_fund_size.loc[pandas.to_datetime('today'),:] / daily_fund_size.loc[pandas.to_datetime('today'),:].sum() nnb = temp['nnb'] temp2 = pandas.DataFrame(columns=portion.index,index=nnb.index) temp2.loc[:,:] = portion.values nnb = temp2.multiply(nnb,axis='index') sliced_fund_size=sliced_fund_size+nnb.cumsum(axis='index') final_fund_size = daily_fund_size.combine_first(sliced_fund_size) select_revenue = final_fund_size[['Select UK Growth Shares','Select UK Income Shares']].sum(axis='columns') select_revenue = select_revenue*(0.006/365) hlf_revenue = final_fund_size.drop(['Select UK Growth Shares','Select UK Income Shares'], axis='columns').sum(axis='columns') hlf_revenue = hlf_revenue*(0.0075/365) total_hlf = select_revenue+hlf_revenue total_hlf.name='hlf_revenue' result = general.convert_fy_quarter_half_index(total_hlf,total_hlf.index) if period == 'month_no': final_result = result.groupby(['calendar_year',period]).sum().loc[idx[general.recent_end_year:,:],:] else: final_result = result.groupby(['financial_year',period]).sum().loc[idx[general.recent_end_year:,:],:] if general.last_result_month == 6: final_result = final_result.drop((general.recent_end_year,1),axis='index') final_result = final_result.stack() final_result.index = final_result.index.droplevel(2) return final_result
def disc_cash_flow(dic_data, input_dic, now=False, fractional=False, dcf_p=dcf_period, disc_rate=discount_rate, dcf_start=dcf_start_year): dcf_end = dcf_start + dcf_p # - 1 if now is True: dcf_end = dcf_end - 1 if general.last_result_month == 6: df1 = consolidated.annual_revenue_analysis( dic_data, input_dic).loc[dcf_start:dcf_end, :] df2 = consolidated.annual_costs_analysis(input_dic).loc[ dcf_start:dcf_end, :] ebit = df1['Total net revenue'] + revenue.annual_revenue( dic_data, input_dic).loc[dcf_start:dcf_end, :]['interest_on_reserve'] + df2[ 'Total operating costs'] + revenue.annual_revenue( dic_data, input_dic).loc[dcf_start:dcf_end, :]['currency_revenue'] dcf = ebit.to_frame(name='EBIT').transpose() dcf.loc['Depreciation', :] = -(costs.annual_costs(input_dic).loc[ dcf_start:dcf_end, :]['depre_amort_financial']) dcf.loc['Capital Expenditure', :] = -(costs.annual_costs( input_dic).loc[dcf_start:dcf_end, :]['capital_expenditure']) if now is True: dcf.loc[ 'EBIT', dcf_start - 1] = consolidated.convert_report_revenue_data( False, year=dcf_start - 1 ).loc['Total net revenue', dcf_start - 1] + report_reformat( 'revenue' ).loc[ 'interest_on_reserve'] + consolidated.convert_report_costs_data( False, year=dcf_start - 1).loc['Total operating costs', dcf_start - 1] + report_reformat( 'revenue').loc['currency_revenue'] dcf.loc[ 'Depreciation', dcf_start - 1] = -(report_reformat('costs').loc['depre_amort_financial']) dcf.loc['Capital Expenditure', dcf_start - 1] = -(report_reformat('costs').loc['capital_expenditure']) dcf = dcf.sort_index(axis='columns') tax_rate = general.fillna_monthly( input_dic['tax rate']).reindex(index=dcf.columns).transpose() dcf.loc['Tax', :] = dcf.loc['EBIT', :] * tax_rate.loc['Tax', :] dcf.loc['EAT', :] = dcf.loc['EBIT', :] - dcf.loc['Tax', :] dcf.loc['Free cash flow', :] = dcf.loc['EAT', :] + dcf.loc[ 'Depreciation', :] - dcf.loc['Capital Expenditure', :] else: df1 = consolidated.annual_revenue_analysis( dic_data, input_dic, cal_year=True).loc[dcf_start:dcf_end, :] df2 = consolidated.annual_costs_analysis( input_dic, cal_year=True).loc[dcf_start:dcf_end, :] ebit = df1['Total net revenue'] + revenue.annual_revenue( dic_data, input_dic, cal_year=True ).loc[dcf_start:dcf_end, :]['interest_on_reserve'] + df2[ 'Total operating costs'] + revenue.annual_revenue( dic_data, input_dic, cal_year=True).loc[dcf_start:dcf_end, :]['currency_revenue'] dcf = ebit.to_frame(name='EBIT').transpose() dcf.loc['Depreciation', :] = -(costs.annual_costs( input_dic, cal_year=True).loc[dcf_start:dcf_end, :]['depre_amort_financial']) dcf.loc['Capital Expenditure', :] = -(costs.annual_costs( input_dic, cal_year=True).loc[dcf_start:dcf_end, :]['capital_expenditure']) if now is True: dcf.loc[ 'EBIT', dcf_start - 1] = consolidated.convert_report_revenue_data( False, year=dcf_start - 1, cal_year=True ).loc['Total net revenue', dcf_start - 1] + report_reformat( 'revenue', cal_year=True ).loc[ 'interest_on_reserve'] + consolidated.convert_report_costs_data( False, year=dcf_start - 1, cal_year=True ).loc['Total operating costs', dcf_start - 1] + report_reformat( 'revenue', cal_year=True).loc['currency_revenue'] dcf.loc['Depreciation', dcf_start - 1] = -(report_reformat( 'costs', cal_year=True).loc['depre_amort_financial']) dcf.loc['Capital Expenditure', dcf_start - 1] = -(report_reformat( 'costs', cal_year=True).loc['capital_expenditure']) dcf = dcf.sort_index(axis='columns') tax_rate = general.fillna_monthly( input_dic['tax rate']).reindex(index=dcf.columns).transpose() dcf.loc['Tax', :] = dcf.loc['EBIT', :] * tax_rate.loc['Tax', :] dcf.loc['EAT', :] = dcf.loc['EBIT', :] - dcf.loc['Tax', :] dcf.loc['Free cash flow', :] = dcf.loc['EAT', :] + dcf.loc[ 'Depreciation', :] - dcf.loc['Capital Expenditure', :] s1 = pandas.Series(1, index=dcf.columns) s2 = pandas.Series(1 + disc_rate, index=dcf.columns) if fractional == True: first_y = general.recent_end_year + 1 last_factor = ( 365.0 - (pandas.Timestamp(first_y, general.last_result_month + 1, 1) - pandas.Timedelta(days=1) - pandas.Timestamp.today()).days) / 365 first_factor = ( (pandas.Timestamp(first_y, general.last_result_month + 1, 1) - pandas.Timedelta(days=1) - pandas.Timestamp.today()).days) / 365 #============== fractional discount factor, varies with time========================= s1.iloc[0] = first_factor s1.iloc[-1] = last_factor dcf.loc['Free cash flow', dcf_start] = dcf.loc['Free cash flow', general.recent_end_year + 1] * first_factor dcf.loc['Free cash flow', dcf_end] = dcf.loc['Free cash flow', general.recent_end_year + 1] * last_factor #==================================================================================== s1 = s1.cumsum() discount_factors = s2**s1 dcf.loc['Discounted cash flow', :] = dcf.loc['Free cash flow', :] / (s2** s1) return dcf