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.
rustymutt wrote:Return=(ending balance - starting balance - contributions) / (starting balance + contributions)
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.
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
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.
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.
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.
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.
Time weighted performance estimation based upon an assumption that all cash flows occur midway through the period of evaluation.
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.
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?
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.
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
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.
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?
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.
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
JustinR wrote:Thanks guys.
JustinR wrote:Question about XIRR. It's annualized right?
JustinR wrote: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 wrote:JustinR wrote:Thanks guys.
My pleasure.JustinR wrote:Question about XIRR. It's annualized right?
Yes.JustinR wrote: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).
=(1.0748+1)^(1/(1461/365.25)) - 1 = 0.200173573 = 20.02%
=GEOMEAN(1.2,1.3,0.95,1.4) - 1 = 0.200173573 = 20.02%
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.
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.
DR wrote:It appears you may not have entered a beginning balance for the first entry at the beginning of the year.
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.
DR wrote:Yes, that looks correct now!
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!
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 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
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?
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.
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!
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.
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:
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.
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.
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
Code: Select all
01/01/2012 $1000
12/31/2012 -$1100
XIRR = 10%
Code: Select all
01/01/2012 $1000
07/01/2012 -$500
12/31/2012 -$1100
XIRR = 76.47%
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?
Return to “Investing - Theory, News & General”
Users browsing this forum: Baidu [Spider], ehec, ScarletIris, SnowSkier and 59 guests