+ Reply to Thread
Results 1 to 3 of 3

Complex Reverse tax de calculation

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    57

    Complex Reverse tax de calculation

    I've got a formula which was provided by a tax brazilian federal agency to calculate the taxes.

    Note: the tax agency did not want to provide the decalculation excel formula so it's up to me to figure it out.

    The formula works great to calculate the value after tax from the value without tax.

    However I need to decalculate the tax to make sure we are aligned.

    There are 3 taxes and they are linked to each other in such a way that despite my best efforts I have failed to come up with something that correctly decalculates it.

    IPI is calculated on the value without tax, ICMS and PIS/Cofins are each calculated on the value of the item including IPI.

    I have attached the Excel file and I hope someone here can think of something that would work!
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Complex Reverse tax de calculation

    A
    B
    C
    D
    E
    F
    G
    H
    1
    w/o tax
    PIS / COFINS
    IPI
    ICMS
    w/ tax
    w/o tax
    2
    $200.00
    9.25%
    5.00%
    4.00%
    $242.63
    $200.00
    E2: =A2 / ( (1 - B2) / (1 + C2) - D2)
    3
    $500.00
    7.00%
    10.00%
    5.00%
    $628.57
    $500.00
    F2: =E2 * ( (1 - B2) / (1 + C2) - D2)
    Last edited by shg; 04-09-2014 at 04:15 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Complex Reverse tax de calculation

    Fantastic!

    Actually your previous formula worked, I tried the exact one you just changed it to but it doesn't work.

    =E2 * (1 - D2 - B2 - C2*D2) / (1 + C2)
    Last edited by witchcraftz; 04-09-2014 at 04:24 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Reverse Calculation OIL
    By nemesis1973 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-27-2013, 03:07 PM
  2. [SOLVED] Reverse calculation
    By JimDandy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-27-2011, 06:49 PM
  3. Reverse Calculation
    By leo73pk in forum Excel General
    Replies: 11
    Last Post: 06-26-2010, 04:39 AM
  4. Reverse Calculation
    By Stephan1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-13-2008, 09:48 AM
  5. [SOLVED] reverse principle calculation
    By Demi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-29-2006, 07:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1