IRR vs XIRR for valuing equity
Hello all,
Can somebody please explain the main difference when to use IRR & XIRR? I hear even pros get this mixed up in their models and get in trouble..
Hello all,
Can somebody please explain the main difference when to use IRR & XIRR? I hear even pros get this mixed up in their models and get in trouble..
+94 | Those Of You Who Have Made It To Millionaire Status, What Advice Do You Have? | 36 | 50m | |
+61 | Family Office Exit Ops | 23 | 3h | |
+58 | Etiquette for Giving Notice | 18 | 1d | |
+56 | NYU SPS MSRE Reputation (and others) | 18 | 1h | |
+40 | Assessing / Analyzing Office RE | 5 | 1h | |
+30 | Contemplating job opportunity | 8 | 1d | |
+24 | Learning Resources - CRE Capital Stack | 4 | 8h | |
+20 | Palatine Partners | 4 | 2d | |
+20 | Have few weeks to get ready for RE interview with zero relevant experience | 5 | 2d | |
+20 | Pref/Mezz for LIHTC | 18 | 3d |
Career Resources
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.
always use XIRR to be safe.
Type the formulas into excel and you will see the difference (dates vs. no dates).
we use XIRR in our development models when cash flows are irregular and are monthly
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
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 )
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
Annnnddd I just realized this was a really old thread.
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.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...