# Ceating a formula in one cell to calculate the profit/loss on a bond price.

1. ## Ceating a formula in one cell to calculate the profit/loss on a bond price.

Greetings,

Now, the calculation for a bond is quite obscure. For example, a move from 119.00 to 120.00 is a total of 32 points. so, once the price is at 119.32, the next one point move will take price to 120.00, and so on 120.01, 120.02, 120.03 to 120.32 then 121.00 etc.

Here is my original formula and how it was incorrect with a given example;

A B
1 Sell 125.08
3 Profit 24.27

The formula I used was: (B1-B2-0.0015)*(0.32*100), the answer excel gave in B3 was 24.27.

The correct answer should be 8.9985, the 0.0015 is simply the commission fee. The reason the answer should be 8.9985 is because once price gets from 125.08 to 124.32, that is 9.00 point profit, minus 0.0015 commission would equal 8.9985.

Example: 125.08 to125.00 = 8.00 , 125.00 to 124.32 = 1.00 = Total Profit 9.00 less 0.0015 commission = 8.9985

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

If anyone can assist me with a formula I can use in one cell only, I would greatly appreciate it. Look forward to hearing from you.

Regards,

2. ## Re: Ceating a formula in one cell to calculate the profit/loss on a bond price.

Not clear which version you're using but perhaps:

``Please Login or Register  to view this content.``
above requires activation of Analysis ToolPak pre XL2007.

You say 119 to 120 is 32 points but the implication is 33 points inclusive of 119.00-119.32 (34 if you include 120.00)

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1