Saturday, 28 January 2017

CAGR, XIRR simplified

Inspired by SMOL's post, I decided to do some reading and a short post on these "cheem cheem" terms that previously I do not use.

CAGR stands for Compound Annual Growth Rate. It is useful in measuring (in %) how much an investment has increased in value over a fixed period of time.

Watch the illustration in this video here - 

So if your investment grew from $1000 to $1500 over a period of 3 years, the CAGR is 14.5%. Which means the amount increased by 14.5% on its compounded value each year, as follow
Year 1: $1000+14.5%
Year 2: Year 1 compounded $$ + 14.5%
Year 3: Year 2 compounded $$ + 14.5% = $1500

This is provided no fresh fund is injected into the investment (the investment compounds itself) over the three years. So if investment A returns 14.5% and investment B returns 10%, obviously investment A is doing better and probably worth investing more money in going forward.

The problem is... we do not compound the returns back in all our investments. We may choose to take out the dividends and spend them on other things instead of reinvesting in that same portfolio. Therefore the dividends will not contribute to part of the CAGR and using this formula is not accurate or useful. Just a simple comparison of annual return rate is good enough for determining which investment is a better deal.


XIRR is just an excel function. It calculates the Internal Rate of Return for a supplied series of cash flows (i.e. a set of values, which includes an initial investment value and a series of net income values). Unlike the Excel IRR function, the series of cashflows for the XIRR calculation do not necessarily have to be periodic. [Reference:]

What is 'Internal Rate of Return'?

Internal rate of return (IRR) is a metric used in capital budgeting measuring the profitability of potential investments. Internal rate of return is a discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero. [Reference:]

Don't understand what the above means at first glance?

Me neither. But wait...

We need to first understand what a formula or tool does, in order to use it correctly.
If don't understand, then mai serng (don't calculate) lor!

But my spirit of never-give-up prompted me to do some experimentation on excel. And wallah! I found out that XIRR is a function that can calculate the CAGR mentioned :D

Amount($) Date
Initial -1000 01-01-15
Final 1500 31-12-17
XIRR 14.47%

And it can factor in cashflow. E.g. contribution (negative) and withdrawal (positive) amounts from EXTERNAL source throughout the investment period in calculating the annualized return rate.

It can also factor in TIME...

Initial -1000 01-01-15 Initial -1000 01-01-15
Injected -100 01-01-16 Injected -100 01-09-17
Final 1500 31-12-17 Final 1500 31-12-17
XIRR 11.23% XIRR 11.77%

Because time holds value*, $100 injected at a later timing gives you a higher return rate at the end date than $100 injected at an earlier timing even though your final values of investment at 31 Dec 2017 are the same.

*Your $100 not invested can be used to do something else before you invest it here to give you X returns.

The more money you inject throughout the investment period which gives you the same $1500 return, the lower the annualized return rate. Say if at any point in time you injected $500, and your return at Year X is $1500. What would be your XIRR value?

Answer: 0%


Cos you didn't yield anything from the original capital amount throughout the investment period.

