## 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
 < July 2024 >
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910
Monthly
Go