# Thread: Help with finance formula Thanks: 0 Likes: 0

1. ## Help with finance formula

Hi
I have made a table which i use to calculate potential gains made trading stock if gains are realised every time the stock gains. Hopefully the screenshot demonstrates what im trying to explain. Currently i am manually adding the formulas for each day. The formula changes depending on weather the gains have been on consecutive days or not (the brokerage cost changes). Anyone have any idea how i might go about using a conditional formula to calculate Columns B and D.
 Comment Start amount Formula in B End amount Formula in C action day date Open Adj Close* ILC (ASX20) 30000 29997.35166 "=(B4+(B4*(N4/100)))-(19.95*2)" Tuesday 21-Jan-14 0.124172185 Wednesday 22-Jan-14 -0.289375775 Thursday 23-Jan-14 -1.034340091 Friday 24-Jan-14 -0.166666667 29997.35166 "=D4" 30092.77609 "=(B8+(B8*(N8/100)))-(19.95)" Tuesday 28-Jan-14 0.384615385 30092.77609 "=D8" 30181.921 "=(B9+(B9*(N9/100)))" Wednesday 29-Jan-14 0.296233601 30181.921 "=D9" 30290.40471 "=(B10+(B10*(N10/100)))-(19.95)" Thursday 30-Jan-14 0.425531915 Friday 31-Jan-14 -0.96598068 Monday 3-Feb-14 -0.844238075 Tuesday 4-Feb-14 -0.98206661 Wednesday 5-Feb-14 -1.411462789 30290.40471 "=D10" 30446.51682 "=(C15+(C15*(O15/100)))-(19.95*2)" Thursday 6-Feb-14 0.647109577 Friday 7-Feb-14 -0.042607584 Monday 10-Feb-14 -0.504413619

Ryan  Reply With Quote

2. ## Re: Help with finance formula

If i understand the data correctly the formulas in B & C do only depend on column ILC(ASX20). If that is the case then you have four possible situations

1) ILC negative --> B & C should be zero
2) ILC positive and previous and next day negative --> formula minus 2x brokerage fee
3) ILC positive and previous and next day positive --> formula without any brokerage fee
4) ILC postive and 1 day (previous or next) negative --> formula minus 1x brokerage fee.

You need to extend the formulas in B & C and test these conditions with formulas such as IF, AND & OR. Considering the example you gave I think you can figure the rest out yourself together with some help from Google. However if you still need additional help just let me know and I will write the formula down for you.

Dirk  Reply With Quote

3. ## Re: Help with finance formula Originally Posted by dirkgeraedts If i understand the data correctly the formulas in B & C do only depend on column ILC(ASX20). If that is the case then you have four possible situations

1) ILC negative --> B & C should be zero
2) ILC positive and previous and next day negative --> formula minus 2x brokerage fee
3) ILC positive and previous and next day positive --> formula without any brokerage fee
4) ILC postive and 1 day (previous or next) negative --> formula minus 1x brokerage fee.

You need to extend the formulas in B & C and test these conditions with formulas such as IF, AND & OR. Considering the example you gave I think you can figure the rest out yourself together with some help from Google. However if you still need additional help just let me know and I will write the formula down for you.

Dirk
Thanks for clarifying that. Yes i will have a look around and see what i can hack together. If i can't figure it out ill be back. Thanks for your help.

Ryan  Reply With Quote

4. ## Re: Help with finance formula Originally Posted by dirkgeraedts If i understand the data correctly the formulas in B & C do only depend on column ILC(ASX20). If that is the case then you have four possible situations

1) ILC negative --> B & C should be zero
2) ILC positive and previous and next day negative --> formula minus 2x brokerage fee
3) ILC positive and previous and next day positive --> formula without any brokerage fee
4) ILC postive and 1 day (previous or next) negative --> formula minus 1x brokerage fee.

You need to extend the formulas in B & C and test these conditions with formulas such as IF, AND & OR. Considering the example you gave I think you can figure the rest out yourself together with some help from Google. However if you still need additional help just let me know and I will write the formula down for you.

Dirk
Hello again,
If you could write the formula it would be much appreciated.

Thanks,

Ryan James Hardy  Reply With Quote

5. ## Re: Help with finance formula

I have made the following formula which works for every line except the first one:

=IF(AND(T4>0,T3<0,T5<0),(B4+(B4*(T4/100)))-(19.95^2),IF(AND(T4>0,T3<0,T5>0),(B4+(B4*(T4/100)))-(19.95),IF(AND(T4>0,T3>0,T5>0),(B4+(B4*(T4/100))),IF(AND(T4>0,T3>0,T5<0),(B4+(B4*(T4/100)))-(19.95),0))))

