If I go to this site:
http://www.moneychimp.com/articles/f.../fmbondytm.htm

Look at the Example:
YTM = 7.37%
The formula seems simple enough, so I tried to write my own function:


Function YieldMaturity(C, Y, n, P, R)

Dim sumall As Double
Dim j As Integer

sumall = 0
For j = 1 To n
sumall = sumall + ((C / (1 + Y) ^ n))
Next j

YieldMaturity = ((sumall + (P / (1 + Y) ^ n)) / R)

End Function

I use these inputs:
Nominal Coupon Rate = 70
Interest = 7%
Bond Price = 925.6
Redemption = 1000
Years = 4

Here's me calling the function:
=YieldMaturity(B7,B8,B11,B9,B10)



I get a result of 93.84%

Can someone please tell me what's wrong with my function? I suspect it is
the summing part.

Thanks!
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"ryguy7272" wrote:
sumall = sumall + ((C / (1 + Y) ^ n))
[....]
Can someone please tell me what's wrong with my function?

To begin with, that should be ^i instead of ^n.

But I think you fundamentally misunderstand the algorithm. You are supposed
to derived R.

See the following implementation. The result closely matches the result of
the MoneyChimp pop-up calculator for the example in the aritcle (price $950,
par $1000, interest 7% over 4 years).

I use a binary search algorithm to derive YTM (r). There are better
algorithms. I'm sure Dana will offer one.

-----
UDF

'c(1 + r)-1 + c(1 + r)-2 + . . . + c(1 + r)-n + B(1 + r)-n = P
'whe
'c = annual coupon payment (in dollars, not a percent)
'n = number of years to maturity
'B = par value
'P = purchase price

Option Explicit

'variable result to allow for #NUM result
Function myYTM(intrate As Double, P As Double, B As Double, n As Integer)
Dim pv As Double, c As Double, r As Double, i As Integer
Dim hi As Double, lo As Double, r0 As Double

On Error GoTo myError
c = B * intrate
hi = 2 * intrate
lo = intrate
r0 = 0
Do
'interatively select r until pv is "close to" zero
r = (hi + lo) / 2
pv = 0
For i = 1 To n
pv = pv + 1 / (1 + r) ^ i
Next i
pv = c * pv + B / (1 + r) ^ n - P
If Abs(pv) < 0.005 Then Exit Do
If r = r0 Then GoTo myError
If pv < 0 Then hi = r Else lo = r
r0 = r
Loop
myYTM = r
Exit Function

myError:
myYTM = CVErr(xlErrNum)
End Function


----- original message -----

"ryguy7272" wrote:
If I go to this site:
http://www.moneychimp.com/articles/f.../fmbondytm.htm

Look at the Example:
YTM = 7.37%
The formula seems simple enough, so I tried to write my own function:


Function YieldMaturity(C, Y, n, P, R)

Dim sumall As Double
Dim j As Integer

sumall = 0
For j = 1 To n
sumall = sumall + ((C / (1 + Y) ^ n))
Next j

YieldMaturity = ((sumall + (P / (1 + Y) ^ n)) / R)

End Function

I use these inputs:
Nominal Coupon Rate = 70
Interest = 7%
Bond Price = 925.6
Redemption = 1000
Years = 4

Here's me calling the function:
=YieldMaturity(B7,B8,B11,B9,B10)



I get a result of 93.84%

Can someone please tell me what's wrong with my function? I suspect it is
the summing part.

Thanks!
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

That's right to four decimal places, which is fine for me!! Thanks so much!!!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joe User" wrote:

"ryguy7272" wrote:
sumall = sumall + ((C / (1 + Y) ^ n))
[....]
Can someone please tell me what's wrong with my function?

To begin with, that should be ^i instead of ^n.

But I think you fundamentally misunderstand the algorithm. You are supposed
to derived R.

See the following implementation. The result closely matches the result of
the MoneyChimp pop-up calculator for the example in the aritcle (price $950,
par $1000, interest 7% over 4 years).

I use a binary search algorithm to derive YTM (r). There are better
algorithms. I'm sure Dana will offer one.

-----
UDF

'c(1 + r)-1 + c(1 + r)-2 + . . . + c(1 + r)-n + B(1 + r)-n = P
'whe
'c = annual coupon payment (in dollars, not a percent)
'n = number of years to maturity
'B = par value
'P = purchase price

Option Explicit

