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