How to calculate return on a portfolio

 Posts: 24
 Joined: Sat Jan 21, 2012 3:36 am
How to calculate return on a portfolio
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
Thnks
Re: How to calculate return on a portfolio
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.
Dividends reinvested need not be concerned about, but put into another account would need to be factored into this.
Knowledge is knowing that the Tomato is a fruit. Wisdom is knowing better than to put the tomato in a fruit salad.
Re: How to calculate return on a portfolio
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.
Re: How to calculate return on a portfolio
rustymutt wrote: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
Re: How to calculate return on a portfolio
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 (SE)/S where S is the starting balance and E is the ending balance.
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.
rustymutt wrote: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
 Posts: 8066
 Joined: Mon Feb 26, 2007 11:40 am
 Location: Home on the range in Medina, Texas
 Contact:
Re: How to calculate return on a portfolio
See Appendix #1 of my free online book, Serious Money, Straight Talk About Investing for Retirement., published in 1999. It's a stepbystep return calculation guide.
Rick Ferri
Rick Ferri
Choose a few lowcost index funds in different asset classes, rebalance occasionally and forgetaboutit!
Re: How to calculate return on a portfolio
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
 Posts: 6181
 Joined: Tue Feb 27, 2007 9:12 am
 Location: Wisconsin
Re: How to calculate return on a portfolio
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.
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.
press on, regardless  John C. Bogle
Re: How to calculate return on a portfolio
diyinvestor wrote: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.
Simplify the complicated side; don't complify the simplicated side.
Re: How to calculate return on a portfolio
jeffyscott wrote: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 prerecession market peak of midOctober 2007 (XIRR=3.45% since then  no complaints).
To the OP: Try reviewing some of "Gummy's" educational pages:
http://www.financialwebring.org/gummystuff/xirr.htm
http://www.financialwebring.org/gummys ... f.htm#XIRR
http://www.financialwebring.org/gummys ... .htm#XIRR2
Pete
Re: How to calculate return on a portfolio
petrico wrote:jeffyscott wrote: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.
Re: How to calculate return on a portfolio
2retire wrote:petrico wrote:jeffyscott wrote: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 portfoliolevel 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 yearend statements to update cost basis accounting spreadsheets for individual taxable funds once a year. But, again, more detail is required for that.
Pete

 Posts: 24
 Joined: Sat Jan 21, 2012 3:36 am
Re: How to calculate return on a portfolio
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.
Re: How to calculate return on a portfolio
rustymutt wrote: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).
Re: How to calculate return on a portfolio
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 dollarweighted 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 portfoliohub 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 dollarweighted returns and balance trends.
So far I have been happy with the results.
However, I found it a bit tedious to keep track of everything in Excel. Recently, I started using portfoliohub 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 dollarweighted returns and balance trends.
So far I have been happy with the results.

 Posts: 7501
 Joined: Mon Dec 17, 2007 7:32 pm
Re: How to calculate return on a portfolio
SLH wrote: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
Re: How to calculate return on a portfolio
Thanks for the link to the gummy articles. I found a page of his Excel templates here
http://www.financialwebring.org/gummystuff/Excel/
scroll down half way for the two XIRR templates.
http://www.financialwebring.org/gummystuff/Excel/
scroll down half way for the two XIRR templates.

 Posts: 8204
 Joined: Fri Sep 18, 2009 1:10 am
Re: How to calculate return on a portfolio
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.

 Posts: 95
 Joined: Mon Jul 05, 2010 10:15 am
Re: How to calculate return on a portfolio
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?

 Posts: 1303
 Joined: Thu May 26, 2011 9:36 pm
Re: How to calculate return on a portfolio
Wild Willie wrote: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
Last edited by FinancialDave on Tue Jan 01, 2013 10:52 pm, edited 1 time in total.
I love simulated data. It turns the impossible into the possible!

 Posts: 1303
 Joined: Thu May 26, 2011 9:36 pm
Re: How to calculate return on a portfolio
letsgobobby wrote: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
I love simulated data. It turns the impossible into the possible!
Re: How to calculate return on a portfolio
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.
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.

 Posts: 8204
 Joined: Fri Sep 18, 2009 1:10 am
Re: How to calculate return on a portfolio
FinancialDave wrote:letsgobobby wrote: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?
Re: How to calculate return on a portfolio
JustinR wrote: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 threefactor and fourfactor terms for you; 107% is not unreasonable.
Simplify the complicated side; don't complify the simplicated side.

 Posts: 1303
 Joined: Thu May 26, 2011 9:36 pm
