DA,HRA,Bonus,P.tax calculation| Protect & Unprotect sheet| Create/Clear PivotTable or PivotChart report

Distance allowance calculation in Excel

at first click on D.A cell in excel sheet then type here = and click on Basic salary cell then type * icon and write your allowance percent (%).

Basically formula of  D.A calculation: = Basic salary * your allowance percent

Example: D.A = B2 * 15%

House rent allowance calculator in excel

Allowance is a fixed monetary amount paid by the employer to the employee, Whether personal or for the performance of his duties. These allowances generally taxable unless a specific deduction/exemption is provided by law. So in this article we will discuss about House Rent Allowance broadly. So you can understand and calculate it very easily. Please read complete article including the notes given at the end of the article.

House Rent Allowance (HRA) is give by the employer to the employee to meet the expenses in connection with rent of the accommodation.  HRA is exempt under section 10(13A) to the extent of the minimum of the following three amounts :

Actual House Rent Allowance received by the employee

Excees of rent paid for the accommodation occupied by him over 10 % of the salary.

50% of salary where the residential house is situated at Mumbai, Calcutta, Delhi or Chennai and 40% of the salary where the house is situated at any other place.

The minimum of the above three amounts shall be exempt from tax and the balance shall be taxable and thus included in gross salary of employee.

As per circular no. 5/2011 dated 16 August 2011, if the house rent paid per annum Rs. 180000 or 15000 per month or above, it is mandatory to submit PAN number of house onwer or landlord . If PAN number not to be yet submit, the house onwer/landlord must submit a declaration on plain paper with the name and address of land/houseonwer, which submit the employees.

Meaning of Salary for calculation the exemption of HRA

Salary means (Basic + D.A + Commission based on fixed percentage on turnover).

Salary is to be taken on due basis in respect of the period during which the period accommodation is occupied by the employee in the previous year.

Calculation Monthly or Yearly?

The exemption in respect of HRA is based upon the following factors:

Salary

HRA received

Place of Residence,

Rent Paid,

HRA exemption should not be calculated on yearly basis, if there was any change in above factors during previous year.

Examples for calculation of exemption/deduction of HRA

X has received following amount during the previous year.

1. Basic Salary – Rs. (5000*12) – Rs. 60,000/-
2. Dearness Allowance (D.A) – Rs. (1000*12) – Rs. 12000/-
3. House Rent Allowance (H.R.A.) – Rs. (2000*12) – Rs. 24000/-
4. Actual Rent Paid – Rs.(2000*12) – Rs. 24000/-

Calculation

The minimum of the following amount shall be exempt

• Actual HRA received (2000*12) – Rs. 24000/-
• Rent Paid in excess of 10% of salary ( 24000-7200) – Rs. 16800
• 40% of Salary – Rs. 28800/-

Therefore, Rs. 16800 shall be exempt and the balance Rs. 7200 shall be included in gross salary.

or, step by step flowing:

H.R.A calculation: at first click on H.R.A cell in excel sheet then type here = symbol and write if then first bracket open ( then click basic salary cell and type greater than symbol > write a salary amount on which you give allowance then then type comma symbol , then same to click basic salary cell and type * symbol then write a maximum numeric percent which you wished to give on basic salary then type comma symbol , (Note: here the comma means Nor) then same to click basic salary cell and type * symbol then write a low numeric percent which you wished to give on basic salary then first bracket closed )

Example: H.R.A = if (B2 > 14000, B2 * 9%, B2 * 5%)

Bonus calculation: = if (Designation = “occupation”, Basic salary * your allowance percent, if (Designation = “deferent occupation”, Basic salary * your allowance percent, if (Designation = “deferent occupation”, Basic salary * low percent allowance from before percent allowance, nor Basic salary * low percent allowance from before low percent allowance)))

Example: = if (A2 = "Doctor", B2*19%, if (A2 = "Teacher", B2 * 15%, if (A2 = "Advocate", B2 * 12%, B2 * 10%)))

P. Tax calculation: = if (((Basic + D.A + H.R.A) * year) > write maximum salary amount, (((Basic + D.A + H.R.A) * tax charges percent))

Example: = if (((B2+C2+D2) * 12) > 80000, ((B2+C2+D2) * 9.5%))

–: Following the chart below :–

Designation	Basic	D.A	H.R.A	Bonus	P. Tax
Doctor	50,000
Teacher	40,000
Advocate	35,000
Clark	30,000

How to set protection 

Open Microsoft Excel workbook. Click 'Microsoft Office' button and click 'Save As'. Click 'Tools' and click 'General Options'. In 'Password to open' box, enter desired password. Select 'Read-only recommended' check box. Click 'OK'. Enter password again for confirmation. Click 'OK'. Click 'Save'.

Details
Microsoft Excel enables you to set a password for a workbook. You can use passwords to prevent other users from opening or modifying the workbook. It also prevents a user from accidentally or deliberately changing, moving, or deleting important data. Passwords allow only authorized users to view or modify the content of the workbook and can secure your entire document.

To set a password for a workbook:

1) Open Microsoft Excel workbook.
2) Click the 'Microsoft Office' button on the top left.
3) Click the 'Save As' button. (The 'Save as' dialog box appears.)
4) Click the 'Tools' button and click 'General Options'. (The 'General Options' dialog box appears.)
5) In the 'Password to open' box, enter the desired password.

NOTE: In the 'Password to modify' box, enter desired password if you want users to enter a password before they can save changes to the workbook.

6) Select the 'Read-only recommended' check box if you do not want reviewers to accidentally modify the file.
7) Click 'OK'. (The 'Confirm Password' dialog box appears.)
8) Enter the password again for confirmation.
9) Click 'OK'.
10) Click the 'Save' button.

How to Unprotect an Excel Sheet

Unprotecting an Excel sheet is surprisingly easy. If your Excel sheet is password protected and you do not know the password, there is no way that you can unprotect the sheet in that particular workbook – but you can create a new workbook which will contain exactly the same formulas, formatting and values that will not be protected and will be editable.

How to Unprotect a Non-Password Protected Excel Sheet

1. Open up the Excel document.
2. Click on the “Review” tab.
3. Click “Unprotect Sheet” or “Unprotect Workbook.”

How to Unprotect a Password Protected Excel Sheet

• The first way to do this is to copy and paste the contents into a new workbook. This sounds very simple and is very simple but most people do not even consider this option due to its simplicity.
• In the case that your Excel sheet is password protected and you cannot select the cells on the Excel sheet, you are going to need to make use of a web-based spreadsheet solution – namely Google Spreadsheet.
1. Upload your spreadsheet into Google Spreadsheet.
2. You can now already edit the file, from within Google Spreadsheet. If you wish to make a copy that you can also edit in Excel, simply export the spreadsheet into an .xls file. Simple.

or, following formula:

How to set protection?

Review > protect sheet > type any password > ok > retype password > ok.

Or,

Right click on any sheet > protect sheet > type any password > ok > re password > ok.

How to set un-protection?

Review > un-protection > type your password > ok.

How to create a PivotTable or PivotChart report?

Type perfect data > select the data > insert > Table > click on PivotTable point > PivotTable 

Or,

PivotChart > ok and drag fields between areas below from above fields.

How to clear PivotTable or PivotChart report?

Select the PivotTable/PivotChart > home > editing > clear > clear all.

Or,

Select the PivotTable/PivotChart > press delete from keyboard.