In the course of my past duties I was responsible for the weekly data load of investment portfolio activity which was made up almost exclusively of fixed-income securities. The problem with the process was that the source system was formatted in traditional bond pricing whereas the target system used strictly decimals, so I sought an excel-based solution to speed up the process and reduce the chances of human error.

Legacy bond pricing adheres to the following format (further details are available here): a bond sold at PAR, or 100% of the face value is written as 100-00. The 100 to the left of the hyphen represents the integer portion of the price while the value after the hyphen is represented in 32nds. Hence, it the price were 100-08, that is the same as saying 100 and 8/32nds or 100.25. Creating a formula in excel to handle this situation is pretty straight-forward and can be done with little difficulty.

Of course, sometimes bonds are traded in pricing increments smaller than 32nds and while ANY value is possible, pricing normally occurs in increments of 64ths: the legacy pricing format usually denoted this by adding a “+” at the end of the quote. So, using the previous example, a price of 100-08+ actually means 100 and 8.5/64ths. This is where using excel can become a bit of a challenge, but thanks to some googling and help from some forums, there are pretty quick and simple formulas which can handle these situations.

The code I originally used was as follows:

=IFERROR(LEFT(A1,FIND(“-”,A1)-1)+SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(“-”,A1)),”+”,””)/32+IF(RIGHT(RIGHT(A1,LEN(A1)-FIND(“-”,A1)),1)=”+”,1/64,0),A1)

This code essentially reads the value in cell A1, looks for the hyphen and does the calculations as one would expect, only it looks for the “+” at the end and if present, 1/64th is added. In more recent searches, I came across a more compact formula which seems to give the same output:

=LEFT(A1,FIND(“-”,A1)-1)+MID(SUBSTITUTE(A1,”+”,””),FIND(“-”,A1)+1,9)/32+(RIGHT(A1)=”+”)/64

Either works just as well and should get the job done. Of course, some bond pricing systems go even further (i.e. 100-087 which translates to 100 and 8.7/32nds) but I have not yet seen an algorithm which handles that level of granularity to pricing and since our system did not use that methodology I never had the need to deal with it.

Anyway, I hope this helps in the event you were looking for help on converting bond prices to decimals, and if not, I applaud your patience for reading about something you had no practical interest in learning.