J
John C
Um, not to be a stickler, but that is coming up with the security's annual
yield, not the security's annual rate.
yield, not the security's annual rate.
H
Harlan Grove
John C said:
It's easy. It's essentially
Price = x + Rate * y - Rate * z
so
Rate = (Price - x) / (y - z)
where Price is a given,
x = redemption / (1 + yld / frequency) ^ (N - 1 + DSC / E)
y = Sum[k=1..N, 100 / frequency / (1 + yld / frequency) ^ (k - 1 +
DSC / E)]
= 100 / frequency / (1 + yld / frequency) ^ (DSC / E - 1)
* Sum[1..N, (1 + yld / frequency) ^ -k]
z = 100 * A / frequency / E
x and z are straightforward. The latter Sum[] for y can be derived
simply as PV(yld / frequency, N, -1).
J
John C
I see no excel portion of it. I was referring to the algebra in excel format,
but I don't see that.
--
** John C **
but I don't see that.
--
** John C **
Harlan Grove said:
J
John C
Hey, got it figured out, though, I think it fails if the rate is above 99% or
below 1% (untested, and don't care to test, if the OP is still around, he can
take it for what it's worth).
In one more column, type the following in a row 2 cell:
=IF(AND(A$8>=C2,A$8<C3),(A$8-C1)/(C2-C1)/100+B1,"")
copy down as needed. This will find which RATE the result is in between, and
then calculate the percent in between (i.e.: between 5 & 6 % doesn't tell me
it is actually 5.75%).
below 1% (untested, and don't care to test, if the OP is still around, he can
take it for what it's worth).
In one more column, type the following in a row 2 cell:
=IF(AND(A$8>=C2,A$8<C3),(A$8-C1)/(C2-C1)/100+B1,"")
copy down as needed. This will find which RATE the result is in between, and
then calculate the percent in between (i.e.: between 5 & 6 % doesn't tell me
it is actually 5.75%).
J
John C
With SIGNIFICANT HELP from Gary's Student, here is your formula. Format the
cell for percentage. This will give you the interest rate if you (not yield),
if you have all the other pertinent data.
A1=Settlement Date
A2=Maturity Date
A4=Percent Yield
A5=Redemption Value
A6=Frequency
A7=Basis
Your formula for is as follows:
=(A$8-PRICE(A$1,A$2,0%,A$4,A$5,A$6,A$7))/(PRICE(A$1,A$2,2%,A$4,A$5,A$6,A$7)-PRICE(A$1,A$2,1%,A$4,A$5,A$6,A$7))/100
cell for percentage. This will give you the interest rate if you (not yield),
if you have all the other pertinent data.
A1=Settlement Date
A2=Maturity Date
A4=Percent Yield
A5=Redemption Value
A6=Frequency
A7=Basis
Your formula for is as follows:
=(A$8-PRICE(A$1,A$2,0%,A$4,A$5,A$6,A$7))/(PRICE(A$1,A$2,2%,A$4,A$5,A$6,A$7)-PRICE(A$1,A$2,1%,A$4,A$5,A$6,A$7))/100
J
John C
Even better, here is a single formula. Many many thanks to you 
=(A$8-PRICE(A$1,A$2,0%,A$4,A$5,A$6,A$7))/(PRICE(A$1,A$2,2%,A$4,A$5,A$6,A$7)-PRICE(A$1,A$2,1%,A$4,A$5,A$6,A$7))/100
=(A$8-PRICE(A$1,A$2,0%,A$4,A$5,A$6,A$7))/(PRICE(A$1,A$2,2%,A$4,A$5,A$6,A$7)-PRICE(A$1,A$2,1%,A$4,A$5,A$6,A$7))/100
H
Harlan Grove
John C said:
Need spoon feeding? OK.
....
For my own convenience, I'll assume there are the following named
cells with the following initial trial values.
settlement____03/12/2008
maturity______11/15/2019
rate__________3.75%
yield_________4.50%
redemption____100
I'll also note that the OP mentioned frequency of 4 and basis 4
(European 30/360), so I'll hardcode around those.
I'll also assume there are the following names defined as formulas.
N =4*DATEDIF(settlement,maturity,"Y")
+ROUNDUP(DATEDIF(settlement,maturity,"YM")/3,0)
A =90-DAYS360(settlement,DATE(YEAR(settlement),
MONTH(settlement)+MOD(DATEDIF(settlement,maturity,"YM"),3)
+(DAY(settlement)>DAY(maturity)),DAY(maturity)),0)
Note that E = 90 for basis 4 and DSC = E - A, so DSC is redundant.
Then
x = redemption / (1 + yield / 4) ^ (N - A / 90)
y = 100 / frequency * (1 + yield / 4) ^ (A / 90) * PV(yield / 4, N,
-1)
z = 100 / frequency * A / 90
Put it all together, and given Price, Rate is given by the formula
=(Price-redemption/(1+yield/4)^(N-A/90))*0.04
/((1+yield/4)^(A/90)*PV(yield/4,N,-1)-A/90)
The formula
=PRICE(settlement,maturity,rate,yield,redemption,4,4)
given the initial trial values above returns 93.21674378. Naming the
cell containing this formula Price, the formula
=(Price-redemption/(1+yield/4)^(N-A/90))*0.04
/((1+yield/4)^(A/90)*PV(yield/4,N,-1)-A/90)
returns 0.0375, or 3.75%, the annual coupon rate.
Replacing the defined names N and A with the expressions used to
define them in the formula immeidately above I leave as an exercise
for you if you're up to it.
HAND
J
John C
Good for the example, but you start falling off if you have other data. And
this from the person who claims to want to account for all scenarios. What
happens if I change my frequency? Your formula goes to heck. Yes, you said
you hardcoded it, but why would you hardcode it? Why wouldn't you give it the
flexibility?
this from the person who claims to want to account for all scenarios. What
happens if I change my frequency? Your formula goes to heck. Yes, you said
you hardcoded it, but why would you hardcode it? Why wouldn't you give it the
flexibility?
D
Dana DeLouis
Hi. If interested, here is the Price equation, as listed in Excel help,
reversed to give Rate....
Sub Demo()
'// Variables
Dim dsc, e, n, a
Dim Price
Dim dteSett
Dim dteMat
Dim Yld
Dim Redem
Dim f 'frequence
Dim Basis
Dim k 'a constant
Dim Num
Dim Den
'// Fill in given values from example
Price = 94.6343616213221
dteSett = DateSerial(2008, 2, 15)
dteMat = DateSerial(2017, 11, 15)
Yld = 0.065
Redem = 100
f = 2
Basis = 0
'// Calculate other variables.
dsc = WorksheetFunction.CoupDaysNc(dteSett, dteMat, f, Basis)
e = WorksheetFunction.CoupDays(dteSett, dteMat, f, Basis)
n = WorksheetFunction.CoupNum(dteSett, dteMat, f, Basis)
a = WorksheetFunction.CoupDayBs(dteSett, dteMat, f, Basis)
'// Equation..broken up a little...
k = ((f + Yld) / f)
Num = (e * Yld * ((-Redem) * (f + Yld) + f * Price * k ^ (dsc / e + n)))
Den = (100 * ((-a) * Yld * k ^ (dsc / e + n) + e * (f + Yld) * (-1 + k ^
n)))
Debug.Print "Rate: "; Num / Den
End Sub
Returns:
Rate: 0.0575
HTH :>)
Dana DeLouis
reversed to give Rate....
Sub Demo()
'// Variables
Dim dsc, e, n, a
Dim Price
Dim dteSett
Dim dteMat
Dim Yld
Dim Redem
Dim f 'frequence
Dim Basis
Dim k 'a constant
Dim Num
Dim Den
'// Fill in given values from example
Price = 94.6343616213221
dteSett = DateSerial(2008, 2, 15)
dteMat = DateSerial(2017, 11, 15)
Yld = 0.065
Redem = 100
f = 2
Basis = 0
'// Calculate other variables.
dsc = WorksheetFunction.CoupDaysNc(dteSett, dteMat, f, Basis)
e = WorksheetFunction.CoupDays(dteSett, dteMat, f, Basis)
n = WorksheetFunction.CoupNum(dteSett, dteMat, f, Basis)
a = WorksheetFunction.CoupDayBs(dteSett, dteMat, f, Basis)
'// Equation..broken up a little...
k = ((f + Yld) / f)
Num = (e * Yld * ((-Redem) * (f + Yld) + f * Price * k ^ (dsc / e + n)))
Den = (100 * ((-a) * Yld * k ^ (dsc / e + n) + e * (f + Yld) * (-1 + k ^
n)))
Debug.Print "Rate: "; Num / Den
End Sub
Returns:
Rate: 0.0575
HTH :>)
Dana DeLouis
D
Dana DeLouis
If I'm not mistaken, here it is as a function...
Function Price_Rate(Price, dteSett, dteMat, Yld, Redem, F, Basis)
'// Variables
Dim dsc, e, n, a
Dim k 'a constant
Dim Num
Dim Den
'// Calculate other variables.
With WorksheetFunction
dsc = .CoupDaysNc(dteSett, dteMat, F, Basis)
e = .CoupDays(dteSett, dteMat, F, Basis)
n = .CoupNum(dteSett, dteMat, F, Basis)
a = .CoupDayBs(dteSett, dteMat, F, Basis)
End With
k = ((F + Yld) / F)
Num = e * Yld * (F * k ^ (dsc / e + n) * Price - Redem * (F + Yld))
Den = 100 * (e * (k ^ n - 1) * (F + Yld) - a * k ^ (dsc / e + n) * Yld)
Price_Rate = Num / Den
End Function
Sub TestIt()
Dim Price, dteSett, dteMat, Yld, Redem, F, Basis
Price = 94.6343616213221
dteSett = DateSerial(2008, 2, 15)
dteMat = DateSerial(2017, 11, 15)
Yld = 0.065
Redem = 100
F = 2
Basis = 0
Debug.Print Price_Rate(Price, dteSett, dteMat, Yld, Redem, F, Basis)
End Sub
Returns: 0.0575
<snip>
Function Price_Rate(Price, dteSett, dteMat, Yld, Redem, F, Basis)
'// Variables
Dim dsc, e, n, a
Dim k 'a constant
Dim Num
Dim Den
'// Calculate other variables.
With WorksheetFunction
dsc = .CoupDaysNc(dteSett, dteMat, F, Basis)
e = .CoupDays(dteSett, dteMat, F, Basis)
n = .CoupNum(dteSett, dteMat, F, Basis)
a = .CoupDayBs(dteSett, dteMat, F, Basis)
End With
k = ((F + Yld) / F)
Num = e * Yld * (F * k ^ (dsc / e + n) * Price - Redem * (F + Yld))
Den = 100 * (e * (k ^ n - 1) * (F + Yld) - a * k ^ (dsc / e + n) * Yld)
Price_Rate = Num / Den
End Function
Sub TestIt()
Dim Price, dteSett, dteMat, Yld, Redem, F, Basis
Price = 94.6343616213221
dteSett = DateSerial(2008, 2, 15)
dteMat = DateSerial(2017, 11, 15)
Yld = 0.065
Redem = 100
F = 2
Basis = 0
Debug.Print Price_Rate(Price, dteSett, dteMat, Yld, Redem, F, Basis)
End Sub
Returns: 0.0575
<snip>
H
Harlan Grove
John C said:
Number of coupons and basis aren't 'data', they're parameters.
Easy to adapt to different number of uniformly spaced coupons. Add a
defined name freq, then the defined names and formula become
N =freq*DATEDIF(settlement,maturity,"Y")
+ROUNDUP(DATEDIF(settlement,maturity,"YM")*freq/12,0)
A =INT(360/freq)-DAYS360(settlement,DATE(YEAR(settlement),
MONTH(settlement)+MOD(DATEDIF(settlement,maturity,"YM"),12/
freq)
+(DAY(settlement)>DAY(maturity)),DAY(maturity)),0)
=(Price-redemption/(1+yield/freq)^(N-A*freq/360))*freq/100
/((1+yield/freq)^(A*freq/360)*PV(yield/freq,N,-1)-A*freq/360)
As for different bases, can be done, but makes for much more
complexity.
H
Harlan Grove
....Dana DeLouis said:
If you're going to go the VBA route, why not a general udf that could
invert any monotonically increasing function? The following is a very
simplistic binary search approach.
Function invfcnbs( _
f As String, _
y As Double, _
Optional reltol As Double = 0.000001, _
Optional neg As Boolean = False _
) As Variant
'-------------------------------
Const MAXITER As Long = 1000
Dim xlo As Double, xhi As Double, xx As Double
Dim ylo As Double, yhi As Double, yy As Double
Dim n As Long
If InStr(1, f, "$$") = 0 Then
invfcnbs = CVErr(xlErrNull)
Exit Function
End If
'step 1 - bracket x value
xhi = 16
yhi = Evaluate(Replace(f, "$$", CStr(xhi)))
xlo = IIf(neg, -xhi, 1 / xhi)
ylo = Evaluate(Replace(f, "$$", CStr(xlo)))
For n = 1 To MAXITER
If Sgn(yhi - y) * Sgn(y - ylo) = 1 Then Exit For
xhi = xhi * 2
yhi = Evaluate(Replace(f, "$$", CStr(xhi)))
xlo = IIf(neg, -xhi, 1 / xhi)
ylo = Evaluate(Replace(f, "$$", CStr(xlo)))
Next n
If n > MAXITER Then
invfcnbs = CVErr(xlErrNum)
Exit Function
End If
'step 2 - use binary search to find x = inverse_of_f(y)
For n = 1 To MAXITER
xx = (xhi + xlo) / 2
yy = Evaluate(Replace(f, "$$", CStr(xx)))
If Sgn(yy - y) = Sgn(yhi - y) Then
xhi = xx
yhi = yy
Else
xlo = xx
ylo = yy
End If
If Abs(yhi / ylo - 1) < reltol Then Exit For
Next n
invfcnbs = IIf(n <= MAXITER, (xhi + xlo) / 2, CVErr(xlErrNum))
End Function
This could be used to find the coupon rate using
=invfcnbs("=PRICE(settlement,maturity,$$,yield,redemption,1,4)",
PRICE(settlement,maturity,rate,yield,redemption,1,4))
It could also be used to find the yield using
=invfcnbs("=PRICE(settlement,maturity,rate,$$,redemption,1,4)",
PRICE(settlement,maturity,rate,yield,redemption,1,4))
or the redemption amount using
=invfcnbs("=PRICE(settlement,maturity,rate,yield,$$,1,4)",
PRICE(settlement,maturity,rate,yield,redemption,1,4))
A little algebra is generally preferable, but this udf provides a
quick & dirty equivalent to Goal Seek.
J
John C
Yep, fortunately already solved the problem via excel though.