### Archives For Personal Rate of Return

Yesterday I received an email from a guy who needed help with figuring out his personal rate of return using Excel’s XIRR formula. He had just started contributing to his 401(k):

3/26/2010 – \$188.50
4/8/2010 – \$377.02
4/16/2010 – Balance of \$593.54

The XIRR formula requires both negative and positive numbers in order for it to work. So, the best thing to do is to enter contributions as negative numbers since they are “outflows” to you. Like this:

Then, the ending balance would be a positive number. I explained this to the reader and this morning he sent me back an email telling me that he did it but got a return of 314%, which didn’t seem correct because his company was telling him the answer was 8.53%.

Both answers are correct. This is because the XIRR formula is annualized. To get the personal rate of return for the this time period, we have to adjust the annualized number. It’s easy enough to do. You just use this formula:

PRR (Personal Rate of Return) = [(1 + 3.148)# of days/365] – 1

To get the number of days you simply subtract 3/26/2010 from 4/16/2010 to get 21 days.

PRR (Personal Rate of Return) = [4.14821/365] – 1

PRR (Personal Rate of Return) = [4.148.0575] – 1

PRR (Personal Rate of Return) = [1.08524] – 1

PRR (Personal Rate of Return) = .08524 or 8.52% (different due to rounding)

In other words, his personal rate of return over that 21-dayperiod was 8.53% but if you annualize the number, it’s 314%.

Anyway, I hope this was helpful. The XIRR formula can be confusing.

I was checking my wife’s 401(k) balance last night and noticed a couple of interesting things:

1. The balance is just a few thousand shy of its previous high-water mark. Yes, that number includes contributions but it’s still shows drastic improvement from the low.

2. Last year’s personal rate of return was somewhere around -40%. This year’s is currently at 29.3%. Remember, the personal rate of return takes into account contributions.

I don’t know where the economy/market is headed but I’m content to stay the course and believe it will pay off in the long run. I have tweaked our 401(k) investment selections a bit but haven’t moved money from asset class to asset class. We are still 100% equities, divided evenly between large-cap, midcap, smallcap, and international. NOTE: I’m NOT recommending this allocation for anyone. I’m just telling you how we are investing our 401(k) account.

I logged into my wife’s 401(k) account this morning to find this:

The definition of personal rate of return (I put together a tutorial here) on Fidelity’s website is:

Your Personal Rate of Return is calculated with a time-weighted formula, widely used by financial analysts to calculate investment earnings. The calculated value reflects the result of your investment selections as well as any activity in the plan accounts shown. Other personal rate of return formulas may yield different results. Remember, past performance is no guarantee of future results.

That explains why our personal rate of return looks so good. For one, we increased our contribution amount AFTER the carnage of January and February. Two, the company’s profit-sharing contribution was also deposited in March, missing the bad months of January and February. In other words, our number could look much worse.

My point?

INVEST!

Invest regularly and forget about it! Have your allocation plan set up and STICK TO IT! Don’t worry about the news. If your 401(k) balance is going to bug you, DON’T LOOK AT IT! The worst thing you can do is allow your emotions to take control. Decisions made on emotion almost never work out.

Okay, that’s it. Carry on…

This comment was left by Leland Holliday on an old post, Fun Math: How to Calculate Returns Using Monthly Data:

I used your method on each of the funds in my portfolio for YTD 2008 (thru 10/31/08). The results I got exactly matched the YTD values published in WSJ and on the Fidelity and Vanguard websites. That surprised me, since I had made major transfers during June (greatly reducing the amounts in stock-based funds). Thus, I expected my personal rate of return in those funds to be different from the published returns. Am I doing something wrong, or is the method wrong for my situation?

Leland,

Most likely what you calculated was not your personal rate of return but the returns of the funds themselves. Your personal rate of return takes into account the timing of your contributions and withdrawals. The easiest way to perform this calculation is with Excel’s XIRR formula.

The only portion of your account that would have received the same returns as those published in the Wall Street Journal are those that were invested during that same exact period.

I hope this helps. For more on the personal rate of return, see this post.

What could possibly be better than a 20% return on your money?? I’ll tell you, and it isn’t a gimmick.

My wife’s company moved their 401(k) to Fidelity in January 2001. For the most part, it was a good move. One feature that Fidelity has that I really like is the Year-to-Date Change screen that looks like this:

(If this were an anonymous blog, I would have no problem giving more details.)

Anyway, Fidelity started offering this information a couple of years ago. I really like the fact that it gives us our year-to-date personal rate of return, which happens to be 8% so far this year. On the last day of each year I like to print out this page along with the transaction history for the entire year because it gives a great summary of what went on with our account. Then I file this information in our 401(k) folder for future reference. I also have a massive Excel file that I have used to track every transaction in the 401(k) but I haven’t updated it since December, 2004.

How much attention do you pay to your 401(k) or retirement account? Do you check the balance daily, weekly, or monthly? I’m a daily guy (unless the market was really down). They say the more frequently you check your balance, the more likely you are to make changes. So far, I have never let the short-term market swings cause me to make changes. Instead, when the market is down I just think about all those extra shares we are going to pick up because of the lower prices!

UPDATE: In response to CK’s comment below, here’s what Fidelity has to say about the way personal rate of return is calculated:

Your Personal Rate of Return is calculated with a time-weighted formula, widely used by financial analysts to calculate investment earnings. It reflects the result of your investment selections as well as any activity in the plan account(s) shown. There are other Personal Rate of Return formulas used that may yield different results. Remember that past performance is no guarantee of future results.

I have written about personal rate of return in the past:

How to Calculate Your Personal Rate of Return

Question From a Reader: Calculating Returns

Question From a Reader: Calculating Personal Rate of Return

Figuring Your Personal Rate of Return

My How ‘Total Returns’ Are Calculated post received the following comment from a reader named Dan:

I get a little confused when I try to make these calculations to work with (1) purchases that occur at different times and (2) account fees. As an example of each:

– Suppose I buy somes shares in January. I earn a dividend in March and reinvest that. Then in April, the price has changed, and I buy more shares. I earn another dividend in September, then December. Can I calculate a “combined APY” that represents my return on both those transactions together?

– Suppose everything is the same as above, except that in August, I have to pay a \$20 fee to maintain the account. It matters -when- this fee is paid, right? How do I consider that in my calculations? Are there any good reference books for this?

Dan,

This is fairly easy to do in Excel. This particular calculation is called the Personal Rate of Return. I did a tutorial on this last April, which you can read here. Just remember for the formula to work, you must have both negative and positive numbers. So, I would enter any deposits you make into the account as negative numbers and withdrawals (such as the \$20 account fee you mentioned) as positive numbers. CORRECTION: I was wrong. You do not include reinvested dividends in the calculation because they are accounted for in the end result. The \$20 fee also should be included as a negative number.

It should looks something like this: