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.148^{21/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.

Wow, that’s some good fund pick’n right there — trounces my PRoR 3.2% over the same date-range. Though I don’t know the value for the 16th, since the market is still open…

Cool tip on XIRR, always wondered how that one was calculated — thanks.

Came across this post and had a question….how do you calculate your PRR for the time period when the Annualized PRR is a negative number??

When you enter a negative number into the Excel IRR function you get an error because you can’t raise a negative number to a power (i.e. -8.43% ^ (180/365) = #NUM error).

Thanks for your help…great blog!