Calculate the price (or yield) of a bond on its issue date when it has an odd first period
Oct
18
Written by:
Charles Flock
10/18/2011 8:13 PM
11 Financial Calculations that you can’t do in EXCEL - Part 4 of 11
4. Calculate the price (or yield) of a bond on its issue date when it has an odd first period.
Set up a worksheet with the following data.
In B10 enter the following formula: =ODDFPRICE(B1,B2,B3,B4,B5,B6,B7,B8,B9)
Here’s the result:
The #NUM! error value arises because EXCEL requires the settlement date to be greater than the issue date. There is no good reason for this, and there are lots of good reasons to want to know the price and yield of a bond on its issue date. Here’s what the answer should have been:
SET NOCOUNT ON
SELECT wct.ODDFPRICE(
'2009-05-17' --settlement
,'2014-11-30' --maturity
,'2009-05-17' --issue
,'2009-11-30' --first coupon
,0.04 --rate
,0.0375 --yield
,102 --redemption
,2 --frequency
,0 --basis
) as ODDFPRICE
This produces the following result.
ODDFPRICE
----------------------
102.866155406085
EXCEL has the same limitation with the ODDFYIELD function, but we will only count this as 1 item towards the total.