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



"mike allen" wrote:

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