Probably something simple i am missing.  Reply With Quote

6. ## Re: Help with finance formula

Hey Ryan,

Good job figuring out yourself, however i think your formula contains still contains two errors:

1. first, your formula doesn't account for the fact that some days there might be a 0 loss/gain. in some of your conditions use signs as "<="(smaller or equal to) or ">="(greater or equal to) instead of < and > otherwise you'll miss possible outcomes. This is probably also what is causing the error on your first line of data because its previous day is probably 0.
2. in the first IF formula you take 19.95 to the power of 2 (=19.95*19.95) instead of multiplying it by 2 as in your initial example. Is this correct? If not, change it to 19.95*2.

Finally i have shortened the formula you wrote down above in case you're interested.

=IF(AND(N4>0,N3<0,N5<0),(B4+(B4*(N4/100)))-(19.95*2),IF(AND(N4>0,OR(N3<0,N5<0)),(B4+(B4*(N4/100)))-(19.95),(B4+(B4*(N4/100)))))

Good luck,

Dirk  Reply With Quote

7. ## Re: Help with finance formula Originally Posted by dirkgeraedts Hey Ryan,

Good job figuring out yourself, however i think your formula contains still contains two errors:

1. first, your formula doesn't account for the fact that some days there might be a 0 loss/gain. in some of your conditions use signs as "<="(smaller or equal to) or ">="(greater or equal to) instead of < and > otherwise you'll miss possible outcomes. This is probably also what is causing the error on your first line of data because its previous day is probably 0.
2. in the first IF formula you take 19.95 to the power of 2 (=19.95*19.95) instead of multiplying it by 2 as in your initial example. Is this correct? If not, change it to 19.95*2.

Finally i have shortened the formula you wrote down above in case you're interested.

=IF(AND(N4>0,N3<0,N5<0),(B4+(B4*(N4/100)))-(19.95*2),IF(AND(N4>0,OR(N3<0,N5<0)),(B4+(B4*(N4/100)))-(19.95),(B4+(B4*(N4/100)))))

Good luck,

Dirk
Thanks for your help. Yes i noticed the X^2 error. I was wondering whether to use <= but decided to just try and get it working first and then tweak later. Thanks for shortening the formula. I'll take a closer look at how you did that so i'll know for next time. Another questions: Is it preferable to use

(T4>0)*(T3<0)*(T5<0)+(T4>0)*(T3="")*(T5<0)

or this

OR(AND(T4>0,T3<0,T5<0),AND(T4>0,T3="",T5<0))

Thanks for your help,
Ryan  Reply With Quote

8. ## Re: Help with finance formula

I prefer the second because in my opinion it's easier to read and less prone to error. If you don't be careful with your conditions in the first example then you can, theoretically have 2 as an outcome which cannot happen in the second example. The second example will always be 0 (false), 1 (true) or give an error message.

If you make this change to your formula just for the first line of your data then don't. If the only line that will benefit from it is the first then I would put a different formula in the first line or extend the ILC column one day before the start your calculations.  Reply With Quote

9. ## Re: Help with finance formula Originally Posted by dirkgeraedts I prefer the second because in my opinion it's easier to read and less prone to error. If you don't be careful with your conditions in the first example then you can, theoretically have 2 as an outcome which cannot happen in the second example. The second example will always be 0 (false), 1 (true) or give an error message.

If you make this change to your formula just for the first line of your data then don't. If the only line that will benefit from it is the first then I would put a different formula in the first line or extend the ILC column one day before the start your calculations.
Thanks,
I just realized that a formula may be needed for Column B (The starting figure). Because to get the end of day price, you first need the start of day price. I could probably work something out but if you know of a quick solution it would be helpful.

Cheers,
Ryan  Reply With Quote

10. ## Re: Help with finance formula

I hope I understood your case correctly but the condition is as follow:

- If the previous day was positive then your end amount changed the previous day because you had a gain and otherwise your end amount remained unchanged because nothing happened.

In formula for cell B4: IF(N3>0,C3,B3)

Put is formula in every cell of column B and you will have the entire B column occupied with numbers. Which brings me back to your previous formula, if you put B4 before the final bracket of your formula in column C you will have the entire column C occupied with numbers as well. Start and End coluymn won't always show a difference (only on positive gain days) but this way you have some thing that looks more like a dataset. Another benefit you'll have from this structure is that you can easily add a delta column which is B4-C4. Just a thought   Reply With Quote

## User Tag List

#### Posting Permissions

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