I have been asked several times "What is the difference between the IRR
and XIRR function in Excel?"
Simply - the IRR function returns the PERIOD INTEREST RATE ("i/n") for a
stream of cash flows of equal time length (i.e. days, months, quarters,
years, etc.). "i" is the "nominal" per annum interest rate and "n" is
the number of compounding periods per year. For example, if the IRR
function in Excel on a stream of monthly cash flows returns a result of
4.5% - then the "nominal" per annum internal rate of return is 54.0% (or
045 x 12 - "i" compounded monthly) and the effective per annum rate of
return is 69.59% (compounded annually). To convert a nominal (per
annum) IRR to an effective (per annum) IRR, you can use the "EFFECT"
function in Excel or use the equation (1 + i/n)^n - 1.
Essentially - any cash flow stream (with only one change of sign) has
two valid IRRs (the nominal IRR and the effective IRR).
The XIRR function return assumes DAILY COMPOUNDING.....period. The user
has to input/designate a specific date each non-zero cash flow occurs
and the cash flows can be irregular. All other cash flows are assumed
to be zero. The XIRR function calculates an EFFECTIVE per annum rate of
return (not a nominal period interest rate). You can convert the XIRR
rate of return result by using the "NOMINAL" function in Excel and
assuming n=365.
IRR is something of an enigma since there are so many different variants ofthis particular rate of return. An IRR may be defined and calculated for almost any investment ranging for a lump-sum investment, and those that makeperiodic returns for a number of periods and also for those that pay dividends forever. The native IRR function is barely able to find the internal rate of return for series of cash flows for fixed number of periods.
In comparison, the tadIRR function which is an Excel IRR function
http://tadxl.com/excel_irr_function.html offers unlimited capabilities to find the IRR - internal rate of return.
tadIRR is one of the many financial functions found in tadXL v3.0 add-in for Excel 2007, 2010 and 2013 found here at
http://tadxl.com/
In this lengthy tutorial, I will illustrate finding internal rate of returnfor various investments that range from single sum investment to those that make payments forever and those than make periodic payments for a limitednumber of periods.
If you purchased a perpetuity by paying $10, that makes never-ending payments in amount of $1. Find the internal rate of return on such an investment.
We will use the Excel IRR function called tadIRR to find the internal rate of return on a perpetuity.
If no other information is at hand about the investment then we will simplyenter an array of values of the cash flows {-10, 1} where the first amount-10 is an outgoing cash flow followed by an incoming cash flow of 1. To tell the IRR function that we have a perpetual income, we have to enter the frequency of cash flows. Since the $10 is paid once we will use a value of 1for its frequency but since the $1 incomes comes in forever we will use a value called INF short for infinity as the frequency of this receipt. Thesevalues will be entered as an array of {1,"INF"}
Now we enter the tadIRR function in a worksheet cell as follows where we omit a number of values that are not needed. The five leading commas ,,,,, mean that we have left out the values for the guess rate, the use_rates flag,the rate schedule, the inflation, and the tax rate. The two commas between{-10,1},,{1,"INF"} mean that we have left out the value for adjust_for_inflation flag.
=tadIRR(,,,,,{-10,1},,{1,"INF"})
IRR = 10%
The function returns an IRR of 10% for this perpetual investment that was paid with $10 and earned us $1 forever.
Now suppose that the first receipt of income in amount of $1 is not due till the 10th year, in other terms we have a deferred perpetuity. How would this change the investor's rate of return for this specific investment. To find the answer, we will add an itermediate cash flow in amount of $0 for nine years thus delaying the first payment till the 10th year. Let us now findthe internal rate of return as follows.
=tadIRR(,,,,,{-10,0,1},,{1,9,"INF"})
IRR = 5.95%
The function returns an IRR of 5.95% on a perpetual income of $1 that is not expected till the 10th year for which we have made an immediate payment of $10.
The previous example assumed that interest rate is compounded annually, however if the compounding frequency of interest is different than annual suchas a monthly compounding of interest then the IRR would be different than 10%. To find such a rate, we need to tell the IRR function that interest compounding is monthly by entering a value of 1/12 for the option called "compounding".
=tadIRR(,,,,,{-10,1},,{1,"INF"},,1/12)
IRR = 9.57%
The function returns an IRR of 9.57% when interest is compounded per month on a perpetual income of $1 that was purchased with $10.
Now suppose that the first receipt of income in amount of $1 is not due till the 10th year, in other terms we have a deferred perpetuity. How would this change the investor's rate of return for this specific investment. To find the answer, we will add an itermediate cash flow in amount of $0 for nine years thus delaying the first payment till the 10th year. Let us now findthe internal rate of return as follows.
=tadIRR(,,,,,{-10,0,1},,{1,9,"INF"},,1/12)
IRR = 5.79%
The function returns an IRR of 5.79% on a perpetual income of $1 that is not expected till the 10th year for which we have made an immediate payment of $10.
Now if we had to pay 10% tax on our perpetual income, the IRR would have tobe adjusted for the tax burden. To tell the IRR function that 10% tax is due on this investment we will enter a value of 10% or 0.1 for option called"tax_rate" as follows.
=tadIRR(,,,,10%,{-10,1},,{1,"INF"})
IRR = 11.11%
The function returns an IRR of 11.11% that reflects the tax-burden
Now suppose that the first receipt of income in amount of $1 is not due till the 10th year, in other terms we have a deferred perpetuity. How would this change the investor's rate of return for this specific investment. To find the answer, we will add an itermediate cash flow in amount of $0 for nine years thus delaying the first payment till the 10th year. Let us now findthe internal rate of return as follows.
=tadIRR(,,,,10%,{-10,0,1},,{1,9,"INF"})
IRR = 6.61%
The function returns an IRR of 6.61% on a perpetual income of $1 that is not expected till the 10th year for which we have made an immediate payment of $10.
Since the expected income comes at future dates, then it is probable that the full value of $1 is not realized and only 90% of the income is expected.We will have to use the hair_cuts options and enter two values for it, thefirst being 0 to specify that full costs in amount of $10 are paid and thesecond value of 0.1 to denote that a 10% hair cut on income is expected onincome of $1.
=tadIRR(,,,,,{-10,1},,{1,"INF"},,,,,{0,0.1})
IRR = 9%
The function now returns an IRR of 9% when only 90 cents are expected on anincome of $1.
Now suppose that the first receipt of income in amount of $1 is not due till the 10th year, in other terms we have a deferred perpetuity. How would this change the investor's rate of return for this specific investment. To find the answer, we will add an itermediate cash flow in amount of $0 for nine years thus delaying the first payment till the 10th year. Let us now findthe internal rate of return as follows.
=tadIRR(,,,,,{-10,0,1},,{1,9,"INF"},,,,,{0,0,0.1})
IRR = 5.54%
The function returns an IRR of 5.54% on a perpetual income of $1 that is not expected till the 10th year for which we have made an immediate payment of $10.
But now if we revert back to the actual values for expenses and incomes in amount of $10 and $1 respectively, but a rogue trader wants to rig the interest rate by a margin of 10%. How would this effect the IRR on this investment, to find the answer we enter a value of 10% for the option called "rate_rigged_by"
=tadIRR(,,,,,{-10,1},,{1,"INF"},,,,,,10%)
IRR = 9.09%
The function now returns an IRR of 9.09% which is lower than the actual IRRof 10%.
Now suppose that the first receipt of income in amount of $1 is not due till the 10th year, in other terms we have a deferred perpetuity. How would this change the investor's rate of return for this specific investment. To find the answer, we will add an itermediate cash flow in amount of $0 for nine years thus delaying the first payment till the 10th year. Let us now findthe internal rate of return as follows.
=tadIRR(,,,,,{-10,0,1},,{1,9,"INF"},,,,,,10%)
IRR = 5.41%
The function returns an IRR of 5.41% on a perpetual income of $1 that is not expected till the 10th year for which we have made an immediate payment of $10.
What if the investment earns us an amount of income that grows per period by a certain percentage. In this case the $1 perpetuity grows by 5% per period, to find the IRR with the growing income we will enter a value of 5% forthe option called "inflation" as follows.
=tadIRR(,,,5%,,{-10,1},,{1,"INF"})
IRR = 15%
The function returns an IRR of 15% on a perpetual income of $1 that grows by 5% for which we paid an amount of $10.
Now suppose that the first receipt of income in amount of $1 is not due till the 10th year, in other terms we have a deferred perpetuity. How would this change the investor's rate of return for this specific investment. To find the answer, we will add an itermediate cash flow in amount of $0 for nine years thus delaying the first payment till the 10th year. Let us now findthe internal rate of return as follows.
=tadIRR(,,,5%,,{-10,0,1},,{1,9,"INF"})
IRR = 9.44%
The function returns an IRR of 9.44% on a perpetual income of $1 that is not expected till the 10th year for which we have made an immediate payment of $10.
What if the interest rate was compounded per month on this income that grewby 5% per period, how would this change the IRR. To find out, we will enter a value of 1/12 for the option called "compounding" to get the new IRR asfollows.
=tadIRR(,,,5%,,{-10,1},,{1,"INF"},,1/12)
IRR = 14.16%
The function now returns an IRR of 14.16% where we paid $10 to receive a perpetual income of $1 that grows by 5% and interest is compounded per month.
Now suppose that the first receipt of income in amount of $1 is not due till the 10th year, in other terms we have a deferred perpetuity. How would this change the investor's rate of return for this specific investment. To find the answer, we will add an itermediate cash flow in amount of $0 for nine years thus delaying the first payment till the 10th year. Let us now findthe internal rate of return as follows.
=tadIRR(,,,5%,,{-10,0,1},,{1,9,"INF"},,1/12)
IRR = 9.13%
The function returns an IRR of 9.13% on a perpetual income of $1 that is not expected till the 10th year for which we have made an immediate payment of $10.
An investment of $100 makes 50 periodic returns in amount of $3. The payments begin a year from the initial investment, the task is to find the investor's rate of return - ROI on this investment. Here we will use the tadIRR function to find this interest rate.
=tadIRR(,,,,,{-100,3},,{1,50})
IRR = 1.723%
The function returns a periodic interest rate of 1.723% for this investment..
For this trivial invesment with periodic returns, the Excel RATE function does the job as well to find the IRR as follows.
=RATE(50,3,-100)
IRR = 1.723%
The Excel function returns the same IRR of 1.723% for this investment that makes 50 periodic payments in amount of $3 that was paid with $100.
=tadIRR(,,,,,{-100,0,3},,{1,12,50})
IRR = 1.122%
What if the compounding frequency of interest is not annual but it is set to monthly compounding. How then would we find the interest rate for this investment. To do so we will pass in a value of 1/12 for the option called "compounding" in the tadIRR function as follows.
=tadIRR(,,,,,{-100,3},,{1,50},,1/12)
IRR = 1.710%
The function now returns a monthly compounded rate of 1.710% for the same investment of $100 that returned 50 periodic annual payments of $3.
The IRR calculations thus far assumed that periodic payments are made at the end of each year. But what if the payments are monthly, how then to find the internal rate of return for such an investment with monthly cash flows.The short answer is to provide a value of 1/12 for the option called "period" in the tadIRR function as follows:
=tadIRR(,,,,,{-100,3},,{1,50},,,1/12)
IRR = 22.756%
Now we see that the monthly IRR of 22.756 is considerably higher than the annual IRR of 1.723%. If you were confined to using native Excel functions, you could have wrapped the RATE function within EFFECT function to get the same rate as illustrated below.
=EFFECT(RATE(50,3,-100)*12,12)
IRR = 22.756%
But now what if the payments are made each month couple that with monthly compounding of interest. Now to find the IRR, the tadIRR would need a value of 1/12 for both of the options for "compounding" and "period"
=tadIRR(,,,,,{-100,3},,{1,50},,1/12,1/12)
IRR = 20.679%
As you have just seen the monthly compounded rate of return for monthly cash flow amounts is 20.679% that happens to be lower than the previously monthly compounded rate for annual cash flows.
But taxes are certain on capital gains, thus a rather realistic measure of an investor's return that makes provision for taxes. To find one we would pass two values of 0% and 30% for tax rates where the income of $3 is taxed at 30%.
=tadIRR(,,,,{0,0.3},{-100,3},,{1,50})
IRR = 2.46%
The function returns an IRR of 2.46% when taxes are considered in finding the internal rate of return.
But future incomes are not certain and we have to consider for cases when the full value of investment does not materialize. For example, if only 90% of the payments were to mature then we would pass a value of 10% or 0.1 forthe option called "haircut" in the tadIRR function as follows.
=tadIRR(,,,,,{-100,3},,{1,50},,,,,{0,0.1})
IRR = 1.25%
The function now returns an IRR value of 1.25% that is considerably lower than the rate of 1.723% when full payments were collected
Yet if you keep an eye of financial news then you would know that most traders working at large financial institutions of repute such as RBS, UBS, JP Morgran have admitted wrongdoing when fixing the key interest rates such asthe LIBOR and EURIBOR. Thus a rogue trader who have rigged the rate by 10%would like to know the rigged IRR rate. This too is possible to find usingtadIRR function when you pass a value of 10% to the option called "rate rigged by".
=tadIRR(,,,,,{-100,3},,{1,50},,,,,,10%)
IRR = 1.57%
The function returns a rigged rate of 1.57% that is altered by a 10% rig ofthe actual IRR of 1.723%
Now let us look at an investment from a business valuation or company valuation perspective albeit this is trivial of an example but we will later expand on this when I discuss IRR for uneven cash flows. Suppose a business generates 3 million dollars in annual revenue throughout the next ten years. Finally, it is determined that business generates an annual revenue in amount of 5 million dollars forever starting in year 11. The task for an analyst is to find the buyers IRR who is willing to pay $100 million dollars for this business at present. We will use the tadIRR function and pass it the three amounts of -100, 3 and 1 followed by the frequnecies of such amounts as 1, 10 and INF for the terminal cash flow.
=tadIRR(,,,,,{-100,3,5},,{1,10,"INF"})
IRR = 4.311%
The function returns an IRR of 4.311% that is the return on investment for the buyer and is the rate of return at which the buyer breaks even on this investment. Obviously the investor will only make money if her cost of capital is lower than the internal rate of return of 4.311%
Now let us assume that the investor has a term schedule of rates at her disposal at which the future cash flows are discounted. This structure may well be the WACC - weighted average cost of capital of the firm. The task now is to find the IRR at which the net present value of these cash flows is the same as the one found using the schedule of discount rates. We will enterthree rates 0 for the cash outflow, 3% for the payments from year 1 to 10 and finally a 5% rate for the terminal cash flow
=tadIRR(,1,{0,0.03,0.05},,,{-100,3,5},,{1,10,"INF"})
IRR = 4.877%
This time the function returns and IRR of 4.877% for the same investment whose cash flows were discounted using a schedule of rates.
Now let us suppose that in addition to having a rate schedule the investor has found that annual revenue of the firm grows annually at a growth rate of 1%. How would this change the IRR calculation, to find the rate of returnwe will now pass in the growth rates to the function as 0 for cash outlay,1% for years 1 to 10 and 1% for terminal value
=tadIRR(,1,{0,0.03,0.05},{0,0.01,0.01},,{-100,3,5},,{1,10,"INF"})
IRR = 1.50%
The function now returns the IRR of 1.50% for this investment where the annual income grows by a constant rate of 1%.
Let us have a look at examples of finding IRR for annuities that have cash flows in uneven amounts along with cash flows that are themselves annuities.. The first example has an initial cash outlay followed by series of incoming money amounts. No other information is available about the investment. For example, it is not known whether the cash flows are annual, quarterly, monthly, fortnightly, weekly, or daily. No information about compouding frequency of interest is given thus annual compounding of interest is assumed. As the data about frequency of cash flows is not provided thus it is assumed that each cash flow payment occurs once. Rate schedule is missing thus nodiscount rates are available, same is true for inflation rates and tax rates thus we will use 0% as values for all such rates. The length of the period is not given so we will take the cash flows as annual. The full year discounting convention will be used as there is no input for the concentrationof cash flows. The hair cut values are not provided thus we will assume that all future amounts are realized at 100% of their face value. Finally rate is clean as there is no information about rigging the rate.
=tadIRR( ,,,,, {-100,75,150,100,1000,50000,2000,90000} )
IRR = 287.67%
The function returns an IRR of 287.67% for this investment, but in later examples we will see the rate drop as more information about the cash flows becomes available to us to find the internal rate of return.
Since we were only dealing with a series of cash flows, we would have foundthe same internal rate of return had we used the native IRR function as follows
=IRR( {-100,75,150,100,1000,50000,2000,90000} )
IRR = 287.67%
But that is the limit of the Excel IRR function that does not permit use ofother information about the investment that was discussed earlier. Now we will move on to use tadIRR function to find the investor's rate of return on investments using tadIRR function as more and more information gets addedto the arsenal.
What if an analyst was the given the cash flows of the investment along with a schedule of discount rates and the task is to find a single interest rate at which net present value of the investment is the same as one that is found when the investment is discounted using a schedule of discount rates.This is akin to finding an IRR Schedule, and here we will use tadIRR function where we enter the rate schedule as an array along with the cash flows.To tell the function that we are making use of the schedule of rates we will enter a value of 1 for option called "use_rates".
=tadIRR( ,1, { 0.04,0.02,0.05,0.03,0.02,0.04,0.03,0.06 } ,,, {-100,75,150,100,1000,50000,2000,90000} )
IRR = 5.31%
The function returns an IRR of 5.31%, and you can confirm the results by using tadNPV function as follows to note that net present value of the investment is the same when using a single IRR and when using the schedule of discount rates.
=tadNPV( {0.04,0.02,0.05,0.03,0.02,0.04,0.03,0.06} ,,, {-100,75,150,100,1000,50000,2000,90000} )
NPV = $103751.41
=tadNPV(5.3064068%,,, {-100,75,150,100,1000,50000,2000,90000})
NPV = $103751.41
Now this confirms to us that the NPV of the investment is the same in amount of 103751.41 when we use the single IRR and when a schedule of discount rates is applied in discounted cash flow analysis.
Let us revert back to the original cash flows for this investment and now the analyst was handed a row of number for frequencies of each of the cash flows. The cash flows now turn into annuities of their own where each annuity makes periodic payments for a number of periods. The first outgoing amount is paid four times, the second amount makes 365 payments, the next one makes 24 payments and one after than makes another 365 payments. The fifth payment is a perpetuity that makes never-ending payments and we use a value of INF for the frequency to tell the tadIRR function that this particular payment is a perpetuity. The last payment that goes on forever would usually taken as the terminal value but the tadIRR function allows you to enter payments along with their number of occurances even after the terminal value. Thus there are 260 more payments after the terminal payment, and following these 260 payments we have another perpetuity followed a series of 5200 payments. This sounds bizarre that one can make payments or receive income after the terminal value but then tadIRR is a unique financial function that allows complex financial analysis. Now Excel IRR function is not going to handle this data so we will put this data into tadIRR function to find the internal rate of return on this odd investment.
=tadIRR( ,,,,, {-100,75,150,100,1000,50000,2000,90000} ,, {4,365,24,365,"INF",260,"INF",5200} )
IRR = 15.02%
The function returns an IRR of 15.02% for this investment where periodic cash flows were itself annuities that made periodic payments for a number of periods coupled with payments that go on forever. Wow, amazing stuff isn't it. Wait till more of the options in tadIRR function are explored in the following discussion to illustrate finding investor's rate of return on investments.
But taxes on capital gains are for certain thus if the analyst was providedwith a schedule of tax rates for this investment, an IRR that includes provision for taxes would be more representative of the return on investment.
=tadIRR(,,,,{0.35,0.36,0.37,0.38,0.39,0.4,0.45,0.5}, {-100,75,150,100,1000,50000,2000,90000},, {4,365,24,365,"INF",260,"INF",5200} )
IRR = 23.52%
The function returns an IRR of 23.52% when taxes apply on capital gains that are made from an investment.
Let us revert back to the investment without the tax burden, in the previous examples it was assumed that the compounding of interest is annual however one may be inclined to use more meaningful compounding frequencies of interest such as monthly, quarterly, weekly, daily and so on. Let us now entera schedule of compounding frequencies in the tadIRR function as follows.
=tadIRR(,,,,, {-100,75,150,100,1000,50000,2000,90000} ,, {4,365,24,365,"INF",260,"INF",5200},, {0.25,0.002739726,0.083333333,0.002739726,1,0.038461538,1,0.019230769})
IRR = 13.96%
The function now returns an IRR of 13.96% in contrast to the rate of returnof 15.02% when interest was compounded annually.
The examples discussed thus far assume periods are of a year's length whereas we would want to define more realistic periods. We will now use the the lengths of our annuities as a quarter, day, month, day, year, a fortnight, a year, and a fortnight. We will pass the schedule of lengths for the periods to our tadIRR function as follows.
=tadIRR(,,,,, {-100,75,150,100,1000,50000,2000,90000},, {4,365,24,365,"INF",260,"INF",5200} ,, {0.25,0.002739726,0.083333333,0.002739726,1,0.038461538,1,0.019230769}, {0.25,0.002739726,0.083333333,0.002739726,1,0.038461538,1,0.019230769})
IRR = 366.33%
The function now returns a whopping IRR of 366.33% as compared to an IRR of13.96% when all periods were considered to be of a years length.
But incomes expected in the future are not for certain thus if only 80% of the incomes were realized, we would enter values such as 20% or 0.2 for hair_cuts in the tadIRR function to find the internal rate of return
=tadIRR(,,,,, {-100,75,150,100,1000,50000,2000,90000},, {4,365,24,365,"INF",260,"INF",5200},,{0.25,0.002739726,0.083333333,0.002739726,1,0.038461538,1,0.019230769}, {0.25,0.002739726,0.083333333,0.002739726,1,0.038461538,1,0.019230769} ,, {0,0.2,0.2,0.2,0,0.2,0.2,0.2})
IRR = 346.78%
The function now returns an lower IRR of 346.78% as compared to an IRR of 366.33% when all incomes materialized.
But if you were a rogue trader then fixing the rate is one of your duties and tadIRR will find the rigged IRR when you enter the percentages by which rate is rigged. Let us now find the internal rate of return when the rate is rigged by 20% as follows.
=tadIRR(,,,,, {-100,75,150,100,1000,50000,2000,90000} ,, {4,365,24,365,"INF",260,"INF",5200} ,, {0.25,0.002739726,0.083333333,0.002739726,1,0.038461538,1,0.019230769}, {0.25,0.002739726,0.083333333,0.002739726,1,0.038461538,1,0.019230769} ,, {0,0.2,0.2,0.2,0,0.2,0.2,0.2}, {0,0.15,0.15,0.15,0,0.15,0.15,0.15})
IRR = 297.84%
The function returns a rigged rate of 297.84% which is comparatively lower than the non-rigged rate of 346.78%.
A last word in closing
An investment of 5 prayers a day for lifetime to await the 40 virgins in after life. Find the IRR for this investment.
=tadIRR(,,,,, {-5,0,40} ,, {1,"INF",1})
IRR = #NUM! error
The function returns a #NUM! error as no such IRR exists for an investment that required 5 times daily prayers to await 40 virgins in after life
An investment of 50 cents at present ensures a single slut at time period 1.. Find the IRR of this investment.
=tadIRR(,,,,, {-0.5,1} ,, {1,1})
IRR = 100%
The function returns 100% as IRR for this investment where it is for certain that an investment at present in amount of $0.50 will hire you a slut
The conclusion
A single slut in better to have at present then to await 40 virgins in afterlife.