Hope this post managed to shed some lights for those who thinks that CAGR and XIRR are too cheem for you. May we all yield high CAGR and Huat ah in the Rooster Year! =)



  1. Rainbow girl,

    A Caramel Sweet Happy CNY to you!

    Good of you to explain with examples the various permutations of calculating Annualised Returns ;)

    The problem with using this XIRR function in Excel is that some people (and some got studied business finance) "conveniently" forget to include these cash injections...

    It has more to do with England comprehension what is a "Portfolio" and what is an Opportunity Fund" than anything else ;)

    Example 1 - Not counting cash in portfolio:
    Start of the year, 1000 consist of 800 in vested stocks and 200 in cash.

    Instead of entering initial as -1000 like what you have done, they enter it as -800. (This is wrong as we should include the whole portfolio - not just the vested part only)

    This 200 cash is invested during the year and now they are part of final 1500 in XIRR.

    Now the returns is a lot more "sexier" ;)

    Example 2 - Not counting cash injections to portfolio:

    The cash sitting in a Portfolio is an Opportunity Fund. This is easy and clear cut.

    But not so clear cut when this cash is spread across savings accounts, fixed deposits, money market funds, etc.

    Only we know how much we have "earmarked" the dollar amount each year to be counted as cash injections for our XIRR calculations. And which month of the year to register them. Wink.

    3 of out 10 Financial bloggers (and I am being polite) will only enter dividends and capital gains as cash injections for their XIRR.

    See? Its not the tool that's "broken" ;)

    1. Hi SMOL,
      Happy CNY and Gong Xi Fa Cai!

      Now can I poke you a bit? =P

      In Example 1, it's ok to enter -$800 as initial investment, just that must remember to include the -$200 when it gets injected later on as part of the investment. If it is an investment portfolio returns that I want to be measuring with XIRR, then my cash need not be 'all in' at the start, since not all my cash are being invested yet.

      In Example 2, if someone enter dividends and capital gains as cash injection to calculate XIRR - that is wrong.
      The injection of money should be from external source. Capital gain from rising investment value e.g. stock price is considered as 'internal'. Dividends NOT reinvested should not be counted as injection.

      If we want to apply XIRR to a portfolio which includes spare cash (opportunity fund), then you are right - we need to "ear mark" it (maybe yearly??). Otherwise, the result would be skewed because our salary (constant cash injection) became part of the investment returns which is not right.

      This tool is damn cheem to use haha!

    2. Hi Uncle8888,

      Now I see how the correct XIRR is derived. Must first 'isolate' the investment capital to minimize error and confusions.

      Stock Investment Portfolio = Current stock value at market closing price + Investible cash available = Capital + Realized P/L + UnRealized P/L

      Thank you so much. Happy CNY, huat ah!

    3. Rainbow coin,

      1. Cannot use XIRR together with Portfolio in the same sentence and then say you leave out cash since you only measuring one specific stock ;)

      If you want to measure a particular stock investment performance, then a plain vanilla Return On Investment (ROI) will suffice. Why do we need to call it XIRR when we already have ROI?

      See? XIRR is not standard investment term like ROI and CARG ;)

      XIRR is Singlish to mean CARG/ROI/IRR/Annual Return :)

      2) LOL! I see you don't quite understand the concept of an Total Investment Portfolio too ;)

      Dividends not invested do not count? Then you would say realised profits in cash taken during the year but not re-invested do not count also...

      3) I think its easier to grasp the concept of a Total Investment Portfolio if you imagine yourself to be a Mutual Fund Manager ;)

      Cash from dividends and realised profits/losses taken are still part of the portfolio whether invested or not. We measure the total Asset Under Management (AUM). Remember, you are charging annual fees based on the total AUM, not just the invested parts only ;)

      If you have earmarked $1000 per month from your salary to go into your Opportunity Fund, these are like new investors putting new funds into your Mutual Fund. Don't count for measuring performance returns? What do you think if you are the retail investor comparing which funds to invest?

      That's why I say only we know how much our regular cash injections "skew" our investment returns.

      Try this exercise:

      If we can earmark $50K per year for investments:

      Year 1 = $50K
      Year 2 = $100K (I doubled my portfolio!)
      Year 3 = $150K (I increased my portfolio by 50%)

      All this without a single act of investing ;)

      Just by pure cash injections every year.


      Who says we need to invest to be financially free?

      The secret is to Earn More and Save More ;)

    4. Hi SMOL,

      1) Yeah you are right! We can just use ROI so save the trouble of using XIRR to calculate the returns if it is single stocks and ROI can be easily found online.

      2) If dividend is taken and spent, then how do we fit it into the XIRR calculation?

      I think must add to the final portfolio value e.g. part of the $1500 (if using my table examples above)? Pardon my bad maths.

      3) That's why I say "The more money you inject throughout the investment period which gives you the same $1500 return, the lower the annualized return rate."
      Cash injections are not equivalent to returns leh. If you talk about savings then the real return is the interest rate only lor. ;)

    5. Rainbow girl,

      If you decide to spend the dividends or realised gains as opposed to keeping it as part of your Opportunity Fund, then treat these "spending" as cash withdrawals from your Portfolio - you do the reverse of cash injections in the XIRR function ;)

      XIRR is just a tool :)

      ROI, IRR, and CARG are proper England and Business Finance terminologies.

      When people say their portfolio has "grown" by 50%, old bird will trust but verify to see if its cash injections or investment returns ;)

      Bei kambing will just be in awe and assume wrongly.


  2. Replying to SMOL comment, I dont think it is about conveniently excluding the cash function. It is just about measuring certain returns from an investment that cash is being utilized from. Perhaps the understanding of the whole point of internal rate of return is wrong. But surely enforcing others to include cash is also a form of enforce slavery from your part that you dont understand what each bloggers are measuring upon.

    1. B,


      I'm not me "enforcing" anything ;)

      Like I've said, its more a case of England comprehension than anything else.

      Annualised return is saying "Pink" - layman can understand.

      CARG is like saying "Magenta" - serious amateur or semi-pro.

      XIRR is using a specific Pantone colour code - illusion of "precision" level. But no mutual or hedge fund use it? LOL!

      Sorry to disappoint you.

      If we are measuring at Portfolio level, when using XIRR, its given that we include investments in all other asset classes like gold and bonds, etc; as well - not only cash ;)

      If using XIRR for specific vehicles only, maybe it would be good to avoid using XIRR and Portfolio in the same sentence or blog post ;)

      I am merely showing what's Standard England. If you want to have your own patois Singlish variant, whatever makes you happy!

    2. Hi B,
      Thanks for dropping by my blog with a thought-provoking comment. Start the poking rolling yeah!

    3. Hahaha, no bad feeling.

      We are just messing around ;)

      No wonder la i failed to comprehend. Pardon my poor England :)


Related Posts Plugin for WordPress, Blogger...