Help with finance formula

ryan james hardy

New Member
Joined
Sep 12, 2012
Messages
19
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

<tbody>
</tbody>

Thanks in advance.

Ryan
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top