## Calculate the PRICE of a bond with a monthly coupon

Oct 18

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

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:

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < March 2023 >
SunMonTueWedThuFriSat
2627281234
567891011
12131415161718
19202122232425
2627282930311
2345678
Monthly
Go