+ Reply to Thread
Results 1 to 4 of 4

Bond price conversion

  1. 01-04-2007, 11:41 AM #1
    freddie mac
    freddie mac is offline
    Registered User
    Join Date
    07-06-2006
    Location
    stockholm
    Posts
    18

    Bond price conversion

    Hi! I have a list with prices of US Treasury bonds. They are quoted in a partcular way that I would like to convert to decimal form. They are quoted like this eg: 99-02. Now this means that the price is 99 and some decimal. The two first numbers are the number of 32:s. Thus in this case the price is 99 and 2/32. However if there are three decimals eg 99-021 the last digit refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256. Sometimes There are only two decimals followed by a plus or minus sign eg 99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64. (This is the correct way, I know it seems strange but that is the way it is). Also number of digits before the line (in the example 99 might be one, two or three, eg it can look like this: 5-01, 99-021 or 100-21). Is there any way that I can convert these price quotes into decimal form using worksheet functions? Any help very much appreciated! Thanks a lot in advance!
    Reply With QuoteRegister To Reply
  2. 01-04-2007, 05:14 PM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Here's one way.....

    If your bond price is in A1 use this formula in another cell

    =IF(A1="","",(LEFT(A1,FIND("-",A1)-1)*32+IF(LEN(A1)-FIND("-",A1)=3,IF(ISERR(RIGHT(A1)+0),IF(RIGHT(A1)="+",1,-1)/2,RIGHT(A1)/4)+LEFT(RIGHT(A1,3),2),RIGHT(A1,2)))/32)

    edit: It isn't quite clear to me what the last digit in 90-021 should represent, you talk about 1/256 and 124s. If you mean 1/128 then the above formula is correct, if it should be 1/256 then replace the 4 in the above formula with an 8

    format as number or currency
    Last edited by daddylonglegs; 01-04-2007 at 05:37 PM.
    Reply With QuoteRegister To Reply
  3. 01-20-2014, 04:11 PM #3
    dtipitino
    dtipitino is offline
    Registered User
    Join Date
    10-24-2013
    Location
    Stamford, CT
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Bond price conversion

    I am looking to get what you wrote above into a VBA function since it is something I do regularly. I have been having some trouble moving it over. Can you advise?
    Reply With QuoteRegister To Reply
  4. 01-21-2014, 01:08 AM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Bond price conversion

    dtipitino,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1