How to Calculate an IRR in Excel

A step-by-step guide to IRR calculation in Excel

Reviewed by Alex Kwan

Last Updated: December 22, 2023

Businesses will often use the Internal Rate of Return (IRR) calculation to rank various projects by profitability and potential for growth. This is sometimes called the "Discounted Cash Flow Method," because it works by finding the interest rate that will bring the cash flows to a net present value of 0. The higher the IRR, the more growth potential a project has. The ability to calculate an IRR on Excel can be useful for managers outside of the accounting department.

Steps

Download Article

{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/f\/fe\/Calculate-an-Irr-on-Excel-Step-1-Version-3.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-1-Version-3.jpg","bigUrl":"\/images\/thumb\/f\/fe\/Calculate-an-Irr-on-Excel-Step-1-Version-3.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-1-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/3\/3d\/Calculate-an-Irr-on-Excel-Step-2-Version-3.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-2-Version-3.jpg","bigUrl":"\/images\/thumb\/3\/3d\/Calculate-an-Irr-on-Excel-Step-2-Version-3.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-2-Version-3.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

Advertisement
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/f\/f7\/Calculate-an-Irr-on-Excel-Step-3-Version-2.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-3-Version-2.jpg","bigUrl":"\/images\/thumb\/f\/f7\/Calculate-an-Irr-on-Excel-Step-3-Version-2.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-3-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

3
Determine the projects or investments you will be analyzing and the future period to use.
- For instance, assume that you have been asked to calculate an IRR for 3 projects over a period of 5 years.
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/6\/6e\/Calculate-an-Irr-on-Excel-Step-4-Version-2.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-4-Version-2.jpg","bigUrl":"\/images\/thumb\/6\/6e\/Calculate-an-Irr-on-Excel-Step-4-Version-2.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-4-Version-2.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

4
Prepare your spreadsheet by creating the column labels.
- The first column will hold the labels.
- Allow one column for each of the projects or investments that you would like to analyze and compare.
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/c\/ca\/Calculate-an-Irr-on-Excel-Step-5-Version-3.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-5-Version-3.jpg","bigUrl":"\/images\/thumb\/c\/ca\/Calculate-an-Irr-on-Excel-Step-5-Version-3.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-5-Version-3.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

Initial Investment, Net Income 1, Net Income 2, Net Income 3, Net Income 4, Net Income 5 and IRR.
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/3\/30\/Calculate-an-Irr-on-Excel-Step-6-Version-3.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-6-Version-3.jpg","bigUrl":"\/images\/thumb\/3\/30\/Calculate-an-Irr-on-Excel-Step-6-Version-3.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-6-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

Input the data for each of the 3 projects, including the initial investment and the forecasted net income for each of the 5 years.
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/c\/cc\/Calculate-an-Irr-on-Excel-Step-7-Version-3.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-7-Version-3.jpg","bigUrl":"\/images\/thumb\/c\/cc\/Calculate-an-Irr-on-Excel-Step-7-Version-3.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-7-Version-3.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

7
Select cell B8 and use the Excel function button (labeled "fx") to create an IRR function for the first project.
- In the "Values" field of the Excel function window, click and drag to highlight the cells from B2 to B7.
- Leave the "Guess" field of the Excel function window blank, unless you have been given a number to use. Click the "OK" button.
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/0\/0c\/Calculate-an-Irr-on-Excel-Step-8-Version-3.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-8-Version-3.jpg","bigUrl":"\/images\/thumb\/0\/0c\/Calculate-an-Irr-on-Excel-Step-8-Version-3.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-8-Version-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

8
Confirm that the function returns the number as a percentage.
- If it does not, select the cell and click the "Percent Style" button in the number field.
- Click the "Increase Decimal" button twice to apply 2 decimal points to your percentage.
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/f\/f5\/Calculate-an-Irr-on-Excel-Step-9-Version-3.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-9-Version-3.jpg","bigUrl":"\/images\/thumb\/f\/f5\/Calculate-an-Irr-on-Excel-Step-9-Version-3.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-9-Version-3.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}
{"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/9\/93\/Calculate-an-Irr-on-Excel-Step-10-Version-3.jpg\/v4-460px-Calculate-an-Irr-on-Excel-Step-10-Version-3.jpg","bigUrl":"\/images\/thumb\/9\/93\/Calculate-an-Irr-on-Excel-Step-10-Version-3.jpg\/aid1425817-v4-728px-Calculate-an-Irr-on-Excel-Step-10-Version-3.jpg","smallWidth":460,"smallHeight":346,"bigWidth":728,"bigHeight":547,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

This is the investment with the most potential for growth and return.

Expert Q&A

Search

Add New Question

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Tips

Remember to enter your "Initial Investment" values as negatives, since they represent cash outlays. The "Net Income" values should be entered as positive amounts, unless you anticipate a net loss in a given year. That figure only would be entered as a negative.

Thanks
Helpful 0 Not Helpful 0
If the IRR function returns a #NUM! error, try entering a number in the "Guess" field of the function window.

Thanks
Helpful 1 Not Helpful 0
The "IRR" function in Excel will only work if you have at least 1 positive and 1 negative entry per project.

Thanks
Helpful 0 Not Helpful 0

Show More Tips

Submit a Tip

All tip submissions are carefully reviewed before being published

Submit

Thanks for submitting a tip for review!

Things You'll Need

Project details
Computer
Microsoft Excel

Log in

How to Calculate an IRR in Excel

Steps

Expert Q&A

Tips

Things You'll Need

You Might Also Like

References

About This Article

Is this article up to date?

Quizzes

You Might Also Like

Featured Articles

Trending Articles

Featured Articles

Featured Articles

Watch Articles

Trending Articles

Explore Categories