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
HTH
--
Don Guillett
SalesAid Software
don...@281.com
"mike allen" <mikea...@charter.net> wrote in message
news:%23MO5H9R...@TK2MSFTNGP15.phx.gbl...
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...
"Don Guillett" <don...@281.com> wrote in message
news:u$42GdS6E...@TK2MSFTNGP15.phx.gbl...
"Don Guillett" <don...@281.com> wrote in message
news:u$42GdS6E...@TK2MSFTNGP15.phx.gbl...
On Thu, 23 Dec 2004 14:37:40 -0600, "mike allen" <mikea...@charter.net>
wrote:
"Myrna Larson" <anon...@discussions.microsoft.com> wrote in message
news:hrans09sfu1jlreua...@4ax.com...
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>
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
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).