How to built exemption formula

Shaitaan

New member
Joined
Aug 26, 2020
Messages
1
Reaction score
0
Points
0
Excel Version(s)
365
[FONT=&quot]Hello Experts,[/FONT]

[FONT=&quot]I am working on exemption formula which exempts first 50,000 dollars and as soon as that 50,000 is reached the excess amount is subject to tax.[/FONT]


[FONT=&quot]
Semi_Monthly_IncomeExemption_for_YrExemption_UsedExemption_BalanceSubject_to_Tax_AmountTax_RateTax_AmountPayment #
30,00050,00050,000-30,00020,0000.002%0.001
30,00050,00020,000-30,0000.0010,0002%2002
30,00050,0000.00-30,0000.0030,0002%6003
[/FONT]


[FONT=&quot]Now issue is how do I built this formula to keep track of exemption_used, exemption_balance & subject_to_tax_amount columns. Keeping in mind if there is a reversal the exemption available should automatically be updated.[/FONT]

[FONT=&quot]Looking for help ![/FONT]

[FONT=&quot]Thanks,[/FONT]
 
For your table in A1:H4:

In D2:

=MAX(0,50000-SUM($A$1:A2))

In E2:

=IF(AND(ISNUMBER(E1),E1>0),A2,MAX(0,SUM($A$1:A2)-50000))

And copy down.
 
Back
Top