Reader Question Regarding 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.

401(k) Just a Few Thousand Away From Previous High

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.

SURPRISE!!!!! Our Personal Rate of Return is 11.5% for 2009!

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

Personal Rate of Return

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 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…

Question From a Reader – Am I Calculating My Returns Correctly?

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?


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’s Better Than A 20% Return?

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

Investor 1 wants to do right by herself and her money. She’s worked hard to stash away $1000, and she’s determined to maximize her return. So over the course of a year she spends much of her time online researching stocks. She keeps up with all the news, trades often, and is alternately exhilerated and stressed with every volatile turn in the markets.

She does well and earns a 20% return — even after taxes and all those trading fees! At the end of the year her $1000 has turned into $1200. She smiles and pats herself on the back. Then she flips on CNBC to see if she can pick up some tips on how to duplicate that return next year.

Investor 2 also worked hard to stash away $1000. She too wants to do what’s best for her financial future by making that money grow as fast as possible. Therefore she sticks her $1000 in an index fund. She knows this will minimize fees and taxes and – more importantly – that she can just let that money sit there without having to spend a lot of time tracking the markets, trading stocks, or doing research.

She’s diversified, and at the end of the year she has returned 8% on her money with little to no effort. She smiles, satisfied, and knows that over time she’ll probably end up averaging that return.

Investor 2! [trumpets blare] So wait–why is Investor 2 better off than Investor 1!? No, not because she’s taking the slow and steady route to win the race, and not because she appears to be more sensible than Investor 1 or because she better manages her time and values balance in her life (although those are all good reasons).

Investor 2 wins simply because she ended the year with over $1,700 while Investor 2 only had $1200 in her account. “What?!” you cry. “But Investor 2 only made 8%; how can she end up with $1,700??–that’s a 70% return!” Good catch by you. I left out one detail: Investor 2 managed to put away an additional $50 a month during the course of the year. She used her extra time not to chase returns on the latest booming sector but rather to make lunches for work, learn to effectively grocery shop, mow her own lawn instead of pay the neighbor kid, and cook dinner more often.

The Moral of the Story
OK, so if she actually did all those things she could have saved a lot more than $50/mo. She could have spent all her free time playing Guitar Hero 3, but the point is that putting away more money is a whole lot more effective than trying to maximize your return.


Caveat: Ok, so once you have over $1,000,000 in investments your return starts to matter–and at that point it matters a LOT. The difference between an 8% return and a 10% return is $20,000 a year when you have a million in the bank. But if you have $100,000 the difference is only $2,000.

Sure, $2000 is a lot of money, but it’s only $166 a month. That’s not exactly worth paying expensive financial advisors, racking up trading commissions and taxes, or being chained to the Wall Street Journal and CNBC to track your bet-of-the-week.

Why not just accept market returns, keep saving, and enjoy your life?

More from Meg at The World of Wealth

Fidelity’s Year-to-Date Change Feature

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

Question From a Reader – Calculating Returns

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?


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:

Personal Rate of Return Example

The 26.23% was found using the XIRR formula in Excel. Read this post to learn more about the XIRR function.