Yield vs XIRR Discrepancy

L

loki9x

New Member
Joined
May 5, 2009
Messages
1
I input the following information for a standard semi-annual 30/360 bond and calculated the Yield. I then tried to replicate the Yield using a string of cash flows and XIRR, but the numbers differed. I've also experimented with converting one or the other from semi-annual to annual or vis-a-versa and that doesn't seem to solve the problem. It's also possible that I've miscalculated the cash flows or accrued interest. Could someone please help explain how these calculations differ and how to align them? Thank you.

<TABLE style="WIDTH: 111pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=147><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20 width=78>Par</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl67 width=69 align=right>100.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>Price</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl67 align=right>73.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>Yield</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl66 align=right>13.128% </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>XIRR</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl66 align=right>13.544% </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>Coupon</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl68 align=right>5.875% </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>Frequency</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl67 align=right>2.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>Issue Date</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl69 align=right>08/13/04</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20>First Pay</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 align=right>02/13/05</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" height=20></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>02/15/09</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl69 height=20 align=right>05/05/09</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>- </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>05/08/09</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>(73.00)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>05/08/09</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>(1.35)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>08/15/09</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>02/15/10</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>08/15/10</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>02/15/11</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>08/15/11</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>02/15/12</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>08/15/12</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>02/15/13</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>08/15/13</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>02/15/14</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>2.94 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl70 height=20 align=right>08/15/14</TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; BACKGROUND-COLOR: transparent; BORDER-TOP: #ffffff; BORDER-RIGHT: #ffffff" class=xl65 align=right>102.94 </TD></TR></TBODY></TABLE>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
352
esben
E
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
839
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
R
  • Question
Macro not working after adding two new items
Replies
4
Views
112
myall_blues
myall_blues

Forum statistics

Threads
1,215,086
Messages
6,123,038
Members
449,092
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top