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.
Hi and thank you for your thorough answer. I'm sorry for the scare information, the provided excel sheet is just an extract of a larger file and not in English and I wanted to limit the data. You were correct in your assumptions regarding the column data.
I do agree that it would be simpler if the deposits were made once a month and in set date, but one of my issues here is that I want to be able to make a deposit arbitrarily and still get a correct rate of return - I'm aiming to find a way to calculate it basically the same way my bank does, i.e. incorporate the effects of my deposits and their reduced market exposure.
I may add that I'm not sure if I used the correct annotation, I'm not an economist, nor is my first language English, so there may have been some lost in translation in both planes. But basically, what I want is a way to correctly display my interest development. So I may not be using the correct terminology, but I do want to weigh my balance with the lapsed time. I hope I am making sense.
Correct me if I'm wrong, but if I want to use your provided formula - I need to extract the dates for deposits? If there are several deposits in a month, how do I account for that?
I have attached a sheet with the raw data. If other metadata is necessary, I can probably provide that as well. The format is the same as follows, where the rows with deposits are marked green.
Regards
/Q
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.
Thank you for another thorough reply and guidance.
I can only apologise for not being clear enough, but the formulas in your example was exactly what I was looking for! Thank you very much! I really liked your expanding sumproduct, haven't considered one could produce a formula like that, very neat indeed. Today I learned.. 😄
I think I'll need to read through your explanation a few more times before fully comprehending it, but very useful indeed!
Colour me impressed! Thank you again!
/Q
QWeelon wrote: ``Colour me impressed! Thank you again!``
Another child of the 1960s? (wink)
You're welcome. But I should have explained that we select the single cell E4 and array-enter the formula by pressing ctrl+shift+Enter, then copy E4 down the column.
Do not select the column of cells (E4:E33) and array-enter the formula. That does not work. But even if it did, it would be unnecessary.
(BTW, I use Excel 2010. I'm not sure you need to explicitly array-enter formulas by pressing ctrl+shift+Enter in Excel 365 and recent versions of Excel.)
Haha, I'm actually from the 80's, I've just been raised with US TV. 😉
Yes, I've managed to get it to work, so no problem there I'd say.
Although, I do have a follow-up question. Is there a simple way to augment the formula to ignore empty rows? Would prefer to keep it general and avoid simple addition of sumproducts, if possible that is.
/Q