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!

Improve this question
$\endgroup$
3
  • $\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$
    – nbbo2
    Dec 21, 2017 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, 2017 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, 2017 at 0:36

1 Answer 1

Reset to default
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'$.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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