Investment return calculation - am I doing this right?

[Manguy888]

I have a question about calculating my percentage investment return over time.

I keep a spreadsheet where I put my total investment values every 6 months. I use it for rebalancing and also just to see where we're at. My wife correctly pointed out that these numbers don't tell us how much of a given increase was recent investment 'buys' and how much was returns on existing investments. It's all muddled in there.

I tried my hand at some simple excel formulas to calculate what she wanted, and I reproduced them in the attached excel sheet with some fake data.

My question is: is the return percentage I've calculated correct? Am I doing the math wrong? Missing something?

I'm guessing this is simple for someone with a finance background, but it's a tough problem for me. I appreciate any help!

[skyrefuge]

What you want to use is Excel's XIRR function. Here's how:

http://whitecoatinvestor.com/how-to-calculate-your-return-the-excel-xirr-function/

[Manguy888]

Thanks skyrefuge! This is exactly the kind of thing I thought might exist, but had no idea how to find

[Manguy888]

Looking closer into this, I'm a bit confused about the 'estimated return' parameter of the XIRR function. What I'm dealing with is this:

time 1 = amount of money I had at one point in the past
time 2 = amount I have now (more than time1 due to investing)
variable 1 = amount I put into investments in that time period (the 'buys')

outputs I want:

1. how much of the change between time2 and time1 was my 'buys' versus my investment returns?
2. what's my investment return considering this?

I'm not seeing how XIRR gets me there

[beltim]

I was going to explain, but it turns out the article that sky posted has a great explanation in the comments.  I'm copying here:

Two questions,
Why do you have to “guess” a 10% or 5% return? What is that doing in the formula?
Do you have to put in the date 2012,10,23, or could you put just the cell numbers that the date range is in?
Thanks, really appreciate the info.

    Stan on August 27, 2013 at 8:51 am said:

    The guess is initiating a search. Lots of higher level mathematics have no closed form solution (eg an equation that gives you a definite answer in a finite number of steps) instead they have answers that either involve an infinite amount of steps or can only be approximated. Other answers are too difficult to exactly solve so using an approximate solution requires much fewer calculations than a “true” answer.
    The branch of mathematics that deals with efficient search algorithms is known as numerical analysis. Inputting a reasonable guess will either save machine calculations or allow an actual answer. If your guess is too unreasonable the algorithm may not converge to an answer. With what you are using it for this is unlikely even with a really bad initial guess; however the underlying algorithm is known as “solver” in excel and this is one of the functions that calls solver up. In multidimensional searches etc… the starting guess would be much more important. Hope that helps.

        Brian on August 4, 2014 at 9:46 am said:

        FWIW,

        Regarding the 5% or 10% guess discussion, I used the YTD formula from the author’s row 28 example and played around with the different guess return numbers. I used, 5%, 10%, 100%, 1000%, or used the formula with no guess percentage. For all results, including not entering a guess percentage in the YTD formula, all results were exactly the same.

        Brian

[Manguy888]

<brain explode>

This is exactly what I needed explained - thanks!

[skyrefuge]

Looking closer into this, I'm a bit confused about the 'estimated return' parameter of the XIRR function. What I'm dealing with is this:

time 1 = amount of money I had at one point in the past
time 2 = amount I have now (more than time1 due to investing)
variable 1 = amount I put into investments in that time period (the 'buys')

outputs I want:

1. how much of the change between time2 and time1 was my 'buys' versus my investment returns?
2. what's my investment return considering this?

I'm not seeing how XIRR gets me there

XIRR gets you #2: your total annualized investment return considering BOTH the return of the underlying investments AND your "buys".

It doesn't tell you #1 (what your investment return would have been without your "buys"), but do you actually want to know this? It sounds like your goal is to calculate your total annualized investment return, perhaps as a comparison to other investment options. Your wife pointed out that you were doing it wrong because your "buys" were overstating your rate-of-return. With #1, you're essentially looking for "how wrong was my previous calculation?" Who cares? Just do the calculation with XIRR going forward.

The 'estimated return' parameter is simply a requirement of the formula, because the mathematics behind the formula means that it can arrive at multiple different answers. So you have to give it a guess to start at to make sure it doesn't give you some crazy answer instead. Just put in '0.1' (for 10%) and I think that works in almost all cases. Only ever think about that value again if you ever see it give you a crazy, totally-unexpected result, which it probably won't.

ETA: and for utmost accuracy, your "variable 1 = amount I put into investments in that time period (the 'buys')" should really be a whole set of rows in your spreadsheet, one row with the dollar-amount and date of each investment. If you don't know the dates and amounts any more, you can lump a year's worth of "buys" into a single amount on a single date, but that will lower the accuracy of the calculation.

[Doulos]

I suggest monthly, because monthly contributions to investments seem the norm.  If you contribute on some other rate I would use that as your base, as opposed to 6 months.
To get number figured with simple math you need lots of columns.  You start to use the differences of previous entries to get your return.  It is not exact because you cannot really compare a growing number to previous numbers without choosing some kind of weighting.
For instance,
- do you consider 1% gain in month month and 1% in the next month 2.01%? 
- Or do you give the second month additional weight because you added more money to the capital?
I would consider both 'correct' yet different ways to look at your growth.

