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)