Login     Register

        Contact Us     Search

Calculate the PRICE of a bond with a monthly coupon

Oct 18

Written by: Charles Flock
10/18/2011 8:11 PM  RssIcon

11 Financial Calculations that you can’t do in EXCEL - Part 7 of 11
7. Calculate the PRICE of a bond with a monthly coupon
EXCEL will calculate the price (and yield) for an instrument with quarterly, semi-annual, and annual coupon payments, but not monthly. Why is that? It’s not like the math is any different based on the coupon frequency. There is nothing special about these coupon frequencies. And there are loads of securities that pay monthly coupons.
This is not just a problem with PRICE, but with YIELD, ACCRINT, DURATION, MDURATION, and all the bond calculation functions in EXCEL. And this has pretty much been the case at least as far back as EXCEL 95. In 16 years the EXCEL product managers couldn’t figure out that there are lots of securities that pay monthly coupons and included that as a feature in these functions?
Here’s what that looks like in SQL Server:
SELECT wct.PRICE(
 '2011-10-14'     --settlement
,'2011-12-31'     --maturity
,.0025            --rate
,.002             --yld
,100              --redemption
,12               --frequency
,0                --basis
)
This produces the following result.
----------------------
      100.010552016251
 

(1 row(s) affected)

 
Previous: Correctly calculate the price of a bond with an odd long first period
Next: Calculate the cumulative interest payments for a loan with a balloon payment
 

 

Tags:
Categories:
Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service