Free Microsoft Excel 2013 Quick Reference

Tax Minus Function

My Texas Instruments calculator has a tax minus (tax-) key that I have used extensively in my business. I am a brand new Excel user and would like to know if there is a "formula" or "equation" that I can use to duplicate this on Excel.

thanks,

Winston


Post your answer or comment

comments powered by Disqus
is there a minus function? say for example I want to have Gross Income - Tax = Net Income whats the minus function called? I cant find it.

Thanks

Hi
If we calculate the difference between 2 dates (the normal minus function), excel gives us the exact number of days between 2 dates. By any function can we know the years, months and days separately like we manually do. Means between 10/01/1998 to 10/02/2002 can excel give 4 years, 1 month and 10 days??

Hello, I'm trying to create a formula where I can compare two cells but accept them as equal is one is plus/minus 0.1.
Example:
Cell A1 is 3, Cell A2 is 3.2, I want Cell A3 to turn red (or report "1")
Cell A1 is 3, Cell A2 is 3.1, I want Cell A3 to turn green (or report "0")

I'm hoping this is an easy one.

Thanks,
Tim

I have 2 dates in 2 columns with the format dd/mm/yy. When I get the max date minus the min date. The result is #Value.

Would you please help me on this matter? Thanks. I also attached the file for your reference.

I need to write some sort of Excel construct (a function?) that will return the federal and state taxes that will be incurred when I withdraw a specified level of spending money from my IRA. This sounds easy, except that the actual taxes incurred are dependent upon the total amount of money that is withdrawn from the IRA--and this total amount includes both the spending money and the money needed to pay the taxes. So computing the taxes cannot be done as a single calculation, but must use some sort of iterative approach. Ideally I would like to start with a spending value, (SV), that would be the amount of money that I want to spend in a year, not counting taxes. The function (or functions) would then go through some sort of iterative calculation process that would compute the total spending level including taxes, (TS), the federal tax, (FT), and the state tax, (ST). These values would have the relationship that TS=SV+FT+ST. When the iteration was finished, the federal tax and state tax amounts would be consistent with the taxes that would be due for this level of total spending as determined by the normal tax tables for both federal and state taxes. I am not concerned that the tax figures be absolutely exact, so a lot of iteration is probably not necessary. Any recommendations for how I might best do this?

Hi all , thanks to the Oheozz for last formular , i have in col B the date , col C person , col D distance , col E im wanting to get distance change from there previous entry , eg , B17 Ringo C17 2100 - Ringo C16 1850 = in E17 +250 , John C15 1800 - John C14 2000 = -200 . George C7 2100 - George C5 2100 = 0 . So in col E i would have +250 , next row -200 , next row 0 , thanks in advance , see sample sheet . Kevin . R/C DateA PersonB DistancC Distance change +/- E
2 1990-12-10 John 1200
3 1990-12-11 Paul 1400
4 1990-12-12 George 2100
5 1990-12-13 Ringo 1600
6 1990-12-14 George 2100
7 1990-12-15 Paul 1200
8 1990-12-16 Ringo 1400
9 1990-12-17 John 1400
10 1990-12-18 John 1600
11 1990-12-26 Ringo 2000
12 1990-12-26 George 3200
13 1990-12-26 Paul 1100
14 1990-12-30 John 2000
15 1991-12-30 John 1800
16 1992-12-26 Ringo 1850
17 1993-06-26 Ringo 2100

I want to calculate the difference between income and expense by subtracting
one total cell from the other total cell. I don't find any reference to
subtract or minus functions in Excel. HELP

I'm having a problem with #Value. I've imported some historical price data from Bloomberg in column A and B.

A B C
ticker x ticker y ticker y - ticker x
1 #N/A N/A #N/A N/A #VALUE! (#N/A N/A)
2 93.707 #N/A N/A #VALUE! (93.707)
3 #N/A N/A 790 #VALUE! (-790)
4 93.375 790.5 -697.125
5 93.707 795.013 -701.306
6 92.504 803.085 -710.581

The problem is with cells C1, C2 , and C3.

