Help with finance formula
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

  1. #1
    New Member
    Join Date
    Sep 2012
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Thanks in advance.

    Ryan

  2. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  3. #3
    New Member
    Join Date
    Sep 2012
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with finance formula

    Quote Originally Posted by dirkgeraedts View Post
    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

  4. #4
    New Member
    Join Date
    Sep 2012
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with finance formula

    Quote Originally Posted by dirkgeraedts View Post
    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

  5. #5
    New Member
    Join Date
    Sep 2012
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  6. #6
    Board Regular
    Join Date
    Mar 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  7. #7
    New Member
    Join Date
    Sep 2012
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with finance formula

    Quote Originally Posted by dirkgeraedts View Post
    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

  8. #8
    Board Regular
    Join Date
    Mar 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  9. #9
    New Member
    Join Date
    Sep 2012
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with finance formula

    Quote Originally Posted by dirkgeraedts View Post
    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

  10. #10
    Board Regular
    Join Date
    Mar 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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