I inherited a excel spreadsheet that has the following code to price a bond given coupon and current yield

    'n - Number of future coupon payments till maturity
    'mtm - months to maturity
    'FREQ = 12
    n = Int((mtm - 1) / (FREQ / 2)) + 1
    f = 2
    X = 1 - (mtm Mod (FREQ / 2)) / (FREQ / 2) ' length of accrual period since last coupon date,  0 <=X<1
    If X = 1 Then
        X = 0
    End If
    'Last term is subtraction of accrued interest. The PV includes accrued interst, if need to exclude, uncomment
    pv = (cpn * (1 + yld / f) + (yld - cpn) / ((1 + yld / f) ^ n)) / (yld * ((1 + yld / f) ^ (1 - X))) '- X * cpn / f

Has anyone seen this formula that can point me to a reference. There are even more complicated formulas for duration and convexity. It seems to work but I can't make heads or tails out of it. Alas the documentation doesn't get any better

  • $\begingroup$ It's simple cash flow discounting, you'll see that formula on any google search for present value. $\endgroup$ – jeff m Apr 11 '15 at 14:19
  • $\begingroup$ unfortunately excel does not expose the "excel functions" such as PV to VBA (easily) so you end up seeing simple code like this get replicated in VBA $\endgroup$ – user25064 Apr 11 '15 at 14:47
  • $\begingroup$ @user25064 any workbook function is called pretty easily via vba by WorksheetFunction.Sum(x,y), WorksheetFunction.xnpv(...), yadda yadda. The reason for this vba code is to catch the accrued interest at the sale date (you split this prorata between the bond seller and buyer based on how much time has elapsed from the last coupon payment) $\endgroup$ – jeff m Apr 11 '15 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.