yield code

[mike allen]

I have code for calculating yield from Mangesh Yadav that I can't get to
equal excel's. Does anyone see anything obviously wrong here? I noticed
that he uses "compounding" and "frequency" as inputs, but they appear to be
the same thing. 9/12/05 settlement, 7/15/08 maturity, .06 rate, 95 price,
100 redemption, 2 frequency, 2 compounding should result in 7.9967%, but
this code yields 6.8387%. Assume 0 "basis" (30/360) Thanks, Mike Allen

Function mangeshprice(Settlement, Maturity, Rate, yield, Redemption,
Frequency, Compounding)


If Not (IsEmpty(Settlement) Or IsEmpty(Maturity)) Then

years = (Maturity - Settlement) / 365

If years < 0 Then
Application.StatusBar = "Maturity should be greater than settlement"
Exit Function
End If


' finding the PV of redemption or maturity value
pvRed = Redemption * (1 + yield / Compounding) ^ (-years * Compounding)


' finding the PV of coupon payments

Coupon = Redemption * Rate / Frequency
nosOfCoupons = Int(years * Frequency)
pvCoupon = 0

For i = 1 To nosOfCoupons

term = Coupon * (1 + yield / Compounding) ^ (-years * Compounding)
pvCoupon = pvCoupon + term

Next

mangeshprice = pvCoupon + pvRed

End If

End Function

Function mangeshyield(Settlement, Maturity, Rate, Price, Redemption,
Frequency, Compounding)

lowValue = -0.2
hiValue = 1
yield = 0.05

comp = mangeshprice(Settlement, Maturity, Rate, yield, Redemption,
Frequency, Compounding)

If Not IsEmpty(comp) Then

Do While (Abs(Price - comp) > 0.001)
yield = (lowValue + hiValue) / 2
comp = mangeshprice(Settlement, Maturity, Rate, yield, Redemption,
Frequency, Compounding)
If (Price < comp) Then
lowValue = yield
Else
hiValue = yield
End If
i = i + 1
If (i > 100) Then
Exit Function
End If
Loop

End If

mangeshyield = yield

End Function

 

[Patrick Molloy]

re your code. First, given that you define the day count as 30/360 , your
calculation for years..
years = (Maturity - Settlement) / 365
is obviously wrong.
Now yuo state that the compounding is '2'. This means that you need to
calculate i for each period. a 5 year bond will have 10 periods. So in your
code you need to calculate the correct value for the term and then each
payment will be 6%/2 ie 3%
The redemtion value is only received at maturity

PVn = c/(1+i)^1 + c/(1+i)^2 +... C/(1+i)^n +100/(1+i)^n
where c = Coupon / Frequency
n = number of payments
PVn is the value at the next coupon date
PV = PVn/(1+i)^f
where f is the fraction of a period from settlement to the next coupon
payment.
Where PV equals the Price, then i is the rate to get the yield , ie 2xi for
semi-annual

HTH

 

[Patrick Molloy]

frequency defines how many times a year that a bond coupon gets paid, once a
year is Annual and twice a year is semi-annual. Some pay quarterly, monthly
etc, but in the USA bonds are normally semi-annual, ie their coupon is paid
twice a year. A bond with a 6% semi-annual coupon thus pays 3% twice.

With regards calculating the yield, semi-annual means the discount rate per
period is doubled. thus if i is the discount rate per period then the yield
'semi-annual' is 2xi
This could be re-stated as an Annual yield (1+i)^2 -1
Other factors to consider are day-count. US corporate bonds accrue interest
on a 30 day month, 360 day year, while US Govt bonds work on an Actual day /
365 basis.

I hope that what this makes clear is that 'semi-annual' is used here in two
different contexts. One is th ecoupon payment frequency and the other in the
yield calculation methodology.
Importantly then, bonds with any kind of frequency can have their yields
quoted using any kind of methodology. However, one must compare like-to-like
to rationalise the investement correctly.
For example a USD Eurobond yielding 6% is in fact a worse rate than a USD
domestic also yielding 6%. How come? well the eurobond, by default is quoted
as an annual yield. The domestic is quoted semi-annual. Now convert 6%
semi-annual to annual and it will yield 6.09% , or 9 basis points over the
eurobnd.

HTH