Most asset managers will publish their results in both time-weighted (TWRR) and dollar-weighted (DWRR) rates of return. TWRR is typically used by portfolio manages to compare their portfolio’s return to either other managed accounts or to an index. TWRR is more difficult to calculate for individuals because it requires more data. One must have not only their cash flows and dates that they moved in and out of the fund (as with DWRR), but the mark-to-market values of their total portfolio at the time of each cash flow. Investment firms with active investment funds will often calculate TWRR daily.

TWRR makes the portfolio comparable to other like accounts by eliminating the effects of external cash flows. It can also be used by individuals that want to measure their market timing. The calculator “Dollar_Time” inputs for both TWRR and DWRR look like this:

I used dynamic named ranges so that the user can enter a number of cash flows, without having to adjust the formulas.

The formulas for TWRR and DWRR are below:

The TWRR formula subtracts the cash flows from the total market values (including cash flows) and divides the results by the previous market value of the account. The results of each of these is then multiplied by each other using the PRODUCT function and then annualized. The calculation would look something like this:

The formula converts the change in market values at each date, starting with one dollar. The PRODUCT function then links these changes into what could be called the Dollar Trail. The chart below shows the Dollar Trail:

Note that the TWRR in this example is negative. The chart shows that the percentage change of one dollar, linked together at each cash flow date, never gets back to a dollar. It ends at $0.998 which is an annual return of -0.108%, while the DWRR is positive at 3.618%. This can be a problem for individuals with using TWRR. It can show a negative return when it is obvious that the return is positive and vice versa. The MWRR calculation, tells the investor how the money that was invested in the fund actually performed. When the TWRR is lower than the DWRR, it means the timing of the cash flows was beneficial to the return and when TWRR is higher than MWRR, the cash flows had a negative impact on return. The chart below shows that deposits were made at lower market levels, and withdrawals were made at higher market levels, for a positive impact.

Download “Dollar_Time“

All spreadsheets created in Excel 2013

John NgDear Don,

You presented two simple concepts in a difficult (to understand) way! After going through your calculations twice, I still don’t know hoe you arrived at the returns. You need to rethink on the drawing board from the angle of a person who do not have any concept at all.

JustynaHi Don,

Is it possible to calculate monthly TWRR using daily/weekly TWRR?

Kind regards,

Justyna

Don PistulkaPost authorJustyna,

I am not sure what you mean by “calculate monthly TWRR”. You can calculate the TWRR monthly by entering end of month dates, even if there are no cash flows on those dates, and marketing the portfolio to market on those dates. If that does not answer your question, send me an example of what you need.

Thanks

Don

JuliaIn the context of private equity funds, does this have to be done on an investment by investment basis or can this be aggregated by quarters? Could you please provide an example using private equity investment? I.e. Total Fund has $1B in commitments, assume invests $250M per year, invests evenly each quarter, and it generally is held at cost each subsequent quarter after the initial investment until the final exit.

Don PistulkaPost authorJulia,

I sent you email and with an attachment.

JOThe formula is annualized. I was trying to calculate my bitcoin returns, which I have held less than a year, and the numbers I was getting were way too high.

Don PistulkaPost authorJo,

Can you send me the cash flows you are using to blog@pistulka.com and I will see if I can help.

Thanks

chris changpraiHi Don,

Thank for the free spreadsheet! I have a question on how to use it. I am testing the spreadsheet by using an example from PWL Capital, ‘How to Calculate your Portfolio’s Rate of Return’ but I could not reconcile the MWRR or TWRR.

The PWL Capital example provides a simple portfolio with Market Value, Cash Flow and Market Value after cash flow. I enter the values on where I thought appropriate but I am not getting the same return as there calculations. Please help!

Here’s the web link to PWL Capital paper: https://www.pwlcapital.com/pwl/media/pwl-media/PDF-files/Justin%20Bender%20Assets/How-to-Calculate-your-Portfolio-s-Rate-of-Return_v03linked.pdf

Don PistulkaPost authorChris,

I found a calculator on the website you gave me and I entered the example on my spreadsheet. Their “average rate of return” is the same as my DWRR. When you use the calculator, note the asterisk below the answer. “If the period is less than one year the actual return is provided.” That means that the example (which is only one quarter) shows a return that is not annualized. All my returns are annualized, regardless of the term.

Pingback: Chained Returns | Excel@CFO