I need to do some further analysis with the data in column C, but because it returns as a #Value, I can't do them. I need the data in Column C to either be numerical or #N/A N/A. I know that if I use =SUM(A2,B2) my data in C3 will return the value in A2 (93.707), but i need to do a subtraction. There is no DIFFERENCE or MINUS function (similar to SUM) that I'm aware of and I can't multiply column A or B by -1 as this will still return #Value as an answer in Column C. any ideas on how i can achieve the results i'm looking for? i've put the answer in parenthesis for waht i'm trying to achieve.

Thanks

dw

Hello everyone

Here's the outline. I have been told to use this to calculate tax: When calculating the tax, assume that the monthly gross profit taxed @ 20% for the first $20,000 then 30% for the remainder of gross profit.

The monthly gross profit is $20,153 (C14) and the tax table (table7) is below:

Taxable Income Fixed Tax Marginal Tax Rate 0 0 0.2 20000 3999.8 0.3
I don't know how to calculate the 20% of the first $20,000.

This is what I have done for the fixed rate: VLOOKUP(C14,Table7,2,TRUE) which gave me 3999.8

and for the 30% of the remainder ($153) I did: (C14-VLOOKUP(C14,Table7,1,TRUE))*VLOOKUP(C14,Table7,3,TRUE) which gave me 45.90

Is there a simpler way to do all this? And can IF function be used along with VLOOKUP at all? Thanks in advance

Faye

Additional info: I also need to allow the formula to show an operational loss (i.e. profit < $0), what does that mean?
Note: All formulae must contain VLOOKUP.

The PMT function should work just fine, with a couple of caveats. Assuming
you've set the number of payments (NPER) to the number of months, you need to
adjust your interest rate accordingly (eg: not 6%, but 6%/12). You'll also
want to put a minus sign on the PV argument. Ex: a 30 year loan at 6.5% for
$145000: =PMT(6.5%/12,360,-145000). If you can't get the function to behave
as you expect, post the terms and the formula you're using. (Also, remember
that your total payment will probably include taxes and insurance, which
won't be captured in this formula).
--Bruce

"JSEVonda" wrote:

> Does anyone have a function to calculate a amortized mtg payment. I tried to
> use the pmt function, and it came out all wrong. Have tried many different
> iterations of the numbers, and they still do not come out to be same as my
> Loan Officer Point software indicates the answer should be.
>
> I need to put this function calulation in an excel spreadsheet.
>
> Thanks

I want to make a function via macro to calculate tax During search I have found a code but the result is not coming correctly. Can some expert please rectify it?
Tax rate is:

Upto 110000 > 0%

More than 110000 but less than 150000 > 10%

More than 150000 but less than 250000 > 4000 + 20%

Above 250000 > 24000 + 30%

For checking I am providing some solution:
Income Tax Payable
110000 > 0
130000 > 2000
150000 > 4000
160000 > 6000
250000 > 24000
490000 > 96000

The function is:

	VB:
	
 Single) 
    Select Case income 
    Case Is

The PMT function should work just fine, with a couple of caveats. Assuming
you've set the number of payments (NPER) to the number of months, you need to
adjust your interest rate accordingly (eg: not 6%, but 6%/12). You'll also
want to put a minus sign on the PV argument. Ex: a 30 year loan at 6.5% for
$145000: =PMT(6.5%/12,360,-145000). If you can't get the function to behave
as you expect, post the terms and the formula you're using. (Also, remember
that your total payment will probably include taxes and insurance, which
won't be captured in this formula).
--Bruce

"JSEVonda" wrote:

> Does anyone have a function to calculate a amortized mtg payment. I tried to
> use the pmt function, and it came out all wrong. Have tried many different
> iterations of the numbers, and they still do not come out to be same as my
> Loan Officer Point software indicates the answer should be.
>
> I need to put this function calulation in an excel spreadsheet.
>
> Thanks

Hi,

What function or calculation could I use in a spreadsheet to calculate the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate in
percent so I could figure out what the price for an item should be so in the
end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Hello,

