• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trying to create a rounding formula with some limits (for stock transactions).

S

SaviourV

New Member
Greetings, everyone. Saviour-V from Malaysia, here.


Sorry if this has been asked before, but I'm not sure about how to create a suitable formula for my stock transaction tracking sheet that I've made in Excel.


The stumbling block is the stamp duty; it's meant to be RM 1.00 (my local currency) for every RM 1,000.00 worth of the contract value or the value of the shares transacted, rounded up to the nearest RM, up to a maximum value of RM 200.00.


http://1-million-dollar-blog.com/how-to-calculate-profit-loss-and-brokerage-fees-in-stock-market-trading/ <- Step 4 of this is where I'm having trouble.


My calculations up to this point are:


1. <B>Total price of shares (E3):</B> = number of shares (C3) * price per share (D3)

2. <B>Brokerage fee (F3):</B>
Code: 
=IF(E3*0.0042 > 12,E3*0.0042, 12)

3. <B>Clearing fee (G3):</B> =ROUNDUP(E3*0.0003,2)


I know that ROUNDUP() is in there somewhere (from experimenting a little), but how do I set the limits in such a way that I can correctly obtain RM 1.00 and RM 200.00 as minimum and maximum values for the formula?


Also, is there a non-nested-IF solution for this? I'm trying to make it look a bit tidy.


If this has been asked in some way, just point me to the nearest section of relevant info, and I'll search there. Sorry for the inconvenience, and thanks for your help, guys! :)
 
Hi, SaviourV!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here. Now your topic is unspammed but please don't ask me why has it been spammed... who knows!, maybe the anti-spam bot was angry.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Just as a tip the following formula keeps a resulting calculation or embedded formula within range from lower to upper value. If that isn't what you're looking after, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the second green sticky post at this forums main page for uploading guidelines.


=MIN(200,MAX(1,A1))

where A1 is the calculated value of the result of the related formula (which can be included directly in this one so as to not use helper cells).


Regards!
 
Thanks, SirJB7. I was wondering why my topic didn't show up initially.


I'll give that piece of code a test run and figure out a formula that'll go with it. I think it's got half my answer, and if it does, I'll post up the full formula.


Also, I'm trying to build up a much stronger project as I go along; I've spotted another stock transaction sheet that's got more features, but it's relying on Google Docs and I'm wondering if Excel's got the equivalent of the FILTER formula...but that's a story for another time. ^_^;;


Here we go...
 
I think maybe I should phrase the problem in pseudo-code so I can see it better:


Code: 
IF ROUND (up/down?) of total price of shares/1000 is more than 200

[code]THEN stamp duty = 200

[code]ELSE IF ROUND (up/down?) of total price of shares/1000 is less than 1

THEN stamp duty = 1[/code]


The above are the limits I'm trying to put into the formula at the moment.


Only thing is trying to determine whether I get RM 2.00 or RM 1.00 worth of stamp duty on a total share cost of RM 1,998.00 - I think I need to get some sample values from my broker(s).


As of right now, my formula looks like this (E3 is the total share cost, by the way):


=IF(ROUND(E3/1000,0)<1,1,IF(ROUND(E3/1000,0)>200,MIN(200,MAX(1,E3)),ROUND(E3/1000,0)))[/code]


But I still think it's wrong. When I tested this out with a total share cost of RM 1,750 .00, I was expecting RM 1.00 of stamp duty instead of RM 2.00.


Maybe there's something I'm missing. I'll keep trying, though.
 
Hi, SaviourV!

According to your definitions for 1750 in E3 you should get 1.75 and not 1, am I wrong?

Despite of that, did you tried the original formula? It works for me; if for you not, please post an example and describe your calculations.

=MIN(200,MAX(1,E3/1000))

Or if you don't want decimal fractions:

=MIN(200,MAX(1,ROUND(E3/1000,0)))

If required, change ROUND function by ROUNDUP or whatsoever it might be suitable for your calculations.

Regards!
 
Just tried your new formula, SirJB7. I think I'm getting close.


You're right, the stamp duty's in whole numbers; the values range from RM 1.00 - RM 200.00, so RM 3.00 is OK, and RM 2.75 is not.


