How to calculate return on a portfolio

[diyinvestor]

I'm trying to calculate the return on my portfolio for the last year, and I have some questions. Is the correct formula IRR? And do dividend reinvetments need to be factored in?
Thnks

[rustymutt]

Return=(ending balance - starting balance - contributions) / (starting balance + contributions)

Dividends reinvested need not be concerned about, but put into another account would need to be factored into this.

[livesoft]

From the wiki:

For investments that are not periodic, where there are arbitrary contributions and withdrawals, use XIRR.
XIRR is the best and most accurate method for calculating your rate of return, if you know the dates you made your investment contributions and your withdrawals. This method is far more accurate than annual average returns, as contributions and withdrawals (cash flows) are weighted for both time and value.[19] See the Excel help file to understand how it works.

[sscritic]

Return=(ending balance - starting balance - contributions) / (starting balance + contributions)

examples:
end = 12000
start = 10000
contr = 0
Return = 2000/10000 = 0.20

same with contr = 1000 on 1/1
Return = 1000/11000 = 0.0909

now with contr on 12/31
Return = 1000/11000 = 0.0909

Why are the returns the same for the last two examples when one contribution was invested for 1 day and the other for 365? My version of the correct answer for the last example is 20% or something very close. I like the second example; that answer makes sense. It appears as if this formula assumes all contributions are made on 1/1; if contributions are added to the starting balance as if they were already there on 1/1, you get the same answer.

[example equivalent to second example above]
end = 12000
start = 11000
contr = 0
Return = 1000/11000 = 0.0909

P.S. Algebraic proof of equivalence:

Return=(ending balance - starting balance - contributions) / (starting balance + contributions)
=(ending balance - (starting balance + contributions)) / (starting balance + contributions)
= (ending balance - investment) / investment, where investment = starting balance + contributions

[gt4715b]

If you made contributions to the account or withdrew money from the account, use XIRR. Reinvested dividends are not considered contributions/withdrawal in this context. If you didn't make contributions or withdraw money then the return is simply (S-E)/S where S is the starting balance and E is the ending balance.

Return=(ending balance - starting balance - contributions) / (starting balance + contributions)

Dividends reinvested need not be concerned about, but put into another account would need to be factored into this.

This assumes all contributions for the year are made at the beginning of the year. XIRR is a more exact way to compute the return.

[Rick Ferri]

See Appendix #1 of my free on-line book, Serious Money, Straight Talk About Investing for Retirement., published in 1999. It's a step-by-step return calculation guide.

Rick Ferri

[livesoft]

XIRR will work no matter what you did. The XIRR algorithm is what programs like MSMoney, Quicken and places like Vanguard, Fidelity and Morningstar use.

[jeffyscott]

I assume, to be precise, XIRR would require tracking the date of every contribution or withdrawal. That would be too much trouble, so I have never bothered to learn how to use XIRR.

I've used Rick's formulas, which assume all contributions occur at the midpoint of the period. I do this quarterly, rather than monthly, this is close enough for me. As the balance grows, the timing of contributions would affect the actual true rate of return less and less, so the approximation becomes more accurate.

I suppose XIRR could be set up to do the same approximation as Rick's formula, by just entering all contributions as if they occurred on the date that is the midpoint of the period.

[magician]

I'm trying to calculate the return on my portfolio for the last year, and I have some questions. Is the correct formula IRR? And do dividend reinvetments need to be factored in? Thnks

The short answer is: Yes, IRR is the correct approach.

The many suggestions above to use the (Excel) XIRR function are saying that IRR is the correct approach; the XIRR function computes IRR when contributions are made at irregular intervals.

[petrico]

I assume, to be precise, XIRR would require tracking the date of every contribution or withdrawal. That would be too much trouble, so I have never bothered to learn how to use XIRR.

It's not really that big a deal if you record the entries as they happen. Following EmergDoc's example, I started recording all contributions (not being retired, there are no withdrawals) at the start of 2007. This year, for example, I had 39 entries: 26 457b contributions, 12 taxable contributions, and 1 lump sum contribution at the beginning of the year to fund a Roth. XIRR is computed on an ongoing basis as the date and portfolio value is revised. (XIRR for the year is 12.38% as of today.)

