Free Microsoft Excel 2013 Quick Reference

Bond yields using Newton Raphson iteration

Has anyone got an example of how to construct bond yield calculations using Newton Raphson iteration?


Post your answer or comment

comments powered by Disqus
Could anyone help me with the following problem:

Write a VBA function that computes one real-valued root of the polynomial defined by

P(x) = (summation mark goes from i = 1 to n) coeffs.cells(i,1) x^(powers.cells(i,1))

where n=coeffs.Rows.Count using Newton's Solver. For the input, coeffs is a Range with n rows (and 1
column) of real-valued coefficientcients, and powers is a Range with n rows (and 1 column) of non-negative,
integer powers.

I guess ''Newton's Solver'' is actually the Newton-Raphson algorithmn.

My solution so far is:

Function polySolver(coeffs As Range, powers As Range) As Double
Dim rowCount As Integer
Dim i As Integer
Dim xn As Double
Dim xnm1 As Double
Dim fx As Double
fx = 0
Dim fxprime As Double
fxprime = 0
xnm1 = 0.1
rowCount = coeffs.Rows.count
Do
For i = 1 To rowCount
fx = fx + coeffs.Cells(i, 1) * xnm1 ^ powers.Cells(i, 1)
fxprime = fxprime + (powers.Cells(i, 1) * coeffs.Cells(i, 1)) * xnm1 ^ _
(powers.Cells(i, 1) - 1)
Next i
xn = xnm1 - fx / fxprime
xnm1 = xn
Loop Until (Abs(fx) < 0.00001)

polySolver = xn
End Function

When I choose different initial values of x0 (in the code denoted with xnm1), I get different solutions. This function probably shouldn't even contain the initial x0 (I suppose that the should't have an assigned value for xnm1). The algorithmn should be valid for any polynomial and the outcome should be one real-valued root of this polynomial.

Hi,

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the
 I have used to
calculate the implied volatility for one option.

Any help is much appreciated.

Thak you.

Hi,
I'm trying to back out the implied volatility from the option prices using a Newton - Raphson method. The problem is that the computer blocks calculating some cells of the matrix. I mean, once the code has been executed, each cells of the matrix has to be calculated but at one moment (at one cells), the calculation stops. It's maybe normal, and it's possible to extrapolate... but how can I write a macro to let the calculation continue? Right now, when the computer is blocked on one of the cells of the matrix, I can wait the hours and it doesn't go further. I'm sure that the macro function is correct. Can anybody help me? Maybe it's possible to write macro containing a limit-time of calculations for each cells to let it move further if it’s blocked???

Can someone help me solve this

Write a VBA macro that uses Newton's method to solve for a root. The function should take 3 arguements: an intial guess for the root, the DeltaX to use for the numerical derivatives (use a central difference approximation), and a relative stopping criteria. The fucntion should return the root of the function,i.e. the value of x that satisfies f(x) = 0. Use the absolute value of the raltive change in two successive x values to test against your relative stopping criteria. For this problem, you don't need any other testing, e.g. number of loops or time taken - just relative stopping criteria (Hint: You obviously need to embed the iterations within some kind of loop)

Assume that the function is available in a VBA function termed myf(). myf() takes 1 arguemet: the x value where the function is to be evaluated. For example, if the function is 3x^2+2x-1, then assume that the following code is already available

Option Explicit
Function myf(x as double) as double
myf = 3x^2 + 2x-1
End Function

Thanks,
Brandon

Hi all,
The yield function in excel does not give the right value if I play around with the redemption value(% of par). I have checked this with other available online bond yield calculators such as
http://www.moneychimp.com/calculator...e=calc_bondytm

If I change the redemption value to 50 and the bond price to $50, ideally the coupon rate and the yield should be equal for any time period. While this happens in the calculator mentioned above, Excel gives me a different number. Is this actually a bug or am I missing the point?

Test this in Excel...