I am currently working on a project that requires me to get the total number of business days between two dates. I have used the NETWORKDAYS function and it works great. However, I need a formula (if their is one) that will give me the total number of business days between the dates and either A) minus 1 from the total or B) not include the first day as an actual work day.

example.

Start date - 3/20/12
End date - 3/23/12
Total number of business days minus 1 = 3

I can do this if i use the function, copy and paste special and then create another row next to the total and do a =A1-1 all the way down but was just wondering if their was an easier way.

Any help with this would be much appreciated.

Problem:

Calculating the retail price and sales tax (7.75 percent) matching each total price in column A.

Solution:

Using the ROUND function, as follows:
To calculate Retail Price:
=ROUND(A2/(1+7.75%),2)
To calculate Sales Tax:
=ROUND(7.75%*B2,2)

I am currently adding a macro to add a function to my BulkQuotesXL package. What I am trying to do is after my AnalyzerXL package gets the quotes for my stocks I am trying to calculate the min value from my Low column and a max column from my High Column. Once the calculation is done I like to mark the low number as red and the high number as green. Than put the determined value for high in column G and the determined value for low in column H. Once this is done I would like to subtract the first high value from the first low value. Put that value in the I column. My current formula for determining the low value is If LowM2 < -0.15 And LowM1 < 0.08 And LowP1 > 0.05 And LowP2 > -0.1 Then
this is not working correctly so realize I have a flaw in my logic any help would be appreciated . I have been thinking of using the application.worsheet.min function but I don't really understand how to set that up.


	VB:
	
 