By starting this tracking in 2007, I am now also able to track the portfolio XIRR since the pre-recession market peak of mid-October 2007 (XIRR=3.45% since then -- no complaints).

To the OP: Try reviewing some of "Gummy's" educational pages:

http://www.financialwebring.org/gummy-stuff/xirr.htm

http://www.financialwebring.org/gummy-s ... f.htm#XIRR

http://www.financialwebring.org/gummy-s ... .htm#XIRR2

--Pete

[2retire]

I assume, to be precise, XIRR would require tracking the date of every contribution or withdrawal. That would be too much trouble, so I have never bothered to learn how to use XIRR.

You are actually required to keep this information if you have taxable accounts. It is usually easy enough to cut and paste (or export) the values from your financial websites into a spreadsheet. It is also easy enough to update your spreadsheet at the end of the year when you get your year end statement.

[petrico]

I assume, to be precise, XIRR would require tracking the date of every contribution or withdrawal. That would be too much trouble, so I have never bothered to learn how to use XIRR.

You are actually required to keep this information if you have taxable accounts. It is usually easy enough to cut and paste (or export) the values from your financial websites into a spreadsheet. It is also easy enough to update your spreadsheet at the end of the year when you get your year end statement.

Cost basis accounting is maybe better accomplished with a different spreadsheet, where each share is tracked in more detail.

For portfolio-level return computations using the XIRR function, the inputs are extremely simple. All you need to do is track contributions (and/or withdrawals) in lumps. There's no need to break anything down to the individual security level. It doesn't even matter what type of account it goes into.

For example, once a month I deposit to a MM fund in a taxable account. That's all I record for an XIRR computation. Which individual fund(s) I purchase after that, the dates and amounts of the purchases, share prices, transaction costs, etc., is all irrelevant for XIRR purposes. Similarly, when a deposit is made to a defined contribution plan, all you need to record is the total deposit amount -- regardless of how many individual funds are bought.

I agree that it's easy to use the year-end statements to update cost basis accounting spreadsheets for individual taxable funds once a year. But, again, more detail is required for that.

--Pete

[diyinvestor]

Thank you, everyone, for your helpful responses, and to Rick for his material. IMO, without question, there is no better investment resource than this one, anywhere.

[JustinR]

Return=(ending balance - starting balance - contributions) / (starting balance + contributions)

Dividends reinvested need not be concerned about, but put into another account would need to be factored into this.

I have this formula:

(End - Start - Contributions) / (Start + (Contributions/2))

OR

(End - (Contributions/2)) / (Start + (Contributions/2)) - 1

Which is the same as yours except you divide the contributions in half in the divisor.

Edit: It's the Simple Dietz Method or Midpoint Dietz.

Time weighted performance estimation based upon an assumption that all cash flows occur midway through the period of evaluation.

I think this is as good as you're going to get for those of us who don't want to enter an entry for every transaction you made during the year (really cumbersome when you have something like a 401k).

[SLH]

Previously, I created an Excel spreadsheet to keep track of the performance of my investments in different accounts. I used the XIRR function to calculate dollar-weighted trailing returns and annual returns for all my accounts.

However, I found it a bit tedious to keep track of everything in Excel. Recently, I started using portfolio-hub to track my investments. I just record all my transactions in an Excel spreadsheet (without providing any personal details) and upload it to the website. It then calculates dollar-weighted returns and balance trends.

So far I have been happy with the results.

[Default User BR]

Previously, I created an Excel spreadsheet to keep track of the performance of my investments in different accounts. . . . However, I found it a bit tedious to keep track of everything in Excel.

I find it pretty easy. I use feeder spreadsheets into the main one, because almost all institutions these days provide an excel export of your account data. The one I have that doesn't is my 401(k), but they do provide a table on the website that I can copy and paste into a feeder sheet.


Brian

[PaddyMac]