=YIELD("6/30/2009","6/30/2022",0.0958,50,50,2,0)

Also test different redemption and price values and compare them with the calculator above.

I work for an investment management firm and I am having trouble calculating municipal bond yields with Excel. My problem is two fold.

The first has to do with the how you write the formula or input the data. My traders use the following format:

yield(trade price, redemption price, rate, 2, settlement date, redemption date, "MUNI")

If you use insert function in Excel, the format is:

yield(settlement date, redemption date, rate, trade price, redemption price, 2)

If one of my traders sends me a spreadsheet using the first format/formula, it will properly calculate on my PC. But if I try to edit the formula in any way or replicate it on my own, Excel returns #NUM! We all appear to be using the same add-ins (I have the bond add-in installed).

The other problem is that if I use the second format, the one built into Excel, it returns the wrong yield. The first format of course returns the correct yield. How do I know it is wrong? If I check the yield using a financial calculator or a Bloomberg terminal it matches what the first formula returns.

Any help would be very much appreciated!!!

Hi all,

First post here so I will keep it brief. I need to fill in the gaps in my data by interpolation, I have 170 bond yields on the spreadsheet so a macro (maybe on the active cell) is preferable. Please see attached for the data, scrolling down shows the missing data.
bond yield example data.xlsx
Thanks

Does anyone have a Bond Yield Template, payment schedule and chart for excel?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Can you calculate "dividend yield using Excel? If so, how?

Can you calculate "dividend yield using Excel? If so, how?

I need to calculate bond yields to different dates such a maturity,
retraction dates etc.

I work in excel 2003

Does anyone have a Bond Yield Template, payment schedule and chart for excel?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi,

I am trying to calculate the Yield on Step up bonds. For example, the bond may have a 0% coupon for 4 years then 6% coupon for years 5-9 and 9% from 10 through to maturity. Does anyone know of a function or of a way to calculate the yield on such a bond using VBA??

The yield function only allows for one coupon rate to be assigned. Im thinking I may have to calculate the yields separately for each coupon rate given the time that rate is outstanding, but even then im still unsure as to make all the separate yields into one afterward.

Any help is greatly appreciated,

thanks,
Tim

Hi,

I hope im not repeating myself through this post but i have through SAS (9.13) tried to backout the implied volatility from the b/s model without much success (sas cannot apparently handle more than 2 iterations - newton) and i was told that this can be done in vba. I have a very limited knowledge of vba and was wondering if somebody could guide me with regards to writing some applicable code. I have attached a sample of the data.

I have also attached a sample of some code that uses goalseek however this doesnt seem to work very well for either short or very long term options. Is there a way to increase the number of iterations in the code?

I've also seen that it is possible to utilise the Newton Raphson model - see http://www.excel-modeling.com/examples/example_028.htm. Does anyone have any idea how to apply this (in terms of coding) with regards to the following dataset.

Any help is much appreciated.

Thankyou.

Could someone please give me detailed instructions on how to calculate the price of a bond (if given all the other elements, such as the coupon rate, etc) using excel? I know it has something to do with the payment function but I'm just really confused!
Thank you so much!

Hi Everyone,

I am very new to writing macros and have come acrossed this problem when I am trying to finish off my assignment. We are not allowed to use UserForms, so I've selected to use inputboxes. My problem lies in calling out the duration function from excel..whenever that line of code is reached, an error message saying "Object doesnt support this property of method" comes out. Can you please help me?

Option Explicit

Sub ValidateInputs()
Dim SettlementDate As Variant
Dim MaturityDate As Variant
Dim CouponRate As Double
Dim Yield As Double
Dim CheckDate As Boolean
Dim test As Boolean
Dim Frequency As Integer
Dim BondDuration As Double

Start:

Do
'Get the settlement date of the bond

SettlementDate = Application.InputBox("Please enter the date when you acquired the bond in the following format, 'YYYY,MM,DD', e.g 2006,12,30", _
"Settlement date of the bond", , , , , 2)
Debug.Print SettlementDate

