IRR In Excel

What is IRR in Excel?

IRR in Excel formula calculates the “Interest Rate of Return” on cash flow based on the discount rate and estimates the return on investments that arise periodically. The interest rate of the return is the interest rate for an investment that includes payment (negative values) and regular income (positive values). IRR function in excel sheet has only two parameters – “value” and “guess”.

John, for example, has put $100 into a company’s bond. In the first two years, he receives no compensation. In the third year, though, he receives $150. So, let us calculate his cash flow’s IRR.

=IRR(B2:B5)
IRR Faq
The result is 14%.

IRR() Formula In Excel

IRR syntax

values =  > It is a mandatory argument.

                > It is an array or reference of a table for which IRR calculation is done.

                > It must have one positive and one negative value.

guess = > It is an optional argument.

              > It is a number that is guessed that will be nearest to the result of IRR.

How To Calculate IRR In Excel?

IRR in Excel sheet is the interest rate at which the net present value (NPV) equals zero. First, let us have a look at the 3 steps in calculating IRR in Excel:

Step 1 – Guessing IRR, which might be, for example, 10%

Step 2 – Calculating the Net Present Value (NPV)

The net present value calculates the worth of investment (done for future gains) on present money. Net present value is the total of all the present values over the period, subtracting an investment’s present value.

PV = FV / (1+r)n

Here,

  • PV = Present Value
  • FV = Future Value
  • r = Rate of Interest
  • n = the number of years

For example: –

Present Value Future Value

NPV = Sum of all PV income – PV payment

Step 3 – Change the IRR Guess until NPV = 0

IRR Examples in Excel

Let us learn NPV and IRR in Excel example:

Example #1 (Basic)

Amy invests ₹1,000 now in a company’s bond. She gets back every year ₹100 for three years and ₹1,100 in the 4th year. The rate of interest is 10%. Calculate IRR.

Here is how you would calculate IRR manually.

PV of 1st year= 100/ (1+0.1) ^1=90.90
PV of 2nd year=100/ (1+0.1) ^2=82.64
PV of 3rd year= 100/ (1+0.1) ^3=75.15
PV of 4th year= 1100/ (1+0.1) ^4=751.31
SUM of all PV income=1000
NPV= 1000 – 1000=0

At 10 % NPV = ₹0

So, IRR = 10%.

Now that we understand this example, let us now calculate IRR In Excel

Step 1: In this example, the table shows the cash flow of investment done by Amy. But, first, we need to find the IRR of the investment.

  • Column A mentions particulars in the starting investment and every year installments.
  • Column B contains the value of “n” number of years.
  • Column C contains the date of investment.
  • Column D contains cash flow every year.
  • Column D calculates the present value.
  • Row 7 contains the sum of all present value income.
  • Row 8 calculates NPV.
  • Row 9 contains the rate of interest.
IRR Basic Example 1.1

Step 2:  Calculate the present value of every year.

PV = FV / (1+r)n    

     =D3/(1+C$9)^B3

Basic Example 1.2

Press ‘Enter’ to get the answer.

Basic Example 1.2.1

Drag down the value in the E3 column to get the present value of all installments.

Step 3: Calculate the sum of all present value income.

=sum(E3:E6)

Sum of Present Value

Step 4: Calculate the net present value. It should be equal to 0.

  • NPV = E7+D2
  • = 1000+(-1000)
  • = 1000-1000 = 0
Basic Example NPV

Step 5: Calculate IRR in Excel.

Basic Example 1.5

=IRR(D2:D6) = 10%

In calculating IRR, the value of NPV should be 0. At which rate the NPV becomes 0 is the interest rate return.

IRR Basic Example Result

Example #2 (IRR Calculations to compare two investments)

John has invested money in two projects. In project A, he invested ₹100 and got back ₹0 in the first year, ₹50 in the second year, and ₹150 in the third year. The discount rate is 29%. In project B, he invested ₹100 and got back ₹150 in the first year, ₹50 in the second year, and ₹0 in the third year. The discount rate is 78%. Find IRR and compare these two investments.

Project A

Present value of the payment is ₹-100

PV of 1st year= 0/ (1+0.29) ^1= 0
PV of 2nd year= 50/ (1+0.29) ^2= 30
PV of 3rd year = 150/ (1+0.29) ^3= 70
SUM of all PV income= 100
NPV = 100 – 100= 0

At 29 % NPV = ₹0

So, IRR = 29%.

Now that we understand this example, let us now calculate IRR In Excel for Project A

Step 1: In this example, the table shows the cash flow of John’s investments in project A. Next, we need to find the IRR of the investment.

  • Column A contains particulars, the starting investment, and every year installments.
  • Column B contains the time number of years.
  • Column C contains cash flow every year.
  • Column D calculates the present value.
  • Row 6 contains the sum of all present value income.
  • Row 7 calculates the net present value.
  • Row 8 contains the discount rate.

Step 2: Calculate the present value of every year

PV = FV / (1+r)n    

     = C3/(1+C$8)^B3

Example 2 (PV 1)

Press ‘Enter’ to get the answer.

 Example 2 (PV result)

Drag down the value in the D3 column to get the present value of all installments.

Step 3: Calculate the sum of all present value income

=sum(D3:D5)

Example 2 (Sum of PV)

Step 4: Calculate the net present value. It should be equal to 0.

  • NPV = D6+C2
  • = 100+(-100)
  • = 100-100 = 0
