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

There are various sorts of internal rate of return calculation

XIRR is an Excel function name that finds IRR for non-periodic cash flows, as such there is no term called XIRR

When calculating IRR you may come across a number of different cases such as

1) Cash flows are all of equal lengths. (thus the Excel IRR function)
2) Actual dates are available for cash flow transactions (thus the Excel XIRR function)

3) Cash flows are periodic but period is not generic, here you would want to define the period such as year, quarter, month, week, day, even biennial, triennial, and so on

4) Cash flows are have a rate schedule at which they are discounted (such as for coupon-bearing bond) for you seek the single discount rate that will set the market value of the bond to its current value

IRR is an IRR, there others are variants of it depending on what sort of data is known about it

See these two links that offer some of these features

Excel IRR function
http://tadxl.thinkanddone.com/excel_irr_function.html

Excel XIRR function
http://tadxl.thinkanddone.com/excel_...ash_flows.html