What is IRR and XIRR and how to Calculate it

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 5,000 , 10,000 , 6,000 , 4,000 and 6,500 in 5 yrs and Get 53,000 at the end of 5 yrs , what is your Return ? Its 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 , Its used to calculate the returns given some amount at a fixed interval . For example On 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 ?

• Enter your Investments (amount which you paid) in each row (you have to put “-” before each value)
• Enter the Amount you Received at the end (put “+” after that amount)
• Formula : =IRR(values) , in place you values , put the range of cells which contains values) , see below .

[ad#big-banner]
Use this Spreadsheet to calculate IRR for yourself

Things to NOTE

• The values need to be a set of Positive and Negative Values .
• The last value is the amount you received .
• Any amount Invested will be Negative , so if you invest Rs 10,000 , put -10000
• Any amount you Receive , will be Positive , you if you get Rs 5,000 , put +5000
• All the payment or receiving of money are equidistant , Like 1st of every month OR May 15th Every year .
• All the payments are assumed to be yearly by default , If its some other time frame like monthly or quarterly , use XIRR and put specific dates .

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

• Put Date and Value for each row
• At the last row , put the Date and amount you received .
• Put the formula as : =XIRR(values , dates) , values and dates are the cell ranges , see below .

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 .

Scenario 1

Suppose you Invest in a Mutual Funds per month on your own , you invest on 15th of every month in year 2006

• June 15 you invested 5000
• July 15 you invested 6000
• Aug 15 you invested 3000
• Sep 15 you receive 5000 (dividend)
• Oct 15 you invested 4000
• Nov 15 you invested 12000
• Dec 15 you Sell everything and Receive 35000

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]

Scenario 2

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 ?

Liked the post , Subscribe to Get Posts in Email or RSS Reader

Tagged as: basics, calculations, how-to-guide, irr