# Calculating Stock Return when Depositing Funds



## KaeJS (Sep 28, 2010)

Hi everyone,

Just want to make sure I'm doing this correctly.

Suppose it's January 1, 2012 and a portfolio is starting the year at $50,000.

If, on March 1, 2012, the portfolio owner were to deposit $3,000 into the account, how you would calculate the annual return, assuming the year end value is $60,000 on December 31, 2012.

Would you take the invested amount on March 1, divide it by 365 days in a year, then multiply it by the number of days left in the year like this:

$3,000/365 x 305 (number of days until December 31) = $2,506.85

Then add $2,506.85 + $50,000

= $52,506.85

$60,000 - $52,506.85 

= $7,493.15

$7,493.15/$52,506.85

*= 14.27% Return??* 

I need to make sure I'm doing this right, because right now my spreadsheet is a little off with the YTD % Gain.

If you don't adjust for this and assume the $3,000 was added on January 1, 2012, then it would be:

$7,000/$53,000

= 13.20%

Which means if you are not adjusting, all of your percentage gains are actually greater than they appear, as you didn't have all funds available on January 1.

Any help is appreciated. I tried to make this as simple to understand as I could.

I think there was a thread on this a few months back, but I couldn't seem to find it. I even tried searching google, but all I kept getting was reinvestment of dividends links.


----------



## Argonaut (Dec 7, 2010)

I usually keep it simple.

Portfolio Value - Money Invested = Profit
Profit / Money Invested = Return %

Don't matter what day you did what.


----------



## GoldStone (Mar 6, 2011)

Use XIRR function in Excel. See gummy's tutorial:

http://www.gummy-stuff.org/XIRR-stuff.htm

It's easier than it looks at the first glance.


----------



## slacker (Mar 8, 2010)

+1 on XIRR()


----------



## GoldStone (Mar 6, 2011)

BTW, it's not just Excel. XIRR is also available in OpenOffice.


----------



## KaeJS (Sep 28, 2010)

Been trying to do XIRR in Exel for an hour.

It's giving me a SMALLER percentage.

How is that even possible?

When I input the values of

$10,000
$(11,000)

It works and gives me 0.10000000 (10% gain) as it should.

As soon as I throw dates in there and input my own numbers, it gives me a 6.7% annualized return? That's not even possible. 

I must be doing something wrong. I just can't figure out what it is that I'm doing incorrectly.

I don't even care about the quarterly, I just want the annual. I want to assume that the value of my portfolio today is the value that it will be on December 31, 2012. That's how I want to calculate it.


----------



## gibor365 (Apr 1, 2011)

Argonaut said:


> I usually keep it simple.
> 
> Portfolio Value - Money Invested = Profit
> Profit / Money Invested = Return %
> ...


I'm doing exactly the same


----------



## KaeJS (Sep 28, 2010)

Argonaut said:


> I usually keep it simple.
> 
> Portfolio Value - Money Invested = Profit
> Profit / Money Invested = Return %
> ...


How is this accurate?

Essentially, you are saying that everything you contributed was done on January 1. Am I wrong?


----------



## gibor365 (Apr 1, 2011)

KaeJS said:


> How is this accurate?
> 
> Essentially, you are saying that everything you contributed was done on January 1. Am I wrong?


we calculating return on investments...just assuming that other 5K you invested, from Jan 1 until March 1 was in cash....


----------



## KaeJS (Sep 28, 2010)

Here is a picture of what I am doing.










$16,000 is my starting amount

Then I contributed $750 on Jan 9,
Then I contributed $250 on Feb 2,
Then I contributed $750 on Feb 21,

And as it stands right now, my portfolio is worth $18,941.

So how in the hell is that only a 6.7% return?


----------



## GoldStone (Mar 6, 2011)

What's your XIRR formula?


----------



## KaeJS (Sep 28, 2010)

goldstone said:


> what's your xirr formula?


=(1+xirr(d100:d104,e100:e104)-1)


----------



## phrenk (Mar 14, 2011)

Your signs (+/-) are backwards. When doing a cash contribution, it's a negative cash flow for you, and when obtaining a cash distribution, it's a positive cash flow for you.

Try doing the same thing but with inverse signs... it gives me an XIRR of 11.45%.

1/1/2012 -16000
1/9/2012 -750
2/2/2012 -250
12/31/2012 18941.38


----------



## slacker (Mar 8, 2010)

My understanding is that signs don't matter.

I yielded 11.45% either way.


----------



## KaeJS (Sep 28, 2010)

My formula must be wrong.

