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
