XIRR Vs IRR continued - This one is a TOUGH question

[BradJB]

Hello,

I have read several threads on the difference between IRR and XIRR however I can not figure out why XIRR and IRR return (slightly) different results. For example: Assume I buy a 1 year, $100 bond with an 8% coupon. The payments would be as follows:

<TABLE style="WIDTH: 265pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=353><COLGROUP><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9472" width=259><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 194pt; HEIGHT: 25.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71 height=34 width=259>Dates</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl72 width=94>Cash Flows</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73 height=17>1/1/10 12:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> $ (100.00)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73 height=17>4/2/10 6:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66> $ 2.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73 height=17>7/2/10 12:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66> $ 2.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73 height=17>10/1/10 6:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66> $ 2.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73 height=17>1/1/11 12:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66> $ 102.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70 height=17>Return calculations</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=17>IRR</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>2.000000000%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=17>IRR adjusted to equivalent annual rate</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>8.243216000%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=17>XIRR</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>8.243909359%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=17>XIRR adjusted to equivalent quarterly rate</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>2.000163342%</TD></TR></TBODY></TABLE>

Notice that I adjusted the payments to fall exactly one quarter of a year apart to eliminate any possible error generated by having some quarters longer than others by a few hours.

Since IRR returns an annual rate and XIRR a quarterly rate I adjusted them so I could make an apples to apples comparison. I used these equations:

Quarterly Rate = ((1+Annual Rate)^(1/4))-1
Annual Rate = ((Quarterly Rate+1)^4)-1

I have tried to think of a way to check which one is correct and came up with this:
<TABLE style="WIDTH: 363pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=482><COLGROUP><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5778" width=158><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 15pt; mso-width-source: userset; mso-width-alt: 731" width=20><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" span=2 width=105><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 119pt; HEIGHT: 25.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70 height=34 width=158>Dates</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71 width=94>Cash Flows</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl72 width=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 79pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73 width=105>IRR</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 79pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl74 width=105>XIRR</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 height=17>1/1/10 12:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67> $ (100.00)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 height=17>4/2/10 6:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69> $ 2.00 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 2.1224160 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 2.1224262 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 height=17>7/2/10 12:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69> $ 2.00 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 2.0808000 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 2.0808067 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 height=17>10/1/10 6:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69> $ 2.00 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 2.0400000 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 2.0400033 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 height=17>1/1/11 12:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69> $ 102.00 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 102.0000000 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 102.0000000 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl69 colSpan=2> Sum of cash flows </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 108.2432160 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76> $ 108.2432361 </TD></TR></TBODY></TABLE>

Since the IRR and XIRR calculation concept assume that all cash payments are reinvested and earn the same return as the computed IRR, each $2.00 dollar payment should be compounded at the quarterly rate until the bond is repaid on 1/1/11. Doing this with the quarterly rate produced by the IRR and XIRR will produce the respective cash flows in the two right hand columns. Summing the cash flows in the IRR column produces $108.2432160 just as one would expect given the IRR equivalent annual rate of 8.243216%. However, XIRR produces $108.2432361 when, given its annual rate, it should produce $108.2439094, a difference of $.0006732.

Does anyone know what the issue is? Why won't XIRR reproduce the correct cash flows?

I have my number of iterations set at the max and the maximum change is .00000000001 so neither of those are the culprit.

Although the actual monetary difference in this case is small, this is a simplification of a real world financing situation I am working on and in that deal the error represents real money.

Any help would be MUCH appreciated.

-Brad

 

[CMLDALLAS]

The XIRR function in Excel assumes DAILY Compounding. For each non-zero cash flow, the user must designate a corresponding date that particular cash flow occurs. All other intermediate cash flows (between user specified dates) are assumed to be zero. The result of the XIRR function is the EFFECTIVE per annum interest rate (or internal rate of return). To convert the XIRR result to a NOMINAL per annum rate you can use the "NOMINAL" function in Excel and assume n=365.

The IRR function in Excel returns the PERIOD INTEREST RATE ("i/n") for a stream of equally spaced cash flows (days, months, quarters, years). To get the NOMINAL per annum rate of return from the IRR function result you must multiple the IRR result by "n", the number of compounding periods per year. To calculate the EFFECTIVE rate of return - you can use the equation (1 + i/n)^n - 1 or use the "EFFECT" function in Excel.

The reason there are slight per annum differences in XIRR (effective rate of return) and IRR (nominal period rate of return) is that the XIRR function uses the exact # of days between cash flows and the IRR function assumes each cash flow period is of equal length (a year is 360 days, a quarter is 90 days, a month is 30 days, etc.).

Hello,

I have read several threads on the difference between IRR and XIRR however I can not figure out why XIRR and IRR return (slightly) different results. For example: Assume I buy a 1 year, $100 bond with an 8% coupon. The payments would be as follows:

