wilmott.com

Hey everyone,I am building a trinomial tree pricer in VBA to price Convertible Bonds. I am quite new to VBA and would appreciate if you have any feedback or can see any errors with my code as it doesn't seem to work and returns #VALUE when i use the udf in excel.Much appreciated all Below is my codeFunction ConvBond(EuroAmer As String, S As Double, K As Double, T As Double, ytm As Double, PF As Double, rf As Double, ConvRatio As Double, NominalAmt As Double, dy As Double, cr As Double, v As Double, n As Integer) As VariantDim CB() As Double 'Convertible Bond price at some position'Dim cn() As Double '# of Coupons remaining at the jth period'Dim PVB() As Double 'PV of Bond at the jth period'Dim CouponNumber() As IntegerDim r() As DoubleDim AN() As DoubleDim adj() As DoubleDim PVBond() As DoubleReDim CB(0 To 2 * n + 1) '# of final branches'Dim dt As Double, u As Double, d As DoubleDim pu As Double, pd As Double, pm As Double, Df As DoubleDim i As Integer, j As Integerdt = T / n 'Delta t'u = Exp(v * Sqr(2 * dt)) 'Amount the tree moves upwards by'd = 1 / u 'So the trinomial tree recombines'b = (rf - dy) 'Adjusted for continuously compounding dividends'pu = ((Exp(b * dt / 2) - Exp(-v * Sqr(dt / 2))) / (Exp(v * Sqr(dt / 2)) - Exp(-v * Sqr(dt / 2)))) ^ 2 'Probability of up movement'pd = ((Exp(v * Sqr(dt / 2)) - Exp(b * dt / 2)) / (Exp(v * Sqr(dt / 2)) - Exp(-v * Sqr(dt / 2)))) ^ 2 'Probability of down movement'pm = 1 - pu - pd 'Complement of pu and pd probabilities'Df = Exp(-b * dt) 'Adjusted for cts compounding dividends'EIR = (1 + ytm * PF) - 1 ' Effective interest rate per period since rates are given yearly'For i = 0 To (2 * n) '2n final possible final nodes'CB(i) = Application.Max(NominalAmt / ConvRatio, (S * (u ^ Application.Max(i - n, 0)) * (d ^ Application.Max(n - i, 0)))) ' Intrinsic value of option component at each possible stock price''Value at expiry'NextFor j = (n - 1) To 0 Step -1 'Step Backwards in time from n-1'For i = 0 To (2 * j) 'Two possible branches that are different for every node'CouponNumber(j) = Application.Max(Application.RoundUp((T - j * (T / n)) / PF, 0)) '# of Coupons remaining at time j'coupon = (cr * PF * NominalAmt)If CouponNumber(j) <> 0 Then 'If the number of remaining coupons is not equal to zero, i.e. we are not at maturity'If (j * dt) - (T * CouponNumber(j) * PF) = 0 Then 'Checks if the current position is a coupon date, assigns indicator variable z of 0 if it is and 1 if not'z = 0Elsez = 1End Ifr(j) = NominalAmt * (1 + EIR) ^ (-(CouponNumber(j)))AN(j) = coupon * ((1 - (1 + EIR) ^ (-CouponNumber(j))) / EIR)adj(j) = (1 + EIR) ^ (z * (j * dt - (T - CouponNumber(j) * PF))) 'Adjustment interval if not valued at the previous coupon date'PVB(j) = (AN(j) + r(j)) * adj(j)ElsePVB(j) = (1 + cr * PF) * NominalAmtEnd Ifcn(j) = CouponNumber(j) 'Tracks # of Coupons remaining for the jth period of time to feed into the annuity to value the Present Value of the Bond coupons + Bullet'PVBond(j) = PVB(j) 'PV of the bond at each node in time, if j=T then it's just the value of the redemption value and the coupon'CB(i) = Application.Max((pu * CB(i + 2) + pm * CB(i + 1) + pd * CB(i)) * Df, 0) 'Intrinsic option at each point i'If EuroAmer = "Amer" Then 'In the case of American Style convertability at any point in time n'CB(i) = Application.Max((S * u ^ (Application.Max(i - j, 0)) * d ^ (Application.Max(j - i, 0))), CB(i), PVBond(j) / ConvRatio)'Max at each point between retaining the bond, Exercising, or the Intrinsic value'End IfNextConvBond = CB(0)NextEnd Function

You can use break point and step-by-step execution to determine where the error happens in VBA.Google "VBA break point step execution" and it should return some useful references.The "locals" windows would be another helpful tool.

Thanks alot! knowing how to use this method is extremely helpful!