Thanks for the link to the gummy articles. I found a page of his Excel templates here

http://www.financialwebring.org/gummy-stuff/Excel/

scroll down half way for the two XIRR templates.

[letsgobobby]

I gave the old XIRR a try this year, keeping track of what appears to be 83 different contributions throughout the year. Not sure I'd do it again, but it was a good exercise. I only did this because I asked exactly your question last year.

[Wild Willie]

Let me jump in here with a different wrinkle: My case is different in that I am retired and make no contributions during the year, only withdrawals. The formula I use is (end + withdrawals - start) / start. Now, I realize that doesn't allow for the interest or dividends or appreciation effects of the withdrawals on a timed basis, but it seems to me that this is close enough for "horseshoes and hand grenades" or am I way off base?

[FinancialDave]

Let me jump in here with a different wrinkle: My case is different in that I am retired and make no contributions during the year, only withdrawals. The formula I use is (end + withdrawals - start) / start. Now, I realize that doesn't allow for the interest or dividends or appreciation effects of the withdrawals on a timed basis, but it seems to me that this is close enough for "horseshoes and hand grenades" or am I way off base?

As you can see from the above there are many ways to calculate a return -- the method you are using is what the textbooks call Holding Period Rate of Return (HPR). The withdrawals could simply be the dividends paid to you in cash, but the formula is the same.

If you want to annualize the number you do need to take into account the time period if it is other than 1 year.

fd

[FinancialDave]

I gave the old XIRR a try this year, keeping track of what appears to be 83 different contributions throughout the year. Not sure I'd do it again, but it was a good exercise. I only did this because I asked exactly your question last year.

Did you try downloading the transactions in Excel format from your website - these usually come with the transaction dates intact and takes less than 10 min. to slap in an XIRR formula.

fd

[JustinR]

How do you link annual returns for a total lifetime return?

Does Rick's formula for linking quarterly -> annual work the same way?

So if you had annual returns of 20%, 30%, -5%, and 40% it would be:

1.2 * 1.3 * .95 * 1.4 - 1 = 107%?

That doesn't seem right to me for some reason.

[letsgobobby]

I gave the old XIRR a try this year, keeping track of what appears to be 83 different contributions throughout the year. Not sure I'd do it again, but it was a good exercise. I only did this because I asked exactly your question last year.

Did you try downloading the transactions in Excel format from your website - these usually come with the transaction dates intact and takes less than 10 min. to slap in an XIRR formula.

fd

No, I wasn't aware of that. Where do I find this on Vanguard's website?

[magician]

How do you link annual returns for a total lifetime return?

Does Rick's formula for linking quarterly -> annual work the same way?

So if you had annual returns of 20%, 30%, -5%, and 40% it would be:

1.2 * 1.3 * .95 * 1.4 - 1 = 107%?

That doesn't seem right to me for some reason.

Well . . . it is.

20% + 30% + -5% + 40% = 85% as a lower bound (that ignores compounding).

Add in 6% (= 20% x 30%),
-1% (= 20% x -5%),
8% (= 20% x 40%),
-1.5% (= 30% x -5%),
12% (= 30% x 40%),
-2% (= -5% x 40%),
and you get 85% + 6% - 1% + 8% - 1.5% + 12% - 2% = 106.5%.

I'll leave the three-factor and four-factor terms for you; 107% is not unreasonable.

[FinancialDave]

I gave the old XIRR a try this year, keeping track of what appears to be 83 different contributions throughout the year. Not sure I'd do it again, but it was a good exercise. I only did this because I asked exactly your question last year.

Did you try downloading the transactions in Excel format from your website - these usually come with the transaction dates intact and takes less than 10 min. to slap in an XIRR formula.

fd

No, I wasn't aware of that. Where do I find this on Vanguard's website?

When you log into your account, at the top of the page use the pull down on MY ACCOUNT, TRANSACTION HISTORY, then across the top highlighted menu bar at the far right is a symbol of a large down arrow with a bar across the top -- click on that -- go to bottom right of page DOWNLOAD SPREADSHEET.

