On Monday, December 16, 2013 12:56:53 PM UTC+5, wrote:
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.
@Abraham A.
I think you missed out on one crucial aspect of IRR calculation in your Excel IRR function.
You remember the popular phrase.
Two things are for certain. Death and After-Tax cost of debt.
An IRR may be viewed as cost of debt from the perspective of fixed income securities and taxes apply on capital gains. Thus an after-tax IRR is a better measure of an investor's return on investment - ROI.
It would seem that you have now made a provision for tax rates in your IRR calculations as the last time I checked the help pages of your Excel IRR function
http://tadxl.com/excel_irr_function.html it showed input option for a schedule of tax rates for internal rate of return calculations.
Now I played around with your tadXL v3.0 add-in with example data that you use to demonstrate the usage of such financial functions and noted down the following 8 x 9 matrix of data for IRR and NPV calculations.
Rates 4% 2% 5% 3% 2% 4% 3% 6%
Tax Rates 35% 36% 37% 38% 39% 40% 45% 50%
Cash Flows -100 75 150 -100 1000 50000 2000 90000
Frequencies 4 365 24 365 INF 260 INF 5200
Types 1 0 1 0 0 1 0 0
Compoundings 1/4 1/365 1/12 1/365 1 1/26 1 1/52
Periods 1/4 1/365 1/12 1/365 1 1/26 1 1/52
Concentrations 1 1/2 2 10 1 1/2 1 3/4
Hair Cuts 0% 20% 20% 20% 0% 20% 20% 20%
Looking closely at the series of cash flows it would seem you have a series of annuities along with the frequency of payments of such annuities and other related data.
I tried to make sense out of the sequence of such payments and it made sense till the first never-ending payment in amounts of -100.
But how come you have annuity payments that follow the never-ending stream of payments such as the $1,000 annuity, the $50,000 annuity, the $2,000 perpetuity and the $90,000 annuity.
How in this world is it possible to make payments or receive incomes after a never-ending stream of payments (or receipts)?
Putting that aside, I put your cash flows in a narrative shown below
4 quarterly payments in (negative) amount of $100
followed by 365 daily receipts in amount of $75 each
followed by 24 monthly receipts in amount of $150 each
followed by 365 daily payments in (negative) amount of $100 each
followed by infinite annual receipts in amount of $1,000 each
followed by 260 fortnightly receipts in amount of $50,000 each
followed by infinite annual receipts in amount of $2,000 each
followed by 5200 weekly receipts in amount of $90,000 each
I then used your Excel NPV function
http://tadxl.com/excel_npv_function.html that is part of tadXL v3.0
http://tadxl.com using the following net present value formula in an Excel worksheet cell.
=tadNPV( B1:I1, B2:I2, B3:I3, B4:I4, B5:I5, B6:I6, B7:I7, B8:I8, B9:I9 )
It reported a pre-tax NPV in amount of $43,095.08 and an after-tax NPV in amount of $73,949.02
Wow! thats an amazing piece of Excel NPV function that is able to find the net present value of such complex set of data which includes options for receipts and payments after the never-ending stream of payments (or receipts)..
This reminds me of those Mullahs in foreign lands that promise you return on investment - ROI in after life for the deeds you have done on Earth.
From this perspective, it would seem to me that the receipts of money after the never-ending payments are the returns that are promised to you in after life. Obviously to receive such money you would have to wait indefinitely..
And if I looked closely at the formulas that you have used for such NPV calculations, all such large sums of money that are promised after a perpetual income are worth a big resounding ZERO not unless the discount rates dropped to 0% at which point the NPV of such an investment will become infinite. And if the discount rate of -200% were to be applicable then the net present value of such an investment would be nothing but the sum of un-discounted cash flows.
@Abraham A.
Have you ever wondered where you are getting all such ideas from to come up with these fancy financial calculations.
Nope! it's not a divine revelation. It's the program that you are coded with that unfolds as time value of money passes by. Your god is a project manager and you are a subject of an experiment albeit the experiment is carried forward without your consent.
And how much money did you make from tadXL v2.0 that now you are risking your efforts with tadXL v3.0 code named Matrix
And would you still risk the gamble to program tadXL v4.0 code named Cube :)
I been told that PJ Hooker has passed away and his replacement Robert Miller has taken over the vacant position. So should we expect Bob to come along and point out something that I have overlooked in your Excel IRR function
http://tadxl.com/excel_irr_function.html