YIELD at par price not equal to Coupon Rate?

[Guest]

Can someone explain why if the settlement date is not equal to a coupon
date, excel returns a yield different from the coupon rate although the
price is 100?

Example:
Settlement : 01 Jun 2005
Maturity : 04 Mar 2020
Coupon : 5%
Price : 100
Redemption : 100
Frequency : 1
Basis : 0

This gives a yield=4.9978%, and of course defeats the other
computations I am making based on that.

That must be basic, and obviously there is something I don't
understand.

Thanks for help.

 

[keepITcool]

since your dates do not match a multiple of a year
there is some date/period rounding involved.

basis decides some of the rounding.

 

[keepITcool]

further to other post:
the datediff must be an exact multiple of frequency

so if frequency =4 then dates which are "exact" quarters
apart will give "5% yield"

01 jun 2005/01 mar 2020
freq 1/basis 0 => .049997..
freq 4/bases 0,1,2,3,4 = >5%

 

[sebas_cp]

Seems more like a Finance question than a excel question. I believe the
answer is in the days used for the accrued coupon. I suggest you look
at the excel help and see the formula that excel uses for the yield
function. The formulaI believe is correct, in the sense that the market
uses them calculate prices that can be agreed by everyone.

Like any other financial model, it does not necessarily give the
"right" answer. So you are free to use your own models or functions to
see if you can make an arbitrage, but to trade in the market your
certainly need to speak the markets language.

 

[Guest]

Hum I tried that (settlement 1 Jun 2005, Maturity 1 Dec 2020 (I also
tried 31/12/20). Basis 4 (or 3 or 2 or 1 or 0). Still got a difference.

 

[keepITcool]

which frequency did you use?

 

[Guest]

You're right, that more a finance question. Since I have another one
(related to this) theoritical problem with IRS and premium
amortization, can you please suggest which is the appropriate
newsgroup?

- Philippe.

 

[sebas_cp]

Seems people here are happy to asnwer you so......

 

[Guest]

I am using frequency=1. I understand that if I put another frequency
and choose a date that is a coupon date, in that case yield will be
equal to coupon rate for a 100 price. My problem is when the settlement
date is *not* a coupon date.

 

[keepITcool]

it appears you are not familiar with compound interest
math.

IF the interest is 1,25% per quarter the annual interest is not 5%
as in Q2 you'll earn interest on the interest of Q1.
thus the annual interest will be slightly higher than 5%.

so if the period between the Issue and Maturity dates
is not an exact multiple of the coupon frequency
you will NEVER get an interest rate that is exactly the coupon's 5%.