fd

[FinancialDave]

How do you link annual returns for a total lifetime return?

Does Rick's formula for linking quarterly -> annual work the same way?

So if you had annual returns of 20%, 30%, -5%, and 40% it would be:

1.2 * 1.3 * .95 * 1.4 - 1 = 107%?

That doesn't seem right to me for some reason.

That is right for the total return 107.48%, but it just makes more sense to me to track the total compounded annualized return, for which you can easily do using the Excel GEOMEAN formula, which for the above gives you 20.02% annual return over the 4 years, which then allows you to compare yourself to a number of other benchmarks.

fd

[#Cruncher]

So if you had annual returns of 20%, 30%, -5%, and 40% it would be:
1.2 * 1.3 * .95 * 1.4 - 1 = 107%?
That doesn't seem right to me for some reason.

Yes, as magician and FinancialDave say 107% is right. You can see this by plugging the four annual change figures into an example. In the one below $1,000 grows to $2,074.80 which is an increase of 107%.

Code: Select all

  Col:     A        B         C           D          E
  Row     Year    Growth    Value    Avg Growth    Value
  ---     ----    ------  --------   ----------  --------
   2       0              1,000.00               1,000.00
   3       1       20%    1,200.00     20.017%   1,200.17
   4       2       30%    1,560.00     20.017%   1,440.42
   5       3       (5%)   1,482.00     20.017%   1,728.75
   6       4       40%    2,074.80     20.017%   2,074.80
   7               20.017% <-- avg annual growth =(C6/C2)^(1/4)-1

Use the formula above to compute the 20.02% average annual growth that FinancialDave mentions. The two right columns of the table show this average growth applied against the same $1,000 starting balance and producing the same $2,074.80 end result.

[JustinR]

Thanks guys.

Question about XIRR. It's annualized right? Is there a way to un-annualize it or another formula or function so that I can get total return similar to the 107% figure above (using total lifetime spreadsheet data)?

In other words, I'm not looking for "average annual return" (is that what the XIRR is telling me?), but the total return using the transaction data.

[magician]

Thanks guys.

My pleasure.

Question about XIRR. It's annualized right?

Yes.

Is there a way to un-annualize it or another formula or function so that I can get total return similar to the 107% figure above (using total lifetime spreadsheet data)?

In other words, I'm not looking for "average annual return" (is that what the XIRR is telling me?), but the total return using the transaction data.

Once you have the annual number, you can compound it for whatever period you want. In your example, once you have the annual number of 20.02%, if you want the four-year holding period return, you simply compute (1 + 0.2002)^4.

In your Excel spreadsheet, if you subtract the first date from the last date and divide by 365 (or 365.25, if you prefer), that calculation will give you the number of years you use (in place of the "4", above).

[JustinR]

Thanks guys.

My pleasure.

Question about XIRR. It's annualized right?

Yes.

Is there a way to un-annualize it or another formula or function so that I can get total return similar to the 107% figure above (using total lifetime spreadsheet data)?

In other words, I'm not looking for "average annual return" (is that what the XIRR is telling me?), but the total return using the transaction data.

Once you have the annual number, you can compound it for whatever period you want. In your example, once you have the annual number of 20.02%, if you want the four-year holding period return, you simply compute (1 + 0.2002)^4.

In your Excel spreadsheet, if you subtract the first date from the last date and divide by 365 (or 365.25, if you prefer), that calculation will give you the number of years you use (in place of the "4", above).

Hmmm. Thanks. This works, except the annual average using FinancialDave's and another method is different from XIRR by about 6%.

The two formulas I used for the annual average are (using the example above):

=(totalreturn+1)^(1/(1461/365.25)) - 1

=(1.0748+1)^(1/(1461/365.25)) - 1 = 0.200173573 = 20.02%

=GEOMEAN(1+A1:A4)) - 1

=GEOMEAN(1.2,1.3,0.95,1.4) - 1 = 0.200173573 = 20.02%