Example 2 (NPV)

Step 5: Calculate IRR in Excel

Example 2 (irr in excel)

=IRR(C2:C5) = 29%.

In calculating IRR, the value of net present value should be 0. At which rate the net present value becomes 0 is the interest rate return.

Example 2 (Result)
Project B

Present value of the payment is ₹-100

PV of 1st year= 150/ (1+0.78) ^1=70
PV of 2nd year = 50/ (1+0.78) ^2=30
PV of 3rd year = 0/ (1+0.78) ^3 =0
SUM of all PV income =100
NPV = 100 – 100 = 0

At 78% NPV = ₹0

So, IRR = 78%.

This table shows the cash flow of John’s investments in project B. In this project, the installment return is different, and due to this, the interest rate of the return changes.

Now that we understand this example, let us now calculate IRR In Excel for Project B

IRR Example 2.1

Calculate the present value of every year

PV = FV / (1+r)n

Example 2.1 (PV Value)

Calculate the sum of all present value income

=sum(D3:D5)

Example 2.1 (PV Sum)

Calculate the Net Present Value, and it should be equal to 0.

  • NPV = D6+C2      
  • = 100+(-100)    
  • = 100-100 = 0
IRR Example 2.1 (NPV)

Calculate IRR in Excel

=IRR(C2:C5)

= 78%.

In calculating IRR, the value of net present value should be 0. At which rate the net present value becomes 0 is the interest rate return.

IRR Example 2.1 Result

Therefore, the investments done by John in two projects, project A and project B, are the same, but the installment return is different. Due to this, both the IRR values have a vast difference.

Example #3 (Calculating CAGR)

CAGR is Compound Annual Growth Rate. It is the most accurate way to calculate the interest rate of return (IRR). Excel has no formula for calculating CAGR; it is calculated through the following formula.

 CAGR = (EV/BV)(1/n) – 1 * 100%

  • EV = Ending Value
  • BV = Beginning Value
  • n = the number of years

For example, Ron has invested ₹10,000 in the XYZ Co. For the first year, he gets ₹13,000 return, for the second year, he gets ₹14,000 return, and for the third year, he gets a ₹19,000 return amount. So, we have to find the CAGR of his investment.

The beginning value or investment amount is ₹10,000.

Number of years Amount
1st year ₹13,000
2nd year ₹14,000
3rd year ₹19,000 (Ending Value)
CAGR (((19000/10000)^(1/3))-1)*100=23.86%
IRR example 3
  • =((B5/B2)^(1/3)-1)*100
  • CAGR = 23.86%.
CAGR Result

Example #4

Supper Export Co. has cash flow data for an investment of ₹30,000. The cash flow returns on the investments are: –

  • Year 1 – ₹6,000
  • Year 2 – ₹4,000
  • Year 3 – ₹5,800
  • Year 4 – ₹6,900
  •  Year 5 – ₹8,200
  •  Year 6 – ₹9,500

Let us calculate IRR.

Column A contains periods of investments.

Column B contains a description.

Column C contains the cash flow of investment.

Row 9 calculates IRR.

IRR Example 4

=IRR(C2:C8) = 8.22%.

Example 4 Result

IRR Excel Function Not Working

  • Multiple Solutions –
    • If the first cash flow is not negative, the interest rate of return will have more than one output, but excel will generate only the first output that it finds. 
    • Whenever the cash flow value changes from negative to positive, the possibility of the output of interest rate of return will be more than one, and excel will generate only the first output it finds.
  • #NUM! Error –
    • Suppose there are more than 20 iterations to find the interest rate of return, then the #NUM! error is generated. 
    • There should be at least one positive and one negative value; otherwise, the #NUM! error is generated. 

Important Things To Note About IRR In Excel

  • Insert the IRR in Excel formula properly.
  • The value parameter is the necessary argument.
  • The guess parameter is the optional argument.
  • There should be one positive and one negative value in cash flow.
  • The iteration values should not be more than 20 in cash flow.
  • With the same reinvestment rates, the IRR assumes that all project cash flow is reinvested with the same investment rate.

Frequently Asked Questions (FAQs)

How do I calculate the IRR in Excel?

The IRR in Excel sheet is calculated by using the following syntax: –

=IRR(values,[guess])

For example, John has invested ₹100 in a company’s bond. He gets no return in the first two years. However, in the 3rd year, he gets ₹150 in return. So, let us find the IRR of his cash flow.

=IRR(B2:B5)
IRR Faq
The result is 14%.

What is IRR in Excel?

IRR in Excel is a function that calculates the “Interest Rate of Return” on cash flow based on the discount rate and estimates the return on investments that arise periodically. IRR function in Excel has only two parameters – one is “values,” and the other is” guess.”

What is the importance of IRR in Excel?

– IRR in Excel is used to calculate the interest rate of the cash flow of any investment.
– It is the easiest and fastest way to calculate the interest rate of return.
– The IRR in Excel formula is easy to apply and understand.
– It states that one should pursue a project or investment if its IRR is greater than the minimum required rate of return, also known as the ‘hurdle rate.’
– The IRR rule helps companies decide whether or not to proceed with a project. 

Download Template

This article must be helpful to understand the IRR in Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to IRR in Excel. Here we learn how to use IRR(), its formula, examples with calculation, and a downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.