Excel IRR Function to calculate Internal Rate of Return

This Excel tutorial explains how to use Excel IRR Function to calculate Internal Rate of Return.

You may also want to read

Excel NPV Function to calculate Net Present Value

Internal Rate of Return

Internal Rate of Return (IRR) is the discount rate of cash flow at which an investment breaks even. Similar to Net Present Value (NPV), which discounts a series of cash flow back to present value.

For example, an initial investment is $10000, at the end of 1st year, the cash flow received is $1000, the 2nd year is $2000.

Year Cash Flow
0 -10000
1 8000
2 10000

The net present value = -10000 + 8000/(1+IRR) + 10000/(1+IRR)2

We have to assume an IRR to calculate the net present value.

If we assume the net present value = 0, then we calculate IRR.

0 = -10000 + 8000/(1+IRR) + 10000/(1+IRR)2

IRR = 0.477

Excel IRR Function to calculate Internal Rate of Return

It is very difficult to calculate IRR without using financial calculator or computer. Excel has a function called IRR that you can use.

Syntax of Excel IRR Function

IRR(values, [guess])
values Cash flow that contain at least one positive value and one negative value to calculate the internal rate of return.
guess Optional. A number that you guess is close to the result of IRR.
If Excel fails to find a result after 20 tries, the #NUM! error value is returned.

Example of Excel IRR Function

Excel IRR Function

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *