How To Calculate Rate of Return Using Excel

640px-microsoft_excel_2013_default_screen

Alright! The mathies out there are giving me a hard time because I’m bragging about my 9.2% return in my TFSA, but they’re pointing at my mistakes of how a $45,500 principal can only be worth $67,000 after seven years of investing. Either I’m pulling numbers out of my ass and lying to everyone, or I’m pushing an agenda that buying stocks are a good when in fact the greedy corporations are stealing my gains. The haters don’t stop coming.

OK to all those math nerds out there that scored 100% on their exams but failed economics in school let’s get one thing straight. The TFSA has a contribution limit of $5500 per year. Back in 2009 the limit was only $5000 and we only had one year where the contribution limit was $10,000 until Justin Trudeau ended the party early. Yeah, he’s not always a cool guy.

Even the CRA spelled it out for us:

The annual TFSA dollar limit for the years 2009, 2010, 2011 and 2012 was $5,000.

The annual TFSA dollar limit for the years 2013 and 2014 was $5,500.

The annual TFSA dollar limit for 2015 is $10,000.

That means I didn’t start with a principal of $45,500 in 2009 so therefore that $19,000 gain is not understating my 9.2% gain. So how in the hell did I get 9.2% as my annual compound gain? Well certainly random numbers don’t drop from the sky for me to type it on this blog. I use a beautiful tool called Microsoft Excel. Hate me Apple fan boys!

The Beautiful XIRR Function

Calculating your rate of return can be difficult if you are adding to your principal over time. Since the money wasn’t added at the same time, it’s not possible to just use a simple compounding interest formula to figure out just how much your investments have been making.

Thankfully, Microsoft Excel provides a neat function called XIRR. What it really stands for is an internal rate of return function based on cash flows that are added and subtracted. The good thing about using the XIRR function is that it can calculate the rate of return using random inflows and outflows of cash. OK, I know you’re quivering in your pants waiting to see how it works (probably not) so here goes:

inflow-outflow

First off notice how I am not adding up the value of my TFSA for each year that goes by. I’m only providing the amount of money that was put into my TFSA account. This is easy to do because the TFSA is capped at how much you can contribute on any given year.I’m an eager beaver, so when January 1st happens to come around my New Year’s resolution is to contribute to my TFSA right away.

You’ll notice the last line actually shows a negative value. That doesn’t mean that I’m losing money, but for the XIRR function to work properly I pretend to be selling all my assets and withdrawing from my account. Since my current value is $67,000, I put the negative amount for today’s date.

xirr

Alright now let’s get to the magic. We use the XIRR to calculate our internal rate of return. The function takes two parameters: An array of values and an array of dates.

=XIRR(array of values, array of dates)

Honestly I don’t type out the range in Excel for my values and dates, I just click and drag with the mouse, but do it any way that you like. Once you have the formula typed out, we just need to hit Enter and do some formatting.

xirr_result

Voila! 9.2% return! The XIRR function takes into consideration the amount of money contributed over the years as opposed to doing a simple compound interest methodology of calculating return.

Don’t Be Fooled By Bank Statements

Ever buy some kind of fixed income asset, just to have the bank declare that you’ve been losing money while holding it? To be honest this could happen a lot.

xirr_sample

If you’ve been investing for a long time and constantly reinvesting dividends there could become a situation where you can be in a capital loss situation. This could happen simply because you keep reinvesting at a lower market price than your original purchase value.

In the example above, I’ve demonstrated how preferred shares purchased at 5% over 5 years might behave. If you bought $5,000 worth of shares at $20 a share, you would have owned 250 shares at the beginning. Over time, the 5% dividend is reinvested into more shares and therefore your value is compounding. Your investment statement might now show that you own 295 shares at $19.50 and that the average price of each share purchased was $19.75. The bank will also declare you have a capital loss. Does this mean you lost money buying preferred shares over 5 years?

Heck no! The mathies should be all over this. If you initially invested $5,000 and the value after 5 years is $5752.50 there is no way you lost money. Who’s the crazy one now?! Even though the bank says you lost $73.75, that’s not true. The bank has antiquated systems that only show capital loss and capital gains based on the average purchase price and the current market value. It doesn’t know about the fancy XIRR function. If we do the calculation ourselves, we can find the real gain over 5 years.

xirr_real_result

Using our magic formula we find that over the 5 years invested, our real rate of return is actually 2.8% compounded yearly. Not quite the 5% that the preferred shares were returning as a dividend, but certainly not losing money either. You can relax now.

Excel Guru

I’m no math genius. Far from that. But I have awesome Excel skills, so you mathies should take heed. Using the XIRR function takes the difficulty away from figuring out how well your investment portfolio has performed. It’s much easier to track your inflows and outflows of cash from your investments to really determine what your annual rate of return actually was.

So the next time your buddy asks you how much you have been making with your investments, don’t pull out bank app to tell him you’ve been losing money. Use this Excel formula to impress your friends (they won’t be impressed because you’re a nerd) and tell them you’ve been kicking ass with your investments!

Advertisements

One thought on “How To Calculate Rate of Return Using Excel

  1. Pingback: Why Gains Can Appear Small In Your Portfolio | Financially Yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s