Try the formula =64*DOLLARDE(B2-B1, 64)
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; ...
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
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
Try the formula =64*DOLLARDE(B2-B1, 64)
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).
Like this thread? Share it with others