'variable result to allow for #NUM result
Function myYTM(intrate As Double, P As Double, B As Double, n As Integer)
Dim pv As Double, c As Double, r As Double, i As Integer
Dim hi As Double, lo As Double, r0 As Double

On Error GoTo myError
c = B * intrate
hi = 2 * intrate
lo = intrate
r0 = 0
Do
'interatively select r until pv is "close to" zero
r = (hi + lo) / 2
pv = 0
For i = 1 To n
pv = pv + 1 / (1 + r) ^ i
Next i
pv = c * pv + B / (1 + r) ^ n - P
If Abs(pv) < 0.005 Then Exit Do
If r = r0 Then GoTo myError
If pv < 0 Then hi = r Else lo = r
r0 = r
Loop
myYTM = r
Exit Function

myError:
myYTM = CVErr(xlErrNum)
End Function


----- original message -----

"ryguy7272" wrote:
If I go to this site:
http://www.moneychimp.com/articles/f.../fmbondytm.htm

Look at the Example:
YTM = 7.37%
The formula seems simple enough, so I tried to write my own function:


Function YieldMaturity(C, Y, n, P, R)

Dim sumall As Double
Dim j As Integer

sumall = 0
For j = 1 To n
sumall = sumall + ((C / (1 + Y) ^ n))
Next j

YieldMaturity = ((sumall + (P / (1 + Y) ^ n)) / R)

End Function

I use these inputs:
Nominal Coupon Rate = 70
Interest = 7%
Bond Price = 925.6
Redemption = 1000
Years = 4

Here's me calling the function:
=YieldMaturity(B7,B8,B11,B9,B10)



I get a result of 93.84%

Can someone please tell me what's wrong with my function? I suspect it is
the summing part.

Thanks!
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Very nicely done!

Tom

Hi. Not any better, but it did converge in 6 loops. :0
The UL is there in case the solution cycles between two numbers that
don't exactly match)


Sub Testit()
'0.0853
Debug.Print MyYTM(0.07, 1000, 950, 4)
End Sub


Function MyYTM(Y, B, P, n)

Dim r
Dim c
Dim k
Dim z
Dim UL ' Upper Limit safety check

c = B * Y
r = 0.1 'Default Guess

Do While z < r And UL < 20
z = r
k = 1 + r
r = r - (r * k * (c - B * r + k ^ n * (P * r - c))) / _
(c * k ^ (1 + n) + B * r ^ 2 * n - c * (k + r * n))
UL = UL + 1
Loop

MyYTM = r
End Function

HTH :)
Dana DeLouis



On 4/6/2010 4:18 PM, Joe User wrote:
"ryguy7272" wrote:
sumall = sumall + ((C / (1 + Y) ^ n))
[....]
Can someone please tell me what's wrong with my function?

To begin with, that should be ^i instead of ^n.

But I think you fundamentally misunderstand the algorithm. You are supposed
to derived R.

See the following implementation. The result closely matches the result of
the MoneyChimp pop-up calculator for the example in the aritcle (price $950,
par $1000, interest 7% over 4 years).

I use a binary search algorithm to derive YTM (r). There are better
algorithms. I'm sure Dana will offer one.

-----
UDF

'c(1 + r)-1 + c(1 + r)-2 + . . . + c(1 + r)-n + B(1 + r)-n = P
'whe
'c = annual coupon payment (in dollars, not a percent)
'n = number of years to maturity
'B = par value
'P = purchase price

Option Explicit

'variable result to allow for #NUM result
Function myYTM(intrate As Double, P As Double, B As Double, n As Integer)
Dim pv As Double, c As Double, r As Double, i As Integer
Dim hi As Double, lo As Double, r0 As Double

On Error GoTo myError
c = B * intrate
hi = 2 * intrate
lo = intrate
r0 = 0
Do
'interatively select r until pv is "close to" zero
r = (hi + lo) / 2
pv = 0
For i = 1 To n
pv = pv + 1 / (1 + r) ^ i
Next i
pv = c * pv + B / (1 + r) ^ n - P
If Abs(pv)< 0.005 Then Exit Do
If r = r0 Then GoTo myError
If pv< 0 Then hi = r Else lo = r
r0 = r
Loop
myYTM = r
Exit Function

myError:
myYTM = CVErr(xlErrNum)
End Function


----- original message -----

"ryguy7272" wrote:
If I go to this site:
http://www.moneychimp.com/articles/f.../fmbondytm.htm

Look at the Example:
YTM = 7.37%
The formula seems simple enough, so I tried to write my own function:


Function YieldMaturity(C, Y, n, P, R)