I inverted the +/- signs, and I got an XIRR of 6.9%.

My formula has to be the problem


----------



## Four Pillars (Apr 5, 2009)

KaeJS said:


> Here is a picture of what I am doing.
> 
> 
> 
> ...


Why do you think 6.7% is wrong? You've contributed $17,750 to the account - if you pretend that was all done on Jan 1, your return would be ($18,941 - $17,750)/$17,750 which is 6.7%. [edit - correct my error]

I'm not 100% sure how XIRR works, but I think that moving some of the contributions further out in the year, reduces the time weight for those contributions and changes your overall return a bit. 

I can't verify if 6.7% is accurate, but it seems reasonable to me.


----------



## CanadianCapitalist (Mar 31, 2009)

KaeJS said:


> My formula must be wrong.
> 
> I inverted the +/- signs, and I got an XIRR of 6.9%.


It seems correct to me. Why do you think it is wrong?

If you want to annualize your returns so far, then you should put in today's date in the last row. If you do that, your annualized return is 53%. Congratulations!


----------



## Ethan (Aug 8, 2010)

KaeJS said:


> =(1+xirr(d100:d104,e100:e104)-1)


The purpose of XIRR is to annualize your gains. The formula essentially tells you what rate your investment stream is appreciating at on annualized basis. By setting the value in cell E104 to 12/31/2012, you are telling the formula have earned those returns between the contribution dates and December 31 of this year, whereas in reality you earned those returns in a much shorter period. Try punching this formula into cell E104

=today()

This formula will always update to todays date based on the date on your computers clock. 

Given the values you have shown, my XIRR returns 53.75%, which is impressive.

Edit: Oops, I see Canadian Capitalist previously stated what I did, in less words.


----------



## Four Pillars (Apr 5, 2009)

For the record, I use the method of subtracting any contributions for the year from the capital change and divide that by the starting position.

http://www.moneysmartsblog.com/investment-performance-for-2007/

For example if I start with $100k, contribute $8k thru the year and end up with $112k. My "return" is ($112k-$100k-$8k)/$100k = 4%.

As Kae points out this is not perfectly accurate.

For anyone who's annual contributions are a small percent of their portfolio value, this method is reasonably accurate. Ie if your contributions are 5% of your portfolio, then any error from not noting the correct timing of the contributions won't be very significant.

If you are just starting out however, this approximation won't be very accurate.

However, as Kae's example has shown - XIRR isn't what I consider to be accurate for short term performance measuring either. The reality is that there is no 100% perfect way to measure performance of mid-year contributions.


----------



## hboy43 (May 10, 2009)

CanadianCapitalist said:


> It seems correct to me. Why do you think it is wrong?


Now you have two trained as engineers saying the result looks reasonable. If you invested the entire amount of 17750 on Jan 1 and had 18941 on Dec 31, that would be 6.7% (not 7.4% as someone above calculated). Now given that some of the funds were added a few months later, that is going to boost the returns a bit, so all in all, I'd say 6.9% is about right.

One of the most interesting things about engineering and mathematics is how much of it is based on guess and estimation. We don't spend much time getting things exactly right, but are rather interested in not being grossly wrong. Nobody cares if the engineer shaves 100 board feet of lumber from the roof trusses, but they sure are interested if the roof collapses.

What value does it add to your investing to know your return is 6.9% as opposed to estimating it to be 6.7% and then rounding up to 7%? This isn't really directed at the OP, but rather the likely dozens viewing this that use IRR. What will or would you change in your investing philosophy or holdings based upon the extra precision? Are you spending time being exactly right that would be better spent avoiding being grossly wrong with asset allocation, size of house you own, or whatever other big important long term things?

Bringing IRR to investment returns is like killing a fly with a hammer. Many people do it, just see all the folks reporting their annual returns every January using IRR calculations. I humbly submit that it is not a productive exercise. Many would have 100-200 entries, with dividends, savings from employment, dozens of trades a year etc. Serious work with no real value, and with a hundred entries, almost certainly wrong too.

hboy43


----------



## Toronto.gal (Jan 8, 2010)

Four Pillars said:


> For example if I start with $100k, contribute $8k thru the year and end up with $112k. My "return" is ($112k-$100k-$8k)/$100k = 4%.


Not perfectly accurate is right due to the spread during the year, but I think more accurate than your calculation would be *3.703703704%.*  [$4K: $108K x 100].

I agree with Argo, just keep it simple KaeJS!


----------



## Four Pillars (Apr 5, 2009)

hboy43 said:


