Forum Discussion
Time Weighted Rate of Return using dates
Just to clarify.... I was not suggesting any change in your format or frequency of data. It was not clear to me what problem you wanted to solve. So my intention was only to demonstrate the TWRR calculation. I should have made that clear the first time. Sorry.
-----
Re: ``I'm aiming to find a way to calculate it basically the same way my bank does``
To that end, it would be helpful to see what the "bank" calculates, and how it is reported.
Re: ``nor is my first language English, so there may have been some lost in translation``
I understand. And to make matters worse, IMHO, the financial community uses terminology that is misleading or confusing, even to a native English speaker.
That is why a concrete example of the actual calculations that you want to accomplish would be helpful.
I will try to keep my English as simple as possible. But my English is not so good either, being a born American (wink). So feel free to ask for clarification, as needed.
-----
Re: ``Correct me if I'm wrong, but if I want to use your provided formula - I need to extract the dates for deposits?``
Yes. The purpose of the TWRR is to reflect the true market rate of return, excluding "external factors" like deposits and withdrawals.
For example, if the ending balance was 100,000 yesterday, and the ending balance is 110,000 today because the market rate of return was 1% (1000) and we deposited 9000, we want the TWRR to be 1% (101000/100000 - 1), not 10% (110000/100000 - 1).
Suppose 6 days later, the ending balance is 120,000 because the market rate of return over that period was again 1% (1100) and we deposited 8900 on the last day. Again, we want the TWRR for that period to be 1% (111100/110000 - 1), not 9.09% (120000/110000 - 1).
And the cumulative market rate of return and TWRR for the total of 7 days is (1+1%)*(1+1%) - 1 = 2.01%, not 120000/100000 - 1 = 20%.
With that in mind, see the attached Excel file.
The TWRR in column E is the __cumulative__ market rate of return.As a proof of concept, see the periodic market rate of return and the cumulative market rate of return that are calculated in columns G and H.
-----
If the cumulative period is a year or less, investment firms report the cumulative TWRR.
But if the cumulative period is more than a year, investment firms report the average compounded annual TWRR, which might be calculated by (1+cumTWRR)^(days/365) - 1.
QWeelon - it is time weighted because if you change the timing of the cashflows that will have an impact on the return (given your holding period is granular enough).
Kashan786 wrote: ``it is time weighted because if you change the timing of the cashflows that will have an impact on the return``
You are responding to a discussion that was resolved 9 months ago correctly and to the satisfaction of the OP. More importantly, I believe your "clarification" is incorrect.
In fact, the very point of the TWR is to eliminate the effect of external cash flows from the calculation. Therefore, it is not impacted by their timing. That is what distinguishes the TWR from the IRR.
Refer to the wikipage for "time-weighted return". The English version is at https://en.wikipedia.org/wiki/Time-weighted_return .
As for the derivation of the term "time-weighted", I believe the last sentence of the first paragraph should read:
``Mathematically, the rate of return per unit time for each sub-period is geometrically weighted by the duration of the sub-period.``
IMHO, that is a mathematical nitpick, and it is incomplete. I think it should be relegated to the later section titled ``Why is it called "time-weighted"``. But beware: I believe that some details in that later section are flawed.
I am seeking to improve that wikipage without editing it directly. But I digress.
[EDIT] I've attached Excel file that demonstrates both the straight-forward sub-period calculation and the daily-weighted calculation of TWR.