If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MORE XIRR vs. IRR help
I had posted this yesterday and did not recieve a
response - I included prior questions for reference... What I'm trying to do is use the most accurate formula to calculate the return for a series of quarterly payments that are being compounded annually. Why do I get such vastly different numbers when i calculate the IRR and annualize it vs. just the regular xirr? Does the XIRR compound in every period? Also does the IRR and XIRR formula give the effective or nominal result? Thank you very much. -----Original Message----- Suppose IRR returned a (quarterly) rate of 2%. When you try to multiply by four to get the annual rate, you get 8%, which would turn $100 into $108 in a year. But if you invest $100 at 2% quarterly, you get more than $108 after a year, because of the compounding effect. You actually get 100* (1.02)^4 or $108.24. Therefore the effective annual interest rate is 8.24%. This, by the way, should be the same as XIRR, which automatically calculates the annual interest rate. It won't be exact, because IRR would assume all deposits are made 91.25 days apart, which of course couldn't be the case with XIRR. However, XIRR and IRR should be within 5 bps of each other. If not, then your dates are probably out. There are several ways to convert from a nominal (eg, quarterly) to an effective rate (eg, annual). Harlan gave you one. The EFFECT function is another. I like to use the FV function because it helps me think through "how much money would I have after a year if I invested a dollar at this rate?" -- Regards, Fred Please reply to newsgroup, not e-mail wrote in message ... -----Original Message----- "Jen" wrote... I would like to know which function is more accurate to use when calculating the IRR for quarterly cash flows. I know XIRR is to be used for irregular cash flow periods, but I also heard that it is more accurate. When I compare the quarterly cash flow result calculated using IRR and multiplied by 4 to annualize the result, I get a different number (by about 100 basis points) than if I use the XIRR. Also, to make sure, both IRR and XIRR compound on an annual basis correct? XIRR must use some approximations in order to deal with dates, so if your cashflows are all quarterly, you'd be better off using IRR to calculate the quarterly IRR, then converting the result to an annual IRR. -- To top-post is human, to bottom-post and snip is sublime. . Is there a more accurate way of annualizing the quarterly IRR other than multiplying by 4? Thank you. . .. |
#2
|
|||
|
|||
MORE XIRR vs. IRR help
" wrote...
I had posted this yesterday and did not recieve a response . . . ... You did receive many responses, but maybe they're all over your head, or you don't really mean what you wrote above. If your cashflows are regularly spaced, then IRR is likely to be more accurate than XIRR. If the cashflows were quarterly, IRR will return an effective (i.e., compound interest) *QUARTERLY* rate, but XIRR *ALWAYS* returns an effective *ANNUAL* rate. If the resulting rates are 'far' from zero in either direction, then attempting to annualize the IRR result by multiplying it by 4 *SHOULD* produce a resulting rate that isn't close to the XIRR result because 4 times an effective quarterly rate *IS* *NOT* an effective annual rate. YOU NEED TO LEARN THIS! If the correctly annualized IRR result and the XIRR result are still far apart, then are there multiple sign changes in your cashflows? If there are, then there are likely to be multiple IRR/XIRR solutions (IRR/XIRR rates are just zeros of polynomials). It's not inconceivable that in such circumstances that IRR and XIRR could return different rates from multiple, equally valid results. The only way to confirm this is using the resulting rates along with the cashflows in NPV for IRR and XNPV for XIRR. If both NPV/XNPV results are zero, then you have two distinct but equally valid IRR rates. The economically and mathematically obtuse may offer 'guidance' for selecting between them, but either would be as valid and meaningful as the other. -- To top-post is human, to bottom-post and snip is sublime. |
#3
|
|||
|
|||
MORE XIRR vs. IRR help
Thanks. I get it now.
-----Original Message----- " wrote... I had posted this yesterday and did not recieve a response . . . ... You did receive many responses, but maybe they're all over your head, or you don't really mean what you wrote above. If your cashflows are regularly spaced, then IRR is likely to be more accurate than XIRR. If the cashflows were quarterly, IRR will return an effective (i.e., compound interest) *QUARTERLY* rate, but XIRR *ALWAYS* returns an effective *ANNUAL* rate. If the resulting rates are 'far' from zero in either direction, then attempting to annualize the IRR result by multiplying it by 4 *SHOULD* produce a resulting rate that isn't close to the XIRR result because 4 times an effective quarterly rate *IS* *NOT* an effective annual rate. YOU NEED TO LEARN THIS! If the correctly annualized IRR result and the XIRR result are still far apart, then are there multiple sign changes in your cashflows? If there are, then there are likely to be multiple IRR/XIRR solutions (IRR/XIRR rates are just zeros of polynomials). It's not inconceivable that in such circumstances that IRR and XIRR could return different rates from multiple, equally valid results. The only way to confirm this is using the resulting rates along with the cashflows in NPV for IRR and XNPV for XIRR. If both NPV/XNPV results are zero, then you have two distinct but equally valid IRR rates. The economically and mathematically obtuse may offer 'guidance' for selecting between them, but either would be as valid and meaningful as the other. -- To top-post is human, to bottom-post and snip is sublime. . |
Thread Tools | |
Display Modes | |
|
|