0
$\begingroup$

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?

enter image description here

enter image description here

Thanks!

$\endgroup$
  • $\begingroup$ 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/… $\endgroup$ – noob2 Dec 21 '17 at 15:31
  • $\begingroup$ Thank you. Is it just Actual/365 in the final period? Also, importantly, can we use the YIELD() function in Excel to compute this? $\endgroup$ – equanimity Dec 21 '17 at 15:58
  • 1
    $\begingroup$ The standard day count convention for BTPs (fixed coupon bonds) is Actual/Actual. The screenshot you took actually lists this. $\endgroup$ – Helin Dec 22 '17 at 0:36
2
$\begingroup$

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.