This post has been closed and is not open for comments or answers.
Quicken Community Home Page

Calculating sales tax backwards from final price

I'm in the EU and my pricelist includes 19% VAT (sales tax).  (This is required by local law.  We cannot provide retail pricing without VAT included.  So, all prices are rounded after VAT inclusion.)  The problem:  If I enter each item with its price divided by 1.19 (to get the pre-VAT price), it almost works but the problem is the final total (due to rounding errors) is not accurate.  For example,  Product Price is EUR 125 (pre-VAT that is approximately 105.042016..., not a nice round number) Shipping cost EUR 10 (pre-VAT that is 8.40336134...)  When VAT is added for the invoice, these end up showing a total of 134.99 instead of the correct 135, because of intermediate rounding errors.  What I want to do instead:  I would like the calculation to go backwards.  That is, for the total, start from the 135 (based on the declared prices of 125 and 10 for the example given), allocate VAT [by doing 135*19/119, or price*VAT_RATE/(100+VAT_RATE)] and then do the same for each individual invoice line [calculating pre-VAT price for each item line by doing price*100/(100+VAT_RATE)]. In all cases results should be rounded to two decimal digits. That way, the totals will be correct both for each line(specially, in case of multiple units per item) and for the grand total.  Is this possible?  (I can do all the calculations and roundings by hand in advance but it's a waste of time.)Also, is there a way to tell Quicken to round on two (or four) decimal digits for each invoice line seperately?  Although, this will work a little better it still isn't as accurate as the above because when dealing with multiple units, the per unit price will be off.
    tonypdmtr:You haven't made it clear which version of Quicken you are using, but it appears that you are talking about H&amp_B.I read your post yesterday and have been giving it some thought._ As you probably know, most of the users on this forum are from the United States and hence are not very familiar with tax laws or business practices in other countries._ I fall into that category and this post is going to be more of a question than a suggestion._ You stated in your post, <STRONG>"I'm in the EU and my pricelist includes 19% VAT (sales tax). (This is required by local law. We cannot provide retail pricing without VAT included. So, all prices are rounded after VAT inclusion.)"</STRONG>I can read this in one of two ways.__The first way is that your_published "Price List," must include VAT and the second way is that the "Price List" you use in Quicken H&amp_B (or any other accounting software for that matter) must include VAT.It would seems to me (based only on logic, not your local law) that as long as_your published price list included VAT that your Quicken price list could omit VAT as long as it is_added to the invoice in a similar fashion as we in the U. S. add state and local sales taxes._ If this is the case, then rounding shouldn't be a problem.As I said, this is a question._ I won't bore you with anything else until you post back._
      Thank you for your response.      Yes, I use H&B 2008 (new user, only a few days.)       OK, let me try to make the situation clearer.      Just like in the US sales tax (VAT in my case) is added on the invoice at the end.  This part is the same.  Individual items on the invoice are listed without VAT.  Same here, also.      The difference is that the published list price in the US does not include sales tax, while in my case it must include VAT. So, if I tell my customer this item costs 99 euros, that means with VAT included.  So, to get the invoice pre-VAT price, I must divide with VAT rate. 99/1.19=83.19 (rounded to cents).  In this example, because the amount is small, rounding will bring the total back to 99, which is correct.  (So, main difference is that the unit price shown on the invoice should be calculated from the actual value given for that item using the current sales tax rate.)   But imagine I sold 10 units.  If I use a declared unit price of 83.19 (99 without VAT, to get around this problem), this would subtotal to 831.9 and when 19% VAT is added, the total comes to 989.96 (instead of the expected 990.00 based on the price my customer knows).  Obviously, as the prices and/or quantities increase, the error increases.      What I would like is for the calculation to go the other way.  So, for the same example:      Unit price 99.00 (with 19% VAT) Qty: 10  Invoice total: 990.00 (with 19% VAT)    The invoice should show the following amounts:      Item price: 83.19 (i.e., 99*100/119 rounded)  Subtotal: 831.93 (i.e., 990*100/119 rounded)  Invoice VAT: 158.07 (i.e., 990*19/119 rounded)    Adding Subtotal and VAT gives the correct total.      (It'd be nice if it could list the item price rounded to 4 digits so one can see the price has sub-cent amounts that are reflected in the subtotal but that's not a big deal.  So, I'm more OK with rounding errors in the pre-VAT item price than in the VAT and grand total.)Hope this is somewhat clearer.
        tonypdmtr:I wasn't clear yesterday with respect to my question._ I also didn't make it clear that I don't use and have never used Quicken H&amp_B._ However, I have used many other business accounting packages._ Obviously, the rounding problem could be minimized if Quicken is capable of using more than two decimal places in its Invoicing Module._ Perhaps a H&amp_B user could comment on this.I understand the rounding dilemma._ What I was trying to say yesterday was that I believe you would have the same problem with any accounting package unless the software were specifically designed with VAT in mind._ In other words, the software would have to be able to work backwards, as you have suggested, from a published "Unit Retail Price" including VAT to an implicit "Unit Retail Price" excluding VAT with the actual amount of VAT being the difference based on the number of units sold.Having said that, I am left with a couple of more specific questions.First, questions about Quicken._ I am only aware of the Canadian and U. S. versions of Quicken H&amp_B, but there may be others._ Which version are you using?_ Are you sure Quicken is appropriate for your needs?Have you spoken to other business owners in your industry about how they handle this VAT problem?_ Have you spoken to the government agency responsible for collecting VAT?If this problem must be accurately addressed (rounding <STRONG>must</STRONG> be accounted for), it suggests to me that Quicken is unsuitable for the task.
          Thank you again for your response.      As far as I know I use the US version (since that's where I purchased it from).      Official accounting software in this country do it correctly, but cost thousands of euros.  My accountant takes care of all the actual accounting.  I got Quicken as a cheaper solution so I can keep my own (unofficial) records, along with my home finances.      I can't complain, Quicken works very well for the purposes and price I got it.      I was just hoping that since it does all those things so well maybe it would save me some time by not having me pre-calculate unit prices one at a time for each invoice, separately.  (Maybe if it had an option somewhere that prices include sales tax, it could accommodate this.  But, I guess I'm out of luck.  No big deal.)  Thanks again.
            tonypdmtr:You're welcome.I'm certainly not the H&amp_B expert you were hoping to find on the forum._ It's still possible that someone else will see this thread and be able to provide some_better suggestions.Good Luck.

              Similar questions other people found helpful: