Excel Custom Number Formatting for US Treasury Bond Quotes

Question

I am having some trouble using the custom number format feature in Excel to display US Treasury futures quotes.

The format by which they are displayed in is:

121'167

Which is equivalent to 121 + 16.75/32

If the last digit is a 5, as in 121'165, then it is equivalent to

121 + 16.5/32

If the last digit is a 2, as in 121'162, then it is equivalent to

121 + 16.25/32

and finally if the last digit is a 0, as in 121'160, then it is equivalent to

121 + 16/32

Is there a way to implement this definition using the custom number formatting feature or is it beyond it's capability?

Answer 1

I know this is a bit dated, but stumbled upon this today and needed the same information. Here is what I use, replace the cell across the formula to pull the quote.

5 yr: =LEFT(J22,3)+(RIGHT(J22,3)/320)+IF(INT(RIGHT(J22,1))=2,0.0015625,IF(INT(RIGHT(J22,1))=7,0.0015625,0))

10 yr: =LEFT(B28,3)+(RIGHT(B28,3)/320)

Answer 2

=TRUNC(A1)&"'"&ROUND((MOD(A1,1)*32),1)

Where A1 = 109.6406

This is what worked best for me. I simply had to take a 2-year note quote in the form of, as an example, 109'20.5 and replicate in excel. I converted the quote into 109.6406 (109 + 20.5/32) and applied the above formula.

Answer 3

This is one insane format: add fractions, then use decimals in the dividend, and then truncate those decimals...

But enough of the ranting. This should do the job, if I understood your specification correctly:

=TRUNC(A1)&"'"&INT(MOD(A1,1)*320)

Number formats alone cannot do this, because they cannot do actual conversions of the numbers (apart from the built-ins like date/time).

Answer 4

you can apply the TEXT() function to both of your values and concatenate them into a single string.

example (A1 = 121, B1 = 16.75)

C1: =TEXT(A1,"###") & "'" & TEXT(B1*10-1,"###") & "/32"

You need to work out rounding (I put a constant *10-1 into the 2nd term) and how to obtain the "/32" ... I couldn't spot that from your question

Hope that helps

good luck