These both work for the above example (20%, 30%, -5%, and 40%) as well as my real numbers. But XIRR for the four years on my real numbers using the transaction data is 6% off. And therefore after applying your formula to the two different annual values, the difference is REALLY off. For example, 10% compared to 15% annual return is almost a 25% difference after using the formula.

Would XIRR probably be more accurate? (I realize it's hard to say without my real numbers)

Edit: I just checked this against my 401k provider's personal return numbers on the site. The XIRR seems much more accurate than the other calculation. It's only 1% off annualized and 2% off cumulative, while the other method is off 5% annualized and 25% off cumulative.

[Supermercado]

After seeing this thread and the one about 2012 returns, I've been trying to calculate my return. I copied all my data into Google Docs and used the XIRR function to do so, but the number it's spitting out is a lot higher than I was anticipating and was hoping for a sanity check or maybe an explanation of what's going on. The XIRR shows 24.21%. Fidelity's Personal Rate of Return for my 401k says 17.2% and Vanguard says the 1 year performance for my Roth IRA is 11.3%. I'm not sure how those two numbers can combine to be 24.21%.

The data is coming from two sources, my 401k and my Roth IRA. The Roth IRA had nothing but contributions, the 401k was almost all contributions but there are a few negative values where fees were taken out. I think I was correct in including those figures since they affect the balance. This was my first year following Boglehead principles so there were a few exchange transactions from both sources as I settled on my asset allocation, I left those out. I also left out dividends that were paid.

I copied the data into a separate Google Docs spreadsheet that can be found here: https://docs.google.com/spreadsheet/ccc?key=0Apz7MC_uiPTddHRub2h4eE0tZkRONlJRR3N0Y3VIUXc. If anyone has a minute to take a quick look and see if I'm doing it correctly, it would be greatly appreciated. Thanks!

[sscritic]

the 401k was almost all contributions but there are a few negative values where fees were taken out. I think I was correct in including those figures since they affect the balance.

You don't take them out for the same reason you don't put dividends in. If I start at 1000 and end up at 1200, the only thing that matters are contributions and withdrawals. If I have neither, I am up 20% even if there are $2000 of dividends added and $1800 of fees taken out.

[Supermercado]

You don't take them out for the same reason you don't put dividends in. If I start at 1000 and end up at 1200, the only thing that matters are contributions and withdrawals. If I have neither, I am up 20% even if there are $2000 of dividends added and $1800 of fees taken out.

Thanks for the reploy, that makes sense. I've removed those fees from the spreadsheet. They weren't much, not enough to affect the calculation significantly, after removing them, the XIRR is 24.08%.

[DR]

It appears you may not have entered a beginning balance for the first entry at the beginning of the year.

[DR]

I don't believe you want to divide by 100 there in the formula. It is already going to give you a percentage. The return on that series is 2408.44% ! That's because you begin with $416 and end with $114K. I suspect you went into 2012 with some existing balance? You contributed 14,754 but end with $114,381.

[Supermercado]

It appears you may not have entered a beginning balance for the first entry at the beginning of the year.

Ah, okay, that makes more sense, thank you! I think when I tried adding the beginning balance, I had added it as a negative number and it was giving me an error that it couldn't compute the return. Apparently I was just having a moment of madness, because as soon as I changed it to a positive value, the return dropped from 24.08% to a much more reasonable value of 16.36%. That seems a lot closer in line with what I was expecting, thank you!

I don't believe you want to divide by 100 there in the formula. It is already going to give you a percentage. The return on that series is 2408.44% ! That's because you begin with $416 and end with $114K. I suspect you went into 2012 with some existing balance? You contributed 14,754 but end with $114,381.

Wouldn't it be nice if the return was 2408.44%?! We can wish, haha. I think that division by 100 was a product of my messing around with the display, trying to get it to display 24.08%. I think I had gotten some formatting messed up and it was coming out even more incorrect.

I think the issue is fixed, though, between leaving my fees in, not having an initial balance entered properly, and having wonky formatting, I was all sorts of out of order. 16.36% seems like it's in line with my 401k's stated rate of return and my Roth IRA's personal performance.

Thanks for all the help!

[DR]

Yes, that looks correct now!

[Supermercado]

Yes, that looks correct now!

Excellent, thanks again for your help! I've always been curious how to properly calculate my return across both investment vehicles and now I know and have a working example for 2013 and beyond, very happy about that. Thanks!

[JustinR]

I wonder why Vanguard doesn't have return in percentage form anywhere on the site, other than "1 year". Or am I missing something?

[DR]

Yes, that looks correct now!

Excellent, thanks again for your help! I've always been curious how to properly calculate my return across both investment vehicles and now I know and have a working example for 2013 and beyond, very happy about that. Thanks!

Yes, I really like the XIRR function. If you spend some time in Excel, you can find some ways to quickly enter your periodic contributions through the year--I don't think it takes much time. Using that formula, you'll get the annualized return--even when the date series goes beyond a year. Of course less than a year and you'll get some really skewed results until you get close to the end of the year. So there's a twist on the XIRR which allows you to see YTD returns--not just annualized returns as you see now. http://www.gummy-stuff.org/XIRR-stuff.htm

[Supermercado]

Yes, that looks correct now!

Excellent, thanks again for your help! I've always been curious how to properly calculate my return across both investment vehicles and now I know and have a working example for 2013 and beyond, very happy about that. Thanks!

Yes, I really like the XIRR function. If you spend some time in Excel, you can find some ways to quickly enter your periodic contributions through the year--I don't think it takes much time. Using that formula, you'll get the annualized return--even when the date series goes beyond a year. Of course less than a year and you'll get some really skewed results until you get close to the end of the year. So there's a twist on the XIRR which allows you to see YTD returns--not just annualized returns as you see now. http://www.gummy-stuff.org/XIRR-stuff.htm

Yeah, I'm definitely going to play with it over the course of this year and not have to enter everything at the end of the year. I'll take a look at the link you posted and see what I can do for YTD returns. Thanks again.

[FinancialDave]

I wonder why Vanguard doesn't have return in percentage form anywhere on the site, other than "1 year". Or am I missing something?

It may depend on how long you have had your portfolio. I know the first year, I could not get YTD because I wasn't invested Jan 1.

At the very bottom of the MY ACCOUNTS summary page is a pick called PERSONAL PERFORMANCE, this is where you can get the % PRR.

You are right though - I don't see anything but the one year.

On the INVESTMENTS PRICES AND RETURNS you can get YTD on each fund you own.

fd

[2retire]

the 401k was almost all contributions but there are a few negative values where fees were taken out. I think I was correct in including those figures since they affect the balance.

You don't take them out for the same reason you don't put dividends in. If I start at 1000 and end up at 1200, the only thing that matters are contributions and withdrawals. If I have neither, I am up 20% even if there are $2000 of dividends added and $1800 of fees taken out.

That doesn't make sense to me. I include fees in my XIRR calculations. I don't see any difference between a withdraw and a fee I have to pay. If the number of shares I own is adjusted downwards and the price of each share stays the same, that is different than a dividend where shares*price stays the same before and after the dividend. My 401k provider takes $8 a month out of my account to pay fees.

[2retire]

I copied the data into a separate Google Docs spreadsheet that can be found here: https://docs.google.com/spreadsheet/ccc?key=0Apz7MC_uiPTddHRub2h4eE0tZkRONlJRR3N0Y3VIUXc. If anyone has a minute to take a quick look and see if I'm doing it correctly, it would be greatly appreciated. Thanks!

FYI, your spreadsheet is much more detailed than it needs to be. You don't need to track the individual dollars flowing into each fund as their own line item. You can simply record the total dollar value deposited on that day.

This can reduce you to somewhere in the neighborhood of:

12-24 entries for the total amount of money taken out of each paycheck (monthly or semi-monthly) for your retirements accounts.
1-24 entries for the amount of money put into your Roth (if you put in all 5k at once you will only need one entry)
12-24 entries for the amount of money put into taxable (assuming you invest somewhere in the neighborhood of once or twice a month)

My XIRR spreadsheet has 61 entries every year (not including start and end balance) with over 10 funds involved. 24 for 401k, 1 for Roth, 24 for taxable, and 12 for fees.

[sscritic]

You don't take them out for the same reason you don't put dividends in. If I start at 1000 and end up at 1200, the only thing that matters are contributions and withdrawals. If I have neither, I am up 20% even if there are $2000 of dividends added and $1800 of fees taken out.

That doesn't make sense to me. I include fees in my XIRR calculations. I don't see any difference between a withdraw and a fee I have to pay.

If you pay the fee out of your checking account you should, it is just like a contribution. If you don't, it isn't.

If you want to know your return, you have to look at your money. If you want to look at the return the fund makes, look at the fund's money.

Here are three funds; you made no contributions or withdrawals during the year:
A starts at 1000 and your account goes to 1120 on 12/31 when the annual $20 fee is charged. You have a balance of $1100 at the end of the year.
B starts at 1000 and your account goes to 1100 on 12/31; no fee is charged. You have a balance of $1100 at the end of the year.
C starts at 1000 and a $20 annual fee is taken out on June 30. Your account then goes to 1100 on 12/31. At the end of the year, your account is at $1100.

Which fund gave you the better return?

The three fund internally will have had different returns, but that doesn't help you. I am interested in how my money grew, so I look at my money. If you count the internal fees as withdrawals, you will get three different answers for the exact same return to you, but $1100 is $1100, no matter how you got there.

[Supermercado]

I copied the data into a separate Google Docs spreadsheet that can be found here: https://docs.google.com/spreadsheet/ccc?key=0Apz7MC_uiPTddHRub2h4eE0tZkRONlJRR3N0Y3VIUXc. If anyone has a minute to take a quick look and see if I'm doing it correctly, it would be greatly appreciated. Thanks!

FYI, your spreadsheet is much more detailed than it needs to be. You don't need to track the individual dollars flowing into each fund as their own line item. You can simply record the total dollar value deposited on that day.

This can reduce you to somewhere in the neighborhood of:

12-24 entries for the total amount of money taken out of each paycheck (monthly or semi-monthly) for your retirements accounts.
1-24 entries for the amount of money put into your Roth (if you put in all 5k at once you will only need one entry)
12-24 entries for the amount of money put into taxable (assuming you invest somewhere in the neighborhood of once or twice a month)

My XIRR spreadsheet has 61 entries every year (not including start and end balance) with over 10 funds involved. 24 for 401k, 1 for Roth, 24 for taxable, and 12 for fees.

That's about what I was thinking it would look like for 2013. The data for 2012, I just copied from the transaction history spreadsheets I downloaded from my 401k and Roth IRA. When I pulled the data, it broke everything out on a fund by fund basis instead of the whole amount for each day being reported. As I enter the data manually for 2013 every couple weeks or so, I'll definitely follow your advice and have one value for each day, possibly breaking out Roth IRA contributions on the 1st of the month that coincide with 401k contributions hitting my account the same day, just to make the record keeping a little easier. I didn't want to take the time to add up the contributions for 2012 and leave just the per day numbers, but I'll definitely do that as it happens for 2013. It will certainly be a lot easier to manage in years to come now that I know what I'm doing.

Thanks for the suggestion!

[petrico]

the 401k was almost all contributions but there are a few negative values where fees were taken out. I think I was correct in including those figures since they affect the balance.

You don't take them out for the same reason you don't put dividends in. If I start at 1000 and end up at 1200, the only thing that matters are contributions and withdrawals. If I have neither, I am up 20% even if there are $2000 of dividends added and $1800 of fees taken out.

That doesn't make sense to me. I include fees in my XIRR calculations. I don't see any difference between a withdraw and a fee I have to pay. If the number of shares I own is adjusted downwards and the price of each share stays the same, that is different than a dividend where shares*price stays the same before and after the dividend. My 401k provider takes $8 a month out of my account to pay fees.

If you are trying to figure out the return of your portfolio, you should not include fees -- unless they are being paid to you. How are you including fees, as negative numbers (withdrawals)? If so, you are probably slightly inflating your true returns. Or more accurately, you are computing your gross portfolio returns. Finding the portfolio returns net of all fees and expenses is a more common goal.

Because they are not being paid to you, from your perspective the fees are internal to the portfolio. What you are really interested in is accounting for what you, yourself, put in or take out. If your hedge fund manager takes 20% of your portfolio gains, what is being taken is of no use to you, and your computed returns should reflect that unfortunate reality. "Accounting" for the hedge fund manager's take as legitimate withdrawals would mask the loss to you.

--Pete

[magician]

If you are trying to figure out the return of your portfolio, you should not include fees -- unless they are being paid to you. How are you including fees, as negative numbers (withdrawals)? If so, you are probably slightly inflating your true returns. Or more accurately, you are computing your gross portfolio returns. Finding the portfolio returns net of all fees and expenses is a more common goal.

Because they are not being paid to you, from your perspective the fees are internal to the portfolio. What you are really interested in is accounting for what you, yourself, put in or take out. If your hedge fund manager takes 20% of your portfolio gains, what is being taken is of no use to you, and your computed returns should reflect that unfortunate reality. "Accounting" for the hedge fund manager's take as legitimate withdrawals would mask the loss to you.

--Pete

Excellent point.

Fees (paid to someone else) are the same as investment losses.

If you look at the cash flows from the investor's point of view, then it's easy to see that fees are neither a cash inflow (received by the investor) nor a cash outflow (contributed by the investor). The only way you consider the fees is that they make your final balance (the final cash flow received by the investor) smaller.

[2retire]

@sscritic and petrico:

I still don't see it. It seems to me everyone is overlooking the name of the function; Internal Rate of Return. Meaning, what must your portfolio earn within itself, to be sure you get to the stated value at the end of the year. If your balance decreases during the year, your return must increase to get you back to the same place at the end of the year.

Example: Lets consider a portfolio that had no deposits or withdraws.

Code: Select all

01/01/2012  $1000
12/31/2012 -$1100
XIRR = 10%


I think we can agree that is simple and straight forward.

Now let's consider that you withdrew $500 mid way through the year.

Code: Select all

01/01/2012  $1000
07/01/2012 -$500
12/31/2012 -$1100
XIRR = 76.47%


As you can see, in order for you to get back to your $1100 at the end of the year, your money within the portfolio had to work a hell of a lot harder. It is my understanding that that is what XIRR really means; not what percent your portfolio increased by (or what you made or lost).

Now, lets change that $500 from being a withdraw you took versus a fee that was paid to the fund. The fact still remains that your portfolio had to achieve a 76% return to get you back to where you started. Where is my logic breaking down?

[magician]

@sscritic and petrico:

I still don't see it. It seems to me everyone is overlooking the name of the function; Internal Rate of Return. Meaning, what must your portfolio earn within itself, to be sure you get to the stated value at the end of the year. If your balance decreases during the year, your return must increase to get you back to the same place at the end of the year.

Example: Lets consider a portfolio that had no deposits or withdraws.

Code: Select all

01/01/2012  $1000
12/31/2012 -$1100
XIRR = 10%


I think we can agree that is simple and straight forward.

Now let's consider that you withdrew $500 mid way through the year.

Code: Select all

01/01/2012  $1000
07/01/2012 -$500
12/31/2012 -$1100
XIRR = 76.47%


As you can see, in order for you to get back to your $1100 at the end of the year, your money within the portfolio had to work a hell of a lot harder. It is my understanding that that is what XIRR really means; not what percent your portfolio increased by (or what you made or lost).

Now, lets change that $500 from being a withdraw you took versus a fee that was paid to the fund. The fact still remains that your portfolio had to achieve a 76% return to get you back to where you started. Where is my logic breaking down?

If it's a withdrawal, you have $500 in your pocket; if it's a fee, you don't. Thus, you aren't getting back to where you started; you're getting back to a place where you're $500 worse off. Just as if your investment had lost $500.