Sub NewCalculation2() 
    Dim ws As Worksheet 
     
     'First Loop defines high low values
     '21710 Changed R1 from long to Double because Loop is marking everything green fixed problem
    Dim R1 As Double 
     'Second Loop
    Dim R2 As Double 
    Dim LowM2, LowM1, LowP1, LowP2 As Double 
    Dim HighM2, HighM1, HighP1, HighP2 As Double 
    Dim MarkedRed As Double ' Tracker for column H
    Dim MarkedGreen As Double ' Tracker for column G
    Dim SR As Long 
     'LR is Last Row
    Dim LR As Long 
     'Dim LastRow1 as Double
    Dim LR1 As Double 
    Dim BUY1 As Double 
    Dim BUY2 As Double 
    Dim BUY3 As Double 
    Dim BUY4 As Double 
    Dim SELL1 As Double 
    Dim SELL2 As Double 
    Dim SELL3 As Double 
    Dim SELL4 As Double 
    Dim STOP1 As Double 
    Dim STOP2 As Double 
    Dim STOP3 As Double 
    Dim STOP4 As Double 
     
     'Last Row first loop
    Dim LRL1 
     'Last Row second loop
    Dim LRL2 
     
    Dim R3 As Long 
     'R4 is range4
    Dim R4 As String 
     'Low Calculation minus 1 cell
     
    Dim ShName As String 
    Dim CellRef As Range 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.EnableEvents = False 
     
     
    Call BulkQuotesXL.UpdateData 
     'Start update format
     
    For Each ws In Worksheets 
        With ws 
            .Activate 
            Range("A3").Select 
             'Calculate Last Row
            LR = ActiveSheet.UsedRange.Rows.Count 
             
             
            Select Case .Name 
            Case "BulkQuotesXL Settings" 
            Case Else 
                 'Freeze Panes
                Rows("2:2").Select 
                ActiveWindow.FreezePanes = True 
                 
                 ' Setup Worksheet
                BUY1 = 1 
                BUY2 = 1.236 
                BUY3 = 1.382 
                BUY4 = 1.5 
                 
                SELL1 = 0.618 
                SELL2 = 0.786 
                SELL3 = 1 
                SELL4 = 1.236 
                 
                STOP1 = 1.2 
                STOP2 = 1.3 
                STOP3 = 1.4 
                STOP4 = 1.6 
                 
                 
                 
                 
                 
                Range("G1").Value = "High" 'B leg High header
                Range("H1").Value = "Low" 'A leg Low header
                Range("I1").Value = "Diff" 'b-a or a-b difference header
                Range("J1").Value = "Buy @ " & BUY1 'Buy1 output
                Range("K1").Value = "Stop @ " & STOP1 ' Stop1 Output
                Range("L1").Value = "Buy @ " & BUY2 'Buy2 output
                Range("M1").Value = "Stop @ " & STOP2 'Stop2 Output
                Range("N1").Value = "Buy @ " & BUY3 ' Buy3 output
                Range("O1").Value = "Stop @ " & STOP3 'Final output
                Range("P1").Value = "Buy @ " & BUY4 ' Buy4 output
                Range("Q1").Value = "Stop @ " & STOP4 'Stop4 output
                Range("R1").Value = "Sell @ " & SELL1 'Final output
                Range("S1").Value = "Sell @ " & SELL2 'Final output
                Range("T1").Value = "Sell @ " & SELL3 'Final output
                Range("U1").Value = "Sell @ " & SELL4 'Final output
                 
                 ' Ini Variables & Fill Static Vars.
                MarkedRed = 0: MarkedGreen = 0 
                 '** START - Main Routine **
                For R1 = 2 To LR '
                     'Min Value Calculation // A leg Low location
                    If R1 = 2 Then LowM2 = (Range("D" & R1) - Range("D" & R1 + 2)) 
                    If R1 = 2 Then LowM1 = (Range("D" & R1) - Range("D" & R1 + 1)) 
                    If R1 = 3 Then LowM2 = (Range("D" & R1) - Range("D" & R1 + 2)) 
                    If R1 = 3 Then LowM1 = (Range("D" & R1) - Range("D" & R1 + 1)) 
                    If R1 > 3 Then LowM2 = (Range("D" & R1) - Range("D" & R1 - 2)) 
                    If R1 > 3 Then LowM1 = (Range("D" & R1) - Range("D" & R1 - 1)) 
                    LowP1 = Range("D" & R1 + 1) - Range("D" & R1) 
                    LowP2 = Range("D" & R1 + 2) - Range("D" & R1) '
                     'Max Value Calculation // B leg High location
                    If R1 = 3 Then HighM2 = (Range("C" & R1) - Range("C" & R1 + 2)) 
                    If R1 = 3 Then HighM1 = (Range("C" & R1) - Range("C" & R1 + 1)) 
                    If R1 > 3 Then HighM2 = (Range("C" & R1) - Range("C" & R1 - 2)) 
                    If R1 > 3 Then HighM1 = (Range("C" & R1) - Range("C" & R1 - 1)) 
                    If R1 > 3 Then HighP1 = Range("C" & R1 + 1) - Range("C" & R1) 
                    If R1 > 3 Then HighP2 = Range("C" & R1 + 2) - Range("C" & R1) '
                     ' Leg LOW validator    '
                     'Setting up buy signal
                    If LowM2 < -0.15 And LowM1 < 0.08 And LowP1 > 0.05 And LowP2 > -0.1 Then 
                        Range("H" & R1).Value = (Range("D" & R1)) 
                        Range("D" & R1).Interior.Color = vbRed '        ' record cell value for later calculations
                        MarkedRed = Range("H" & R1) '        ' check other tracking marker to see if
                         ' Sell calcuations are to be done at this time
                        If MarkedRed  0 Then '            ' Put answer in Column I.
                             
                        ElseIf MarkedGreen = 0 Then 
                             
                        End If 
                    End If '    ' Leg HIGH validator    '
                    If HighM2 >= -0.2 And HighM1 >= -0.05 And HighP1 < -0.2 And HighP2 < -0.05 Then 
                        Range("G" & R1).Value = (Range("C" & R1)) 
                        Range("C" & R1).Interior.Color = vbGreen ' record cell value for later calculations
                        MarkedGreen = Range("C" & R1) ' check other tracking marker to see if
                         ' Buy calcuations are to be done at this time
                        If MarkedRed  0 Then '            ' Put answer in Column I.
                            Range("I" & R1).Formula = (MarkedGreen - MarkedRed) 
                            Range("J" & R1).Formula = (((MarkedGreen - (Range("I" & R1).Value) * BUY1))) 'First Buy Point
                            Range("K" & R1).Formula = ((MarkedGreen - (Range("I" & R1).Value) * STOP1)) 'New Formula First
