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.

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

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.

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

On Tuesday, December 17, 2013 11:57:46 AM UTC+5, Michael Marshall wrote:
On Monday, December 16, 2013 12:56:53 PM UTC+5,
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

Thanks Mike for passing on the baton.

Having read through the series of replies thus far and going through the tadXL v3.0 download for a hands-on demonstration, I have a couple of comments to make

Those who have done business with Abraham A. https://plus.google.com/109666872827367554171/posts call him Honest Abe

This title is so fitting of Abe whose ethics are well known to American society at large dating back to the days when he lived in New York circa. 1992

But in finance, being honest isn't the trademark of traders who make their living by siphoning money from their clients pockets

Finding internal rate of return with Excel IRR function http://tadxl.com/excel_irr_function.html can be made reflective of the greedy mindset of bankers and traders if it were to offer an option to rig the rate

Rate rigging is common practice amongst traders, just look at the news headlines from financial press in recent past and you will find reputable banks and bankers from RBS, UBS, JP Morgan, and others that have admitted to rigging major interest rates such as LIBOR, and EUROBER to make a quick buck

I would suggest that Abe to add a new row for the tadIRR and tadNPV functions to accept values for "Rate Rigged By" as shown below

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%
Rate Rigged by 0% 15% 15% 15% 0% 15% 15% 15%

Doing so will exaggerate the actual internal rate of return and bring down the net present value

I know it will be a hard decision to make for Honest Abe to indulge in such a shady practice, but then those who make a buck don't have any ethics :(

On Wednesday, December 18, 2013 1:27:39 PM UTC+5, Robert Miller wrote:
I would suggest that Abe to add a new row for the tadIRR and tadNPV functions to accept values for "Rate Rigged By" as shown below



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%

Rate Rigged by 0% 15% 15% 15% 0% 15% 15% 15%



Doing so will exaggerate the actual internal rate of return and bring down the net present value



I know it will be a hard decision to make for Honest Abe to indulge in such a shady practice, but then those who make a buck don't have any ethics :(

Thanks Bob but you know we don't Rob people in broad day light, it may be different if its late at night and someone is standing alone in a dark alley!

Bob what you stated about rigging rates reminds me of what Mike was once quoted as saying that is

"There are lies, damn lies and then there are interest rates" - Michael Marshall

The above quote is abstracted from a fairy tale called "Once upon a time in New York" starring Michael Marshall at a much younger age.

Your idea does ring bells and I had to fight my inner self when I decided to include options for rigging rates in Excel IRR function http://tadxl.com/excel_irr_function.html and Excel NPV function http://tadxl.com/excel_npv_function.html

However I will keep my fingers crossed that the Feds don't find out or else the domain http://tadxl.com will be seized by Federal agents and I will be out of business once more :(

Now that I wanted to check the results of NPV using non-riggged and rigged rates, I noted that your assertion of expecting a lower net present value with rigged rates did not come through

See the same schedule of data for our 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%
Rate Rigged By 0% 15% 15% 15% 0% 15% 15% 15%

And using the Excel NPV function as follows passing it all 10 rows of data resulted in a pre-tax non-rigged NPV of $43,095.08 and a pre-tax rigged NPV of $43,466.15.

=tadNPV( B1:I1, B2:I2, B3:I3, B4:I4, B5:I5, B6:I6, B7:I7, B8:I8, B9:I9, B10:I10 )

Pre-tax non-rigged NPV = $43,095.08
Pre-tax rigged NPV = $43,466.15
After-tax non-rigged NPV = $73,949.02
After-tax rigged NPV = $74,192.46

Pre-tax NPV
Rigged NPV Non-rigged NPV
$43,466.15 $43,095.08

After-tax NPV
Rigged NPV Non-rigged NPV
$74,192.46 $73,949.02

I wouldn't doubt your authority on subject of finance, so I investigated the unexpected higher NPV for the rigged rate.

It dawned on me that since expense in payment number 4 in amount of -100 has a hair cut and a rigged rate yet the payment number 5 which is a perpetuity has no hair cut and rigged rate. This led to a lower negative present value of -100 for 365 period yet a higher positive present value of 1000 for infinite payments. Thus the resulting sum that is the net present value of all payments turned out higher in case of the rigged rate.