If SettlementDate = IsDate(SettlementDate) Then
test = True
Debug.Print SettlementDate
Else
MsgBox "Please enter the settlement date in an appropriate format", vbCritical, "Warning"
test = False
End If
Loop Until test

Do
'Get the maturity date of the bond

MaturityDate = Application.InputBox("Please enter the maturity date of the bond in the following format, 'YYYY,MM,DD', e.g 2006,12,30", _
"Maturity date of the bond", , , , , 2)
Debug.Print MaturityDate

If MaturityDate = IsDate(MaturityDate) Then
test = True
Debug.Print MaturityDate
Else
MsgBox "Please enter maturity date in an appropriate format, e.g '2005,12,30'", vbCritical, "Warning"
test = False
End If
Loop Until test

Do
' Check if maturity date is later than settlement date

If DateDiff("d", SettlementDate, MaturityDate) <= 0 Then
test = False
MsgBox "Maturity date must be later than the Settlement Date", vbCritical, "Warning"
Debug.Print DateDiff("d", SettlementDate, MaturityDate)
GoTo Start
Else
test = True
Debug.Print DateDiff("d", SettlementDate, MaturityDate)
End If
Loop Until test

Do
'Get the coupon rate of the bond

CouponRate = Application.InputBox("Please enter the coupon rate of the bond in its per annual percentage term, e.g enter 8 if the coupon rate is 8%", _
"Coupon Rate of the bond", , , , , 2)

If CouponRate > 0 Then
test = True
Debug.Print CouponRate
Else
MsgBox "Coupon Rate needs to be positive'", vbCritical, "Warning"
test = False
End If
Loop Until test

Do
'Get the annual yield of the bond

Yield = Application.InputBox("Please enter the annual yield of the bond in its per annual percentage term, e.g enter 8 if the coupon rate is 8%", _
"Annual yield of the bond", , , , , 1)

If Yield > 0 Then
test = True
Debug.Print Yield
Else
MsgBox "Yield needs to be positive'", vbCritical, "Warning"
test = False
End If
Loop Until test

Do
'Get the frequency of coupon payments per year

Frequency = Application.InputBox("Please enter the frequency of the coupon payments", _
"Frequency of the coupon payments", , , , , 1)

If Frequency > 0 And 0 Or 1 Or 2 Or 4 Then
test = True
Debug.Print Frequency
Else
MsgBox "Frequency of coupon payments needs to be 0 or 1 or 2 or 4", vbCritical, "Warning"
test = False
End If
Loop Until test

' Calls the duration function in-build in Excel to calculate the duration of the bond
' Basis is set in European format since this program is designed for use in Australia

BondDuration = Application.Duration(SettlementDate, MaturityDate, CouponRate, Yield, Frequency, 4)
MsgBox "BondDuration", vbOKOnly, "Bond Duration"
Debug.Print BondDuration '(SettlementDate, MaturityDate, CouponRate, Yield, Frequency, 4)

End Sub

Thank you!

I'm having problems with the porblem stated below:

Write a VBA macro that uses Newton's method to solve for a root. The function should take 3 arguements: an intial guess for the root, the DeltaX to use for the numerical derivatives (use a central difference approximation), and a relative stopping criteria. The fucntion should return the root of the function,i.e. the value of x that satisfies f(x) = 0. Use the absolute value of the raltive change in two successive x values to test against your relative stopping criteria. For this problem, you don't need any other testing, e.g. number of loops or time taken - just relative stopping criteria (Hint: You obviously need to embed the iterations within some kind of loop)

Assume that the function is available in a VBA function termed myf(). myf() takes 1 arguemet: the x value where the function is to be evaluated. For example, if the function is 3x^2+2x-1, then assume that the following code is already available

