Calculating Return Rates, especially when money is added to or subtracted from the Portfolio during the period under consideration.
Most of you probably know how to calculate a return rate, but when money is added to, or subtracted from the portfolio, the return rate calculation is a bit more complicated.
During the early nineties I wasn't sure how to do it, and I learned that many other people are confused about it too. At that time a friend asked a highly compensated Chartered Financial Analyst to explain how to calculate return rates when money was added to, or subtracted from the portfolio.
The analyst could not explain how return rates were calculated because the firm used software that had been approved by the SEC, but she did tell him that Excel's IRR Function would duplicate the return rates calculated by the firm's software.
That was not correct. It is now obvious to me that almost all individual investors and most financial service professionals are confused about return rate calculations.
Professional money managers, including mutual funds and anyone else reporting returns to the SEC use Time Weighted Return Rates.
Time Weighted Return Rates are NOT dependent on the amount of money invested. That means that you could invest 1,000 during one period and 10,000 during the next period, but your Time Weighted Return Rate would ignore the money weighted differences.
Individual investors should use Money Weighted Return Rates to calculate their return rates because their return rates will be dependent on the money they invest, and there could be huge differences between the results found by each method, especially if large amounts are added to or subtracted from the portfolio.
However, individual investors should ALSO calculate their Time Weighted Return Rates because a comparison between their individual Money Weighted Return Rate and their individual Time Weighted Return Rate will tell them if their market timing is adding or subtracting value to their portfolio.
This is EXTREMELY important. You may not consider yourself a market timer, but when you move money into a portfolio, or when you take money out of a portfolio, you are employing a market timing strategy even if you are simply rebalancing your portfolio, or even if your are simply adding money as part of a savings & investment plan or withdrawing money for living expenses.
If your Money Weighted Return Rate is greater than your Time Weighted Return Rate, then your market timing is adding value to your portfolio.
If your
Time Weighted Return Rate is greater than your Money Weighted Return
Rate, then your market timing is subtracting value from your
portfolio.
This may seem confusing right now, but the good news is that both rates are relatively easy to calculate if you have a computer with an Excel program.
The bad news is that you must have an accurate record of the amount of money that was added to or subtracted from the portfolio, and you must know the dates of the transactions.
Here's a summary of this page's contents:
1. I'll explain why your personal return rate is important.
2. I'll review some Basic Return Rate calculations.
3. I'll explain how to calculate Time Weighted Return Rates (TWRR)
I’ll explain why TWRRs are used by ALL professional money managers.
I’ll explain how a DAILY TWRR is calculated.
I'll explain how an individual investor could use a simplified version of the daily TWRR to calculate their own returns. A computer with an Excel program will quickly calculate TWRR. In the dark ages, before computers were available, the folks with the green eye shades did this manually, but I do not recommend it.
4. Money Weighted Return Rates.
Excel’s IRR Functions calculate Money Weighted Return Rates.
I’ll explain how to use them, how they work, and why they should only be used to calculate the return rate of the individual investor. Money Weighted Return Rates can not be calculated without a computer because there is no formula for them; instead an iterative process is used by the computer to find a solution using a trial and error method.
Why is your personal return rate important?
During the late nineties a friend and I were discussing our latest strategy to make more money than God had - remember, this was during the late nineties, when EVERYBODY thought they were gonna get rich. At some point in our conversation, his Mother-in-Law, walked in.
She knew we were discussing investing, so she proudly told us her financial advisor was doing a wonderful job for her.
I know I should not have said anything at that point, except perhaps something comforting, like, "That's wonderful - you're lucky to have a good advisor working for you".
But the Devil made me say this instead:
How do you know your advisor is doing a wonderful job for you?
Of course she did not understand the question, and she looked at me like a deer caught in the headlights. As far as she was concerned, she was making more money than she ever imagined she would, and she gave her financial advisor credit for making the money.
Is it rational to give your financial advisor (or yourself) credit for making more money than you ever thought you would, and, just as importantly, is it rational to BLAME your financial advisor (or yourself) for losing more money than you ever thought you would?
If your financial advisor or you are outperforming the risk adjusted return of your benchmark (either on the upside or downside), then your financial advisor or you deserve credit for being smart or lucky.
It is EXTREMELY difficult to determine whether an outperforming investor is smart or lucky. Most statisticians agree that it takes a MINIMUM of ten years of performance history to be able to BEGIN to quantify the answer. And, unfortunately, by the time a reasonably accurate answer has been found, it is usually too late to profit from the answer.
Once Again, Why is your personal return
rate important?
Most of you probably believe the answer to that question is intuitively obvious, but I was astounded a few months ago when an investor, who spent some time as a professional financial advisor, told me he did not care what his personal return rate was.
As long as he was making money, he was happy.
Since then I heard a few other investors say essentially the same thing. In my opinion, that is so bizarre it should be in Ripley's Believe it or Not!
How many of you remember Ripley's Believe it or Not?
Ripley's Believe it or Not! was featured in many popular newspapers during the fifties and sixties, and always had a bizarre, but true story that generally appeared in cartoon form. It is now ripleys.com.
 |
