1

We have:

  • an initial deposit of $2,500,000
  • Then, every year end we make a deposit of $750,000
  • We make this same deposit for 13 years
  • At the end, we have a final value of $50,000,000
  • Our system calculates a Cumulative TWR of 855.29%
  • Our system calculates an Annualized TWR of 17.53%
  • This site validation calculates an Annualized TWR of 17.35%

To validate this Annualized TWR number of 17.53%:

  • We created an ss, set the initial value to $2,500,000, in the next cell calculated Profit + Beginning Value using Ann TWR.
  • In the next line, we added the deposit of $750,000 to the previous portfolio value, calculated Profit + Beginning Value using Ann TWR and repeated the exercise for 13 years.

We were hoping to get $50,000,000 at the end, but got $60,033,198.

Did we try to validate the Annualized TWR the correct way? Obviously, if we had correctly worked out the validation, we should have received $50,000,000 at the end but the number was $60,033,198.

May we request your help to help us validate the Annualized TWR of 17.53% please? We would very much appreciate your valuable time on this. Calculation Spread Sheet

  • So "your system" calculates a TWR that gives a wrong result and you're asking us to verify it? – D Stanley Mar 7 at 15:14
  • I applied for validating 17.35 also. But that too didn't match with $50,000,000 – Smith Dwayne Mar 7 at 15:40
  • 1
    To calculate time-weighted return you need the balances (and inputs/outputs) at the end of each period, not just the ultimate ending balance. You can calculate an IRR, which I calculate is 17.28%. – D Stanley Mar 7 at 16:58
  • @DStanley:Thank you for your feedbacks. I am thankful to you all for taking the time to help me out. Let me provide you with a ss image to illustrate the numbers we used to calculate the per period deposits, profits, capital bases, and returns. TWR is calculated by linking per period returns. Annualized Return for 14 years is then calculated. To validate the Annualized TWR, we used the same deposits and using the Annualized TWR value calculated market values. We hoped the market value would reach 50,000,000 at the end (after 14 years). But, we got 60,033,198.00. – Smith Dwayne Mar 8 at 15:23
  • We believe we are making a mistake in Validating TWR. Kindly help. We would very much appreciate your help. If you can please attach an image of your ss calculation, it would help us too. – Smith Dwayne Mar 8 at 15:23
2

Did we try to validate the Annualized TWR the correct way?

No - that validation would work if you calculated IRR, but not TWR. Since you have larger returns in the early periods and smaller returns in later periods (when the balances are larger), adding the same return to each period will give you a different end result.

Also, I think your formula for annual return is incorrect - you should be taking the ending balance less any inflows, divided by the prior ending balance.

So for year 1 your period return should be (5,882,394 - 750,000)/4,250,000 - 1 = 20.76% However, with that method I get a TWR of 18.3%, so I'm not able to reconcile the 17.35%

  • Thank you very much for your help. The TWR calculation for a period as you have calculated differs from the calculation of a leading Portfolio Management Software package in USA. We cross verified this package's TWR for each period against the calculation we used in our ss and found them to be the same. We read package's manual and their objective is making sure for each TWR calculation per period they want to eliminate the cash flow for that period in their TWR calculation formula. – Smith Dwayne Mar 9 at 9:42
  • In other words, their denominator(Captial Base) is the Market Value of the portfolio the day before the Cash Flow + the Cash Flow, which implies that the cash flow comes into the portfolio at the very first second of the flow's date, and the portfolio manager has access to the Cash Flow for investment on the date of the Cash Flow. For e.g., on the second period(when the first 750,000 flow comes into the portfolio) the capital base of the portfolio would be the market value of the portfolio the day before (12/31/1999 - 4,250,000) + Cash Flow (01/01/2000 - 750,000) = 5,000,000. – Smith Dwayne Mar 9 at 9:42
  • The profit made on this Capital Base of 5,000,000 by the end of the period = market value at the end of the period (12/31/2000 - 5,882,352.94) - Capital Base of the period (01/01/2000 - 5,000,000) = 882,352.94. So, Return for this period = Profit 882,352.94 ÷ Capital Base 5,000,000 = 17.65%. The package's manual says that they have thus eliminated the Cash Flow from the formula. – Smith Dwayne Mar 9 at 9:43
  • Linking each and every return calculated the same way { [(1+(R1 ÷ 100)) x (1+(R2 ÷ 100)) x ... x (1+(Rn ÷ 100)) ] - 1 } x 100 for each period gives the TWR for the whole 14 periods. That is how we arrived at a cumulative TWR of 855.29% and Annualized TWR of 17.53%. – Smith Dwayne Mar 9 at 9:43
  • Then, we attempted to validate using a ss the Annualized TWR of 17.53%, could not arrive at the ending value of 50,000,000, and we requested you and others for help. Kindly say a few words about using the previous period's ending Market Value as Captial Base(Denominator) and subtracting the Cash Flow from the current period's ending Market Value as profits(Numerator) for calculating the period's return please. – Smith Dwayne Mar 9 at 9:44
0

As I think D Stanley is saying, your validation is equivalent to using an internal rate of return (IRR). However this won't replicate time-weighted return (TWR) with cash flows.

With cash flows and valuations, time-weighted return gives the most accurate result. Without valuations, IRR gives a useful result, but as the second example below shows, the additional valuation information improves the picture.

Without cash flows, TWR and IRR produce the same result.

Year     Value    Gain
1998      125
2001      250     100 %
2002      220     -12 %
2004      275      25 %

TWR = 2*0.88*1.25 - 1 = 120%   Annualised = 2.2^(1/6) - 1 = 14.0435 %

IRR : 125 (1 + x)^6 = 275    ∴ x = 14.0435 %

With valuation information TWR still produces the expected return. IRR simply doesn't take the valuation information into account. (Useful if you don't have that information.)

Year     Value    Gain    Cash flow  New value
1998      125   
2001      250     100 %     + 50       300
2002      264     -12 %     + 35       300
2004      375      25 %

TWR = 2*0.88*1.25 - 1 = 120%   Annualised = 2.2^(1/6) - 1 = 14.0435 %

IRR : 125 (1 + x)^6 + 50 (1 + x)^3 + 35 (1 + x)^2 = 375  ∴ x = 12.8738 %

If you replay the IRR calculation with the TWR result the answer is not 375

x = 14.0435 %

125 (1 + x)^6 + 50 (1 + x)^3 + 35 (1 + x)^2 = 394.68

This is equivalent to how your result is not matching. The same rate is being applied to every year.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.