IRR is periodic cash flows (annually monthly, etc) XIRR is irregular cash flows. Makes more sense to use this because cash/earnings are received throughout the year and not at the end of the year.

"Look, you're my best friend, so don't take this the wrong way. In twenty years, if you're still livin' here, comin' over to my house to watch the Patriots games, still workin' construction, I'll fuckin' kill you. That's not a threat, that's a fact.
 

IRR is the periodic rate of return - IRR uses 30/360 day count convention

XIRR is the annual effective yield - XIRR uses Actual/365 day count convention XIRR is itself not a rate of return, it is simply an effective yield The rate of return may be found by using NOMINAL function around XIRR function

150 measures of an investor's return on investment. If you think I am joking, then find out for yourself with tadXL
 

An easier way to explain this is that XIRR accounts for the cash flows when they are received, rather than IRR which values the cash flows at the end of the month. Given the essence of TVM and the concept that present cash flows are worth more than those in the future, XIRR is a more accurate way to calculate the return of cash flows as they occur in real time.

 

As such there is no thing called an XIRR. It is only a name of an Excel financial function that finds IRR given the schedule of transaction dates

Excel IRR itself is a primitive form of internal rate of return calculation that only accepts a series of cash flows

The IRR itself can be re-branded as an XIRR when you allow for defining the length of the payment periods.

There are however a number of options that are desired in an IRR function. These include allowing a schedule of discount rates to find the yield on an investment that pays a periodic return that changes over time

You would also want to allow for possible growth in future earnings

A series of cash flows turn itself into series of annuities or perpetuity when one allows for the frequency of the cash flows

The compounding frequency of interest option in IRR calculation will allow you to find nominal IRR for almost any type of interest compounding

Possible loss of future income ( haircut ) can be entered in to the IRR calculation that will reflect the after loss IRR

For rogue traders allowing rigged rates in IRR calculation would bring bags of cash at the bank

All of the above can be summarized as a matrix of 13 x N data for the purposes of finding an IRR

Rates 3% 5% 4% 2% 5% 6% 7% 8% Growth 0% 0% 0% 0% 0% 0% 0% 0% Tax_Rates 35% 36% 37% 38% 39% 40% 45% 50% Cash_Flows $(100) $200 $300 $(150) $400 $500 $600 $700 Adjust_for_inflation - - - - - - - - Frequencies 4 365 24 365 INF 260 INF 5,200 Types 1 - 1 - - 1 - - Compoundings 1 0.019231 0.5 0.002740 - 0.083333 0.250000 1 Periods 1 0.019231 0.5 0.002740 - 0.083333 0.250000 1 Concentrations 1 0.5 2 10 1 0.5 1 0.75 Hair_Cuts 0% 25% 25% 0% 25% 25% 25% 25% Rate_Rigged_By 0% 20% 20% 0% 20% 20% 20% 20%

=tadIRR( guess, use_rates, rates, inflation, tax_rates, cashflows, adjust_for_inflation, frequencies, types, compoundings, periods, concentrations, hair_cuts, rate_rigged_by )

150 measures of an investor's return on investment. If you think I am joking, then find out for yourself with tadXL
 
Best Response

I am going to try to put this into practical, real world terms for you as it applies to RE financial modeling.

XIRR = continuously compounded annual rate of return; let's just pretend its daily compounded to make our lives simpler. IRR (in excel) = periodic rate of return. How you express it as an annual return is up to you.

Let's say I give you $1 and the next day you give me $1.01. What is my PERIODIC return? Simple, 1%. Now how do I express that as an ANNUAL return? Well, if I were to use daily compounding, my annual return would be (1+1%)^365-1. My period is one day and I am compounding my periodic return daily. Another way to think of it is I get a 1% return on my money each day, based on my daily balance. So today I get 1% on $1.00; tomorrow I get 1% on $1.01, next day 1% on 1.02, and so on.

Go ahead and put that into excel. Today I give $1.00, tomorrow I get $1.01. Using the IRR function, I get my PERIODIC rate of return, which is 1%. To express that as an annual rate of return assuming daily compounding, use the formula above, or to put it into one cell: =(1+IRR(range))^365-1. This will return the same result as using XIRR on the same numbers, because XIRR calculates periodic rate of return for each day and converts that periodic return into an annual return by compounding daily.

