Portfolio return with cash inflow and outflow – Part II

In a previous article on this topic we have discussed this unique project I delivered to one of AlphaOverBeta’s client where he requested to display his portfolio risk metrics visually and textually by providing his broker trades report, the portfolio had complex issues, and inputs which can be reviewed in the previous article I wrote.

For reference, here are the requirements for the portfolio equity and risk calculations:

  1. The portfolio is constructed of 4 sub-portfolios (sub-accounts under one major account)
  2. The portfolio is traded in a number of currencies (in his case CAD, SGD, USD) in the same sub-account
  3. Each sub-account has random cash inflows (cash injected into the portfolio) and outflows of cash
  4. Each asset in the portfolio may pay dividends
  5. An asset position may change value along the way, meaning it may be increased or reduced ( to 0 in case it’s totally closed)
  6. The traded assets are from different exchanges
  7. Long and short side support

We are left with issues 3,4 to solve in this part of the post-cash inflow and outflow.
Handling cash inflows and outflows is tricky since an inflow of cash increases the amount in your portfolio artificially, for example, a 100K$ portfolio that had a 20K$ cash added to it is now 120K$ portfolio but did not increase its pnl by 20% (120K$ from 100K$) so when calculating historical performance this event must be taken into calculations.

As we have stated before, the portfolio is actually a table with the trade dates in the rows and the assets invested in the columns, one specific column is dedicated to the amount of cash in the portfolio, when an asset is bought ,two columns are added to the database one for the current size of the asset and another one for the current quote of the asset, a summation of each row produces the total value of the portfolio (see the table in the previous post of this topic).

Script in play

Here is the code handling the logic above:

def ticker_performance(ticker, hq, ticker_order_history):
    '''
    calculate the performance of a specific symbol
    :param ticker:  the symbol
    :param hq: historical quotes
    :param ticker_order_history: buy sell,dividend history for the ticker
    :return: dataframe with daily performance
    '''
    hq_c = hq.copy()
    hq_c['Quantity'] = 0
    # mark order dates
    for order_date in ticker_order_history['Date']:
        hq_order_date = order_date
        if not order_date in hq_c.index:
            if order_date < hq_c.index[0]:
                logging.debug('date {} not in ticker {} history, performance may be incorrect'.format(order_date.strftime(('%Y-%m-%d')),ticker))
                ticker_order_history = ticker_order_history[ticker_order_history['Date']!=order_date]
                continue
            hq_order_date = hq_c[order_date:].head(1).index

        oh_type = ticker_order_history[ticker_order_history['Date'] == order_date]['Type'].iloc[0]
        if oh_type in ['Buy', 'Sell']:
            hq_c.loc[hq_order_date, 'Quantity'] = \
            ticker_order_history[ticker_order_history['Date'] == order_date]['Quantity'].iloc[0]

    buy_sell_orders = ticker_order_history[(ticker_order_history['Type']=='Buy') | (ticker_order_history['Type']=='Sell')]
    if not len(buy_sell_orders)or ((2==len(buy_sell_orders) and (1==(buy_sell_orders['Date'].iloc[-1]-buy_sell_orders['Date'].iloc[0]).days))):
        return pd.DataFrame()
    buy_sell_orders.reset_index(inplace=True, drop=True)
    order_history = {}
    one_day = datetime.timedelta(days=1)
    for bsidx in range(len(buy_sell_orders)):
        if bsidx:
            order_history[bsidx]={'end_date':buy_sell_orders.iloc[bsidx]['Date']-one_day, 'start_date':buy_sell_orders.iloc[bsidx-1]['Date']}
    order_history[len(buy_sell_orders)] = {'start_date':buy_sell_orders.iloc[-1]['Date'], 'end_date':hq_c.index[-1]}

    quantity=0
    column_names=[]
    for idx in order_history.keys():
        start_date = order_history[idx]['start_date']
        end_date = order_history[idx]['end_date']
        quantity += ticker_order_history[ticker_order_history['Date'] == start_date]['Quantity'].iloc[0]
        if not quantity: continue
        column_name = ticker+str(idx)
        column_names.append(column_name)
        hq_c[column_name]=0
        for hq_date in hq_c[start_date : end_date].index:
            hq_c.loc[hq_date, column_name] = quantity * hq_c.loc[hq_date]['Close']

    return hq_c[column_names]

Taking the simplistic sum() method introduces the problem of a “sudden” shock to the equity in the form of cash added to the portfolio, summing that line increases the total portfolio value by the amount of cash added to the portfolio thus distorting the calculations, on the other hand, you can’t ignore the cash line for the same reason, if you sum the assets lines only, ignoring the cash column, the equity curve is distorted every time an asset is sold or bought because that reduces or increase the dollar value in the respective column, how do you solve this conflict? Enter time weighted cash flow.

Time-weighted cash flow

The idea behind time-weighted cash flow is to introduce the cash to the portfolio gradually making its contribution to the equity in an iterative manner rather than one shock on a specific date. Calculating total return is in the formula:

  • TR = 100 [LDV – (FDV + CF)] / [DFV + TWCF]
  • TR – total return
  • LDV – last dollar value of the portfolio
  • CF – cash flow
  • TWCF – the sum of all time-weighted inflows and outflows

This way the cash does not impact the equity in the portfolio as a sudden blow but gradually over time. TR is calculated for each day of the portfolio in a separate column taking into account the current quote for each invested assets, the cash and the TWCF (which may be calculated in a separate column, but this is just a temp calculation), this solution smoothes the equity curve making total return a more realistic report to the investor.

Here is a sample of one of the portfolios:

AlphaOverBeta is a group of trading specialists, we trade our own money in the US stock, options market on a daily basis , we use automated systems and discretionary methods to produce a consistent gain , in addition we develop custom made software solutions for the financial domain (such as the one in this article).

Trade safely,
Alon

Related Posts

Leave A Reply