def build_libor(df_liquidity, df_libor_all, forecast_start, valuation_date):
for index, row in df_liquidity.iterrows():
if date(index.year, index.month,
index.day) == utilities.calc_month_end(forecast_start, 'date'):
''' first month does not use forward curve '''
libor = get_libor(df_libor_all, index)
df_liquidity.set_value(index, ['LIBOR'], libor)
elif date(index.year, index.month,
index.day) > utilities.calc_month_end(
forecast_start, 'date'):
libor = calc_libor_forward(df_libor_all, valuation_date, index)
df_liquidity.set_value(index, ['LIBOR'], libor)
return df_liquidity
def build_tlb_int_exp(df_liquidity, forecast_start):
start = utilities.calc_month_end(forecast_start, 'date')
df_liquidity.loc[start:,['TLB Int Exp']] = df_liquidity['Swap Avg Daily Balance'] * df_liquidity['Swap Fix Rate'] \
+ (df_liquidity['TLB BOP']- df_liquidity['Swap Avg Daily Balance']) * df_liquidity['TLB Float Rate']
df_liquidity.loc[start:, ['TLB Int Exp']] = df_liquidity[
'TLB Int Exp'] * df_liquidity.index.day / 360
return df_liquidity
def build_swap_rate(df_liquidity, df_swap, forecast_start):
for index, row in df_liquidity.iterrows():
if date(index.year, index.month,
index.day) >= utilities.calc_month_end(forecast_start, 'date'):
month_start = date(index.year, index.month, 1)
month_end = date(index.year, index.month, index.day)
swap_balance = df_swap[month_start:month_end]['swap_balance'].sum()
if swap_balance > 0:
df_liquidity.set_value(index, ['Swap Avg Daily Balance'],
swap_balance / index.day)
swap_rate = df_swap[month_start:month_end][
'calc_swap_rate'].sum() / swap_balance
df_liquidity.set_value(index, ['Swap Fix Rate'],
swap_rate + .045000)
else:
df_liquidity.set_value(index, ['Swap Avg Daily Balance'], 0)
df_liquidity.set_value(index, ['Swap Fix Rate'], 0)
return df_liquidity
def build_change_work_cap(df_liquidity, forecast_start):
for index, row in df_liquidity.iterrows():
if date(index.year, index.month, index.day) < forecast_start:
change_cash = row['Cash EOP'] - row['Cash BOP']
activity_cash = row['EBITDA'] - row['Capex'] + row[
'Other Cash Use'] - row['TLB Int Exp'] - row[
'TLC Int Exp'] + row['TLB Change'] + row[
'DSRA Change'] - row['Distributions']
change_work_cap = change_cash - activity_cash
elif date(index.year, index.month,
index.day) == utilities.calc_month_end(
forecast_start, 'date'):
change_work_cap = -df_liquidity.loc[:forecast_start,
['Change Work Cap']].sum(
) - 20000000
else:
change_work_cap = 0
df_liquidity.set_value(index, 'Change Work Cap', change_work_cap)
return df_liquidity
def build_tlc_int_exp(df_liquidity, forecast_start):
start = utilities.calc_month_end(forecast_start, 'date')
df_liquidity.loc[start:,
['TLC Int Exp']] = df_liquidity['TLC BOP'] * df_liquidity[
'TLC Float Rate'] * df_liquidity.index.day / 360
def build_debt(df, forecast_start, forecast_end):
''' set initial debt balances '''
tlb_bop = get_inception_debt_balance('Lightstone TLB')
tlc_bop = get_inception_debt_balance('Lightstone TLC')
df_debt_activity_tlb = get_debt_activity_all('Lightstone TLB')
df_debt_activity_tlb['payment_date_date'] = pd.to_datetime(
df_debt_activity_tlb['payment_date'])
df_debt_activity_tlc = get_debt_activity_all('Lightstone TLC')
df_debt_activity_tlc['payment_date_date'] = pd.to_datetime(
df_debt_activity_tlc['payment_date'])
last_activity_date_tlb = utilities.calc_month_end(
df_debt_activity_tlb['payment_date_date'].max(), 'date')
last_activity_date_tlc = utilities.calc_month_end(
df_debt_activity_tlc['payment_date_date'].max(), 'date')
for index, row in df.iterrows():
''' tlb first '''
tlb_addl_borrow = 0
tlb_prepay = 0
tlb_amort = 0
if date(index.year, index.month, index.day) <= max(
last_activity_date_tlb, forecast_start):
df.set_value(index, 'TLB BOP', tlb_bop)
tlb_addl_borrow = get_debt_activity(df_debt_activity_tlb,
'additional borrowing', index)
df.set_value(index, 'TLB Addl Borrow', tlb_addl_borrow)
tlb_amort = get_debt_activity(df_debt_activity_tlb, 'amortization',
index)
df.set_value(index, 'TLB Amort', tlb_amort)
tlb_prepay = get_debt_activity(df_debt_activity_tlb, 'prepayment',
index)
df.set_value(index, 'TLB Prepay', tlb_prepay)
tlb_eop = tlb_bop + tlb_addl_borrow - tlb_amort - tlb_prepay
df.set_value(index, 'TLB EOP', tlb_eop)
tlb_bop = tlb_eop
''' tlc second '''
if date(index.year, index.month, index.day) < max(
last_activity_date_tlc, forecast_start):
df.set_value(index, 'TLC BOP', tlc_bop)
tlc_addl_borrow = get_debt_activity(df_debt_activity_tlc,
'additional borrowing', index)
df.set_value(index, 'TLC Addl Borrow', tlc_addl_borrow)
tlc_amort = get_debt_activity(df_debt_activity_tlc, 'amortization',
index)
df.set_value(index, 'TLC Amort', tlc_amort)
tlc_prepay = get_debt_activity(df_debt_activity_tlc, 'prepayment',
index)
df.set_value(index, 'TLC Prepay', tlc_prepay)
tlc_eop = tlc_bop + tlc_addl_borrow - tlc_amort - tlc_prepay
df.set_value(index, 'TLC EOP', tlc_eop)
tlc_bop = tlc_eop
''' forecast period '''
if date(index.year, index.month, index.day) >= forecast_start:
if row['TLB BOP'] <= 0:
''' calc amortization '''
if index.month % 3 == 0:
required_amort = calc_required_amort(
df, 'Lightstone TLB', index)
cum_prepay = df.loc[:index]['TLB Prepay'].sum()
cum_amort = df.loc[:index]['TLB Amort'].sum()
tlb_amort = max(
min(1625000000 * 0.0025,
required_amort - cum_prepay - cum_amort), 0)
df.set_value(index, ['TLB Amort'], tlb_amort)
df.set_value(index, 'TLB BOP', tlb_bop)
tlb_eop = tlb_bop + tlb_addl_borrow - tlb_amort - tlb_prepay
df.set_value(index, 'TLB EOP', tlb_bop - tlb_amort)
tlb_bop = tlb_eop
if row['TLC BOP'] <= 0:
df.set_value(index, 'TLC BOP', tlc_bop)
tlc_eop = tlc_bop + tlc_addl_borrow - tlc_amort - tlc_prepay
df.set_value(index, 'TLC EOP', tlc_bop)
tlc_bop = tlc_eop
return df
def get_debt_activity(df, activity, period):
month_start = date(period.year, period.month, 1)
month_end = utilities.calc_month_end(period, 'date')
mask = (df['payment_date'] >= month_start) & (
df['payment_date'] <= month_end) & (df['payment_type'] == activity)
return df[mask]['amount'].sum()
df_hedges_relevant = df_hedges_relevant.reset_index(drop=True)
for i, hedge in df_hedges_relevant.iterrows():
ws.cell(row=i+5, column=1, value=hedge.loc['tid_kindle'])
ws.cell(row=i+5, column=2, value=hedge.loc['counterparty'])
instrument = hedge.loc['receive_index'] + "/" + hedge.loc['pay_index']
ws.cell(row=i+5, column=3, value=instrument)
ws.cell(row=i+5, column=4, value=hedge.loc['notional'])
fixed_price = hedge.loc['receive_value'] if hedge.loc['receive_index'] == 'fixed' else hedge.loc['pay_value']
ws.cell(row=i+5, column=5, value=fixed_price)
ws.cell(row=i+5, column=6, value=hedge.loc['frequency'])
ws.cell(row=i+5, column=7, value=hedge.loc['date_start'])
ws.cell(row=i+5, column=8, value=hedge.loc['date_end'])
"""show monthly results for remainder of forecast year, then sum remaining into one column"""
loop_date = utilities.calc_month_end(forecast_start, 'date')
month_offset = 0
remaining_cashflow = 0
while loop_date <= forecast_end:
if hedge.loc['date_start'] <= loop_date and hedge.loc['date_end'] >= loop_date:
price_receive = hedge.loc['receive_value'] if hedge.loc['receive_index'] == 'fixed' else get_specific_price(df_all_prices, hedge.loc['receive_index'], loop_date)
price_pay = hedge.loc['pay_value'] if hedge.loc['pay_index'] == 'fixed' else get_specific_price(df_all_prices, hedge.loc['pay_index'], loop_date)
monthly_cashflow = calc_monthly_cashflow(price_receive, price_pay, hedge.loc['notional'], hedge.loc['frequency'], loop_date)
if loop_date.year == forecast_start.year:
ws.cell(row=i+5, column=9+month_offset, value=monthly_cashflow)
ws.cell(row=i+5, column=9+month_offset).style = data_format
else:
remaining_cashflow += monthly_cashflow
if loop_date > hedge.loc['date_end']:
ws.cell(row=i+5, column=remaining_column, value=remaining_cashflow)