Re: How to calculate return on a portfolio
letsgobobby wrote:FinancialDave wrote:letsgobobby wrote: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
I love simulated data. It turns the impossible into the possible!

 Posts: 1303
 Joined: Thu May 26, 2011 9:36 pm
Re: How to calculate return on a portfolio
JustinR wrote: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
I love simulated data. It turns the impossible into the possible!
Re: How to calculate return on a portfolio
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%.JustinR wrote: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.
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
Re: How to calculate return on a portfolio
Thanks guys.
Question about XIRR. It's annualized right? Is there a way to unannualize 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.
Question about XIRR. It's annualized right? Is there a way to unannualize 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.
Re: How to calculate return on a portfolio
JustinR wrote:Thanks guys.
My pleasure.
JustinR wrote:Question about XIRR. It's annualized right?
Yes.
JustinR wrote:Is there a way to unannualize 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 fouryear 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).
Simplify the complicated side; don't complify the simplicated side.
Re: How to calculate return on a portfolio
magician wrote:JustinR wrote:Thanks guys.
My pleasure.JustinR wrote:Question about XIRR. It's annualized right?
Yes.JustinR wrote:Is there a way to unannualize 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 fouryear 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.
Last edited by JustinR on Tue Jan 01, 2013 10:57 am, edited 1 time in total.

 Posts: 47
 Joined: Tue Mar 13, 2012 7:28 pm
 Location: Florida
Re: How to calculate return on a portfolio
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!
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!
Re: How to calculate return on a portfolio
Supermercado wrote: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.

 Posts: 47
 Joined: Tue Mar 13, 2012 7:28 pm
 Location: Florida
Re: How to calculate return on a portfolio
sscritic wrote: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%.
Re: How to calculate return on a portfolio
It appears you may not have entered a beginning balance for the first entry at the beginning of the year.
Re: How to calculate return on a portfolio
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.

 Posts: 47
 Joined: Tue Mar 13, 2012 7:28 pm
 Location: Florida
Re: How to calculate return on a portfolio
DR wrote: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!
DR wrote: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!
Re: How to calculate return on a portfolio
Yes, that looks correct now!

 Posts: 47
 Joined: Tue Mar 13, 2012 7:28 pm
 Location: Florida
Re: How to calculate return on a portfolio
DR wrote: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!
Re: How to calculate return on a portfolio
I wonder why Vanguard doesn't have return in percentage form anywhere on the site, other than "1 year". Or am I missing something?
Re: How to calculate return on a portfolio
Supermercado wrote:DR wrote: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 yearI don't think it takes much time. Using that formula, you'll get the annualized returneven 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 returnsnot just annualized returns as you see now. http://www.gummystuff.org/XIRRstuff.htm

 Posts: 47
 Joined: Tue Mar 13, 2012 7:28 pm
 Location: Florida
Re: How to calculate return on a portfolio
DR wrote:Supermercado wrote:DR wrote: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 yearI don't think it takes much time. Using that formula, you'll get the annualized returneven 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 returnsnot just annualized returns as you see now. http://www.gummystuff.org/XIRRstuff.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.

 Posts: 1303
 Joined: Thu May 26, 2011 9:36 pm
Re: How to calculate return on a portfolio
JustinR wrote: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
I love simulated data. It turns the impossible into the possible!
Re: How to calculate return on a portfolio
sscritic wrote:Supermercado wrote: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.
Re: How to calculate return on a portfolio
Supermercado wrote: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:
1224 entries for the total amount of money taken out of each paycheck (monthly or semimonthly) for your retirements accounts.
124 entries for the amount of money put into your Roth (if you put in all 5k at once you will only need one entry)
1224 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.
Re: How to calculate return on a portfolio
2retire wrote:sscritic wrote: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.

 Posts: 47
 Joined: Tue Mar 13, 2012 7:28 pm
 Location: Florida
Re: How to calculate return on a portfolio
2retire wrote:Supermercado wrote: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:
1224 entries for the total amount of money taken out of each paycheck (monthly or semimonthly) for your retirements accounts.
124 entries for the amount of money put into your Roth (if you put in all 5k at once you will only need one entry)
1224 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!
Re: How to calculate return on a portfolio
2retire wrote:sscritic wrote:Supermercado wrote: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
Re: How to calculate return on a portfolio
petrico wrote: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.
Simplify the complicated side; don't complify the simplicated side.
Re: How to calculate return on a portfolio
@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.
I think we can agree that is simple and straight forward.
Now let's consider that you withdrew $500 mid way through the year.
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?
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?
Re: How to calculate return on a portfolio
2retire wrote:@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.
Simplify the complicated side; don't complify the simplicated side.