> If you invested the entire amount of 17750 on Jan 1 and had 18941 on Dec 31, that would be 6.7% (not 7.4% as someone above calculated).


Haha - oops. I was close.


----------



## Argonaut (Dec 7, 2010)

Annualized returns is a bit of an artificial number. What matters is the actual return on your investments. 

The easiest one I find to calculate is the TFSA, because I know exactly how much has been contributed and how much it sits at now. Contributing on January 1st "decreased" my returns percentage-wise, but I'm not too worried about that.


----------



## HaroldCrump (Jun 10, 2009)

Argonaut said:


> Annualized returns is a bit of an artificial number. What matters is the actual return on your investments.


Yes and no, I think both annualized return as well as gross return are meaningful in different contexts.

If you pull a KaeJS and make $20 in 5 mins. of trading, the annualized return % is astronimical.
But in reality, you barely made enough for a decent lunch money.

On the other hand, you can make $5K (for example) by investing $100K for several years, but the annualized return is below that of a bank GIC.


----------



## Four Pillars (Apr 5, 2009)

HaroldCrump said:


> If you pull a KaeJS and make $20 in 5 mins. of trading, the annualized return % is astronimical.
> But in reality, you barely made enough for a decent lunch money.


Where do you eat lunch? $20 buys me a mighty fine lunch.


----------



## Argonaut (Dec 7, 2010)

I agree, Harold. Which is why I think the benchmark is 7.2% annually for an investment/portfolio, or a double every ten years.


----------



## HaroldCrump (Jun 10, 2009)

Four Pillars said:


> Where do you eat lunch? $20 buys me a mighty fine lunch.


If you eat alone, then yes


----------



## HaroldCrump (Jun 10, 2009)

BTW, I also get 6.77% annualized returns as on 12/31/2012 for the same numbers and dates.
And as of today, I get 52.58%.


----------



## Ethan (Aug 8, 2010)

Annualized returns can be a bit of a misnomer over the short-term, but I think its relevant over longer time periods. Returns on pension funds and mutual funds are quoted on an annualized basis, not to mention dividend yield, bond yield, GIC's, commercial paper, money market instruments etc. are all quoted on an annualized basis. Using XIRR quickly calculates what can be a rather large calculation (if you have multiple deposits/withdrawals in a year) and takes into account the time value of your money.

Absolute returns are not as relevant over the long term because your returns are not readily comparable to either benchmark indices or the CPI.

This is pretty standard practice in the investment industry. I use XIRR because I compare my returns to various benchmarks, as well as calculate my alpha relative to the S&P/TSX composite index. If my alpha were to turn negative I'd look into a couch potato portfolio, but this is far from the case for me.


----------



## Ethan (Aug 8, 2010)

KaeJS said:


> =(1+xirr(d100:d104,e100:e104)-1)


If you want to calculate alpha, or how you are doing relative to the a benchmark index (in this case the TSX) do the following:

In cell d98, enter today's closing value for the TSX.

In cells f100:f103, enter the closing value for the TSX on those contribution dates. Note that if your contributions are positive balances, the TSX values will need to be negative.

In cell g100, enter the following formula:

=$d$98/f100*d100

Drag the formula down to cell g103. This calculates what those dollar amounts, invested directly in the TSX, would have appreciated/depreciated to today based on the change in the value of the TSX.

In cell d104, enter the formula:

=sum(g100:g103)

Also ensure cell e104 is

=today()

Calculate XIRR in a new cell with the formula

=xirr(D100104,E100:E104)

This is the TSX annualized return over that time period based on your contribution dates and amounts. Subtract this amount from your previously calculated XIRR of 53.75% to see what your Alpha is, in other words by how many points you are outperforming the TSX on an annualized basis.


----------



## HaroldCrump (Jun 10, 2009)

Ethan said:


> NOTE - the above smiley face should be colon D, no spaces. This forum converts that to an emoticon, I don't know how to prevent that.


Uncheck the box that says _Disable smilies in text_.
Thus:


----------



## Ethan (Aug 8, 2010)

HaroldCrump said:


> Uncheck the box that says _Disable smilies in text_.
> Thus:


Thanks! That's been bugging me for a while. My post has been fixed.


----------



## Jungle (Feb 17, 2010)

FYI you can use XIRR in google spreadsheets, it works much better than Excel did for me. Google spreadsheets was able to calculate a 255 line XIRR from seven accounts! 

XIRR is also an industry standard, when there are irregular cash flows and investment returns to be measured over a 365 day period. 


(PS Excel kept giving me errors, even after some add on pack was installed.)


----------



