The BYROW() function allows calculations to be done on a row by row basis. This allows for new functions to be created that are similar to the SUMPRODUCT() function, but are much more customizable.
In this example, we will be creating a LAMBDA() function which uses the BYROW() function to calculate income taxes payable.
The LAMBDA() function, along with the example on this post is in the file below.
This function, which will be explained in the post below, is as follows:
LAMBDA(taxableamount, taxgrid,
SUM(
BYROW(
taxgrid,
LAMBDA(row,
LET(lbound,CHOOSECOLS(row,1),
ubound,CHOOSECOLS(row,2),
rate,CHOOSECOLS(row,3),
IFS(
(taxableamount-ubound)>0,(ubound-lbound)*rate,
(lbound-taxableamount)>0,0,
AND(taxableamount>lbound,taxableamount<ubound),(taxableamount-lbound)*rate)
)
)
)
)
)What is a Marginal Tax Schedule?
Marginal taxation is used by many countries in order to calculate total outstanding income tax. In marginal taxation, tax rates increase as income increase.
This can be compared to flat taxation; for example, a sales tax. On a sales tax, a $100,000 car and a $1 candy bar are taxed at the same rate. At 8%, the car would be taxed $8,000, and the candy bar would be taxed $0.08.
However; with marginal taxation, normally on income, the more that you earn, the higher the rate. But that higher rate is not applied evenly.
In the example above, $140,000 in income was earned.
Since that is over $50,197, they pay 15% tax on the first $50,197 – that is, they pay $7,529.55.
On income between $50,197 and $100,392, they pay at a rate of 20.5%, for a total of $10,289.98.
For the remainder of their income between $100,392 and $140,000, they pay 26%, which is another $10,298.08.
They pay nothing in the 29% or 33% tax brackets, as their income is below the $155,625 threshold.
In total, they pay $28,177.61. Let’s see how we can calculate this amount using a BYROW() formula.
What is the BYROW() formula?
The BYROW() formula applies a specific function as specified by a LAMBDA() function to every row. The formula will return the result of the LAMBDA() for each row.
Lets say we wanted the lowest value for each row in the data set below.
The BYROW() function that needs to be created is:
=BYROW(a2:e5,LAMBDA(row, min(row)))When it runs, it creates a new array with the minimum number on a row by row basis.
Let’s break down what this formula is doing. First, it just takes the array of numbers that the calculation will need to be applied to. This is range a2:e5.
In the next part of the formula, we create a LAMBDA() function, which takes in one variable, the row. This row variable stands for each row in the array.
It applies the formula MIN() for each row in the table; such as in the example below, where the BYROW() formula looks at the second row. In that row, the lowest number is a 2, so the formula returns a 2 for that row – but does not consider the 1 in the row below.
Now that we have the basics of BYROW(), lets take a look at how we can get individual cells within a row; something we’ll need for our marginal tax example!
Getting Individual Cells Using CHOOSECOLS()
Let’s say we wanted to modify our previous function so that we were only looking at the middle 3 rows. In this case, we’d need to reduce the number of columns to consider on a row by row basis.
The CHOOSECOLS() function allows individual columns within an array to be selected. In the case of the BYROW() function, this is applied on a row by row basis.
=BYROW(A2:E5,LAMBDA(row,MIN(CHOOSECOLS(row,2,3,4))))In the formula above, the CHOOSECOLS() function is applied on a row by row basis to limit the MIN() function to columns 2 through 4. As a result, the value for the 3rd row is 4, not the 1 value in the first or 5th columns.
Now that we can get individual cells on a row by row basis, we can create a formula which will apply logic on a column by column basis, not just apply the same formula!
Row Level Logic for the Marginal Tax Schedule
Now that we can get individual cells on a row by row basis, we can determine the cases that will be required in order to properly calculate the values that we need.
As part of the LAMBDA() function, we should have the following information:
- The total income that is being considered for the calculation.
- The tax grid, consisting of a lower bound (the starting point to which the percentage applies), an upper bound (the end point to which the percentage applies) and the marginal percentages.
For each row, the calculation needs to consider 3 possibilities:
- The income number is higher than the number on the row in the higher bound. In this case, the entire range applies, with the row’s calculation being equal to the higher bound less the lower bound multiplied by the percentage rate.
- The income number is lower than the number on the row in the lower bound. In this case, there is no relevant income within the range, and the value for the row is zero.
- The income number is between the higher and lower bounds. In this case, only part of the range applies, so the calculation is the income number less the lower bound multiplied by the percentage rate.
Let’s look at all 3 cases.
Example: Bracket Fully Applies
In this example, the $140,000 income exceeds the $100,392 top of the tax bracket, so the full tax bracket applies. This case is calculated as ($100,392-$50,197) X 20.5% = $10,289.98.
Example: Bracket Not Applicable
In this example. the income is $140,000. but the minimum income in the tax bracket is $155,625. In this case, the tax for this bracket is zero.
Example: Bracket Partially Applies
In the final case, the $140,000 income is between the top and bottom of the range. In this case, the income amount makes up the top of the range in the calculation.
This case is calculated as ($140,000-$100,392)*26% = $10,298.08
Now that we know the logic, let’s write the row level formula!
Row Level Formula for the Marginal Tax Schedule
Before starting with the formula, it is strongly recommended to use the LET() function in this example. There are examples on this site in my Amortization Schedule post on how to use the LET() function.
First, we start with the BYROW() function, where we take the tax grid in as our array (it is one of the 2 variables defined in the LAMBDA() function). The variable name row is used for the active row.
BYROW(
taxgrid,
LAMBDA(row,To make our logical statements easier, we rename the low range, high range and percentages on a row by row basis to lbound, ubound and rate. This is accomplished by using a LET() function along with choosing each column using CHOOSECOLS().
BYROW(
taxgrid,
LAMBDA(row,
LET(
lbound, CHOOSECOLS(row, 1),
ubound, CHOOSECOLS(row, 2),
rate, CHOOSECOLS(row, 3),Next, we use an IFS() statement to build out the 3 cases which can apply. For these examples, the income level is the taxableamount variable which will be defined in the LAMBDA() function.
The IFS() function allows different cases to be considered that do not necessarily use the same variables (like with CHOOSE() or SWITCH()).
Fully Applies
The taxableamount is greater than the top of the bracket, ubound, the full bracket applies.
(taxableamount - ubound) > 0, (ubound - lbound) * rateDoes Not Apply
The taxableamount is greater than the bottom of the bracket, lbound, no charge in the bracket.
(lbound - taxableamount) > 0, 0Partially Applies
The taxableamount is less than the top of the bracket, ubound as well as less than the bottom of the bracket, lbound, a portion of the bracket applies.
AND(taxableamount > lbound, taxableamount < ubound), (taxableamount - lbound) * rateOnce all the BYROW() and IFS() logic is added, the formula should look as follows:
BYROW(
taxgrid,
LAMBDA(row,
LET(
lbound, CHOOSECOLS(row, 1),
ubound, CHOOSECOLS(row, 2),
rate, CHOOSECOLS(row, 3),
IFS(
(taxableamount - ubound) > 0,
(ubound - lbound) * rate,
(lbound - taxableamount) > 0,
0,
AND(taxableamount > lbound, taxableamount < ubound),
(taxableamount - lbound) * rate
)
)
)The last step is to add the variables into the LAMBDA() function to finish the full function!
Finishing the LAMBDA() function
When building the BYROW() example, we used 2 variables that would need to be created in the LAMBDA() function:
- The income amount (taxableamount)
- The tax grid (taxgrid)
To fully complete the function, we need to add these 2 variables before the BYROW() code.
=LAMBDA(taxableamount, taxgrid,Once these are added, the LAMBDA() function will be fully functional.
=LAMBDA(taxableamount, taxgrid,
BYROW(
taxgrid,
LAMBDA(row,
LET(
lbound, CHOOSECOLS(row, 1),
ubound, CHOOSECOLS(row, 2),
rate, CHOOSECOLS(row, 3),
IFS(
(taxableamount - ubound) > 0, (ubound - lbound) * rate,
(lbound - taxableamount) > 0, 0,
AND(taxableamount > lbound, taxableamount < ubound),
(taxableamount - lbound) * rate
)
)
)
)
)However; this formula does not provide a single value for tax, it provides the values on a bracket by bracket basis. While this may be helpful in some instances, a more relevant number may be a single value for all tax to pay.
To get to a single value, wrap the BYROW() function in a SUM() function. Since the SUM() function returns only one value, it will add up the calculations made row by row from the BYROW() function into a single number.
Once this additional code is added, the Marginal Tax LAMBDA() formula will return a single value that will calculate the tax values on a row by row basis.
