Skip to content

Excel function · Financial

Excel IRR function

IRR gives the rate that makes the net present value of an investment's cash flows zero, which is the project's return per period. Use it to compare two projects or to check a return against a target rate.

In Turkish Excel: İÇ_VERİM_ORANI

What it does

The values range holds every cash flow in order, including the initial investment: money paid today is negative, money coming in later is positive. There must be at least one negative and one positive value. The flows are assumed to be evenly spaced; with yearly flows the result is a yearly return, with monthly flows a monthly one.

The result is the rate that brings the net present value to zero when NPV is applied to the same cash flows. If it is higher than the return you expect (your cost of capital), the project is considered worthwhile.

Excel searches for the rate by trial and error; usually no guess is needed. If no result is found you get #NUM!, and giving another guess can help. For cash flows on irregular dates use XIRR; if financing and reinvestment rates differ, use MIRR.

Syntax

English Excel=IRR(values, [guess])
Turkish Excel=İÇ_VERİM_ORANI(değerler; [tahmin])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
valuesRequiredThe range with the cash flows, including the initial investment, e.g. B2:B6; it must contain at least one negative and one positive value.
guessOptionalThe rate to start the search from; 10% if omitted. You only need to change it when no result is found.

Examples

1The rate of return of two investment projects

Both projects need the same initial investment; A's cash comes early and B's late. The year-0 investment is inside the range as a negative value.

F2fx=IRR(B2:B6)
The rate of return of two investment projects: example table
ABCDEFG
1YearProject AProject BMeasureProject AProject B
20-250,000.00-250,000.00Internal Rate of Return26.68%22.18%
3180,000.0020,000.00
42110,000.0060,000.00
53130,000.00140,000.00
64140,000.00260,000.00
  • F2=IRR(B2:B6)
  • G2=IRR(C2:C6)

Project A returns 26.68% and Project B 22.18%. B brings in more cash in total, but most of it arrives late, so its rate is lower.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2The monthly return in a credit sale and its annual equivalent

Goods worth 6,000 are handed over today and paid for in 6 monthly instalments of 1,100. The flows are monthly, so the result is a monthly return; E2 turns it into a compound annual rate.

D2fx=IRR(B2:B8)
The monthly return in a credit sale and its annual equivalent: example table
ABCDE
1MonthCash FlowMonthly IRRAnnual Equivalent
20-6,000.002.79%39.18%
311,100.00
421,100.00
531,100.00
641,100.00
751,100.00
861,100.00
  • D2=IRR(B2:B8)
  • E2=(1+D2)^12-1

The monthly return built into the instalments is 2.79%; its compound annual equivalent is 39.18%. Multiplying the monthly rate by 12 would leave out compounding and give a lower figure.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#NUM!

Cause: All values have the same sign (e.g. the investment was entered as positive), or Excel could not reach a result in its attempts.

Fix: Enter the investment as negative; if needed, give a different guess: =IRR(B2:B6,0.3).

Monthly rate read as annual

Cause: The rate from monthly cash flows was read as an annual return.

Fix: For the compound annual equivalent, use =(1+IRR(B2:B8))^12-1.

More than one answer

Cause: The cash flows change sign more than once (e.g. a large refit mid-way), so more than one rate can make NPV zero.

Fix: Check the result with NPV, or use MIRR.

Good to know

  • To check the result, work out NPV at that rate: =NPV(IRR(B2:B6),B3:B6)+B2 should come out very close to zero.
  • When choosing between two projects, look at net present value as well as IRR; for projects of different sizes the two can rank them differently.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a summary that works out the internal rate of return of two projects from their yearly cash flows and shows which one is higher

Convert to table

Bring your project table into the tool and type this. Nexell works out the rates with real Excel formulas and shows them in the preview; in the downloaded file, the rates change when a year's cash flow does.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between IRR and NPV?

NPV tells you how much value a project creates today at a given discount rate. IRR is the rate of return at which NPV becomes zero. One is an amount and the other a percentage; decisions use both.

Related functions

All financial functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free