Stop
                            Range("L" & R1).Formula = (((MarkedGreen - (Range("I" & R1).Value) * BUY2))) 'Second Buy Point
                            Range("M" & R1).Formula = ((MarkedGreen - (Range("I" & R1).Value) * STOP2)) 'New Formula Second
Stop
                            Range("O" & R1).Formula = ((MarkedGreen - (Range("I" & R1).Value) * STOP3)) 'New Formula Third
Stop
                            Range("P" & R1).Formula = (((MarkedGreen - (Range("I" & R1).Value) * BUY4))) 'Third Buy Point
                            Range("Q" & R1).Formula = ((MarkedGreen - (Range("I" & R1).Value) * STOP4)) 'New Formula Third
Stop
                            Range("R" & R1).Formula = (MarkedRed + (Range("I" & R1).Value) * SELL1) 'First Sell point
                            Range("S" & R1).Formula = (MarkedRed + (Range("I" & R1).Value) * SELL2) 'Second Sell point
                            Range("T" & R1).Formula = (MarkedRed + (Range("I" & R1).Value) * SELL3) 'Third Sell point
                            Range("U" & R1).Formula = (MarkedRed + (Range("I" & R1).Value) * SELL4) 'Fourth Sell point
                             ' ' RESET trackers for next group of numbers (if any)
                            MarkedRed = 0: MarkedGreen = 0 
                        End If 
                    End If 
                Next R1 
                 '' ** END - Main Routine**'
                 'MsgBox "Differential Calculations Are Done ", vbOKOnly
                 '
                 
                Columns("V").Select 
                Range("V1").Value = ("Prev Close-High") 
                Range("V3" & ":" & "V" & LR - 1).Formula = "=(C3-E2)/E2" 
                 
                Columns("W").Select 
                Range("W1").Value = ("Prev Close-Low") 
                Range("W3" & ":" & "W" & LR - 1).Formula = "=(D3-E2)/E2" 
                 
                Columns("X").Select 
                Range("X1").Value = ("Prev Close-Open") 
                Range("X3" & ":" & "X" & LR - 1).Formula = "=(B3-E2)/E2" 
                 
                Columns("Y").Select 
                Range("Y1").Value = ("Day High to Day Low") 
                Range("Y3" & ":" & "Y" & LR - 1).Formula = "=(C2-D2)/D2" 
                 
                Columns("Z").Select 
                Range("Z1").Value = ("Prev High-High") 
                Range("Z3" & ":" & "Z" & LR - 1).Formula = "=(C3-C2)/C2" 
                 
                Columns("AA").Select 
                Range("AA1").Value = ("Prev Low-Low") 
                Range("AA3" & ":" & "AA" & LR - 1).Formula = "=(D3-D2)/D2" 
                 
                 
                 '20 Day Moving Average
                Range("AB1").Value = "20 day Average" 
                Range("AB20:AB" & (LR - 1)).Formula = "=SUM(E1:E20)/20" 
                 
                 '50 Day Moving Average
                Range("AC1").Value = "50 day Average" 
                Range("AC50:AC" & (LR - 1)).Formula = "=SUM(E1:E50)/50" 
                 
                 '100 Day Moving Average
                Range("AD1").Value = "100 day Average" 
                Range("AD100:AD" & (LR - 1)).Formula = "=SUM(E1:E100)/100" 
                 
                 '150 Day Moving Average
                Range("AE1").Value = "150 day Average" 
                Range("AE150:AE" & (LR - 1)).Formula = "=SUM(E1:E150)/150" 
                 
                 '200 Day Moving Average
                Range("AF1").Value = "200 day Average" 
                Range("AF200:AF" & (LR - 1)).Formula = "=SUM(E1:E200)/200" 
                 
                 
            End Select 
             
             
            Select Case .Name 
            Case "BulkQuotesXL Settings" 
                Columns("B:C").Select 
                With Selection 
                    .NumberFormat = "mm/dd/yyyy" 
                End With 
                Columns("F").Select 
                With Selection 
                    .NumberFormat = "text" 
                End With 
                 
                Columns("J").Select 
                With Selection 
                    .Columns.Autofit 
                    .VerticalAlignment = xlCenter 
                    .HorizontalAlignment = xlRight 
                End With 
                 
                Columns("K").Select 
                With Selection 
                    .Columns.Autofit 
                    .VerticalAlignment = xlCenter 
                    .HorizontalAlignment = xlRight 
                End With 
                 
                Range("A3").Select 
            Case Else 
                Rows("2:2").Select 
                ActiveWindow.FreezePanes = True 
                Columns("A").Select 
                With Selection 
                    .NumberFormat = "mm/dd/yyyy" 
                End With 
                Columns("B:E").Select 
                With Selection 
                    .NumberFormat = "0.00" 
                End With 
                Columns("F").Select 
                With Selection 
                    .NumberFormat = "#,##0" 
                End With 
                Columns("G:U").Select 
                With Selection 
                    .NumberFormat = "0.00" 
                End With 
                Columns("A:U").Select 
                With Selection 
                    .Columns.Autofit 
                    .VerticalAlignment = xlCenter 
                    .HorizontalAlignment = xlRight 
                End With 
                Columns("V:AA").Select 
                With Selection 
                    .NumberFormat = "0.00%" 
                End With 
                 
                Columns("AB:AF").Select 
                With Selection 
                    .NumberFormat = "0.00" 
                    .Columns.Autofit 
                    .VerticalAlignment = xlCenter 
                    .HorizontalAlignment = xlRight 
                End With 
                 
                 
                 
                R3 = ActiveSheet.UsedRange.Rows.Count 
                R4 = "F" & R3 
                If R3 > 21 Then SR = R3 - 20 
                If R1 > 15 Then SR = R1 - 14 
                If R1 > 10 Then SR = R1 - 8 
                If R3