To confirm your claim, I changed the sign of 4th annuity payment to positive such as 100 and now that I used Excel NPV function http://tadxl.com/excel_npv_function.html it showed a net present value for the rigged rate that is lower than the NPV for the non-rigged rate.

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%
Rate Rigged By 0% 15% 15% 15% 0% 15% 15% 15%

=tadNPV( B1:I1, B2:I2, B3:I3, B4:I4, B5:I5, B6:I6, B7:I7, B8:I8, B9:I9, B10:I10 )

Pre-tax non-rigged NPV = $93,957.24
Pre-tax rigged NPV = $93,285.19
After-tax non-rigged NPV = $127,553.03
After-tax rigged NPV = $127,112.02

Pre-tax NPV
Rigged NPV < Non-rigged NPV
$93,285.19 < $93,957.24

After-tax NPV
Rigged NPV < Non-rigged NPV
$127,112.02 < $127,553.03

Bob, good and evil resides in all of us. It is our choice to select one of them. Remember that company called Google that calls itself "epitome of all good" but you know and I know that such a claim is nothing more than the cover for Vanity Fair magazine.

On Thursday, December 19, 2013 8:53:21 AM UTC+5, Abraham A wrote:

Bob, good and evil resides in all of us. It is our choice to select one of them. Remember that company called Google that calls itself "epitome of all good" but you know and I know that such a claim is nothing more than the cover for Vanity Fair magazine.

Abraham, that's what you call The Devil's own.

Abe, Season's Greetings to you and the other financial engineers at the tad Portal http://thinkanddone.com

New Year is just 8 days ahead before those down under at the Sydney Opera House ring in the year 2014 with fireworks and bottles of champaign in hot weather.

But I would rather enjoy a White Christmas at a farm house in New England that reminds me of the following parody of Robert Frost's famous poem keeping in view that we are analyzing investments using the tadXL add-in v3.0 for Excel 2007, 2010 and 2013.

Whose banks these are, I think I know
His house is in the village though
He will not see me stopping here
To watch his bank vaults fill up with dough

- Bobby Frost

Abe! Wishing you a Merry Christmas and a Happy New Year that will bring in profits that will offset your previous losses that were incurred in the summer of 2013.

Abe, I noticed that in the last few days you have extended the functionality of your Excel NPV function http://tadxl.com/excel_npv_function.html and Excel IRR function http://tadxl.com/excel_irr_function.html to include the effect of inflation on forward looking cash flow amounts. Not only your tadNPV function allows for a growth rate but it also permits you to use annuity payments that are worth their face value or those that are adjusted for inflation.

There are a lot more variables in your tadNPV function now as compared to what you had just a week ago. The following shows the syntax for this Excel NPV function that now accepts up to 12 different set of values. These values may be entered as single numbers or an array of numbers thus making the input array a 12 x N matrix (recall that Matrix is the code name for tadXL v3.0)

=tadNPV( rate, growth, tax_rate, cash flows, adjust_for_inflation, frequency, type, compounding, period, concentration, hair_cut, rate_rigged_by )

And one may either enter the values for this Excel NPV function as a range of cells such as

=tadNPV( B1:D1, B2:D2, B3:D3, B4:D4, B5:D5, B6:D6, B7:D7, B8:D8, B9:D9, B10:D10, B11:D11, B12:D12 )

or one may enter the values for this Excel net present value function as an array of numbers such as

=tadNPV( {0.15, 0.15, 0.15}, {0.1, 0.1, 0.1}, {0.4, 0.4, 0.4}, {1, 1, 1}, {0, 0, 0}, {50, 50, 50}, {0, 0, 0}, {1, 1, 1}, {1, 1, 1}, {1, 1, 1}, {0.25, 0.25, 0.25}, {0.2, 0.2, 0.2} )

All of these options are documented here at http://tadxl.com the home page of tadXL add-in v3.0

I took three annuities in amount of $1 each that all made 50 different payments one after the other thus there were 150 payments in total each in amount of $1

Then I tested your Excel NPV function http://tadxl.com/excel_npv_function.html with a combination of input values to find net present value of such annuities using the face value of $1 or the inflation adjusted $1. The discount rate of 15% was used for discounting the cash flows. A 10% growth rate was applied to these calculations. A tax rate of 40% was due on the annuity payments. A hair cut of 25% was used in some of these calculations. And I rigged the discount rate by 40% to see the difference in net present value.

