Excel IRR Function

The IRR, internal rate of return, is an important factor to judge the feasibility of a project. In usual, we can set the NPV (net present value) to 0, and figure out the discount rate with the trial-and-error approach. Now, you can apply the IRR function to calculate the IRR easily in Excel.

Function syntax and arguments

How to calculate IRR (the Internal Rate of Return) in Excel? And once you've calculated it, how to interpret IRR? #IRR is used in #investmentanalysis and #ca. The formula or function for calculating IRR in excel considers the initial investment as the first cash flow (negative Cash flow). So the formula (or function) for calculating IRR in Excel is: IRR (value1,value2.

IRR(values, [guess])

(1) Values: Required. They are the cash flow values that you will calculate the IRR for.

Use the IRR function in Excel to calculate a project's internal rate of return. The internal rate of return is the discount rate that makes the net present value equal to zero.

It can be an array such as {-50000, 5000, 8000, 13500, 18800, 20500}, or a reference of cells such as B3:G3.
Values must consist of cash out (negative values) and cash received (positive values).

(2) Guess: Optional. It’s an estimate number close to the result of IRR. If omitted, it will be assumed to 0.1 (10%).

Return Value

Numeric value.

The IRR function returns the internal rate of return for a series of cash flows that occur at regular intervals, says monthly or annually.

Usage notes

(1) The IRR function will ignore text values, logical values, or empty cells in the values argument.

(2) If the IRR function returns the #NUM! error value, or the calculation result is far from the expected interest rate, you can add or change the guess argument.

(3) The IRR function returns the interval rate of return for a period. You can use the formula =(1+r)^n -1 to convert the IRR to annual internal rate of return.

• For monthly stream of cash flows, the IRR function will return the monthly interval rate of return (says 0.5%), and you can convert it to annual rate 6.17% (formula: =(1+0.5%)^12-1);
• For quarterly stream of cash flows, the IRR function will return the quarterly interval rate of return (says 1.5%), and you can convert it to annual rate 6.14% (formula: =(1+1.5%)^4-1);
• If you list your cash flows every half year, the IRR function will return the semi-annual interval rate of return (says 3%), and you can convert it to annual rate 6.09% (formula: =(1+3%)^2-1)

Formula Examples

Example 1: Calculate IRR with monthly cash flows in Excel

Supposing you will spend $5,940 to get a power mower at the beginning of this year, and then rent it out. With the forecast rental in each month, you can easily figure out the IRR (internal rate of return), and judge the feasibility of this project.

How To Irr In Excel

For calculating the IRR after 6 months, the values are cash flows in Range C4:C10, therefore you can apply one of below formulas:

=IRR(C4:C10)

=IRR({-5940,253.8,296.85,393.5,450.2,523.3,655.3})

For calculating the IRR after 12 months, the values are cash flows in the Range C4:C16, therefore you can apply one of below formulas:

=IRR(C4:C16)

How To Use Irr In Excel

=IRR({-5940,253.8,296.85,393.5,450.2,523.3,655.3,697.1,750,800,850,900,950})

Tips:
1. In Cell C4, the initial cost should be entered as negative number: -5940.00 as it is the cash you pay out.
2. In this example, the cash flows are listed by month, therefore the IRR function returns the monthly internal rate of return. If you need to return an annual rate, you can use this formula:

Example 2: Use IRR function to calculate the Money-weighted rate of return in Excel

Let’s say you purchased 5 shares of Company A with the price of $100 per share on 2017/1/1. And then you bought 8 shares of the Company A with price of $125 per share again on 2018/1/1. You received a per-share dividend of $8 on 2018/1/1 too. Finally, you decided to sell all shares of the Company A with the price of $120 per share on 2019/1/1. In this case, you can figure out the money-weighted rate of return with the IRR function easily in Excel.

In this case, the cash flows of 3 years can be calculated as follows:
(1) In Year 2017, you paid $500 out (=-100*5);
(2) In Year 2018, you received share dividend $40 (=5*8), and paid $1,000 (=-125*8) for purchasing 8 shares, so the total cash flow is -$960 (=-1000+40);
(3) In Year 2019, you received $1560 (=120*(5+8));

Therefore, you can apply one of below formulasto figure out the money-weighted rate of return.

=IRR({-500,-960,1560})

=IRR(G4:G6)

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

• Designed for 1500 work scenarios, helps you solve 80% Excel problems.
• Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
• Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
• 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

How To Work Out Irr In Excel

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• One second to switch between dozens of open documents!
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Increases your productivity by 50% when viewing and editing multiple documents.
• Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.

Using Irr Function In Excel