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)

The result is 14%.

IRR() Formula In Excel

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)ⁿ

Here,

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

For example: –

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 4^th 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.

Step 2:  Calculate the present value of every year.

PV = FV / (1+r)ⁿ    

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

Press ‘Enter’ to get the answer.

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)

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

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

Step 5: Calculate IRR in Excel.

=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.

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)ⁿ    

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

Press ‘Enter’ to get the answer.

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)

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

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

Step 5: Calculate 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.

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

Step 1: Calculate the present value of every year

PV = FV / (1+r)ⁿ

Step 2: Calculate the sum of all present value income

=sum(D3:D5)

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

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

Step 4: 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.

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%

• =((B5/B2)^(1/3)-1)*100
• CAGR = 23.86%.

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(C2:C8) = 8.22%.

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

• 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)

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.

Recommended Articles

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 –

• HLOOKUP
• Index Excel Function
• CONCATENATE In Excel