Doctors removed a 9-inch fork from a man’s stomach ten years after he swallowed it! |
If you go to Ripleys.com you'll see a photo of the man who swallowed the fork. Ten years ago he got drunk with some of his friends. One of them maintained that if the fork were swallowed, then stomach acids would dissolve it.
So the idiot swallowed the fork, and apparently forgot about it. Ten years later he started vomiting blood, so he went to an emergency room, where the surgeons removed the fork.
You may be glad to learn that this story did not occur in the United States
Here's my submission:
Ripley's Believe it or Not:
 |
Famous Rochester, NY Investing Pro Claims he doesn't care about his Return Rate! "As long as I'm Making Money, I'm Happy!" |
I hope I've convinced any doubters that an essential part of the investment process is to determine your return rate, but I'm old enough to know that it is extremely difficult to change anyone's mind about anything, and some of you are gonna prefer the "As long as I'm Making Money, I'm Happy" method of investing regardless of what anyone says.
But you may still be interested in learning how to do it, just in case anyone ever asks you:
How do you know you're doing a good job with your investments?
Let's review some Basic Return Rate Calculations
Total Return for One Period (Year, Quarter, Month or Day etc.)
1,000 = Start Value
1,200 = End Value
Profit or Loss = End Value
– Start Value
Profit or Loss = 200
Total Return = Profit or Loss / Start Value
Total Return = 200 / 1,000
Total Return = 20.00%
Is that a Time Weighted Return Rate?
OR
Is that a Money Weighted Return Rate?
It is BOTH a Money Weighted Return Rate and a Time Weighted Return Rate because no money was added to, or subtracted from the portfolio during the period.
That was too easy. Let's make it a little more complicated. Let's assume that the return rate that was found was for the first year of our beginning investor's career i.e.,
Year 1 Rate = 20.00%
Let's further assume that our beginning investor was invigorated by the success of his financial advisor, so he gave his advisor $10,000 more to invest at the beginning of the second year of his investing career.
Start Value of Year 2 = End Value of Year 1 + Money Added or
Subtracted
Start Value of Year 2 = 1,200 + 10,000 = 11,200
However, Year 2 was not a good year for our beginning investor or his financial advisor.
End Value of Year 2 = 10,000
Year 2 Profit (Loss) = End Value Year 2 – Start Value Year 2
Year 2 Profit (Loss) = 10,000 – 11,200 = – 1,200
Total Return = Profit or Loss / Start Value
Total Return = – 1,200 / 11,200
Total Return = – 10.71%
Is that a Time Weighted Return Rate?
OR
Is that a Money Weighted Return Rate?
It is BOTH a Money Weighted Return Rate and a Time Weighted Return Rate because no money was added to, or subtracted from the portfolio during the period.
Our beginning investor now has a two year history:
Year 1 Rate = 20.00%
and
Year 2 Rate = – 10.71%
How is the TOTAL Return Rate calculated?
General formula for calculating a Total Return Rate when the rates for the number (N) of periods under consideration are known:
Total Rate = ( 1 + Rate 1 ) ( 1 + Rate 2 ) ( 1+ Rate 3 ) ... ( 1 + Rate N ) – 1
Rate 1 = 20.00%
Rate 2 = – 10.71%
Total Rate = ( 1 + Rate 1 ) ( 1 + Rate 2 ) – 1
Total Return = 7.14%
Is that a Time Weighted Return Rate?
OR
Is that a Money Weighted Return Rate?
It's a Time Weighted Return Rate because the rate was calculated without considering the money that was added to the portfolio at the beginning of Year 1 or 2
Let's review the beginning investor's two year history:
1,000 was added at the beginning of Year 1
10,000 was added at the beginning of Year 2
OR
11,000 was added to the portfolio during Year 1 and Year 2
AND
10,000 was the value of the portfolio at the end of Year 2
That means the portfolio LOST 1,000 over its two year life, but we just calculated a Total Return Rate of 7.14% over the two year life of the portfolio.
How is that possible?
Welcome to the wonderful world of professional investing.
Does that make sense?
Yes. A mutual fund or even an advisor has little or no control over the amount of money a customer invests, so it is logical for a professional to use a reporting system that is independent of money under management. However, unless you're living in an alternate universe, you probably won't be happy if your investment advisor informs you that she just reported a gain of 7.14% to the SEC for your portfolio when you know that you lost 1,000 over that same reporting period.
Now we're gonna calculate the true return rate i.e., we're gonna use Excel's XIRR function to calculate the true return rate.
IRR is an acronym for Internal Rate of Return
An Internal Rate of Return is a Money Weighted Rate of Return. A Money Weighted Rate of Return will return a true and precise return.
The XIRR function is very user friendly, but before we solve the problem, let's try to understand what we're trying to solve.
Our beginning investor gave his advisor 1,000 on
12/31/2001
Our beginning investor gave his advisor 10,000 on 12/31/2002
Our beginning investor's portfolio was worth 10,000 on 12/31/2003
What's his true rate of return?
# Days = Total # of Days the money was invested
R is the unknown Rate at which 1,000 will be invested for 730 days,
and at which 10,000 will be invested for 365 days.
Value X is the value of 1,000 invested for 730 days at Rate R
Value Y is the value of 10,000 invested for 365 days at Rate R
This is a fairly simple problem because it only has two periods, so it could be solved using a quadratic formula, but if there were many more than two periods, then it would be impossible to solve with a formula. Excel's XIRR function uses a trial and error method to solve for R
This Excel workbook can be used to solve for the annual return rate R
Combining the TWRR and XIRR in one Excel table
The shaded yellow cells are required input. Excel does everything
else.
The Table shows an annual XIRR of minus 8.39%. Now, let's test the solution.
1,000 invested at an annual rate of – 8.39% for 730 days = 839.20
10,000 invested at an annual rate of – 8.39% for 365 days = 9,160.80
839.20 + 9,160.80 = 10,000.00
Once again, This Excel workbook contains the table below and it shows how to add additional rows of data to the table.
The shaded yellow cells are required input.
The period could be whatever time interval you wanted.
Equality is NOT required for time intervals e.g.,
Period 1 could be one year, period 2 could be one day etc.
However, if you do not include a separate period when you add or subtract money to or from the portfolio, then your results will NOT be accurate.
Once the table is set up, the date, $ added or $ subtracted, and the new value of the portfolio must be entered. Excel then does everything else to calculate the total and annual Money Weighted Return Rate (XIRR) and Time Weighted Return Rate (TWRR). It's incredibly easy.
The TWRR in the table shown above is exactly what is required of professional investors who report their TWRRs to the SEC, except they must report DAILY ending balances to be compliant. Regardless of whether or not you entered daily balances, if you enter data whenever money is added to or subtracted from your portfolio, then your money weighted and your time weighted return rates will be precise.
Earlier I showed you that if your Time Weighted Return Rate is greater than your Money Weighted Return Rate, then your market timing is subtracting value from your portfolio. It was probably obvious to all of you that our beginning investor was a terrible market timer, but most of the time it is not obvious that an investor's timing is adding or subtracting value to a portfolio. That's why calculating both rates is extremely important.
It is reasonably well known that it is extremely difficult to beat the risk adjusted return of an appropriate index. In spite of that fact, many investors try, probably because we humans have been programmed to believe we can beat the odds. Many of them don't know how to calculate return rates.
That may preserve their egos, but if you don't know how to calculate your return rates, then that's worse than competing in a competitive athletic event without knowing how to keep score.
How will you know if you're winning or losing?
If you're winning, then continue with whatever you're doing, but
if you're losing,
then you're wasting your time, and you're wasting
your money.
Here's the alternative to calculating your return rates: