Why Excel YIELD Cannot Match Mixed Coupon and Yield Frequencies

Summary

The note addresses whether Excel’s YIELD function can calculate the yield of an Italian government bond that pays coupons semiannually while its yield convention uses annual compounding. It explains that the bond’s price equation must discount each coupon and principal payment using the annual yield convention, while the coupon cash flows still reflect semiannual payments. The two frequencies therefore differ in the pricing calculation.

The answer concludes that Excel’s YIELD function cannot directly represent this convention because it requires coupon frequency and yield compounding frequency to match. The bond example concerns a specific near-maturity settlement and quoted yield, but the response does not show a workaround or independently verify that numerical result. The practical lesson is to check a bond’s market conventions before relying on a spreadsheet function: a function that handles standard coupon schedules may not support a market’s distinct discounting rules.

Key ideas

• Some government bonds pay coupons at a frequency different from the yield compounding frequency.
• Bond price calculations must preserve the bond’s coupon and discounting conventions.
• Excel YIELD requires coupon and yield frequencies to match, so it cannot directly model the described case.
• Spreadsheet yield results depend on whether the function’s assumptions match the bond market convention.

Tags

# Is it possible to use the YIELD() function in Excel to compute the yield of an Italian government bond?


# Is it possible to use the YIELD() function in Excel to compute the yield of an Italian government bond?












I'm interested in using the YIELD() function in Excel to compute the yield of an Italian government bond. The bond in question is:

0.750% 15-January-2018

Italian government bonds have special pricing conventions. According to http://help.derivativepricing.com/1311.htm, yields are calculated using annual compounding even though coupons are semi-annual.

According to http://help.derivativepricing.com/1298.htm, the final period yield method is "compound yield".

If we price the bond at 100 and settle the bond on 12-January-2018 (a few days before the maturity date), is it possible to hack the YIELD() function in Excel to solve for a yield of 0.746739%, as in the second screen print below?

Thanks!

## Answer by Helin (score 2, accepted)

https://quant.stackexchange.com/a/37462

Unfortunately, the answer is no. As you have mentioned, Italian BTPs pay semi-annual coupons, but the discount frequency for yield is annual. The price-yield formula is therefore:

$$ P + AI = \frac{c/f}{(1 + y/f')^{t_0 f'}} + \cdots + \frac{100 + c/f}{(1 + y/f')^{t_nf'}}, $$ where $f = 2$ and $f'=1$.

The `YIELD` function in Excel can only handle scenarios where $f = f'$.

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.