IRR Function

IRR (Internal rate of return)

In simple financial terms the IRR means: The rate at which the net cash inflow are equal to net cash outflows.

The IRR function returns the internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount that occur at regular time intervals.

Syntax of the formula is:

IRR(values, guess)

Values: A collection that contains the cash flow values. values is a collection containing number values. Income (a cash inflow) is specified as a positive number, and an expenditure (a cash outflow) is specified as a negative number. There must be at least one positive and one negative value included within the collection. Cash flows must be specified in chronological order and equally spaced in time (for example, each month). If a period does not have a cash flow, use 0 for that period.

guess:  An optional argument specifying the initial estimate for the rate of return. guess is a number value and is either entered as a decimal (for example, 0.1) or with a percent sign (for example, 10%). If omitted, 10% is assumed. If the default value does not result in a solution, initially try a larger positive value. If this does not result in an outcome, try a small negative value.

remember this is a optional argument, even if you leave this argument results will  not get effected.


(1) Values : Range  of cells with cash flows. Cash out is negative, cash in is positive.       Ranges of values must contain at least one positive and one negative value.

(2) Guess is not required. But if you get a #NUM! error, try different guesss – once you think might be close.

(3) Cash flow must happen at the end of each period.

(4) Cash flow start at time 0

(5) Cash flow do not have to be equal in amount.

(6) Time between each cash flow must be the same.

(7) IRR gives you the period rate. If you give it annual cash flows, it gives you annual rate, if you want a monthly rate multiply the result rate with 12 ( this is the secret).

Now lets see an example


Cash flow in period 0, 1 ,2,3, is -160000,60000,70000,80000

to calculate the IRR we have used the formula =irr(B2:B5) the result is 0.141744514349864 i had just formed it to percentage using % in the menu bar to be more understandable to the viewer.

now lets is month rate

If we have taken loan of $ 100000 for a period of 24 month at the rate of 12% p.a. the EMI works out to $4707.35 per month. Now we will calculate the IRR to get the rate.

The formula in cell F1 is


we are multiplying the resultant value of irr with 12 to get the annual rate. you may guess value 10%.

the above table is same what we have seen the PMT function. the link to PMT function is

In this way we can calculate IRR for insurance policies, lease, mortgage’s etc

4 Responses to IRR Function

  1. Appreciate it for all your efforts that you have put in this. Very interesting information. “The rule is perfect in all matters of opinion our adversaries are insane.” by Mark Twain.

  2. Pat Zicker says:

    That is really interesting, You’re a very professional blogger. I have joined your feed and look forward to in quest of extra of your great post. Also, I have shared your website in my social networks!

  3. Very interesting IRR Function anandexcels topic, thanks for posting🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: