Treasury Bond Pricing conversion

[pakeez]

As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which
is equivalent to 110 16/32 or 99-245 which is equivalent to 99
24.5/32)

Now I need to do some calaculaltions while trading Bonds and I want to
do in excel 03.

I was given a hint that I need to convert the above price format first
in decimal value and then format the result in xxx-xxx.

e.g 110-16 is in cell A1 then formula for converting to decimal would
be:
=Left(A1,3)+Right(A1,3)/320

Since I have been out of touch with excel for long time I am having
hard time how to format back in xxx-xxx.

So I am giving an example below to do calculations to do price
difference:

say cell A1 has value of 110-160
say cell B1 has value of 99-245

The answer is 10-235.

Now how can I get this calculations done in excel. Detail formulas
would be appreciated.

Thank YOu

 

[Jacob Skaria]

Just a hint to go forward

Suppose A1 = 111-222

The below formula will give you the numbers before the hyphen no matter what
the length is
=VALUE(LEFT(A1,FIND("-",A1)-1))

And the below formula will give you the numbers after the hyphen to a
maximum of 10..which is more than enough

=VALUE(MID(A1,FIND("-",A1)+1,10))

If this post helps click Yes

 

[Rick Rothstein]

Assuming your decimal value is in C1, this will convert it back to the
format you showed...

=INT(C1)&"-"&320*MOD(C1,1)

 

[JoeU2004]

110-16 is in cell A1 then formula for converting to
decimal would be: =Left(A1,3)+Right(A1,3)/320

Yes. But according to your example, the left part is not always 3 digits.
Ostbensibly, the more versatile conversion to decimal is:

left(A1,find("-",A1)-1)+right(A1,3)/320

However, initially you said "110-16 which is equivalent to 110 16/32". Did
you really mean 110-160, as you use later? That is, is the right part
always 3 digits?

I hope so. It would be difficult, but not insurmountable, to differentiate
2-digit and 3-digit right parts.

Since I have been out of touch with excel for long
time I am having hard time how to format back in xxx-xx.

If C1 is =A1-B1, then:

int(C1)&"-"&text(mod(C1,1)*320,"000")


----- original message ----

 

[JoeU2004]

=INT(C1)&"-"&320*MOD(C1,1)

Test with 110-160 minus 99-159. I believe the desired result is 1-001.
Ostensibly, your formula will result in 1-1. But it actually results in
11-0.999999999999091.


----- original message -----

 

[JoeU2004]

Errata....

If C1 is =A1-B1, then:
int(C1)&"-"&text(mod(C1,1)*320,"000")

I meant that A1 and B1 are the decimal equivalents. I probably should have
use A2, B2 and C2 in my example to avoid confusion.


----- original message -----

 

[Rick Rothstein]

Good point. Your TEXT function call to limit the entry to 3 digits is the
way to go. Thanks for noting the problem with my formula.

 

[Rick Rothstein]

Yes. But according to your example, the left part is not always 3 digits.

Ostbensibly, the more versatile conversion to decimal is:

left(A1,find("-",A1)-1)+right(A1,3)/320

Just been thinking about this; perhaps this, with only one function call,
would be the better way to calculate the decimal value of the OP's formatted
numbers...

=--(SUBSTITUTE(A1,"-"," ")&"/320")

 

[Rick Rothstein]

However, initially you said "110-16 which is equivalent to 110 16/32".

Did you really mean 110-160, as you use later? That is, is the right
part always 3 digits?

I hope so. It would be difficult, but not insurmountable, to
differentiate 2-digit and 3-digit right parts.

And this variation will distinguish between 2 and 3 digit values after the
dash...

=--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,LEN(A1)-2,1)="-","","0"))

so that 110-16 and 110-160 will evaluate to the same value.

 

[JoeU2004]

Nifty!


And this variation will distinguish between 2 and 3 digit
values after the dash...
=--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,LEN(A1)-2,1)="-","","0"))

I had a similar thought after seeing your first follow-up. But for
defensive-programming purposes, I would suggest:

=--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,MAX(1,LEN(A1)-3),1)<>"-","","0"))

That allows for the right part to be 1 digit as well as 2 or 3. I don't
know if that's necessary; I'm not familiar with the format of T-bond prices,
other than what Pakeez wrote. But the flexibility comes with no cost, even
if it is unnecessary.

(Well, almost "no cost". I put MAX(1,...) in at the last minute to handle
cases like 1-1.)

Fun stuff!


----- original message -----

 

[Rick Rothstein]

See inline comments...

Thanks



I had a similar thought after seeing your first follow-up. But for
defensive-programming purposes, I would suggest:

=--(SUBSTITUTE(A1,"-","
")&"/32"&IF(MID(A1,MAX(1,LEN(A1)-3),1)<>"-","","0"))

That allows for the right part to be 1 digit as well as 2 or 3. I don't
know if that's necessary; I'm not familiar with the format of T-bond
prices, other than what Pakeez wrote. But the flexibility comes with no
cost, even if it is unnecessary.

(Well, almost "no cost". I put MAX(1,...) in at the last minute to handle
cases like 1-1.)

I think this would work as well (and it avoids the need for the MAX function
call)...

=--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(LEN(A1)-FIND("-",A1)=3,"0",""))

Fun stuff!

Yep! It sure is. ;-)

 

[pakeez]

I like to Thank You all of you nice guys, who took time to help with my
problem.

Incorporating everybody's suggestion my basic spreadsheet looks per
attachment. Please look to comment in cell E5.

Thank You


+-------------------------------------------------------------------+
|Filename: Bons price calculation example.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=119|
+-------------------------------------------------------------------+