In a salary table if any one draw upto 3000 tax deduction will be 40 ,upto 5000 tax deduction will be 60 and upto 10000 tax deduction will be 100. What would be the function for that?

Hi,

I am trying to show the status of the task completion by using IF function. I have the focus and actual columns in my worksheet. The focus is formatted as date, and the actual is date or percentage (if it is date or 100% then mean that the task is completed). The result which i am looking for is as below:

- If Focus is blank then it would show nothing in the Status
- If Actual is 100% or any date then it would show DONE the Status
- If Focus is smaller than today date then it would show OVERDUE in the Status
- If Focus minus today date is smaller than 8 then it would show TO FINISH IN 7 DAYS in the Status

The enclosed attachment is my sample worksheet. Please kindly advise.

A similar question was post http://www.excelforum.com/excel-gene...ml#post2526109

Sorry for any inconvenience.

Thanks,
sanlen

can anyone help....?

please see attached example to oz final

i cant work this out!!!!!

1. row 17 needs to: equal row 3 until row 3 hits a value>0
2. following the first entry in row 17 the next cell must equal the previous cell minus the cell below that one eg: f17=E17-E18
3. this all needs to be in one formula!

I would like to create a VBA function that calculates federal and state income taxes. I know how to do this in Excel using a table, but I do not want to put the calculator in an Excel spreadsheet. Instead I want the data (i.e., the tax tables) to be in the VBA macro that I can access as a function in any spreadsheet. I would think that I would need an array, and a loop of some kind. I'd like to be able to enter the tax table easily into the VBA macro. I think that the arguments of the tax rate table are taxable income, filing status and year. I've got to believe that this has been done before -- I did it many years ago in BASIC but i forgot how to do it. I remember that I would type in a line that would be something like (7,825,31,850,.15,782.50) and the program would look at the taxable income. If it were between (in this example) $7,825 and $31,850 it would take the taxable income, subtract $7,825 from it, multiply that by .15 and add 782.50. That would be the function's result. If the taxable income were greater than $31,850, it would go to the next line, which would also be four numbers representing the parameters of the next tax bracket. Do you know where either I could find sample code that I could adapt, or could you provide more guidance on how I write the code myself? Thanks.

