yield function
mike allen
a 'yield' function that allows 1, 2, or 4 compound periods per year (I need
12). I have a custom function written by a friend that calculates yield,
but it too, does not calculate monthly compounding. In fact, it is only
accurate semi-annually and is inaccurate outside of that.
My question is: can I get monthly compounding for the 'yield' or 'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank you,
Mike Allen
Jim Thomlinson
Money Calculations you can just increase the number of periods as years * 12
(months) and decrease the interest rate by Interest / 12 (months). This
should change to 12 compounding periods per year.
HTH
Don Guillett
--
Don Guillett
SalesAid Software
don...@281.com
"mike allen" <mikea...@charter.net> wrote in message
news:%23MO5H9R...@TK2MSFTNGP15.phx.gbl...
Don Guillett
pv = current price of bond
fv = 100 (maturity value)
pmt = coupon payment.
--
Don Guillett
SalesAid Software
don...@281.com
"Don Guillett" <don...@281.com> wrote in message
news:e8plHYS6...@TK2MSFTNGP12.phx.gbl...
mike allen
but, i did get 'RATE' to work. do you know if it is usable in code ('YIELD'
and 'PRICE' are not)? i'll look into it. thanks, mike allen
"Don Guillett" <don...@281.com> wrote in message
news:u$42GdS6E...@TK2MSFTNGP15.phx.gbl...
mike allen
not sure about issues such as accrued interest due to non-even dates:
12/23/04 settlement, 4/3/09 maturity, 7%coupon, monthly payments...
any thoughts? thanks, mike allen
"Don Guillett" <don...@281.com> wrote in message
news:u$42GdS6E...@TK2MSFTNGP15.phx.gbl...
Myrna Larson
VBE, set a reference to it at Tools/References.
On Thu, 23 Dec 2004 14:37:40 -0600, "mike allen" <mikea...@charter.net>
wrote:
mike allen
use 'yield' and 'price' in code. i still need to figure 12 compound periods
per year on bonds in code. 'rate' function somewhat accomplishes this, but
not on bonds w/ accrued interest (i don't think).
Can I get the actual code for YIELD and PRICE? if so, maybe i can alter it
to accept compounding periods other than 1, 2, and 4. I don't guess there
is a setting, add-in, preference, etc. i can set to allow 12 compounds/yr?
thank you very much, mike allen
"Myrna Larson" <anon...@discussions.microsoft.com> wrote in message
news:hrans09sfu1jlreua...@4ax.com...
Myrna Larson
friend. Can you modify that? Perhaps you can find and fix the bug while you're
at it.
OTOH, here's some information from a David Garlock's "Federal Income Taxation
of Debt Instruments" on yield calculations. It requires listing the amounts on
a worksheet. I have paraphrased his example below:
The situation is a $1 million bond with 5 annual payments of 100,000, 300,000,
500,000, 500,000 and 300,000. There is an initial one-month delay in the
receipt of the payments, so they will be received at the end of months 13, 25,
37, 49, and 61. The solution Garlock gives is this:
In cell D1, put an initial guess for the MONTHLY yield to maturity, (say
1.5%). Put the 5 positive cash flows in cells D3:D7. In E3:E7, put a formula
for the present value of the amount in the adjacent cell, discounting at the
rate in D1 for the appropriate number of months. For example, the first
formula would be =-PV(D$1,13,0,D3).
[In the second formula, replace the 13 with 25, etc. With the layout as
suggested, you could change the formula to
=-PV(D$1,(ROW()-2)*12+1,0,D3)
and just copy it down through E7.]
In cell E8, the formula is =SUM(E3:E7). This will give the value 939,425. Now
use Goal Seek, setting cell E8 to the value 1,000,000 by changing cell D1. The
result -- the monthly yield -- is 1.3347289%. Convert that to annual yield by
multiplying by 12.
Whether this information will help with a VBA solution depends on whether you
can use Goal Seek with VBA arrays rather than worksheet ranges. AFAIK, you
can't.
I also tried putting the amounts and dates of the original purchase and coupon
payments in adjacent columns, then used the XIRR function. That gave me 17.26%
(vs 16.03% with Garlock's method).
Maybe one of the financial gurus can explain the reason for the difference.
BTW, I have MS's document providing not the actual code (which would probably
be written in C, not VBA), but the formulas which are solved. For the rate
functions, they show a formula involving rate, number of periods, payment, PV,
FV and 'type' which, if the values for all arguments are correct, will
evaluate to 0.
If one of these variables is unknown and the others known, this formula can be
used with iteration to solve for the unknown parameter: they start with a
guess for that value, and keep changing it until the result of the formula is
0.
The trick to this to minimize the iterations by the method you use to change
the value for the guessed parameter for the next iteration. Usually that's a
Newton-Raphson method, which in turn requires knowing the derivative of the
function.
If you are interested in this documentation, I can email it to you. AIR, you
can't find it on the MS web site any more.
On Fri, 24 Dec 2004 10:10:23 -0600, "mike allen" <mikea...@charter.net>
Myrna Larson
YIELD function), but allows coupon frequencies of 1, 2, 3, 4, 6, or 12. It
supports only 0 and 1 (30/360 and actual/actual) for the day count basis (last
argument). The arguments are the same, and in the same order, as those for
Excel's YIELD. If any arguments are invalid, it returns text telling you where
the 1st error is.
The code is based on the formulas that Excel's Help shows for the YIELD and
PRICE functions. I don't know what the derivative of the PRICE function is, so
I used the same logic as in a binary search to select the yield value for the
next iteration. (Maybe Harlan can improve on this!)
For coupon frequencies of 1, 2, or 4, the result is typically identical to
Excel's for 7 decimal places (i.e. 0.#######).
12 coupons don't increase the yield much compared to 4. The difference is
typically less than 0.00001.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Type BondInfoType
'supplied parameters
Settlement As Date
Maturity As Date
Rate As Double
Price As Double
Redemption As Double
Frequency As Long
Basis As Long
'calculated parameters
Coupon As Double
NumCoupons As Long
FraxPeriod As Double
AccrInt As Double
End Type
Function BondYield(Settlement As Date, Maturity As Date, _
Rate As Double, Price As Double, Redemption As Double, _
Frequency As Long, Optional Basis As Long = 0) As Variant
Dim BondInfo As BondInfoType
Dim Diff As Double
Dim i As Long
Dim MaxYield As Double
Dim MinYield As Double
Dim Msg As String
Dim Yld As Double
Const Accuracy As Double = 0.0001
Const MaxIterations As Long = 200
With BondInfo
.Settlement = Settlement
.Maturity = Maturity
.Rate = Rate
.Price = Price
.Redemption = Redemption
.Frequency = Frequency
.Basis = Basis
End With
If CheckArguments(BondInfo, Msg) = False Then
BondYield = Msg
Exit Function
End If
CalculateRemainingParameters BondInfo
With BondInfo
If .NumCoupons = 1 Then
Yld = YieldWith1Coupon(BondInfo)
Else
MinYield = -1#
MaxYield = .Rate
If MaxYield = 0 Then MaxYield = 0.1
Do While CalculatedPrice(BondInfo, MaxYield) > .Price
MaxYield = MaxYield * 2
Loop
Yld = 0.5 * (MinYield + MaxYield)
For i = 1 To MaxIterations
Diff = CalculatedPrice(BondInfo, Yld) - .Price
If Abs(Diff) < Accuracy Then Exit For
'if calculated price is greater, correct yield is greater
If Diff > 0 Then MinYield = Yld Else MaxYield = Yld
Yld = 0.5 * (MinYield + MaxYield)
Next i
End If
BondYield = Yld
End With
End Function 'BondYield
Function BondPrice(Settlement As Date, Maturity As Date, _
Rate As Double, Yield As Double, Redemption As Double, _
Frequency As Long, Optional Basis As Long = 0) As Variant
Dim BondInfo As BondInfoType
Dim Msg As String
With BondInfo
.Settlement = Settlement
.Maturity = Maturity
.Rate = Rate
.Price = 100 'dummy value for CheckArguments
.Redemption = Redemption
.Frequency = Frequency
.Basis = Basis
End With
If CheckArguments(BondInfo, Msg) = False Then
BondPrice = Msg
Else
CalculateRemainingParameters BondInfo
BondPrice = CalculatedPrice(BondInfo, Yield)
End If
End Function 'BondPrice
Private Function CalculatedPrice(BondInfo As BondInfoType, Yld As Double)
Dim Coupon As Double
Dim K As Long
Dim n As Long
Dim Price As Double
Dim t As Double
Dim y As Double
With BondInfo
n = .NumCoupons
y = 1 + Yld / .Frequency
t = .FraxPeriod 'time to first coupon in periods
Coupon = .Coupon
'present value of the redemption price
Price = .Redemption * (y ^ -(n - 1 + t))
'add present value of the coupons
If Coupon > 0 Then
For K = 1 To n
Price = Price + Coupon * (y ^ -t) 'Y^(-t) = 1/(Y^t)
t = t + 1
Next K
End If
'subtract accrued interest
Price = Price - .AccrInt
End With
CalculatedPrice = Price
End Function 'CalculatedPrice
Private Sub CalculateRemainingParameters(BondInfo As BondInfoType)
Dim CouponAfter As Long
Dim CouponBefore As Long
Dim DaysSettleToCoupon As Long
Dim CouponPeriodLength As Long 'in days
Dim Settle As Long
With BondInfo
.Coupon = 100 * .Rate / .Frequency
GetCouponDates BondInfo, CouponBefore, CouponAfter
If .Basis = 0 Then
CouponPeriodLength = Application.Days360(CouponBefore, CouponAfter)
DaysSettleToCoupon = Application.Days360(.Settlement, CouponAfter)
Else
CouponPeriodLength = CouponAfter - CouponBefore
DaysSettleToCoupon = CouponAfter - .Settlement
End If
.FraxPeriod = DaysSettleToCoupon / CouponPeriodLength
.AccrInt = .Coupon * (1 - .FraxPeriod)
End With
End Sub 'CalculateRemainingParameters
Private Function CheckArguments(BondInfo As BondInfoType, _
Msg As String) As Boolean
Dim OK As Boolean
With BondInfo
OK = False
Msg = ""
Do
If .Settlement >= .Maturity Then _
Msg = "Settlement date >= maturity date": Exit Do
If .Rate < 0 Then Msg = "Rate < 0": Exit Do
If .Price <= 0 Then Msg = "Purchase price <= 0": Exit Do
If .Redemption <= 0 Then Msg = "Redemption price <= 0": Exit Do
Select Case .Frequency
Case 1, 2, 3, 4, 6, 12
Case Else
Msg = "Frequency must be 1, 2, 3, 4, 6, or 12"
Exit Do
End Select
Select Case .Basis
Case 0, 1
OK = True: Exit Do
Case Else
Msg = "Basis must be 0 or 1": Exit Do
End Select
Loop
End With
CheckArguments = OK
End Function 'CheckArguments
Private Sub GetCouponDates(BondInfo As BondInfoType, _
PrevCoup As Long, NextCoup As Long)
Dim MonthsBetweenCoupons As Integer
With BondInfo
MonthsBetweenCoupons = 12 \ .Frequency
PrevCoup = DateSerial(Year(.Settlement) + 1, Month(.Maturity),
Day(.Maturity))
If PrevCoup > .Maturity Then PrevCoup = .Maturity
Do While PrevCoup > .Settlement
PrevCoup = DateAdd("m", -MonthsBetweenCoupons, PrevCoup)
Loop
.NumCoupons = DateDiff("m", PrevCoup, .Maturity) \ MonthsBetweenCoupons
NextCoup = DateAdd("m", MonthsBetweenCoupons, PrevCoup)
End With
End Sub 'GetCouponDates
Private Function YieldWith1Coupon(BondInfo As BondInfoType) As Double
Dim Cost As Double
Dim Gain As Double
Dim Proceeds As Double
Dim t As Double
With BondInfo
Proceeds = .Redemption + .Coupon 'receive at maturity
Cost = .Price + .AccrInt 'pay at purchase
Gain = Proceeds / Cost - 1
t = .FraxPeriod / .Frequency 'time in years = frax * 1 / freq
End With
YieldWith1Coupon = Gain / t
End Function 'YieldWith1Coupon
Myrna Larson
code is 2.33 times FASTER.
But, as I said yesterday, it does have the limitation of fewer options for the
Basis argument. Looking at the formulas in Help, this seems to be relevant in
the calculation of the number of days from settlement to first coupon (the
value DSC).