xlf sample :: QandA series

ATO company tax rates → User Defined function (UDF)


The QUESTION



The full company tax rate in Australia is 30%. In some years, lower rates are available to base rate entity companies. Base rate entityy tax rates apply from the 2017-18 income year and onwards.


"From the 2017-18 to 2019-20 income years, companies that are base rate entities must apply the lower 27.5% company tax rate. The rate reduced to 26% in the 2020-21 income year and then to 25% for the 2021-22 income year and future years." Source: https://www.ato.gov.au/rates/changes-to-company-tax-rates/

The rates are summarised here:

xlf-coy-tax-ratest


For the purposes of this question, a base rate entity is a company whose aggregate turnover is less than the threshold. Read the entire question before commencing part a.

a..Required: write VBA code for a UDF, following the syntax provided in the comments section, to return the relevant company tax rate for 2021-21 or 2021-22 income years. The return value to the cell should be in decimal form. No error checking or handling is required. (6 marks)



' Function: CTaxRate Returns the Company Tax Rate as ...
' source: /www.ato.gov.au/rates/changes-to-company-tax-rates/
' Syntax: CTaxRate(TOver, Yr)
' Arguments:
' TOver (required): aggregated turnover (whole number)
' Yr (required): (4 digits) 2021 is the 2020-21 income year, _
' 2022 is the 2021-22 and future income years
 

b.. The return value (to the cell) is now required to be in percentage form by amending the relevant code statement from your part a answer.
Required: Write the existing code statement as a comment, then the new code statement on the next line. (4 marks)

c.. Required: Write a macro procedure to test the CTaxRate function. Include code statements and parameter values, to facilitate the following outcome, if the code is run from the VBE. (4 marks)

xlf-ctaxrate-mouse-over

 

 

xlf star Answer

Suggested solution based on 90045 content.


Code a: suggested solution CTaxRate procedure 

' ===============================
' Function: Returns the Company Tax Rate as ...
'   source: /www.ato.gov.au/rates/changes-to-company-tax-rates/
' Syntax: CTaxRate(TOver, Yr)
' Arguments:
'   TOver (required): aggregated turnover (whole number)
'   Yr (required): (4 digits) 2021 is the 2020-21 income year, _
'   2022 is the 2021-22 and future income years
'   2022 is recognition that rates my change in the future
' ===============================

VBA :: If statement version 
Function CTaxRate(TOver As Long, Yr As Integer) As Variant
Dim Tmp As Double

    If Yr = 2021 And TOver < 50 * 10 ^ 6 Then
        Tmp = 0.26
    ElseIf Yr = 2022 And TOver < 50 * 10 ^ 6 Then
        Tmp = 0.25
    Else
        Tmp = 0.3
    End If

    CTaxRate = Tmp

End Function

VBA :: Select Case statement version 
Function CTaxRate(TOver As Long, Yr As Integer) As String
Dim Tmp As Double

    Select Case Yr
        Case 2021 And TOver < 50 * 10 ^ 6
            Tmp = 0.26
        Case 2022 And TOver < 50 * 10 ^ 6
            Tmp = 0.25
        Case Else
            Tmp = 0.3
    End Select

    CTaxRate = Format(Tmp, "General Number")

End Function

Notes:


Code b: the return value in percentage form 
    'CTaxRate = Tmp
     CTaxRate = Format(Tmp, "Percent")

    'CTaxRate = Tmp
     CTaxRate = Format(Tmp, "0.00%")


Code c: 
Sub TestCTaxRate()
Dim Ans As Variant

Ans = CTaxRate(15 * 10 ^ 6, 2022) ' $15m in the year 2022 returns 25.00%

Stop

End Sub