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

• In this govt document (in English) the formula for yield is given on page 19 and an example on page 22. Basically it is actual/365 day count and $P=CF_1 (1+y)^{-t1}+\cdots+CF_n (1+y)^{-tn}$ so it is very simple and standard dt.tesoro.it/export/sites/sitodt/modules/documenti_en/… – noob2 Dec 21 '17 at 15:31
• Thank you. Is it just Actual/365 in the final period? Also, importantly, can we use the YIELD() function in Excel to compute this? – equanimity Dec 21 '17 at 15:58
• The standard day count convention for BTPs (fixed coupon bonds) is Actual/Actual. The screenshot you took actually lists this. – Helin Dec 22 '17 at 0:36

## 1 Answer

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'$.