Contribution _ Capital _ MonthBase _ Total _ delta% _ Yearly% _ Total%
10                    110        110                121      10%       10%          10%    What you have here is the 1st month, where you have 110 of your money and 11 interest, thus 10%.
10                    120        131                131      0%         10%          9.17%    The capital for the next month is always your money after adding this month's contribution.
10                    130        141                155.1   10%       20%          19.31%    The MonthBase here is the money including interest and contribution before changes this month.  Just add your contribution to last months total.
10                    140        165.1             148.59 -10%     10%          6.14%

You will see that the yearly is not really meaningful until you get 1 year of entries.  I am just summating the deltas until we get 12 deltas. 
- Like I mentioned above, you could look at that differently if you wanted to weight the returns based on each month's relative size.

The total% is always the difference in size of your Capital and the Total.  It does seem to show some skewing just like your previous numbers because of your contributions changing the numbers.

So, that is how I would get the information you are looking for.  Lots of baby steps where you see all the math.  That is what spreadsheets are for.

[Doulos]

I went ahead and made a spreadsheet, using one of my investments.
I also added more columns to be clearer.
Also... my investment is terrible. *crys*

[skyrefuge]

So, that is how I would get the information you are looking for.  Lots of baby steps where you see all the math.  That is what spreadsheets are for.

Why the heck would you do that? Why not just read the other posts in the thread, save yourself a lot of work, and actually get a meaningful number?

XIRR does all the "weighting" automatically and correctly, doesn't require any sort of "base", and requires just two columns and one formula. In your example, it comes up with a value of -10.99%, a number that doesn't appear anywhere in your spreadsheet.

I probably don't even understand what you're trying to accomplish, but in your "Year%" column, you can't just add up 12 monthly return percentages to get a yearly return percentage. Imagine an investment that grows 10% each month for 12 months. After a year it hasn't grown 120%, it's grown 314%.

[Doulos]

I am capable of errors.
Those deltas should be multiplicative?  I could use the 1 based changes 1.1*0.9*1.1, etc.

The XIRR as advertised is not accurate.  Any function that asks for the answer to make a determination is not trustworthy.

The idea of the spreadsheet and baby steps is to show you all the information.  Even if you like the XIRR, it hides all its work.  If someone has all these kinds of questions, you need to show all the work, help them understand it all.

Demystify the process, dont explode heads.

Here is the multiplicative update.  The investment still looks terrible.

[MDM]

Any function that asks for the answer to make a determination is not trustworthy.

Umm...your statement is not correct.

1) See beltim's post for a brief discussion on iterative calculations (aka "root finding").  XIRR does not ask for the answer, it asks for an initial guess.
2) If the iterative calculation does not converge, reporting its "best guess" could indeed be not trustworthy.  XIRR, however, will tell you if it does not converge.  See https://support.office.com/en-nz/article/XIRR-function-c3cdd4d8-359c-482a-a8ba-7a0052f5c053.

Unless I misunderstood your statement...?

[beltim]

Any function that asks for the answer to make a determination is not trustworthy.

Umm...your statement is not correct.

1) See beltim's post for a brief discussion on iterative calculations (aka "root finding").  XIRR does not ask for the answer, it asks for an initial guess.
2) If the iterative calculation does not converge, reporting its "best guess" could indeed be not trustworthy.  XIRR, however, will tell you if it does not converge.  See https://support.office.com/en-nz/article/XIRR-function-c3cdd4d8-359c-482a-a8ba-7a0052f5c053.

Unless I misunderstood your statement...?

For an example of this, consider the following:
After two years holding an investment, the investment has appreciated 21%.  Each year the investment had the same return.  What was the investment return each year?
Mathematically, we can set this up as (1 + x)² = 1.21
There are two possible solutions to this equation: x = -2.1, and x = 0.1.  Most people ignore the first solution because an investment that returns negative 210% each year doesn't make any sense.  But when you're programming a spreadsheet, sometimes you need to give explicit instructions: in this case, only consider solutions where x > -1, because the worst an investment can do is lose 100% of its value.

[Runge]

OP, here's another link that may help explain a bit more.
http://www.bogleheads.org/wiki/Calculating_personal_returns

In addition to this thread...
http://www.bogleheads.org/forum/viewtopic.php?f=10&t=150025

[Manguy888]

Just to follow up, I was able to get my calculation by downloading my transactions from vanguard.

first I sorted the data and deleted my dividends and exchanges so that I only had the buys and the sells. Then I ran an XIRR function as described in the whitecoatinvestor link. One thing they didn't make clear, which I learned, is that you need a number at the beginning of your date value that is your fund's initial value (positive number).

Just having a year of transactions and the final number without the initial value gets you an astronomical ROI. Sadly I did not achieve 2048% last year...

Thanks for the help everyone. I learned a ton through this discussion