Monday, January 11, 2010

Calculating returns with little data (revisited)

Last Friday I posed a question about how one would derive returns when they had limited data: month-end holdings and period transactions. A couple folks basically explained that this CAN be done. Here's what you do:
  • Take your holdings file and preceding transactions and
  • back into the holdings for any prior period.
By transactions, we're assuming you have buys, sells, income, and corporate actions. With this information, you can get anywhere.

Really simple example: your holdings file for 12/31/09 shows you have 100 shares of IBM and 200 shares of Ford, plus $500 in cash. You'd like to know what you looked like on 11/30/09. Your 11/30/09 - 12/31/09 transactions list:
  • 12/15/09: sale of 1,000 shares of Citicorp for $5,000
  • 12/17/09: purchase 200 shares of Ford for $3,000
  • 12/23/09: withdrawal of $1,500
What we do is start with our current position and reverse all the entries:
  • 12/23/09: withdrawal of $1,500 becomes a contribution of $1,500 (our $500 becomes $2,000)
  • 12/17/09: purchase of 200 shares of Ford for $3,000 becomes a sale of 200 shares (our 200 shares go to zero and we add $3000 to cash)
  • 12/15/09: sale of 1,000 Citi becomes a purchase of 1,000 shares (we pick up 1,000 shares and reduce our cash by $5,000)
Meaning, that on 11/30/09 we held:
  • 1,000 shares of Citi
  • 100 shares of IBM
  • zero cash.
This may help:

As you can see, I took our starting position and simply reversed all the entries. Granted, this was a very simple example, but you at least see how it works. You would need to get pricing for 11/30/09.

By the way, if you plan to do this manually, depending on how many periods you need to create and how many positions and transactions, you could be doing it a while.

What's the lesson here? Well, first, it means you do not have to store every day's position! You can store monthly positions and always back into any specific day. In the world of IT (information technology), there's always the issue about processing time versus space. Granted, disc storage has gotten a lot cheaper, but if you don't need to store dailies, why bother? Second, if you have to reconstruct records and have minimal information, you may still be able to accomplish it.


  1. Sure, performance could be calculate and show the manually process using the example you provide. Let's put aside data storage (which is a very important issue), a more real life example is to give someone 50 to 100 securities and request that person to calculate performance. We're just dicussing about marketable equities. What happen when we throw in other type of financial securities as well? I believe the example used will need more room to show the extract work.

  2. If there are other types, you're faced with the same scenario if you valued every day! If a security isn't marketable and can't be valued, it can't be valued. And so, you'd employ whatever your firm's policies are regarding valuing illiquid assets. Doesn't impact the strategy suggested here.

  3. All right let's use some marketable securities examples.

    Futures that has daily variation margin that relies on cash flow adjustments. Custodian and internal accounting doesn't always match because of different source. Some system adjust prices of the future securities and other relies on the custodian cash flow. How is that going to be apply to your method?

    A mortgage back securities that has pre payment schedule that is not available at monthly payable date but only estimate information is available. Should we rely on company policy or best practice (if there is one) on matters related to retroactive activities between actual versus estimates. This is not an error or mistake but simply lack of available information. However, our pervious returns calculated was incorrect then, right?

    A forward contract that does not require immidate cash payment and is matured some future date. The forward contract will need to market to market every month until maturity. These market to market valuations are only estimated based on some vendor source / custodian. Upon maturity, the pricing of the forward is reflected to the agree price which could differ to the market to market pricing. Does this mean we're using incorrect model even if its a standard firm policy?

    Fixed income security where the pricing source is different (use various methods: cash flow model, basket pricing on similar duration, average life, spread ...). Let's assume the firm uses an standard source but the source is different to Bloomberg or Factset. What is the true valuation?

    What is the true process, company policy, best practice or something else? Should I look at a return if there are derivative holdings or should I look at notional value return? If I look at notional value return, then the number is not the portfolio true return? Private placement doesn't price reqularly, does that mean we should just take the portfolio valuations without the proper valuation of the priviate placements?

    Sure we could have all these policy written up. When a client was to ask these similar questions, should we give them the firm policy as well?


Note: Only a member of this blog may post a comment.