XL: Use of the IRR Function in Excel

Article translations Article translations
Article ID: 59616 - View products that this article applies to.
This article was previously published under Q59616
Expand all | Collapse all

On This Page

SUMMARY

If you want to calculate the interest rate you would pay (or receive) for money borrowed (or invested), and the payments are made in either equal or unequal amounts at regular intervals, use the IRR (internal rate of return) function in Microsoft Excel.

The IRR function is commonly used to compare one investment opportunity with another to determine the best rate of return.

MORE INFORMATION

Example

The format is
=IRR(A1:E1)
where the spreadsheet reads as follows:
   A1:     -1000     B1:  500   C1:  400   D1:  300   E1:  100
   A2: =IRR(A1:E1)   B2:        C2:        D2:        E2:
				
This returns an interest rate of 14.49 percent.

IRR Function Description

The IRR is calculated by using the NPV function iteratively, calculating the interest to give an NPV of zero. This is often referred to as the "break-even" point where inflows equals outflows. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment that consists of payments (negative values) and income (positive values) that occur at regular periods.

The following equation uses the example above to illustrate how this works:
=NPV(IRR(A1:E1),A1:E1)
The resulting value is $0.00, which proves that the IRR has returned the correct rate (for an NPV of zero).

REFERENCES

For more information about the IRR worksheet function, click Microsoft Excel Help on the Help menu, type irr worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 59616 - Last Review: September 19, 2011 - Revision: 3.0
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
Keywords: 
kbhowto KB59616

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com