Option Explicit
Function myf(x as double) as double
myf = 3x^2 + 2x-1
End Function This is currently what I have:

Option Explicit
Sub Newtons()
Dim x As double
Dim s As double
Dim dx As double
Dim i as long
x=1
s=0
dx=2
For i=1 to 1000
c=x-(myf(x)/[(myf(x+dx)-myf(x-dx))/(2*dx)]
If x-c = s Then
End Function
End If
x=c
Next i
EndFunction Thanks,
Brandon

Is there a way to figure bond yields and prices using excel's "price" and
"yield" functions, but on a monthly basis? It appears that only 1
(annually), 2 (semi-annually), and 4 (quarterly) are usable in the frequency
field. Thanks, Mike Allen

Hi!

Good day all!

I am currently trying to figure how to arrive at the yield of an AUD given a price of the bond. Sadly, the '=yield' function in excel does not do the trick (yes, I've tried all the 'basis' or day count options) and I'm starting to think that they may have a different basis for their day count convention. Anyone here familiar with this topic? Maybe someone here with his/her own worksheet he/she can share?

Thanks,
Carlos

Hi everyone,

My goal: My spreadsheet has two inputs and two outputs. I input the
initial values -magic calculations happen- and out come my two outputs,
which are meant to be my new inputs. This process needs to happen
until my outputs match the previous inputs to a degree that I specify,
convergence. Cookie cutter Excel iteration issue?

My problem: If I just make my inputs refer to the same cells as my
outputs, and turn iteration on, I never reached convergence and I end
up with some kind of error.

My pseudo-solution: I wrote a macro that copies the values of the
output to the inputs and after running it about 8 times, I am able to
converge (inputs=outputs)

I would like to use Excel's iteration for this instead of a macro. I
suspect the problem has to do with the timing of the calculations, but
I really dont know. Help is greatly appreciated

Have a good day

Jason

Why does excel seem to only calculate yield using prices under $100. What if
a security is priced above par? Am I missing something?

Hello;

I would very much appreciate your expert help in developing a UDF in XL VBA to determine the real and imaginary roots of a polynomial of degree N and real or complex coefficients.

1) This subject was already discussed using Goal Seek and Solver in the thread:
"Goal Seek with Complex Numbers" located at
http://www.mrexcel.com/forum/showthr...7&goto=newpost

Goal Seek did not work with complex numbers, and I had limited success with XL Solver.
By reviewing the above thread, one would conclude that the Solver procedure for this task had run its course and there would be no added value in pursuing it any further. Hence the idea of developing a general and more reliable UDF procedure.
Here're some of my thoughts on the subject and I would appreciate your comments and suggestions.

2) One of the apparent difficulties in using the Solver procedure (item 1. above) is that its success, if at all, crucially depends on having a good first guess of the root, which by no means is readily available for a general equation. Equally problematic is the fact that the user of the Solver procedure has no control on hunting down the root even if it is accurately bracketed.

3) The first question one would ask: which root-finding method one should implement in the UDF ?? In most reliable algorithms based on Newton-Raphson method a user-supplied root-bracketing is required, either by a subsidiary procedure or by providing an array of guesses and let the procedure zooms in each root to within a specified convergence criterion. To the best of my knowledge, almost all Newton-based methods deal with real polynomial coefficients and determine real roots.

4) I would suggest the Languerre's method here. It is guaranteed to converge to all types of roots in the - oo to + oo range, handles polynomials with complex coefficients, and does not require an initial guess or starting point or trial solutions. (Keep in mind, with complex coefficients, complex roots may or may not occur in conjugate pairs.)
Sounds too good to be true ?? Well, it's actually true! and I successfully used the method in the past

5) The ref. NR, p. 264, 265 has the Languerre's routine (~ 30 lines of code) and its deriver routine ZROOTS (~ 30 lines). Both codes are in Fortran 77 and are relatively easy to follow and convert to VBA.
The UDF FUNCTION CubicEq() written by pgc01, MrExcel MVP, and posted recently in the thread quoted in item 1. above could be used as a template for the procedure. It is really that simple, specially for someone with expertise in XL VBA.

