In the investment management or financial services accounting world, one of the most common calculations you encounter in is the time-weighted rate of return (TWROR). The time-weighted rate of return (sometimes called the geometric mean return) is a method of measuring historical performance of an asset or portfolio when external cash flows are present in the equation.

For example, if in your very own 401k, you make a transfer of funds from your money market account to purchase shares of a mutual fund, you would not want to count that influx of capital towards the overall rate of return on that fund (or your entire portfolio) as it would be inaccurately skewing the results in a positive manner. Conversely if you sold off a holding, or made a withdrawal, that could artificially skew portfolio performance in a negative direction. For more information on the definition and derivation of the time-weighted rate of return, please refer to this Wikipedia entry.

Understanding the TWROR Formula

For the purpose of this exercise, we will be using the following formula for calculating TWROR:

Where:

•  is the time-weighted return for the portfolio
•  is the initial portfolio value
•  is the portfolio value at the end of sub-period 
•  is the net inflow which occurs in sub-period 
•  is the number of sub-periods

What you will notice here is that this formula is a geometric series, otherwise known as a product or compounded calculation. Essentially we will calculate each sub-period’s rate of return (in this case a month) and then take the product of all of those sub period returns to yield the overall true time-weighted Rate of Return for the series of sub periods (in our example a year).

Example: Manually Calculating a Quarterly Portfolio TWROR

Given the above data set, we would like to manually calculate a quarterly TWROR to demonstrate how the formula is applied.

1. First we calculate our sub-period rate of return using the following formula:
   
2. Then we chain all of our sub-period rates of return together to come up with the final time-weighted result for the quarter:

If we were to calculate this number in Excel, we would simply use the PRODUCT function to multiply all of the rows of values in a given column together to give us the final TWROR. Because there is no equivalent aggregate function in Report Studio (or SQL for that matter) we need to use a little mathematical trickery to come to the same solution. This is actually were most report developers throw their hands up and ask their users to finish their analysis in Excel. We are going to show you how to do 100% of the work in report studio.

Calculating the TWROR in IBM Cognos Report Studio

We will be using IBM Cognos 10.2 Report Studio for this example, but this approach will work for any version of IBM Cognos.

1. Start by creating a simple list report in report studio that has the following fields from your investment data mart:

   1. Sub Period (Year Month Number)
   2. Period Beginning Market Value
   3. Period Ending Market Value
   4. Net Capital Flow (Deposits – Withdrawals)
2. Filter your list report by a single year, and/or portfolio
3. Create a data item called Periodic Rate of Return with the following calculation:((-)/)-1
4. Test your report and confirm that your periodic ROR calculation is accurately representing returns, net of any capital movement.
5. Select the Query Explorer in Report Studio and drag a new query from the toolbox and rename it to Portfolio Total.
6. Select the query from your list report (which we renamed to Periodic Holdings) and drag it to the right of your Portfolio Total query until a black bar appears, at which point release the mouse. Your new Portfolio Total query now references the Periodic Holdings query. We do this to force report studio to keep our sub-period ROR calculations at the correct level of detail.
   
7. Double click your Portfolio Total query and drag a new data item from the toolbox into the data items list. Enter the following calculation and give the data item the name TWROR:exp(total(ln(1+.)))-1
8. This is the mathematical trickery we were referring to before. The geometric series or product function can be replicated by taking the exponent of the sum of the natural logarithm of those values. This technique can be used for any geometric calculation you need to reproduce in SQL or Report Studio.
9. Switch back to the page explorer, and drop a new list or singleton in your report that references the Portfolio Total query, and make sure the new TWROR item is present.
10. Export the contents of your report to Excel and validate your numbers using the PRODUCT function.

We used two queries in this example to achieve the result. We could have done it all with a single more complex query, but that can sometimes require local report processing depending on the type of database you are using. A report specification for IBM Cognos 10.2 Report Studio is attached for your reference. Hopefully this technique allows you to simplify the lives of your portfolio analysts by arriving at answers faster without having to use multiple tools.

Next Steps

If you’re embarking on an initiative to provide better business analytics for your organization in the areas of investment management, institutional or private equity asset management, and hedge funds or other related financial services sectors, we encourage you to contact us for more information. Our financial services business analytics experts are familiar the unique challenges of the industry and deeply attuned to the needs of your analyst community. Our unique combination of industry experience and IBM Cognos domain expertise enables us to rapidly arrive at high impact solutions that are tailor made for your financial services organization.