Dim sumall As Double
Dim j As Integer

sumall = 0
For j = 1 To n
sumall = sumall + ((C / (1 + Y) ^ n))
Next j

YieldMaturity = ((sumall + (P / (1 + Y) ^ n)) / R)

End Function

I use these inputs:
Nominal Coupon Rate = 70
Interest = 7%
Bond Price = 925.6
Redemption = 1000
Years = 4

Here's me calling the function:
=YieldMaturity(B7,B8,B11,B9,B10)



I get a result of 93.84%

Can someone please tell me what's wrong with my function? I suspect it is
the summing part.

Thanks!
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


--
= = = = = = =
HTH :)
Dana DeLouis

Just to double check...

Sub Easier()
Dim Cash
Cash = Array(-950, 70, 70, 70, 70 + 1000)
Debug.Print WorksheetFunction.IRR(Cash)
End Sub

' 8.52736277081097E-02


On 4/6/2010 11:53 PM, Dana DeLouis wrote:
Hi. Not any better, but it did converge in 6 loops. :0
The UL is there in case the solution cycles between two numbers that
don't exactly match)


Sub Testit()
'0.0853
Debug.Print MyYTM(0.07, 1000, 950, 4)
End Sub


Function MyYTM(Y, B, P, n)

Dim r
Dim c
Dim k
Dim z
Dim UL ' Upper Limit safety check

c = B * Y
r = 0.1 'Default Guess

Do While z < r And UL < 20
z = r
k = 1 + r
r = r - (r * k * (c - B * r + k ^ n * (P * r - c))) / _
(c * k ^ (1 + n) + B * r ^ 2 * n - c * (k + r * n))
UL = UL + 1
Loop

MyYTM = r
End Function

HTH :)
Dana DeLouis



On 4/6/2010 4:18 PM, Joe User wrote:
"ryguy7272" wrote:
sumall = sumall + ((C / (1 + Y) ^ n))
[....]
Can someone please tell me what's wrong with my function?

To begin with, that should be ^i instead of ^n.

But I think you fundamentally misunderstand the algorithm. You are
supposed
to derived R.

See the following implementation. The result closely matches the
result of
the MoneyChimp pop-up calculator for the example in the aritcle (price
$950,
par $1000, interest 7% over 4 years).

I use a binary search algorithm to derive YTM (r). There are better
algorithms. I'm sure Dana will offer one.

-----
UDF

'c(1 + r)-1 + c(1 + r)-2 + . . . + c(1 + r)-n + B(1 + r)-n = P
'whe
'c = annual coupon payment (in dollars, not a percent)
'n = number of years to maturity
'B = par value
'P = purchase price

Option Explicit

'variable result to allow for #NUM result
Function myYTM(intrate As Double, P As Double, B As Double, n As Integer)
Dim pv As Double, c As Double, r As Double, i As Integer
Dim hi As Double, lo As Double, r0 As Double

On Error GoTo myError
c = B * intrate
hi = 2 * intrate
lo = intrate
r0 = 0
Do
'interatively select r until pv is "close to" zero
r = (hi + lo) / 2
pv = 0
For i = 1 To n
pv = pv + 1 / (1 + r) ^ i
Next i
pv = c * pv + B / (1 + r) ^ n - P
If Abs(pv)< 0.005 Then Exit Do
If r = r0 Then GoTo myError
If pv< 0 Then hi = r Else lo = r
r0 = r
Loop
myYTM = r
Exit Function

myError:
myYTM = CVErr(xlErrNum)
End Function


----- original message -----

"ryguy7272" wrote:
If I go to this site:
http://www.moneychimp.com/articles/f.../fmbondytm.htm

Look at the Example:
YTM = 7.37%
The formula seems simple enough, so I tried to write my own function:


Function YieldMaturity(C, Y, n, P, R)

Dim sumall As Double
Dim j As Integer

sumall = 0
For j = 1 To n
sumall = sumall + ((C / (1 + Y) ^ n))
Next j

YieldMaturity = ((sumall + (P / (1 + Y) ^ n)) / R)

End Function

I use these inputs:
Nominal Coupon Rate = 70
Interest = 7%
Bond Price = 925.6
Redemption = 1000
Years = 4

Here's me calling the function:
=YieldMaturity(B7,B8,B11,B9,B10)



I get a result of 93.84%

Can someone please tell me what's wrong with my function? I suspect
it is
the summing part.

Thanks!
--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.




--
= = = = = = =
HTH :)
Dana DeLouis