6) The developed root-finding UDF VBA procedure would be applicable to any one-dimensional equation of the form:
f(x) = sum [k=1 to k=N+1] A(k) x^(k-1)
where f(x) has only one independent variable "x", N is the degree, and A(N+1) are the complex coefficients of the polynomial.

7) One should always try to get some idea of how the function behaves before trying to find its roots. There's really no excuse for not to, since it is one dimensional and the task can't be more simpler in XL.
The display (on the w/s) would identify where the function changes sign and whether the change in sign is associated with a real root, a finite jump discontinuity, or a singular point. Also, potential problems such as double real roots (value of function is zero at its max or min), multiple real roots in close proximity (oscillating function about the x-axis), and other problems, could easily be identified from a simple display automatically generated on the w/s by the UDF (just a thought).

8) One could validate the developed UDF using the analytical data for 3rd and 4th degree equations. There're no general analytical solutions for polynomials of degree higher than 4, though there're (very complicated) solutions for particular families of polynomials of any degree. One can't, however, justify the effort required in pursuing such solutions. One may instead manufacture such solutions by multiplying a lower degree equation (with known roots) by a known real or imaginary root.

I apologize for the lengthy thread.

Thank you kindly.

Hi, i've got a spreadsheet with a function i would like to duplicate:

There are 2 cells, one with the Yield of a security (in this case a
bond) and another cell with the price of the security (the same bond).
Lets say A1 holds the price and cell A2 holds the yield. Now Excel
functions Price() and Yield() use more or less the same arguments and
let you calculate the Price of a bond, based on a certain yield OR let
you calculate the yield of a bond, based on a certain price. So it's a
bit like the chicken and the egg...you need a price to calculate yield
or you need a yield to calculate a price.
In this spreadsheet i'm having they allow you to type in the Price of a
security in cell A1, which will give you a yield in cell a2. The nice
thing however is the following: if you type a yield in cell a2 it will
give you the price in cell a1. So based upon your input Excel looks at
the cell where you have input the value and puts a function in the other
cell....it calculates the result and puts the original function back
into the cell where you have just typed a value, allowing you to do
further calculations with that function....So even when you type a
value in a cell, somehow excel still knows what functions should behind
it once it has done it's calculations......Maybe i'm not explaining this
very well but if someone has any idea how you can do this i would love
to hear from you ! Thanks in advance!!!!

--
Jan Jansens
------------------------------------------------------------------------
Jan Jansens's Profile: http://www.excelforum.com/member.php...o&userid=26982
View this thread: http://www.excelforum.com/showthread...hreadid=401914

Hi, i've got a spreadsheet with a function i would like to duplicate:

There are 2 cells, one with the Yield of a security (in this case a bond) and another cell with the price of the security (the same bond). Lets say A1 holds the price and cell A2 holds the yield. Now Excel functions Price() and Yield() use more or less the same arguments and let you calculate the Price of a bond, based on a certain yield OR let you calculate the yield of a bond, based on a certain price. So it's a bit like the chicken and the egg...you need a price to calculate yield or you need a yield to calculate a price.
In this spreadsheet i'm having they allow you to type in the Price of a security in cell A1, which will give you a yield in cell a2. The nice thing however is the following: if you type a yield in cell a2 it will give you the price in cell a1. So based upon your input Excel looks at the cell where you have input the value and puts a function in the other cell....it calculates the result and puts the original function back into the cell where you have just typed a value, allowing you to do further calculations with that function....So even when you type a value in a cell, somehow excel still knows what functions should behind it once it has done it's calculations......Maybe i'm not explaining this very well but if someone has any idea how you can do this i would love to hear from you ! Thanks in advance!!!!


No luck finding an answer? You could always try Google.