Login     Register

        Contact Us     Search

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  RssIcon

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.

 
Previous: Rely on the results of the XIRR function
Next: Correctly calculate the accrued interest on a bond with an odd first period
 

 

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