How to Calculate Profit for 10 Year US Treasury Note?

This is a discussion on How to Calculate Profit for 10 Year US Treasury Note? within the Excel Questions forums, part of the Question Forums category; ...

1. How to Calculate Profit for 10 Year US Treasury Note?

Greetings,

I would like to know how to calculate the profit in excel for a 10 year US Treasury Note. Here are some important inputs..

For example
Sell : 134.04
Profit: \$2,328.13

To understand this, the point value is \$15.625 per cent for a 10 year US bond. This move was 1.49 points in total. 1.49 x 15.625 x 100 (multiplier) = \$2,328.13.

It is confusing, if you input in your calculator, 134.04 – 132.19 = 1.85

Remember, 1.0 is considered 64 for the 10 year US bond. So, for every 1.0 point move, that is counted as 64.

132.19 to 133.19 = 1.0 = 64
133.19 to 134.04 = 0.84 = 84
64 + 84 = 149.

I tried this formula in cell b3 =64*(DOLLARDE(b2,64) - DOLLARDE(b1, 64)) = 113.00.

 A B 1 Buy 132.19 2 Sell 134.04 3 Total 113.00

This is the wrong answer. It should be 149.00 I look forward to hearing from you all, thank you for taking the time to read this, much appreciated.

Regards,

2. Re: How to Calculate Profit for 10 Year US Treasury Note?

Try the formula =64*DOLLARDE(B2-B1, 64)

3. Re: How to Calculate Profit for 10 Year US Treasury Note?

I know this is an old post, but this is a tricky calculation and I would like to post the solution. The difficulty here is more in the math and the logic, not so much with excel.

The 10 year treasury note has a face value of \$100,000. Therefore for every full percentage point movement in the market price, the profit/loss is \$1000 (\$100,000 / 100).

The market price is in 32 increments. Each of those is worth \$31.25 (\$1000/32). Each tick is half of that and is therefore worth \$15.625 (\$1000/64).

To calculate the profit/loss, you need to convert each price from 1/32 to a decimal value. That’s where you use the dollarde function, but the fractional value is 32, not 64. Then you take the decimal difference between the two and multiply by \$1000.

For your example, the formula would be (dollarde(B2,32)-dollarde(B1,32))*1000. Your profit is \$1531.25. You gained a full point from 132.19 to 133.19 = \$1000. Then you gained 13/32 to get to 134.00 and another 4/32 to get to 134.04, which adds \$531.25 (17/32 x \$1000).

Posting Permissions

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