Calculation of a Bond Price (Excel 2007)

[MXL]

Greetings,

Now, the calculation for a bond is quite obscure. For example, a move from 125.32 to 124.00 is a total of 64 points.The reason is, in bonds, a move of 32 points is seen as 100, as soon as 125.00 is reached, the next 1 point down will move to 124.32, 124.21, 124.30 until again it reaches 124.00, 123.32 and so on.

Here is my original formula and how it was incorrect with a given example;
A B
1 Sell 125.32
2 Buy 124.00
3 Profit 131.99

The formula I used was: DOLLARFR(DOLLARDE(B1,32)-DOLLARDE(B2,32),32)*100-0.0015, the answer excel gave in B3 was 131.99.
The correct answer should be 63.99, the 0.0015 is simply the commission fee.

This formula I used seemed to work well with certain scenarios, but once price gets to that 32 tip off point, the formula I use is not correct.

Thank you for taking the time to read this, look forward to hearing from you all.

Regards,
Vladmir.

 

[shg]

I think 124.32 is the same as 125, and a one step drop from 125 is 124.31, is it not?

EDIT: Also, is the commission a flat rate per transaction, or a percentage of the sell price, or ...?

 

[shg]

<TABLE style="WIDTH: 330pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=439><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2779" width=61><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 15030" width=329><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 46pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl32 height=16 width=61>Sell</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33 width=49 align=right>125.32</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 247pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 width=329> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 46pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl32 height=16 width=61>Buy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33 align=right>124.00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 46pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl32 height=16 width=61>Shares</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 align=right>100</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 46pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl32 height=16 width=61>Commission</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 align=right>0.015</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 46pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl32 height=16 width=61>Profit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl35 align=right>63.985</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31>B5: =32*(DOLLARDE(B1,32) - DOLLARDE(B2, 32)) - 0.015</TD></TR></TBODY></TABLE>