Sunday, 29 January 2017

Sequel to CAGR, XIRR simplified

A sequel to my previous post.

Can I make use of XIRR's value to help in my investment portfolio goal setting?

Think come, think go... cannot leh. :(


Because XIRR % is like a report card grade which shows how my portfolio has performed over a fixed period of time, say last year. However, it is not practical to set a goal based on the % or confer a meaning to it as the current market value of my portfolio is controlled by Mr Market (although you may argue investment timing matters *cough cough*). So on sunny days, XIRR is good and on rainy days, XIRR is bad.

Notwithstanding the fact that data punching is going to be tedious for the value to be accurate. Diligence... hmm...

Before I put XIRR completely out of picture...

XIRR calculation might be meaningful IF you want to compare some 'investment products' like structured deposit, RSP, Investment-linked insurance or unit trust to see how well their returns fair against stock, ETF or simply inflation within a fixed time period. (I should do that for my POSB Home Balance Fund!)

Another use for it - if you have been actively injecting cash into Investment A and did not inject / inject cash at different intervals for Investment B, you could use their XIRR figures to give you an objective idea of how your Investment A's annualized return compares with Investment B's.

You could also use XIRR to compare your overall portfolio performance last year versus this year. So you can chart a Y-O-Y XIRR hehe!

In my conclusion, the XIRR figure is not very useful on its own but is good to use as a comparison data. Do let me know if you have a different opinion. :)


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! =)

Related Posts Plugin for WordPress, Blogger...