# 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:

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)

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:

• $50m expressed as 50 * 10 ^ 6 to avoid 50000000 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


• Published: 20 December 2021
• Revised: Thursday 27th of January 2022 - 04:23 PM, [Australian Eastern Standard Time (EST)]