A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

MORE XIRR vs. IRR help



 
 
Thread Tools Display Modes
  #1  
Old June 8th, 2004, 04:24 PM
external usenet poster
 
Posts: n/a
Default 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  
Old June 8th, 2004, 06:42 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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  
Old June 8th, 2004, 06:48 PM
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:47 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.