## KaeJS (Sep 28, 2010)

*Okay! I've got it. It only took forever for me to figure it out..*

First of all, I want thank everyone in this thread.

You have been a tremendous help! 

I figured out my own stupidity. I went through all of my Questrade records since I opened the account to find out how much money (book value) I've sunk into Questrade. It turns out, I was actually accounting for $500 more than I initially put it. Then, I went back and re-traced my gains on my 2011 spreadsheet, matched everything up with my 2012 spreadsheet, and here's the big issue:

I started the year with *$15,645* on January 1, 2012.

Then, I contributed:
$750 on January 9
$250 on February 2
$750 on February 21

This brings me to $17,395.

Then, I add in my gains for the year of $1,728.38 and my total is:

$19,123.38.

And my XIRR is no longer showing a 6-7% yield, but a 10% yield, like it should. My spreadsheet is showing a return of 9.94%, but my annualized return must be higher than 9.94%, as this 9.94% is calculated as if I contributed all my 2012 contributions on January 1, 2012.

Here it is folks:










*Woohoo!*



Ethan said:


> If you want to calculate alpha, or how you are doing relative to the a benchmark index (in this case the TSX) do the following:


Ethan, I will implement this either tonight, or in the future. However, I may only do this on an annual basis, as it would be too much to consistently update. I appreciate all of your help. Thank you for the suggestion/instructions!


----------



## gibor365 (Apr 1, 2011)

KaeJS said:


> First of all, I want thank everyone in this thread.
> 
> You have been a tremendous help!
> 
> ...


----------



## KaeJS (Sep 28, 2010)

gibor said:


> and what formula did you use in cell where you got this long number 0.10022.....?


Same formula. I didn't touch it.

=(1+xirr(d100:d104,e100:e104)-1)

There was never anything wrong with the formula. There was something wrong with the numbers I was using in my spreadsheet, which made the percentage gain smaller than it should have been.


----------



## gibor365 (Apr 1, 2011)

deleted


----------



## GoldStone (Mar 6, 2011)

hboy43 said:


> Bringing IRR to investment returns is like killing a fly with a hammer. Many people do it, just see all the folks reporting their annual returns every January using IRR calculations. I humbly submit that it is not a productive exercise. Many would have 100-200 entries, with dividends, savings from employment, dozens of trades a year etc. Serious work with no real value, and with a hundred entries, almost certainly wrong too.


Keeping track of XIRR doesn't have to be complicated.

I have a simple spreadsheet that I created in 1998, the first year I started investing. Every time we add money to one of our retirement accounts, I add a new row in the spreadsheet. Two cells: date and amount. That's it. Takes seconds to do.

At the end of each year, I add a row with portfolio total and XIRR calculation. Takes no effort at all.


----------



## Jungle (Feb 17, 2010)

That's a great way to do it. Saves you a lot of time instead of putting everything in later.


----------



## hboy43 (May 10, 2009)

GoldStone said:


> Keeping track of XIRR doesn't have to be complicated.
> 
> I have a simple spreadsheet that I created in 1998, the first year I started investing. Every time we add money to one of our retirement accounts, I add a new row in the spreadsheet. Two cells: date and amount. That's it. Takes seconds to do.
> 
> At the end of each year, I add a row with portfolio total and XIRR calculation. Takes no effort at all.


Fine, you have reduced your task to an approximate IRR (that is not bothering with dividends and the like) which is fair enough.

Still, does anyone have an answer to my question as to what is the value in knowing your return is EXACTLY 6.7% as opposed to approximately 7%. What are you going to do differently with your portfolio given the extra precision?

The only purpose I can identify is that investor 1 with 6.7% can say he did better than investor 2 at 6.6%, that is ego and bragging rights.

Am I wrong? Please identify the utility of the exercise, I just can't find it.

hboy43


----------



## Sampson (Apr 3, 2009)

hboy,

