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

- POLYNOMIALS vba code newton-raphson algorithmn real-valued root of a polynomial
- Implied Volatility - Newton Raphson
- Newton Raphson-Implied Volatility
- Created VBA using Newton's Method
- Bond Yield to Maturity using the Yield function in Excel
- Need help with bond yields
- Macro to interpolate gaps in bond yield data
- Bond Yield Template with Payment schecule and chart (Excel)
- How do you calculate "dividend yield using Excel?
- How do you calculate &quot;dividend yield using Excel?
- Calcuate bond yields
- Bond Yield Template with Payment schecule and chart (Excel)
- Calculating Bond yields on Bonds with Multiple Coupons
- Implied Volatility - Black Scholes
- Calculating Bond Price using Excel
- Calling the "Duration" function in excel to be used in VBA?
- Newtons Root Finding Method FOR/LOOP error
- Monthly compound price and yield
- AUD denominated bond yield calculator
- Using iteration to solve a two-parameter equation
- Yield on bond priced over 100
- UDF: Roots of 1D Polynomials of Degree N and Real or Complex Coefficients
- Function or number depending on input
- Value or function as result....

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.

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.

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???

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

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.

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!!!

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

----------------

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

retraction dates etc.

I work in excel 2003

----------------

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

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

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.

Thank you so much!

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!

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

"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

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

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

a security is priced above par? Am I missing something?

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.

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

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.