UDF to Calculate YTM

R

ryguy7272

If I go to this site:
http://www.moneychimp.com/articles/finworks/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!
 
J

Joe User

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

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
'where:
'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 -----
 
R

ryguy7272

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 said:
ryguy7272 said:
sumall = sumall + ((C / (1 + Y) ^ n)) [....]
Can someone please tell me what's wrong with my function?
Click to expand...

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
'where:
'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 said:
If I go to this site:
http://www.moneychimp.com/articles/finworks/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!
Click to expand...
Click to expand...
 
D

Dana DeLouis

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



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

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
'where:
'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 said:
If I go to this site:
http://www.moneychimp.com/articles/finworks/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!
Click to expand...
Click to expand...
 
D

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


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



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

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
'where:
'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 said:
If I go to this site:
http://www.moneychimp.com/articles/finworks/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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2007 Rect type problem 1
Excel seems to hang while running a UDF 0
Need to reconcile numbers accounting Harlan Grove code doesn't work for negative numbers 2
Outlook macro - MAPIFolder looping extremely slow 0
dragging array UDFs 1
Help needed to write UDF to calculate value based on 8 conditions 2
Help required to solve a linear equation through excel 1
The object invoked as disconnected from its clients. - To many she 3

Top