## 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 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?

- 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)( place your 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 receive
- Any amount Invested will be Negative so if you invest Rs 10000, put -10000
- Any amount you Receive will be Positive so if you get Rs 5000, 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

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

Hi Manish,

I still dont get this IRR / XIRR calculation logic at all.

Take my investment example: I have a SIP of Rs.3000/month from 1st June 2015 to 1st June 2016 amounting to total investment of 39,000 always done on the first working day of every month in a MF.

Now the current value of this investment on 1st June 2016 is 40,498. Which is (40,498-30,000)/30,000 * 100 = 3.84 % – Absolute return. So 39,000 outflow of cash from me over a period of 13 stand at 40,498.

But when i use XIRR for this scenario, i get the value as 15.61% where i was forced to provide a ‘guess’ in excel sheet formula and i gave it as 0.1.

Could you please explain what exactly is the return on my investment ?

Hi Sundar

I am not sure how you calculated it. I used excel sheet on my end to calculate and I got 7.75% answer

01-06-2015 -3000 7.75%

01-07-2015 -3000

01-08-2015 -3000

01-09-2015 -3000

01-10-2015 -3000

01-11-2015 -3000

01-12-2015 -3000

01-01-2016 -3000

01-02-2016 -3000

01-03-2016 -3000

01-04-2016 -3000

01-05-2016 -3000

01-06-2016 -3000

01-06-2016 40,498

Note that the 3.84% return answer is surely wrong, because that would be the answer if you had invested all 39000 at one go in start and if you got the 40,498 after 13 months, still that return would be absolute return , not a year return as it was 13 months , not 12 .

Use the XIRR formula again and see ..

I never put the GUESS value , it gave answer without that..

Manish

04-जनवरी-07 -5000 04-जनवरी-07 5000

06-मई-07 -10000 06-मई-07 10000

04-अप्रैल-08 -6000 04-अप्रैल-08 6000

01-अगस्त-08 -4000 01-अगस्त-08 4000

04-जून-09 -6500 04-जून-09 6500

06-जुलाई-09 53000 06-जुलाई-09 -53000

XIRR 38.96% XIRR 38.96%

Sir,

In above examples XIRR comes same, how it is possible, please clarify. All data are taken from your above cited example, only out flow & inflow sines are changed.

Thanks.

how to calcullate roi from xirr as in majority of the calcullator they asks for roi along with duration and amount and then they gives the output.

Hence need to know that formula.

XIRR = ROI !

https://www.youtube.com/watch?v=QerPzdK1nGs

XIRR and IRR explained.

Suppose the investments are from different sources and known is the overall project return. How do we calculate the returns due to the different sources that contributed different amounts at random times? Thank you and best regards.

YOu need to combine all of them and just find out the outgo and incoming money at different times and then use XIRR function

Hello,

You can combine the amounts. Remember that money going out is -ve and coming in is +ve. So ideally, if your investments are to be computed for each purpose, as in investment in different instruments, it is best to calculate indvidual XIRR, ie , if I put 5k monthly in ULIP and ELSS for a period of 5 years, then I would, at the end of 5 years, want to know which has given me better returns, including dividends, money back etc. If I club both the instruments in one single bracket called investment, then my future investment decisions may go wrong.

Why is XIRR same for:

(1)

Time Cash Flow

11-01-14 1000

12-05-11 20

13-01-01 30

14-01-01 -2000

24.888%

(2)

Time Cash Flow

11-01-14 1000

12-05-11 20

13-01-01 30

14-01-01 -2000

15-04-01 0

24.888%

Why should;nt it be . you got 0 one a date, even if you have not written than in first example, its still there !

Hi Manish,

I have XIRR values and portfolio values for three Mutual funds as below:

MF Portfolio value, Rs XIRR

A 5,500 26.4%

B 6,500 16.3%

C 6,000 -20.4%

How will I calculate portfolio XIRR?

Thanks

Its not possible, because you dont have individual cashflow date wise. For portfolio XIRR , you need portfolio values !

Pls help with this cash flow issue. I used XIRR to determine the effective interest rate (EIR) of a loan with monthly repayment but the final balance isn’t giving zero but rather 66,927.74. Whereas using IRR, you arrive at zero as the final balance.

Payment Date Opening bal. Interest at EIR Cashflow Closing Balance

15-Jan-2010 (5,600,000.00) 5,600,000.00

1-Feb-2010 5,600,000.00 88,804.03 1,000,000.00 4,688,804.03

1-Mar-2010 4,688,804.03 115,662.41 1,000,000.00 3,804,466.44

1-Apr-2010 3,804,466.44 103,902.88 1,000,000.00 2,908,369.32

1-May-2010 2,908,369.32 76,867.53 1,000,000.00 1,985,236.85

1-Jun-2010 1,985,236.85 54,218.33 1,000,000.00 1,039,455.18

1-Jul-2010 1,039,455.18 27,472.56 1,000,000.00 66,927.74

XIRR = 32.16%

Thanks.

Hi Vic

http://www.jagoinvestor.com/forum is the right place to ask these questions !

-5000

-6000

-3000

5000

-4000

-12000

35000

11% IRR

its 10.63

hi Manish.

Thanks for this good article.

I had a small question.

To calcualte retruns on SIP or some stock scripts which i have bought over few months, can i use the WEIGHTED AVERAGE i.e. can i compare the weighted avg cost price vs current value to give me returns ?

Basically i wanted to understand when to use IRR & when to use some other tool to calculate retrun.

Thanks

I am not sure what is your question ? Look at this video http://jagoinvestor.dev.diginnovators.site/2011/02/calculate-insurance-policies-returns-video.html

Hi Manish.

What i meant was, suppose in invested monthly in a equity MF SIP @ 2000 INR/month for 21 months. Now the current value is 62k. So, if i calculate IRR it comes as 4% (isnt this too low return?). Where as if i calculate using Weighted Avg (wt avg purchase cost comes to 42k) – the retrun is 20k/42 k = ~48% ??

Is this correct ?

Thanks

Yea .. the returns are too low, but then markets have moves like that only.. I hope you know that mutual funds returns are not guaranteed !

Thanks for your reply Manish.

Wanted your views on comparsion of returns basis IRR calculation & weighted avg ? My Avg Cost, because of the market fluctuation is 42k. While current return is 62k – so by this is my return 48% ?

Which one is a better tool for returns calculator in this case specifically.

Its IRR which you should look at !

Sorry, but the explanation provided and formula used for IRR is not correct for your specific case involving monthly cash flows. By default, Excel assumes annual time periods for IRR calculations, therefore, in your case, it is treated as 21 years (not 21 months), hence your cash flows get discounted very aggressively leading to the 3-4% figure that you get.

ok , will check it out and correct it !

Agree with Naren, IRR assumes annual time period.

Puneet,

Use XIRR where you can put monthly dates and with that Rs.2000 invested for 21 months and 62000 as returns will give XIRR rate of 49.51%. It is indeed a very good return.

NOT BEEN ABLE TO GET XIRR CALCULATION. THE DATE AND VALUE FIELD ARE ENTERED AS YR/MONTH/DT AND OUTFLOW IN NEGATIVE BUT THE CALCULATION SHOWS #VALUE. PLS HELP

Can you share the snapshot ?

Simply awesome explanation. I didn’t look any other site after reading this article. I am trying to calculate XIRR of my MF portfolio.

Thanks Abhijit !

Hi Manish,

How I can calculate final value using IRR/XIRR. E.g. the use case I have is, say I know the approximate IRR rate of one of my insurance policy is 6% & I am paying 10000/yr for 20 yrs. I want to get an idea how much my final return value would be. I tried some function NPV/FV in excel, but could not figure out..

Thanks,

IRR is something which needs to be calculated backwords . 6% which you are assuming is just a scenario !

[…] IRR and XIRR and simple but excellent tools to estimate the returns from a periodic investment in a volatile […]

xirr is 12.94 and irr is 12.36%

When the cash flows are at a constant rate – quarterly.. thought certain quarters have no cash flows

Date Cash Flows

26/06/13 (160,488) 12.36% 12.94%

26/09/13 –

26/12/13 – IRR XIRR

26/03/14 –

26/06/14 –

26/09/14 –

26/12/14 –

26/03/15 –

26/06/15 –

26/09/15 –

26/12/15 –

26/03/16 –

26/06/16 –

26/09/16 –

26/12/16 –

26/03/17 7,134

26/06/17 7,293

26/09/17 7,293

26/12/17 7,213

26/03/18 7,134

26/06/18 7,293

26/09/18 7,293

26/12/18 7,213

26/03/19 7,134

26/06/19 7,293

26/09/19 7,293

26/12/19 7,213

26/03/20 7,194

26/06/20 7,273

26/09/20 7,273

26/12/20 7194

26/03/21 7,134

26/06/21 261643

In the said cash flows which are at regular interval we are getting different IRR and XIRR . The difference is quite significant…

Pls suggest

The IRR assumes yearly payments

XIRR is showing the correct value. The value -1.64% is telling us that the value of investment is decreasing by 1.64% every year.

Not decreasing, but what is the return till now !

Manish, I tried out XIRR with my mutual fund investments and wanted your insight on some observations. One of my MFs is at a loss (i.e. current value is lower than principal). When I use XIRR, the value is lower than the actual return.

To quote an example, I invested in UTI Infrastructure in 2007-2008 in 13 SIPs of Rs. 2,500 each. The total investment is Rs. 32,500. The present value of my investment is Rs. 29,946. The absolute return is -7.86%. However, XIRR is coming to -1.64%.

Isn’t it misleading that XIRR is showing a lower negative return? Or am I not interpreting this result correctly? Please guide me.

Can you also put the dates which you have put . Because IRR is the main return you got , not the absolute return .

Let me give an example.

If you invested 1000 Rs on 1-Jan-2009 and got 500 Rs only on your investment on 1-Jan-2013, then your absolute loss is 500 Rs i.e. 50%.

But if you calculate XIRR over the period, it comes out to be -15.90%

This is the loss every year.

Got it?

Yes I got that Anand , but not sure what is your confusion ?

I was trying to clear Karthikeyan’s confusion, not yours.

I learnt this from you only!

Hi,

I have never purchased or invested in any shares or egold or mutual funds. My earning is nominal and has no savings.Now I wish to invest a little amount in either egold or shares or mutual funds. Can you pliz help me where shall I invest to get a good return?

I would really appreciate if you can help me out in this for one time as I am a layman when it comes to investment. Thanks.

In long term, only equity can deliver you good return or real estate

is there any website/software which computes portfolio return(portfolio consisting of stocks,mutual funds, fixed deposits, gold etc) based on time weighted methodology rather than dollar weighted (which is similar to xirr)

Try Mprofit or Perfios

great. trhanks a lot.