How to Map Requirement in Formula in Salary Structure

Frappe HR
1

Hello All, We’re evaluating ERPNext for ERP implementation. Its a great, well designed app. However there are some aspects we cant figure out.

I payroll how can one map the following in a Salary Structure formula?

Payable Income tax in tax bands such as:

Taxable Pay Tax Rate (%)
0 - 10,000 10%
10,001 - 20,000 15%
20,001 - 30,000 20%
30,001 - 40,000 25%
40,000 and above 30%

where Income Tax = [base * Applicable Rate]. Note is that the Tax Rate is cummulative i.e. if one earns 35,000 they will pay the rate on the amount at each band.

Thanks.

1 Like
2

I had the question the other day and the only thing that came to my mind was having the same amount of Salary Component as the Tax Rate you have. For example:
Tax Rate - 15
Tax Rate - 20
And so on.

Then, in the Salary Structure, you will set the formula for every component. Validating that the gross amount is in the range that you’re working with.

Ex. in the condition field you will have something like this for the first one (15%):
base > 10000 && base < 20000

And in the formula you will calculate the value:
base * .15

Have in mind that not every component that is in the Salary Structure will appear in the Salary Slip. Will appear those which the condition is met. So, you don’t have to worry about having Tax Rate - 15, Tax Rate - 20, Tax Rate - 25, etc. with empty values.

If you don’t like that approach, then the last solution will be setting up a python script and connect it using the hooks. This is more a developer approach!

Good luck!

3

Thanks, yefritavarez. That’s a great idea. Im looking into it.

4

Hi all,

I’ve had some success with your suggestion, yefritavarez, thanks alot.

I created Tax Band 1, Band 2, Band 3 and so on to do the evaluation. However if you have about 15 bands to deal with and each requiring two rules (only a single formula can be applied per rule so you must evaluate twice for true and false - which is very tedious by the way), you end up having about 15 Salary Components on the payslip.

Isn’t there an elegant way of aggregating the bands into one e.g. Total Taxes = X given that Total Taxes will be calculated using the Tax Bands above?

Any suggestions are welcome. Thanks.

5

Unfortunately you will have to do in separate rows for now since against each condition there can be one formula. You can raise a github issue though, we will take it up as and when possible.

6

Thanks KanchanChauhan.

7

Hooks

CRUD Events

You can hook to various CRUD events of any doctype, the syntax for such a hook is as follows,

doc_events = {
      "{doctype}": {
        "{event}": "{dotted.path.to.function}",
    }
}

The hook function will be passed the doc in concern as the only argument.

List of events

  • validate
  • before_save
  • after_save
  • before_insert
  • after_insert
  • before_submit
  • before_cancel
  • before_update_after_submit
  • on_update
  • on_submit
  • on_cancel
  • on_update_after_submit
    Eg,
doc_events = {
    "Salary Slip": {
        "after_insert": path.to.my.custom.calculate_tax",
    }
}

Your script might have a logic like this:

def calculate_tax(doc):
        amount = get_amount(doc)

	doc.append("earnings",{
		"default_amount": 0,
		"amount_based_on_formula": 0,
		"denpends_on_lwp": 0,
		"salary_component": "Tax Band",
		"amount": amount
	})
1 Like
8

Aah yes. Let me try this too. Thanks yefritavarez.

9

Hi,

See the following proposal for how to address this issue. Does it address your question?

https://github.com/frappe/erpnext/pull/8429

Pasted image1860×1138 124 KB

Regards,
cksgb

1 Like
10

@Chude_Osiegbu insteading of breaking it down into slabs,if we use a formula like this PAYE =IF(CI<=0=1%G,IF(CI<=300000=CI7%,IF(CI<=600000= 21000+11%(CI-300000),IF(CI<=1100000=54000+15%(CI-600000),IF(CI<=1600000=129000+19%(CI-1100000),IF(CI<=3200000=224000+21%(CI-1600000),IF(CI>3200000=560000+24%*(CI-3200000)))))))) which work well in excel but refuse to work in the salary structure because of syntax (,) i think it will be better.Since this is your intiatiave kindly make any formula that excel accepts to be acceptable in the salary structure formula or condition.Appreciate your quick action

11

Is there an “Editing Row” screenshot of any one of the PAYE Level Base to understand the if statements of the formula?