Calculating Return

[birdman2003]

How do you calculate your return?  Here's my example:

I bought $3000 of VGSIX in February of 2014 with dividends set to reinvest.  My current cost is $3225 and current market value is $4132 with a short term gain of $38 and long term gain of $868.

These are ways I know to compare one investment to another:

Easy Calculation:
Annual Personal Return = ((Market Return / What I invested) - 1)/ (# years) = (($4132 / $3000) - 1) / (2 years) = (1.377 - 1) / (2 years) = 18.85% return each year

Excel Goal Seek Calculation:
Annual Personal Return = 17.36% (from solving goal seek where 4132 = (3000)*(1+annual personal return)^2) or in this case square root of (4132/3000) minus 1

Any other methods that folks use?

[Ursus Major]

I use a modified version of the Excel calculation (XIRR = internal rate of return). This Excel function also handles fractional years well. IMHO this is the best way to calculate your annualized return.

[MDM]

Excel Goal Seek Calculation:
Annual Personal Return = 17.36% (from solving goal seek where 4132 = (3000)*(1+annual personal return)^2) or in this case square root of (4132/3000) minus 1

=(4132/3000)^(1/((today - date bought)/365.25))-1

No goal seek needed.  You could use cell references for the 4132 and 3000 in addition to the ones for "today" and "date bought".

The approach above is fine for an up front purchase with reinvested dividends.  For multiple fresh cash infusions and/or withdrawals, the aforementioned XIRR approach is preferable.

[Proud Foot]

I use a modified version of the Excel calculation (XIRR = internal rate of return). This Excel function also handles fractional years well. IMHO this is the best way to calculate your annualized return.

How did you modify the XIRR calculation?  Do you feel this it gives you a more accurate return than the original calculation?

[Ursus Major]

I use a modified version of the Excel calculation (XIRR = internal rate of return). This Excel function also handles fractional years well. IMHO this is the best way to calculate your annualized return.

How did you modify the XIRR calculation?  Do you feel this it gives you a more accurate return than the original calculation?

Well, with "modified" I meant that I wrote my own version of it in Visual Basic. It is slower than the original one (because of VB, but also the original one uses Newton Iteration, which I didn't get to converge in all cases), but it is more flexible for my purposes.

The XIRR function requires a continuous range of cells, which is probably fine for 99% of use cases. If I remember correctly, it also requires that the values are in proper order by date. My function doesn't have the 2nd restriction and it bypasses the first restriction by having an extra parameter to say, if a cell should actually be used. So it kind of works like SUMIF does vs. SUM. It's been well over two years that I last worked on it and I a speaking from memory, so I hope I got all the details correct.

What I planned to use it for is to dump out my investment transactions from Quicken into an XLS and calculate my annualized gain. However it does require more work to combine the results from multiple brokerage accounts.

Bernhard

[Proud Foot]

That makes sense Ursus.  My records are pretty simple but I see how what you did would make it easier to process a lot of data.