Oh Boy! I was amazed at the options that you have now built into the tadNPV function that makes the foundation for tadIRR function that finds the internal rate of return.

The first set of data shown below using annuity amounts that have a face value of $1 throughout the different time periods. The first example uses no tax rate, no haircut nor any rigged rate. The NPV is found in amount of $17..849904

rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 0 0 0
cash_flow $1 $1 $1
adjust_for_inflation 0 0 0
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 0 0 0
rate_rigged_by 0 0 0

NPV = $17.849904

If the same set of values were used but we adjusted the $1 annuity payments for inflation, then the net present value of same payments in amount of $1 for 150 periods is $19.974575 which is higher than the sum of $17.849904 that we calculated earlier

This is the same value you would find by using present value interest factor of a growing annuity in amount of $1 using the tadPVIFGA function

Notice that this NPV is higher than the last NPV due to the use of inflation adjusted dollar amounts

rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 0 0 0
cash_flow $1 $1 $1
adjust_for_inflation 0 1 1
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 0 0 0
rate_rigged_by 0 0 0

NPV = $19.974575

If we go back to using the face value for $1 annuity and introduce a tax rate of 40% then the net present value is much higher than $17.849904 and it turns out to be $58.662327

rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 0 0
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 0 0 0
rate_rigged_by 0 0 0

NPV = $58.662327

But if we now switch to using inflation adjusted $1 along with a tax rate of 40% then the NPV results in an amount of $293.484911 which is much higher than the NPV of $19.974575 for the adjusted $1 amounts without the tax rate

rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 1 1
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 0 0 0
rate_rigged_by 0 0 0

NPV = $293.484911

Now if we use the unadjusted $1 amounts but take a 25% hair cut with 40% tax charge then the net present value turns out to be $43.996745 as compared to $58.662327 value without the hair cut.

rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 0 0
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 25% 25% 25%
rate_rigged_by 0 0 0

NPV = $43.996745

Taking a 25% hair cut on inflation adjusted $1 payments with a 40% tax rate returns an NPV of $220.113683 which is considerably lower for the same options without the haircut that amounted to $293.484911

rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 1 1
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 25% 25% 25%
rate_rigged_by 0 0 0

NPV = $220.113683

Now if we do not adjust for inflation and take a tax burden of 40%, along with a hair cut of 25% further rigging the rate by 40% the resulting net present value is $19.928734 that is much lower than the non-rigged NPV of $43.996745

rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 0 0
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 25% 25% 25%
rate_rigged_by 40% 40% 40%

NPV = $19.928734

Finally if we rig the rate by 40% and take a hair cut of 25% and pay a tax obligation of 40% on inflation adjusted $1 payments the NPV turns out to be $27.978728 compare this to the net present value of $220.113683 without the rigged rate.

rate 15% 15% 15%
growth 10% 10% 10%
tax_rate 40% 40% 40%
cash_flow $1 $1 $1
adjust_for_inflation 0 1 1
frequency 50 50 50
type 0 0 0
compounding 1 1 1
period 1 1 1
concentration 1 1 1
haircut 25% 25% 25%
rate_rigged_by 40% 40% 40%

NPV = $27.978728

