• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Salary Breakup calculator with circular reference

Baiju G Nath

New Member
Hi Excel Experts,

I am excited to be in the forum to learn and contribute. This is my first post in this forum.

I am working on a salary break up calculator. I am trying to find a formula for Basic and special to fix this.

The CTC is : Fixed salary + Retirals & Incentive.
Basic is 40% of Fixed Salary and not CTC
Incentive is a % of the CTC

I have attached an excel file for your ready reference. I would appreciate if the experts in this forum can crack this for me.

Regards
Baiju
 

Attachments

  • SaLary Breakup Sample.xlsx
    24.9 KB · Views: 7
I've gone through your sheet. Your explanation is a little light but I think I see what's going on.

You have defined Fixed Salary as Basic+HRA+Special Allowance. Which of those figures will you enter into the spreadsheet? Based on the other figures, Basic is 40%, HRA is 16% (40% x 40%), therefore Special Allowance is 44%. Therefore, you need to enter one of those four values to calculate the other three. Which one will you enter?

Here is your file updated to show all four options.
 

Attachments

  • Baiju G Nath=SaLary Breakup Sample.xlsx
    25.8 KB · Views: 8
Looks like I haven't articulated it well. I need the break up for the CTC in Cell B2 under the salary heads.

CTC = Fixed + Retirals & Incentive
Fixed = Basic + HRA + Special Allowance
Incentive = 5%,10%,15% of CTC

So If I enter CTC as 100000 then Incentive is 5000, 10000 or 15000 depending on the % selected in D2 the rest needs to split is such a way that Basic is 40% of Fixed, HRA is 40% of Basic, PF is 12% of Basic, Gratuity is 4.81% of Basic and the reminder is Special Allowance.

So the only value I need to enter is CTC in B2 and the rest should calculate automatically.

Hope I have been able to articulate it properly now.

Regards
Baiju
 

Attachments

  • SaLary Breakup Sample.xlsx
    24.9 KB · Views: 11
In the file you attached, PF and Gratuity are not part of Fixed Salary. I still don't understand how you want to determine Fixed Salary.
 
Hi,

PF and Gratuity are linked to Basic and should be exclude from Fixed Salary. So if I enter the CTC the formula should calculate Basic 40% of Fixed i.e. excluding PF Gratuity (which is linked to Basic and hence should be dynamic) and Variable pay.
 
Back
Top