POSTED BY August 10, 2009 COMMENTS (119)ON
How do you calculate your returns when you every year you invest different amount and at the end you receive your Money back? Suppose your invest 5000, 10000, 6000, 4000 and 6500 in 5 yrs and Get 53,000 at the end of 5 yrs then what is your Return? It’s 17.4%. The concept is called IRR. Read below to understand more:
So Here we will learn two things IRR and XIRR
What is IRR and How to Calculate it?
IRR is Internal Rate of Return and it is used to calculate the returns given some amount at a fixed interval i.e. after every 3 months or after every 1 yr. The only thing which matters is that there should be equal distance between two installments. We will learn how to Calculate IRR in Excel Sheet. You would also love to read what is NPV ( Net Present Value) .
How to calculate?
[ad#big-banner] Use this Spreadsheet to calculate IRR for yourself
Things to NOTE
In the above example, the CAGR return was 17%. See this video post to understand how to calculate CAGR .
What is XIRR and How to Calculate it?
IRR does not solve one problem and that is when the payments are at Irregular interval. In that case we use XIRR. So in a Spreadsheet we put the date and the value both. See the example below:
How to Calculate
Use this Spreadsheet to calculate XIRR for yourself
In the above example the CAGR Return was 38.96% (I have multiplied the return by 100 the actual value will be .3896)
Real Life scenario when you can use it
Suppose you Invest in a Mutual Funds per month on your own , you invest on 15th of every month in year 2006
You can use IRR in this case and calculate your returns , the values you will be -5000 , -6000 , -3000 , +5000 , -4000 , +12000 , Calculate the IRR and put it as comments , lets see if you are correct or not ?[ad#text-banner]
You can also compare two business ideas using the XIRR , and decide which one is better then other . In any business concept you have to invest money and you get back some return , but these returns can be irregular and different amount every time , In that case you can use XIRR and compare the returns of both business and decide the one which has better XIRR
Note : the formula can give answers in a but different ways on Excel , OpenOffice spreadsheet , google docs or Zoho Spread sheet . Use this Spreadsheet to calculate IRR and XIRR for yourself . The spreadsheet is shared , so please dont make any changes other than “values” and “dates” .
Comments ? I would love to hear if these concepts are of use to you or can be of any help to you . is IRR a good way of measuring returns ?