Customizing Benefit and Tax Calculations
Review these examples to help you customize your benefit and tax calculations.
Use Case 1
You want to calculate commission as a percentage of the Commission Basis account, which is a custom account not provided with Workforce. Commission is calculated by multiplying the rate entered in the Benefits and Taxes Wizard times the custom Commission Basis account.
Sample Formula
IF("No Property"->"BegBalance"==[OWP_EarningList.Commission])
IF(@ISMBR(@RELATIVE("OWP_Existing Employees",0)))
"Commission Basis"->"No Property" * ("OWP_Value"/100);
ELSE
#Missing;
ENDIF
ENDIF
Use Case 2
Building on the previous example, you want to add a benefit called Insurance, which is calculated as a percentage of the Merit account, with the following values selected in the Benefits and Taxes Wizard:
- Component Type—Custom
- Payment Terms—Quarterly (Calendar Year)
- Payment Frequency—Pay During First Period
- Maximum Value Type—Flat Amount
- Threshold Scope—YTD
- Taxable Component—Yes
- Rate for all months—10
- Threshold—400
- Value Type—You can select any value for Value Type as we are calculating the benefit as a percentage of Merit in the custom logic.
Sample Formula
IF("No Property"->"BegBalance"==[OWP_EarningList.Commission])
IF(@ISMBR(@RELATIVE("OWP_Existing Employees",0)))
"Commission Basis"->"No Property" * ("OWP_Value"/100);
ELSE
#Missing;
ENDIF
ELSEIF("No Property"->"BegBalance"==[OWP_BenefitList.Insurance])
IF("Cal TP-Index"==1 OR "Cal TP-Index"==4 OR "Cal TP-Index"==7 OR "Cal TP-Index"==10)
"OWP_Expense amount"="OWP_Merit"->"OWP_Expense amount"*("OWP_Value"/100);
ENDIF;
IF("OWP_Expense amount"!=#MISSING)
IF("OWP_Calculated Max Value"!=#MISSING)
IF("OWP_CYTD(Prior)"+"OWP_Expense amount">"OWP_Calculated Max Value")
IF("OWP_CYTD(Prior)"<="OWP_Calculated Max Value")
"OWP_Expense amount"="OWP_Calculated Max Value"-"OWP_CYTD(Prior)";
ELSE
"OWP_Expense amount"=#MISSING;
ENDIF;
ENDIF;
ENDIF;
ENDIF;
"Custom Expense"="OWP_Expense amount";
ENDIF;
Use Case 3
Building on the previous examples, you want to add a Tax (Tiered Tax) named SUTA, which is calculated as a percentage of Benefit1, Benefit2, Benefit3, and Benefit4, with the following values selected in the Benefits and Taxes Wizard:
- Component Type—Custom
- Payment Terms—Monthly
- Maximum Value Type—Threshold
- Threshold Scope—Monthly
- No rates are provided in the Rates page.
- For the custom tiered tax, rates need to be provided as part of the custom formula.
Sample Formula
IF("No Property"->"BegBalance"==[OWP_EarningList.Commission])
IF(@ISMBR(@RELATIVE("OWP_Existing Employees",0)))
"Commission Basis"->"No Property" * ("OWP_Value"/100);
ELSE
#Missing;
ENDIF
ELSEIF("No Property"->"BegBalance"==[OWP_BenefitList.Insurance])
IF("Cal TP-Index"==1 OR "Cal TP-Index"==4 OR "Cal TP-Index"==7 OR "Cal TP-Index"==10)
"OWP_Expense amount"="OWP_Merit"->"OWP_Expense amount"*("OWP_Value"/100);
ENDIF;
IF("OWP_Expense amount"!=#MISSING)
IF("OWP_Calculated Max Value"!=#MISSING)
IF("OWP_CYTD(Prior)"+"OWP_Expense amount">"OWP_Calculated Max Value")
IF("OWP_CYTD(Prior)"<="OWP_Calculated Max Value")
"OWP_Expense amount"="OWP_Calculated Max Value"-"OWP_CYTD(Prior)";
ELSE
"OWP_Expense amount"=#MISSING;
ENDIF;
ENDIF;
ENDIF
ENDIF
"Custom Expense"="OWP_Expense amount";
ELSEIF("No Property"->"BegBalance"==[OWP_TaxList.SUTA])
IF("OWP_Benefit1"->"OWP_Expense amount"+"OWP_Benefit2"->"OWP_Expense amount"+"OWP_Benefit3"->"OWP_Expense amount"+"OWP_Benefit4"->"OWP_Expense amount"<=1000)
("OWP_Benefit1"->"OWP_Expense amount"+"OWP_Benefit2"->"OWP_Expense amount"+"OWP_Benefit3"->"OWP_Expense amount"+"OWP_Benefit4"->"OWP_Expense amount")*(10/100);
ELSEIF("OWP_Benefit1"->"OWP_Expense amount"+"OWP_Benefit2"->"OWP_Expense amount"+"OWP_Benefit3"->"OWP_Expense amount"+"OWP_Benefit4"->"OWP_Expense amount"<=1200)
(("OWP_Benefit1"->"OWP_Expense amount"+"OWP_Benefit2"->"OWP_Expense amount"+"OWP_Benefit3"->"OWP_Expense amount"+"OWP_Benefit4"->"OWP_Expense amount")-1000)*(20/100) + 1000*(10/100);
ELSEIF("OWP_Benefit1"->"OWP_Expense amount"+"OWP_Benefit2"->"OWP_Expense amount"+"OWP_Benefit3"->"OWP_Expense amount"+"OWP_Benefit4"->"OWP_Expense amount"<=1300)
(("OWP_Benefit1"->"OWP_Expense amount"+"OWP_Benefit2"->"OWP_Expense amount"+"OWP_Benefit3"->"OWP_Expense amount"+"OWP_Benefit4"->"OWP_Expense amount")-1200)*(30/100) + (1200-1000)*(20/100) + 1000*(10/100);
ELSE
(1300-1200)*(30/100) + (1200-1000)*(20/100) + 1000*(10/100);
ENDIF
ENDIF
Note:
Do not assign values to the OWP_Expense Amount member. If there is a use case to apply further logic on a computed value, then assign the OWP_Expense Amount to OWP_Custom Expense. Refer to similar Use Case 2 above.