[TABLE="width: 265"]
<colgroup><col style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9472" width="259"><col style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width="94"></colgroup><tbody>[TR]
[TD="class: xl71, width: 259, bgcolor: transparent"]Dates[/TD]
[TD="class: xl72, width: 94, bgcolor: transparent"]Cash Flows[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]1/1/10 12:00 AM[/TD]
[TD="class: xl65, bgcolor: transparent"] $ (100.00)[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]4/2/10 6:00 AM[/TD]
[TD="class: xl66, bgcolor: transparent"] $ 2.00 [/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]7/2/10 12:00 PM[/TD]
[TD="class: xl66, bgcolor: transparent"] $ 2.00 [/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]10/1/10 6:00 PM[/TD]
[TD="class: xl66, bgcolor: transparent"] $ 2.00 [/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]1/1/11 12:00 AM[/TD]
[TD="class: xl66, bgcolor: transparent"] $ 102.00 [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Return calculations[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]IRR[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2.000000000%[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]IRR adjusted to equivalent annual rate[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8.243216000%[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]XIRR[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8.243909359%[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]XIRR adjusted to equivalent quarterly rate[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2.000163342%[/TD]
[/TR]
</tbody>[/TABLE]


Notice that I adjusted the payments to fall exactly one quarter of a year apart to eliminate any possible error generated by having some quarters longer than others by a few hours.

Since IRR returns an annual rate and XIRR a quarterly rate I adjusted them so I could make an apples to apples comparison. I used these equations:

Quarterly Rate = ((1+Annual Rate)^(1/4))-1
Annual Rate = ((Quarterly Rate+1)^4)-1

I have tried to think of a way to check which one is correct and came up with this:
[TABLE="width: 363"]
<colgroup><col style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5778" width="158"><col style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width="94"><col style="WIDTH: 15pt; mso-width-source: userset; mso-width-alt: 731" width="20"><col style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" span="2" width="105"></colgroup><tbody>[TR]
[TD="class: xl70, width: 158, bgcolor: transparent"]Dates[/TD]
[TD="class: xl71, width: 94, bgcolor: transparent"]Cash Flows[/TD]
[TD="class: xl72, width: 20, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 105, bgcolor: transparent"]IRR[/TD]
[TD="class: xl74, width: 105, bgcolor: transparent"]XIRR[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"]1/1/10 12:00 AM[/TD]
[TD="class: xl67, bgcolor: transparent"] $ (100.00)[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"]4/2/10 6:00 AM[/TD]
[TD="class: xl69, bgcolor: transparent"] $ 2.00 [/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] $ 2.1224160 [/TD]
[TD="class: xl76, bgcolor: transparent"] $ 2.1224262 [/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"]7/2/10 12:00 PM[/TD]
[TD="class: xl69, bgcolor: transparent"] $ 2.00 [/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] $ 2.0808000 [/TD]
[TD="class: xl76, bgcolor: transparent"] $ 2.0808067 [/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"]10/1/10 6:00 PM[/TD]
[TD="class: xl69, bgcolor: transparent"] $ 2.00 [/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] $ 2.0400000 [/TD]
[TD="class: xl76, bgcolor: transparent"] $ 2.0400033 [/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"]1/1/11 12:00 AM[/TD]
[TD="class: xl69, bgcolor: transparent"] $ 102.00 [/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] $ 102.0000000 [/TD]
[TD="class: xl76, bgcolor: transparent"] $ 102.0000000 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent, colspan: 2"] Sum of cash flows [/TD]
[TD="class: xl76, bgcolor: transparent"] $ 108.2432160 [/TD]
[TD="class: xl76, bgcolor: transparent"] $ 108.2432361 [/TD]
[/TR]
</tbody>[/TABLE]


Since the IRR and XIRR calculation concept assume that all cash payments are reinvested and earn the same return as the computed IRR, each $2.00 dollar payment should be compounded at the quarterly rate until the bond is repaid on 1/1/11. Doing this with the quarterly rate produced by the IRR and XIRR will produce the respective cash flows in the two right hand columns. Summing the cash flows in the IRR column produces $108.2432160 just as one would expect given the IRR equivalent annual rate of 8.243216%. However, XIRR produces $108.2432361 when, given its annual rate, it should produce $108.2439094, a difference of $.0006732.

Does anyone know what the issue is? Why won't XIRR reproduce the correct cash flows?

I have my number of iterations set at the max and the maximum change is .00000000001 so neither of those are the culprit.

Although the actual monetary difference in this case is small, this is a simplification of a real world financing situation I am working on and in that deal the error represents real money.

Any help would be MUCH appreciated.

-Brad