Hello! I am desperate for help here. I am semi-proficient with VBA, but I can barely read the math formula I have attached below. The formula is called the Bootstrapping Method. If you have a known series of par yields on a standard bond, you should be able to use this formula to calculate what the equivalent zero coupon bond yield would be.
r = the zero coupon yield at a given point n, in its maturity
y = the provided par yield at the same given point in maturity
How would I code this formula into VBA? Any help would be appreciated!
Edit: One thing I forgot to add. I would like my columns headings in my spreadsheet to be "Period"( 1 through n), the second column being "Par Yield" (this would be inputed by user), and the third column to be "Zero Coupon Yield" (the values in this column would be the output of the macro).
THANK YOU SO FREAKING MUCH IN ADVANCE!
Last edited by gtg689a; Jul 20th, 2007 at 03:39 PM.
I'm not familiar with VBA specifics, but this should get you started.
Code:
' Local variables
Dim sum As Double, prod As Double
Dim i As Long, j As Long
sum = 0 ' Initial value for Sum
For j = 1 To n ' Calculate Sum
prod = 1 ' Initial value for Product
For i = j To n ' Calculate Product
prod = prod / (1 + y(i))
Next i
sum = sum + prod
Next j
r = (1 - y(n) * sum) ^ (-1 / n) - 1
The best way to code equations is to start from the innermost brackets: 1 over (1 + y)
This must be multiplied by the previous such value for (n-j) times; one iteration loop here.
At the end of this loop, the result must be added to a cummulative sum and this must be repeated n times; another iteration loop here.
At the end of the second loop, the resultant quantity is multiplied by (1 - y)
Next raise this to -1/n and finally subtract 1 from it.
Of course, the code itself has been given away but I reckon this helps!
Reply With Quote
but I reckon this helps!
