[whiteknight]

Hey everyone!

I'm having difficulty figuring what is the best/most accurate way of calculating returns on an investment that features a drip. That this series of payments as an example (assuming the stock is $10 and the price magically never changes):

Jan 1 - Buy 1000 shares for $10,000 (-10000)
Feb 1 - Dividend, $99 total (+99)
Feb 1 - Re-invest $90 (-90) ($9 left in cash)
Mar 1 - Dividend, $99 (+99)
Mar 1 - Re-invest, $90 (-90) ($9 left in cash)
Apr 1 - Dividend, $99 (+99)
Apr 1- Re-invest, $90 (-90) ($9 left in cash)
Apr 15 - Sell all 1027 shares for $10,270

Which is correct?

a. The annual percentage yield of 10.72% (as calculated using XIRR in Excel).

b. ROI/ROR as calculated: (sum of returns - sum of outlays) / (sum of outlays), in this case: (10567 - 10270) / 10270 = 2.89%

c. ROI/ROR as calculated: (final total cash value - initial investment) / (initial investment): (10297 - 10000) / 10000 = 2.97%

d. ROI/ROR as calculated: (final total cash value - initial investment - additional investments) / (initial investment - additional investments): (10297 - 10000 - 270) / 10270 = 0.26%

I'm not sure what the most accurate measure is (barring the time value of money with the APY calculation). On one hand, I only paid $10,000 to purchase the stock (option C), but on the other hand additional shares were purchased (option D). I'm not sure why option B wouldn't be correct.


p.s. I love Excel.

 

[Jungle]

There seems to be a few ways to measure your returns.

Your XIRR is boosted because like you said, it's calculated on a one year return, but you only invested for a few months. So this boosted your XIRR returns.

But really I would think a total return would be more appropriate, due to the short time frame invested. 2.7%?

Also for reinvested dividends in XIRR, you don't have to include them and then subtract them. The answer will be the same as it comes out of your total return.

For XIRR, the only time you should subtract dividends is when they are withdrawn from the account.

 

[whiteknight]

Thanks jungle!

I understand the boosted XIRR returns, thanks.

As for the 2.7% figure, shouldn't I include the $9 + $9 + $9 in cash that was left over from the dividend re-investments? It sounds like option c is what you are leaning toward.

 

[Jungle]

I don't think XIRR is appropriate for measuring any return that is less than one year.

For example, our XIRR for stocks in 2010 was 56.89% -this does not seem right. (kind of like your example)

The reason why is we started stock investing in July 2010, so it's only counting for half the year. XIRR counts it as a whole year.

The total return for 2010 stocks was 9.33%-but XIRR was 56.89%

 

[whiteknight]

Thanks! All clear on XIRR.

As for the ROR calculation—when you refer to the total return for 2010 stocks of 9.33%—at sounds as though one would not include the drip investments (option D); rather, it would be measured on the initial investment only (option C). Correct?

 

[Jungle]

Sorry I forgot to add your dividends in the total return, so you are correct.

 

[Jungle]

Thanks! All clear on XIRR.

As for the ROR calculation—when you refer to the total return for 2010 stocks of 9.33%—at sounds as though one would not include the drip investments (option D); rather, it would be measured on the initial investment only (option C). Correct?

I don't know how to answer this but the way that makes sense to me would to look at it like this:

Starting investment $10,000
Ending investment $10,270 (includes dividends)
Total return 2.7% over XXX months.

 

[whiteknight]

Makes sense. Thanks Jungle!

 

[Dmoney]

Should be simple enough I would think.

End amount ($10,270 + $27 cash)/Beginning amount ($10,000)

2.97% return over the period.

What XIRR is doing in this case is fairly simply, it's just one calculation to annualize it.

If you want to duplicate it here's what you'd have to do:

(End amount/Beginning amount)^(365/(April 15-Jan 1)) - 1

Gives you the 10.71% XIRR or annualized return.

The annualized return just assumes that you can compound your 2.97% return consistantly.

 

[lost in space]

This is of interest to me because knowing how much I made is helpful in deciding whether to sell or not. It's a bit tricky to calculate when you've bought stocks over a period of time plus DRIP. I bought Artis in September and again in January and again in August than DRIPed everything. So what I did take my ACB plus dividends paid and capital gains made and I entered it into
this calculator comes out as a 16% return per year which seems a bit on the high side. But on the other hand I made 3300 on a 6400 dollar investment.

Of course this assumes the stock price comes back and they don't cut the dividend for some reason.

Edit: wondering if I'm doing the math wrong so will try it this way. 6300 invested 420 x 3 capital gains plus 1360 in dividends enter that into the calculator and I get 9% per year return. That seems more realistic

Edit 2 caught my mistake, first time I did it over 3 years not 4