On Monday, November 18, 2013 10:22:56 AM UTC-8, CMLDALLAS wrote:
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.
--
CMLDALLAS
An IRR calculation can be viewed from various perspectives rather than the trivial one as calculated by Excel IRR function.
Have a look at the following schedule of data that looks like a 8 x 8 matrix. To find IRR with information that is as varied and complex as the one shown here the traditional IRR functions in Excel and other software programs and TI BA II+ calculator will not do the job
Rates 3% 2% 5% 1% 2% 3% 6% 5%
Cash flows -100 35 60 90 -110 190 400 2000000
Frequencies 4 26 12 365 365 4 INF 100
Types 0 0 1 1 0 1 0 0
Compoundings 1/12 1/12 1/12 1/12 1/12 1/12 1/12 1/12
Periods 1 1/26 1/12 1/365 1/365 1/4 1/365 1
Concentrations 1 0.5 1 0.5 1 1 0.5 1
Hair cuts 0% 20% 20% 20% 0% 20% 20% 20%
In comes tadXL v3.0 at
http://tadxl.com that offers an Excel IRR function here at
http://tadxl.com/excel_irr_function.html
=tadIRR( rates, cashflows, frequencies, types, guess, compoundings, periods, concentrations, hair_cuts, iterations, precision )
The tadIRR function integrates functionality of various internal rate of return calculations in one place. These calculations are explained briefly in the text that follows
Please note that only a handful of scenarios are discussed here and tadIRR may be used in a large number of other situations that are common practice in financial analysis
If we only had a series of cash flows then tadIRR would serve the same purpose as Excel IRR function shown in the example that follows
The first argument is omitted as there are rates to deal with, and the remaining missing arguments assume default values such as 10% as guess, 1 for frequency of all cash flows, 1 for annual compounding of interest, 1 for year as the length of the period assuming annual cash flows, 1 for concenration assuming full-year discounting of cash flows.
You can pass the data as range of cells as follows
=tadIRR( , B2:I2 )
or you may pass the values as array of numbers
=tadIRR( , { -100, 35, 60, 90, -110, 190, 400, 2000000 } )
If the cash flows are monthly and compounding is annual we pass it a value of 1/12 for period as follows
=tadIRR( , B2:I2, , , , , 1/12)
=tadIRR( , { -100, 35, 60, 90, -110, 190, 400, 2000000 }, , , , , 1/12)
If the cash flows and compounding of interest are both monthly we pass it a value of 1/12 for period and compounding
=tadIRR( , B2:I2, , , , 1/12, 1/12)
=tadIRR( , { -100, 35, 60, 90, -110, 190, 400, 2000000 }, , , , 1/12, 1/12)
If the cash flows are discounted using mid-year discounting convention, we enter a value of 1/2 or 0.5 for concentration as follows
=tadIRR( , B2:I2, , , , 1/12, 1/12, 1/2)
=tadIRR( , { -100, 35, 60, 90, -110, 190, 400, 2000000 }, , , , 1/12, 1/12, 1/2)
If we find that only 90% of the 80% of the cash receipts and payments are realized, we enter a value of 20% or 0.2 for hair cut.
A prime example of hair cuts on investment from recent financial news would be from bailout of Greek and Cyprus economies where the Government of these countries were unable to make the full interest payments and principal on Treasury bonds to its investors. The investors had to take a 30% hair cut leading to net present value of 70% of the actual amount.
=tadIRR( , B2:I2, , , , 1/12, 1/12, 1/2, 20%)
=tadIRR( , { -100, 35, 60, 90, -110, 190, 400, 2000000 }, , , , 1/12, 1/12, 1/2, 20%)
If the cash flows have a schdule of rates associated with them then we enter the series of discount rates as follows
This IRR calculation is applies for finding yields to maturity - YTM on coupon bearing bonds where the term structure of interest varies for each year.. The investor would be keen to find a single interest rate that sets equal the current value (present value) of the bond to market price of such a bond.
=tadIRR( { 0.03, 0.02, 0.05, 0.01, 0.02, 0.03, 0.06, 0.05 }, { -100, 35, 60, 90, -110, 190, 400, 2000000 } )
=tadIRR( B1:I1, B2:I2 )
To find IRR on perpetuity where you have paid a price of $100 that earns a perpetual income of $25, you would use the following IRR method
=tadIRR( , { -100, 25 }, { 1, "INF"} )
or pass it the range of cells
=tadIRR( , B2:C2, B3:C3 )
If you were tasked with business valuation or company valuation were the terminal value is a perpetual income or loss then you would use the following IRR function call. Here each payment or receipt has an associated frequency of occurance such as the first payment of $100 occurs at time periods 0,1,2, and 3. And the same is true for remaining cash payments
or cell ranges may be passed as follows
=tadIRR( , { -100, 35, 60, 90, -110, 190, 400, 2000000 }, { 4, 26, 12, 365, 365, 4, "INF", 100 } )
=tadIRR( , B2:I2, B3:I3 )
If all of the cash flows have different rates, frequencies, type, compounding frequency of interest, periods of varying lengths, different discounting conventions, various hair cuts then you may enter a m x n array of values to the IRR function as follows
=tadIRR( B1:I1, B2:I2, B3:I3, B4:I4, B5:I5, B6:I6, B7:I7, B8:I8 )
Now that is some Excel IRR function that finds internal rate of return for unlimited scenarios that one comes across in financial analysis.