excel YIELD vs. XIRR formula

= YIELD ( 30/03/2023, 30/09/2025, 6%, 85,100,2,0)

[....]

= XIRR (CF row, dates row)

[....]

Interest is calculated as =6%*100*YEARFRAC(30/3/23,30/9/23,3)

I will have to double-check some details. I'm a little busy at the moment. But some general comments:

1. For freq=2 or 4 (sub-annual), one major difference is how the coupon return is annualized. By convention, YTM is a nominal rate. In contrast, XIRR returns a compounded rate. When a compounded bond yield is stated, it should be called the "effective annual yield" (EAY). Refer to investopedia articles (click here and click here). Unfortunately, many people -- even knowledgeable people -- fail to make that distinction.
2. Historically, both Excel YIELD and XIRR functions were contributed by third-parties. They were part of the ATP until Excel 2010. When these functions were mainstreamed, it appears that MSFT did not make any effort to make the internal algorithms similar to other financial functions. Just comparing Excel IRR (implemented by MSFT) and XIRR, we can infer that there are differences in the implementation. The same is probably true of the YIELD and XIRR implementations. Take this comment with a grain of salt; it is speculative, and it is not based on any internal knowledge.

More details to follow later.

exceluser133 wrote later:

what would be the underlying mathematical formula for the YIELD formula?

According to the YIELD help page (click here)....

[ERRATA.... I copy-and-pasted the wrong formula previously. Corrected below.]

AFAIK, MSFT does not document the details of the YIELD algorithm where there are multiple coupon periods. The help page says only:

But according to an investopedia (see "click here" above), YIELD should use an IRR algorithm based on the following formula:

And in my experience, simple Excel IRR does indeed calculate results similar to Excel YIELD, at least when the settlement date corresponds to the beginning of the first coupon period, as in your example.

But I have not vetted that formula in detail. With other bond functions, I have found that the documented formula is not exactly the same as the internal implementation.

(FYI, it is not uncommon for MSFT documentation to be inaccurate, at least in detail and sometimes blatantly.)

Sorry about the incessant postings. But I found the time to provide some detail....

I wrote previously:

1. For freq=2 or 4 (sub-annual), one major difference is how the coupon return is annualized. By convention, YTM is a nominal rate. In contrast, XIRR returns a compounded rate.

[....]

And in my experience, simple Excel IRR does indeed calculate results similar to Excel YIELD, at least when the settlement date corresponds to the beginning of the first coupon period, as in your example.

To demonstrate (download the Excel -- click here):

Note the infinitesimal difference between the YIELD result in G2 and the Excel IRR result in G3.

The difference can be explained by (unknowable) differences in the internal iterative algorithm; in particular, the iteration termination conditions -- if they even use the same method.

(Caveat: As I mentioned before, we cannot rely on MSFT documentation to be factually accurate.)

In contrast, note the significant differences between YIELD in G2 and the compounded IRR result in G4 and the XIRR result in G5.

Some ancillary obervations....

1. For your example, I do not agree with the coupon dates that we would derive using COUPNCD and COUPPCD, as demonstrated in B11:B16. I believe the correct coupon dates are in D2:D7. (This requires more investigation.)
2. For your example, I do not agree with your total interest calculation, as demonstrated in B19. IMHO, the correct calculation is demonstrated in B20. But I would not trust YEARFRAC, in general. Instead, I believe the correct calculation is simply =COUPNUM*100*B4/B7 in this special case when the settlement date is the beginning of the coupon period.

the formula using Yield

= YIELD ( 30/03/2023, 30/09/2025, 6%, 85,100,2,0)

For the XIRR formula

= XIRR (CF row, dates row)

Interest is calculated as =6%*100*YEARFRAC(30/3/23,30/9/23,3)

When you change 85 to 100.

Yield get the 6%.

XIRR get 6.07%

Issue could be calculation method in XIRR.

Could you share us a dummy data and your formula? It will help to reproduce your issue and find the cause.

Reference:

XIRR function - Microsoft Support

YIELD function - Microsoft Support

Best Regards,

Snow Lu