I think the issue's in the first post here: "...for every RM 1,000.00 worth of the contract value...". Maybe I don't understand that bit too well.


My guess is that if a contract's worth less than the nearest thousand, then the stamp duty should be calculated as a lower value (e.g. on an RM 1,995.00 contract, the stamp duty should be RM 1.00 instead of RM 2.00).


2-MINUTE EDIT: Just used this:


Code: 
=MIN(200,MAX(1,ROUNDDOWN(E3/1000,0)))


While it calculates stamp duty as RM 2.00 on an RM 1,990.00-ish contract, I think this is as close as a solution as I can get it. Thanks for all your help, SirJB7.


Wouldn't have done it without your aid. :)
 
Hi, SaviourV!

If it's "...for every R; 1,000.00 worth of the contract value...", define which results do you want for 500, 900, 1100, 1500, 1900 and 2100 values. If 1, 1, 2, 2, 2, and 3, use the ROUNDUP function; if 1, 1, 1, 2, 2, and 2, use the ROUND function; otherwise write down the values you expect.

Regards!


EDIT: Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

But I think that ROUNDDOWN doesn't match with your definitions. And neither with your calculations: for 1990 ROUND and ROUNDUP display 2 and ROUNDDOWN displays 1. Omit this sentence, I've misunderstood your previous words about 1995 example.
 
Yeah, it's still imperfect. Like I said earlier, I'm using it for calculating costs on my stock transactions.


Haven't made any big transactions lately, but in the event I do, at least I have something to track my profits (or losses) properly. Arguably, there was a better one I saw last time, from Investmentmoats.com, but that spreadsheet was in Google Docs, and not all the formulas came through when I saved an Excel copy.


Also, you'd have to input the extra charges yourself, so I wanted to avoid that with my version.


*raises his hands exasperatedly*


I guess I still have a lot to learn. Again, thanks for the help.
 
Finally got it! ^o^/ *lifts his arms in victory...*


Code: 
=MIN(200,MAX(1,ROUND(E3/1000,0)))


Clarified things with the guy in charge of the website; according to him, on an RM 1,500.00 contract, it's RM 2.00 worth of stamp duty.


Now it's correct. I think you can close this topic now, SirJB7. Thanks again! ^_^


P.S: Excuse my use of the Japanese smileys, by the way. Heh.
 
Final formula, after checking with a few people about the charges:


Code: 
=MIN(200,MAX(1,ROUNDUP(E3/1000,0)))


The owner of the blog I spoke to mentioned this:


">RM0 to RM1000, stamp duty is RM1

>RM1000 to RM2000 stamp duty is RM2

>RM2000 to RM3000 stamp duty is RM3

and so on"


...so that pretty much solves the problem completely. Thanks for your earlier help, SirJB7.


Meanwhile, I think I'll look up the rest of the site. Maybe I might find some other bits of info to improve on my current file. Might improve my chances of getting future profits. ^_^
 
Hopefully, it is. My next project might be figuring out some of the formulas in this monstrosity:


http://www.investmentmoats.com/stock-market-commentary/portfolio-management/introducing-our-free-stock-portfolio-tracker-spreadsheet/


Like I said in my second post here, this beast has quite a number of formulae that don't translate directly to Excel, as far as I can tell. I'm suspecting that SUMIF or SUMPRODUCT might be the equivalents of some of the formulae inside it. For instance:


Code: 
=iferror(INDEX(arrayformula(filter(Transactions!I:I,Transactions!C:C<>"",row(Transactions!C:C)=max(if(Transactions!C:C=B3,row(Transactions!C:C),0)))) ;1),0)


...which shows up in the "Units" column of the "Stock Summary" tab, and possibly references a different tab.


Theoretically, I believe it's got something to do with keeping track of the number of stocks purchased under a company name. The tricky bit's actually figuring out the Excel alternative to this formula and a few others, since I don't think Excel's got a FILTER formula. ^_^;; *sweats nervously*


But like I said, that's a story for another topic. For now, I'm good.
 
You must log in or register to reply here.
Back
Top