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!
PLEASE HELP!!!
<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>