Hey guys,

I'll get straight to the point. I have a n number of rows with 3 columns: Date, Tax, Value. My objective is to have my code loop throught the rows and sum a monthly amount of tax collected. I need to do this for the entire year. Now I don't think rewritting the loop 12 times is the most efficient way of doing this. Also, I need to have the count and the sum reset after the loop is done. Is there a simpler way of doing this? I'm thinking this coudl be done with a function and some defined variables at the beginning, but i'm not sure.

Thanks.

Here is the code...

Sub Monthly_Sum()
Dim row, count As Integer
Dim sum As Long
Worksheets("Monthly").Activate
row = 2
count = 0
sum = 0

'April 2010
Do Until Cells(row, 2) = 20100501
count = count + 1
sum = sum + Cells(row, 4)
row = row + 1
Loop
Cells(8, 8) = row
Cells(6, 8) = count
Cells(7, 8) = sum

'May 2010
count = 0
sum = 0
Do Until Cells(row, 2) = 20100601
count = count + 1
sum = sum + Cells(row, 4)
row = row + 1
Loop
Cells(8, 9) = row
Cells(6, 9) = count
Cells(7, 9) = sum

Hello all!

As the new financial year is approaching, the ATO has released another PAYG tax table, for payments made on or after 1 July 2008.

The code below will calculate the weekly withholding amount for Scale 2 (Where payee is eligible to receive leave loading and has claimed tax-free threshold Scale 2)

You may easily edit the figures below to suite your required tax scale
http://www.ato.gov.au/content/downlo...1n10040508.pdf

Simply paste the following code into a module and whenever you need to use the formula simply type:

=tax(A1) - Where A1 is the cell that contains the weekly Gross wages


	VB:
	
 tax(gross) 
     
     'A and B are co-efficients which should be changed according to your tax Scale
     'Below they are set to Scale 2 Where payee is eligible to receive leave loading
     'and has claimed tax-free threshold
     
     'For more info on the tax formula visit:
     '   http://www.ato.gov.au/content/downloads/BUS00138601n10040508.pdf
     
    Dim a 
    Dim b 
     
     'Add 0.0001 to gross to round to nearest dollar if tax = $X.50 cents exactly, as required by the ATO
    gross = gross + 0.0001 
    x = CInt(gross) 
     
     'Calculate Tax
     
    If x < 186 Then 
        a = 0 
        b = 0 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    ElseIf x < 329 Then 
        a = 0.1514 
        b = 28.2692 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    ElseIf x < 387 Then 
        a = 0.2524 
        b = 61.5554 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    ElseIf x < 571 Then 
        a = 0.1666 
        b = 28.2697 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    ElseIf x < 647 Then 
        a = 0.1868 
        b = 39.8081 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    ElseIf x < 1147 Then 
        a = 0.335 
        b = 135.8235 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    ElseIf x < 1532 Then 
        a = 0.315 
        b = 112.8697 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    ElseIf x < 3455 Then 
        a = 0.415 
        b = 266.1004 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    Else 
        a = 0.465 
        b = 438.8697 
        y = (a * (x + 0.99)) - (b) 
        tax = CInt(y) 
         
    End If 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi there,

Is there a "Sum" function that can be used for a cumulative series of subtractions?

For example, if I have 10, 12 and 15 in 3 respective cells, and use the sum function upon them, it will return 37.

However, I want to say 10 minus 12 minus 15, and thus return -17.

Any help would be greatly appreciated.

Many thanks,

Rich

I have a issue. I have a date that is save as a date variable. Is there any quick functions that can be used in VBA that will get a date split by tax year.

For Example

If I was wanting to look at tax year 06 this would be from April 06 to March 07. The year would have to be determined by a entered year Input box.
After it has been split by year I then if it falls between the dates to split the date by Month. Is there any simple functions and comparitors I can use on a date defined variable.

Thanks for your Help.


No luck finding an answer? You could always try Google.