your argument seems to be that a few basis points does not matter. So when comparing your return vs. alternative strategies, a 6.7% return (KaeJ's) compared to the index (lets say it was 7%), that this difference is meaningless.

Given all-but equal products, would you invest in an ETF with a 0.5% or 0.2% MER? Given all-thinfs-equal, would you use a brokerage that charges you $9.99 per transaction? or on that is $29/transaction?

It makes no difference when doing the calculation (XIRR function in excel), but rounding and level of precision do make a difference if you are comparing multiple strategies.


----------



## GoldStone (Mar 6, 2011)

hboy43 said:


> Fine, you have reduced your task to an approximate IRR (that is not bothering with dividends and the like) which is fair enough.


hboy43,

XIRR calculation treats the portfolio as a *black box*. All it cares about is this:

1. portfolio value at the start of the year (or whatever time period you are tracking.
2. fund inflows / outflows
3. portfolio value at the end of the year

I get the exact XIRR, not an approximate one.

Money movements *inside* the portfolio don't matter. Dividends, interest payments, etc are not used in the XIRR calculation. I don't have to keep track of them.

As to the value of this exercise... I will leave it for another post.


----------



## Ethan (Aug 8, 2010)

hboy43 said:


> Fine, you have reduced your task to an approximate IRR (that is not bothering with dividends and the like) which is fair enough.


How is XIRR not exact? The timing of the dividends within the portfolio does not affect your return. Take for instance two portfolios, A and B, each with $10,000 on January 1, and no contributions or appreciation during the year.

Portfolio A receives a $1,000 dividend on March 1, and at year end has a value of $11,000. The portfolio increased 10% during the year.

Portfolio B receives a $1,000 dividend on September 1, and at year end has a value of $11,000. The portfolio increased 10% during the year.

The two portfolios have the same beginning and ending values, and received the same dividend in dollar terms, only on different dates. It doesn't change that over that 1 year period the portfolio increased 10%.

Receiving the dividend earlier exposes an investor to reinvestment risk. Those funds can be deployed elsewhere which will either increase or decrease the returns, however the additional return/loss on the reinvested amount is included in the year end value of the portfolio and therefore captured by the XIRR calculation.


----------



## hboy43 (May 10, 2009)

Hi:

The two posters above are exactly right with the "black box" bit. I came to that conclusion myself, something just wasn't quite right in my head, and I muddled through this afternoon. I tend to extract my dividends, so they would be part of my personal calculation (if I did one), whereas most just leave them be inside the "black box".

So I concede that the IRR work would be minimal for most, some 1 or 2 dozen entries a year.

Sorry for the moment of denseness on my part, and thanks for taking the time to correct my thinking.

Cheers

hboy43


----------



## slacker (Mar 8, 2010)

@hboy43:

It's a easy problem to solve.

Those who find value in using XIRR should use it.

Those who don't shouldn't.

You seem to take exception to spending effort on getting useless precision, and I respect that. But we already established that the effort is fairly minimal.

The way I use XIRR is indeed to compare to certain benchmarks, and to plug into various programs and scenarios to figure whether I'm on track. And I do not derive any sort of perverted pleasure from having a high precision calculation of my return on investment. In fact, I round it to the closest integer value. (e.g. my ROI is displayed as 6%, instead of 6.31272727272727272727272727272727%) Excessive precision is indeed useless, but incorrect calculation of ROI can (overtime) lead you astray.

For example, I have chosen a couch potato passive portfolio. My theory is that it ought to do better than an actively managed balanced fund. I benchmark my portfolio against Mawer Canadian fund (a pretty good quality fund, IMO). So far I'm beating that fund, but not by much. If Mawer Canadian fund eventually consistently beat my couch potato portfolio, then it shows that my original assumption is incorrect, and I should consider a course correction.

Another example, is that I plug my ROI into various programs and calculations to produce a projection to see if I'm on track. Why would I want to do that? Am I deathly afraid that I don't save enough? Absolutely not. In fact, I'm deathly afraid that I'm saving too much, and not enjoying my wealth while I'm relatively young. The solution is to find out how much I should spend, and I much I should save. Knowing the correct ROI is one part of that calculation, and I get value out of it.

I will not use the formula that other posters have mentioned, not because it lacks precision, but because it is incorrect, and the error will actually increase dramatically overtime, to the point where indeed there would be no point in calculating it.

Once again, I respect those who do not see the value in it, but this is my piece about how I'm getting value out of it. And it is not because I have a fetish with numbers and math. (I do like math though)

But please distinguish between precision, and correctness:

http://en.wikipedia.org/wiki/Accuracy_and_precision


----------



## hboy43 (May 10, 2009)

slacker;117050In fact said:


> Yes! Now we have (hopefully) everyone heading in the right direction. I still think even quoting a single year return to a single digit is optimistic (ie on one year you don't have a standard deviation, and even two years worth of numbers the standard deviation is likely 3 or 5 percentage points), but at least you understand the limits of what you are trying to do. And maybe you are using 10 years worth of annual return numbers to give the exercise a modest level of statistical validity.
> 
> I worry for those who don't have the mathematical concepts and are basing decisions on information that just isn't there. Maybe the first investing course one takes should be the first year physics course.
> 
> ...


----------

