How to Calculate Profit for 10 Year US Treasury Note?

[MXL]

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
Buy : 132.19
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

<tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody></tbody>

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,

Vladmir

 

[Rosen]

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

 

[Moxie]

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).

 

[learnexcel21]

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).

Thank you so much for this formula! I was searching for a number of days. I really appreciate you taking the time to post this and wanted you to know that you're still helping people seven years later. I added one more field - number of contracts and then multiplied your formula by that.