[....]
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 am 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 -----