# Difference between =YIELD and =XIRR

#### realtylerdurdon

##### New Member
Hi all,

this may be a math question, but I hope someone can help: When I use =YIELD I get a different result compared to =XIRR used on the modeled cashflows. When there are more payments per year, IRR goes up and Yield goes down.

Is this, because Yield assumes a zero return (i.e. no re-investing) of the intrayear payments?

Thanks!

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### jj15

##### New Member
i am having the same issue with XIRR and YIELD.

YIELD seems to take coupon payment frequency into account, even with a zero coupon bond. In theory, XIRR and YIELDMAT should be pretty close, save for the 365 v 360 day convention. in my below example, i get diff YIELD results depending on whether i choose 2 (semi) or 1 (annual) coupon periods, even thouugh the example is with a zero coupon bond!

<table x:str="" style="border-collapse: collapse; width: 169pt;" border="0" cellpadding="0" cellspacing="0" width="225"><col style="width: 119pt;" width="159"> <col style="width: 50pt;" width="66"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 119pt;" height="17" width="159">Price</td> <td style="width: 50pt;" x:num="80" align="right" width="66">80.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Par</td> <td x:num="100" align="right">100.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Settlement</td> <td class="xl175" x:num="38353" align="right">1-Jan-05</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Maturity</td> <td class="xl175" x:num="40178" x:fmla="=+B3+365*5" align="right">31-Dec-09</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Coupon</td> <td class="xl174" x:num="0" align="right">0.0%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">YIELD - semi annual</td> <td class="xl177" x:num="4.5155732580295288E-2" x:fmla="=+YIELD(B3,B4,B5,B1,B2,2)" align="right">4.516%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">YIELD - annual</td> <td class="xl177" x:num="4.5665492620166233E-2" x:fmla="=+YIELD(B3,B4,B5,B1,B2,1)" align="right">4.567%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">XIRR</td> <td class="xl177" x:num="4.5639553666114815E-2" x:fmla="=+XIRR(B11:B21,A11:A21)" align="right">4.564%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="38353" x:fmla="=+B3" height="17">1-Jan-05</td> <td x:num="-80" x:fmla="=-B1" align="right">(80.0)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="38533" x:fmla="=+A11+180" height="17">30-Jun-05</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="38717" x:fmla="=+A12+184" height="17">31-Dec-05</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="38898" x:fmla="=+A13+181" height="17">30-Jun-06</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="39082" x:fmla="=+A14+184" height="17">31-Dec-06</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="39263" x:fmla="=+A15+181" height="17">30-Jun-07</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="39447" x:fmla="=+A16+184" height="17">31-Dec-07</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="39629" x:fmla="=+A17+182" height="17">30-Jun-08</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="39813" x:fmla="=+A18+184" height="17">31-Dec-08</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="39994" x:fmla="=+A19+181" height="17">30-Jun-09</td> <td x:num="0" align="right">0.0 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl176" style="height: 12.75pt;" x:num="40178" x:fmla="=+A20+184" height="17">31-Dec-09</td> <td x:num="100" x:fmla="=+B2" align="right">100.0 </td> </tr> </tbody></table>

Replies
28
Views
201
Replies
2
Views
387
Replies
3
Views
596
Replies
0
Views
173
Replies
0
Views
234

1,190,877
Messages
5,983,361
Members
439,840
Latest member
billy1989

### 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.

### Which adblocker are you using?

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

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