Now, going back to the real world, most cash flows in real estate are received monthly. Rent is paid monthly, interest is paid monthly, cash flows are remitted from servicers monthly. Therefor, IRR is usually expressed as a monthly compounded rate of return. To calculate a monthly compounded IRR on a stream of monthly cash flows in excel, use the formula =(1+IRR(range))^12-1. Alternatively, you can use XIRR. As stated earlier, this will actually give you a daily compounded rate of return, but since your periods are spaced one month apart, the result will be very similar.

Where people run in to trouble is calculating IRR based on yearly cash flows, which isn't really fair as you are actually receiving monthly cash flows. Another is they use excel to calculate IRR based on monthly cash flows and multiply it by 12 to get an annual rate of return (i.e., =irr(range) x 12), which does not compound monthly.

Anyway, hope that makes some sense, it took me way longer to type than I thought it would.

 

Let me try once more, IRR and XIRR are both internal rates of return. There are even other IRRs such as IRRSchedule, XIRRSchedule, IncrementalIRR, DecrementalIRR to name a few.

IRR is internal rate of return, all other names mentioned earlier distinguish the way it is calculated.

Your description of XIRR is correct, it is an annual effective yield whereas IRR is the periodic rate of return.

As such no one pays or makes an annual effective yield. Its sole purpose is to allow the consumer to make comparisons between alternative investments. For example a mortgage lender who advertises 7.5% APR does not charge 7.5% interest compounded monthly. The borrower pays an annual interest rate of 7.25% and a monthly interest of 0.604% (the same as IRR)

The way Excel calculates XIRR is by using a Actual/365 day count convention whereas IRR calculation assume 30/360 day count convention. Thus even if you were using monthly cash flows using XIRR there will be a slight difference for the same IRR calculations using IRR function.

Let us take your example of monthly mortgage payments, you can have the IRR return the nominal annual rate this way.

Say you have taken a loan of $500,000 for 30 years and the lender asks for a monthly loan repayment of $1,500. How can you can get the nominal annual rate using IRR

See this where we have defined the length of payment period to be a month =1/12 and the interest compounding to be monthly as well

=tadIRR( ,,,,,, {500000,-1500},, {1,360},, {0.08333333,0.08333333}, {0.08333333,0.08333333} )

This gives the nominal annual rate of 0.518461487%

Whereas the periodic rate of return is 0.043205122%

Which is given by the following where we have used 360 generic periods with generic compounding of interest.

=tadIRR (0.01 ,,,,,, {500000,-1500} ,, {1,360} )

XIRR for this example would have given a rate closer to 0.519695% which is as you stated

( 1+ 0.043205122%)^12 - 1

The annual effective yield calculated from the periodic IRR

150 measures of an investor's return on investment. If you think I am joking, then find out for yourself with tadXL
 

Vitae explicabo saepe autem neque cum. Voluptatum eius explicabo cupiditate. Quia at non velit alias incidunt qui placeat.

Officia et vel quo maxime modi eligendi. Eos non distinctio enim molestiae ut.

Officiis sequi molestiae in nihil repellat pariatur. Voluptas soluta quia similique eum vero quo. Provident est deserunt sed iusto molestiae molestiae. Error a non aut similique reiciendis cumque. Natus reiciendis ipsum a modi amet qui omnis. Quasi alias facilis fuga ut quae culpa.

Alias vel laborum qui. Eum tenetur harum quia voluptas. A omnis repudiandae sed nihil. Facilis voluptate dolorem molestiae quia minus. Ipsam enim minima ex hic dolores. Facilis beatae libero quod fugit consequatur sed et.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
BankonBanking's picture
BankonBanking
99.0
4
Betsy Massar's picture
Betsy Massar
99.0
5
dosk17's picture
dosk17
98.9
6
CompBanker's picture
CompBanker
98.9
7
kanon's picture
kanon
98.9
8
GameTheory's picture
GameTheory
98.9
9
DrApeman's picture
DrApeman
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”