Now you see how those bankers and traders at large lending banks make a quick buck when they rig the rate by a certain percentage that brings down the net present worth of payments that has to be paid to the clients who gave them their hard earned money all in hopes for return on investment - ROI :(

On Monday, December 23, 2013 5:48:55 PM UTC+5, Robert Miller wrote:
On Thursday, December 19, 2013 8:53:21 AM UTC+5, Abraham A wrote:


Abe, I noticed that in the last few days you have extended the functionality of your Excel NPV function http://tadxl.com/excel_npv_function.html and Excel IRR function http://tadxl.com/excel_irr_function.html to include the effect of inflation on forward looking cash flow amounts. Not only your tadNPV function allows for a growth rate but it also permits you to use annuity payments that are worth their face value or those that are adjusted for inflation.



There are a lot more variables in your tadNPV function now as compared to what you had just a week ago. The following shows the syntax for this Excel NPV function that now accepts up to 12 different set of values. These values may be entered as single numbers or an array of numbers thus making the input array a 12 x N matrix (recall that Matrix is the code name for tadXL v3.0)



=tadNPV( rate, growth, tax_rate, cash flows, adjust_for_inflation, frequency, type, compounding, period, concentration, hair_cut, rate_rigged_by )

The tadNPV function in it's current form will become part of tadXL add-in v3.0 that will be released in January 2014

I have uploaded a video demonstration of this Excel NPV function here at https://plus.google.com/109666872827...ts/ES5HMAGNnf6

Let me know if you have any comments on the way it is being demonstrated in this video

On Thursday, December 26, 2013 11:44:39 PM UTC-8, Abraham A 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



IRR is something of an enigma since there are so many different variants of this particular rate of return. An IRR may be defined and calculated for almost any investment ranging for a lump-sum investment, and those that make periodic 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 return for various investments that range from single sum investment to those that make payments forever and those than make periodic payments for a limited number 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.

On Saturday, January 4, 2014 6:43:56 PM UTC+5, Michael Marshall wrote:
@Abraham A.



I had a look at your NPV equation based upon which you have programmed this Excel IRR function http://tadxl.com/excel_irr_function.html , and it all sounds crazy :D



This is what I found on the notes you showed me yesterday at the tad Finance http://thinkanddone.com offices in mid Manhattan



NPV = Sigma [i = 0 to K or Grand Infinity] { PMT_i x PVIFGA ( r%, g%, N or Baby Infinity ) x PVIF( r%, i) }



PVIFGA ( r%, g%, N or Baby Infinity ) = Sigma [j = 0 to N or Baby Infinity] { (1+g%)^j / (1+r%)^(j+1) }



Looking at the NPV formula it sounds as if you have sum of sums of present value of growing annuities where the outer sum goes from zero payments to N payments or upto Grand Infinity payments.



And the inner sum goes from zero periods upto N periods or upto Baby infinity periods.

@Mike

When I took over the job left vacant by late PJ Hooker I found some notes on his desk. The first one on top showed the same NPV formula as you had given yet since it is complicated to write such formula in a text only editor thus a rewrite of Abraham's NPV equation is shown below.

NPV = Sigma [i = 0 to K or Grand Infinity] { PMT_i x PVIFGA ( r%, g%, N or Baby Infinity ) x PVIF( r%, Sigma N-1 ) }

PVIFGA ( r%, g%, N or Baby Infinity ) = Sigma [j = 0 to N or Baby Infinity] { (1+g%)^j / (1+r%)^(j+1) }

The frequencies of payments in PVIFGA formula goes from 0 to N and the sum of all such N-1 terms is then used as a time period to discount the annuity..

Now PJ Hooker did leave us with one of his own Excel add-ins called njsXL add-in for Excel 2007, 2010 and 2013. http://njsxl.njinstruments.com/

njsXL is a collection of 22 statistical functions for Excel yet as PJ wrote in his note that the baton will be passed to Abraham A. to increase the number of these functions to surpass 100 statistical functions hiterto unavailable in native Excel.

The last note from PJ Hooker had a quote from an infamous recent US President who held the High Office from 1993 till 2000. The quote was directed at Abraham A. who thinks he has a chance of making a buck of his online enterprises that sell Excel add-ins such as tadXL, njsXL and others.

"Insanity is doing the same thing over and over again and expecting a different outcome" - Billy Clinton

To honor such a "great" American leader, PJ Hooker programmed an Excel expected value function http://njsxl.njinstruments.com/excel...-function.html

Now Abraham! you take this Excel EV function to confirm the outcome of your hard work.

On Friday, December 27, 2013 12:44:39 PM UTC+5, Abraham A wrote:
IRR is something of an enigma since there are so many different variants of this particular rate of return.

Abe call it quits. This isn't going to work out as expected.

They deleted your posts from Bytes Magazine. The guy said Microsoft has its own set of financial functions in works. I suppose it took them 24 years to realize there were functions from which money can be made.

Abe, remember when Google dropped your site from SERPS as you refused them to pay for AdWords.

Same story here Abe, no matter how much money these skunks got, they want to double it.