UDF to Calculate YTM
in [Programming]
|
From: ryguy7272 on 6 Apr 2010 14:29 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! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''.
From: Joe User on 6 Apr 2010 16:18 "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 '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" wrote: > 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! > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''.
From: ryguy7272 on 6 Apr 2010 17:45 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 > '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" wrote: > > 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! > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''.
From: tompl on 6 Apr 2010 22:25 Very nicely done! Tom
From: Dana DeLouis on 6 Apr 2010 23:53 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 > '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" wrote: >> 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! >> -- >> Ryan--- >> If this information was helpful, please indicate this by clicking ''Yes''. -- = = = = = = = HTH :>) Dana DeLouis
|
Next
|
Last
Pages: 1 2 Prev: Macro Help - Insert row/copy based on criteria Next: Excel and MS Project |