Monday, August 19, 2013

Excel Addin to Calculate Income Tax ,EMI,Interest rate,FD RD Maturity amt and Spell figures to words


on Monday, August 19, 2013

There are number of Formulas/tricks available in excel to do your accounting /reporting /calculation work in a simple and easier way.Some of them are inbuilt in excel ,but a little known to common users and other we have to create on the basis of given logic and specific needs .

Writing specific formulas are not so easy, as to write these excel formulas there are certain set of rules/procedure to write them.

Now take a example of  formula to calculate Income Tax for Male/Female age less than 60 years for financial year 2013-14 (Assessment year 2014-15)

To ease, we have Assume :Total Income=TI,  Rounded off TI=RTI,  Basic Tax=BT

  • Step-1=Round off total Income in multiples of 10 RTI=ROUND(TI,-1)
  • Step-2 Basic Tax(BT)=ROUND(IF(RTI>1000000,(RTI-1000000)*0.30+130000,IF(RTI> 500000,(RTI-500000)*0.20+30000,IF(RTI>200000,(RTI-200000)*0.10,0))),0)
  • Step-3=Calculation of surcharge=ROUND(IF(RTI>10000000,MIN(( RTI-10000000 )*.7, BT*0.10),0),0)
  • Step-4 =Calculation of section 87A: ROUND(IF(TI<500001,MIN(MAX((RTI-200000)*0.10,0),2000),0),0)
  • Step-5=Calculation of  Education cess and SHC=Round((BT+Surcharge-87A)*.03,0)
  • Step-6=Total Tax=BT+Surcharge-87A+Cess
offff ,so confusing and time consuming for common man as well as for professionals also ,typing above formula  is so boring and chances of mistakes also increased.

What if we type Ctrl+Shift+M and all above steps (1-6 above ) shown in excel sheet autometically .wow!!!!

That is what we have done.We have prepared a Excel addin, which will remember few, day to day formulas and provide you a facility to recall formula by typing the short cuts.

  1. Ctrl+shift+M=calculation of  Income Tax of Male/female age less than 60 years in Fy 2013-14
  2. Ctrl+shift+S=calculation of Income Tax of Male/female age between 60-80 years in Fy 2013-14
  3. Ctrl+shift+E=Calculate EMI for given loan amount ,Interest Rate,Period(months)
  4. Ctrl+shift+I=Calculate Interest for given loan amount,Period(months),EMI
  5. Ctrl+shift+B=Calculate balance Loan for given interest,period,EMI
  6. Ctrl+shift+A=Calculate FD maturity amount for amount invested ,period,Interest rate(qtry compound)
  7. Ctrl+shift+R=Calculate RD maturity amount for Monthly fixed amount,period,interest rate(qtry compound)
  8. Spell(Num or select cell) to convert figures in rupees or in your currency .


How to Install


Step-1.1 :  For Excel 97-2003 Download a Small File ,  unzip the file and save it in your hard disk at any place permanently (do not change location there after, otherwise all the formula results for spell will be lost.)

 Download Simple Tax India Excel addin for MS excel 2003

Step-1.2 :  For Excel 2007-2010 Download a Small File ,  unzip the file and save it in your hard disk at any place permanently (do not change location there after, otherwise all the formula results for spell will be lost.)

Step-2.1 Steps for Microsoft Excel 97-2003 or lower version 
  • Open any excel file 
  • Go to tools >Add-ins>Browse
  • Browse the location ,where you have placed the Downloaded File,select the file(if file is not shown at the location ,then in browser window select Type of File "All Files"
  • After selection ,click ok .
  • A new add-in will be shown in Excel Add-ins List named as Simple Tax India
  • Congrats,addin installed ,this is one time process ,no need to do it again and again ,No macro problem ,activate macro or deactivate macro etc.
Step-2.2 Steps for Microsoft Excel 2007/2010 version 
  1. Open any work book of excel.
  2. Click the Microsoft Office Button ,(in 2010 click file menu) and then click Excel Options. 
  3. Click the Add-Ins category. 
  4. In the Manage box, click Excel Add-ins, and then click Go. 
  5. Click on browse button ,search the file "simpletaxindia.net" as downloaded from above link,and select and press ok. Addin will be added in your excel.
  6. Try to execute any of the of the short cuts listed above.
  7. This is one time process.You can start using it now in any sheet.
    Load or unload an Excel add-in program
    1. To unload an Excel add-in, do the following: 
      • In the Add-Ins available box, clear the check box next to the add-in that you want to unload, and then click OK. 
      • To remove the add-in from the Office Fluent Ribbon, restart Excel. 
    2. To load an Excel add-in,again, do the following 
      • In the Add-Ins available box, select the check box next to the add-in that you want to load, and then click OK
      • TIP If the add-in that you want to use is not listed in the Add-Ins available box, click Browse, and then locate the add-in. 
    If Still Have a doubt How to Install this excel add in MS excel 2010/2007 check this video



    How it works 
    1. Install Add-ins first ,as shown above.
    2. Type any short cuts as shown above ,and get the formula on excel sheet 
    3. Suppose you have typed Ctrl+shift+M for calculation of Male/Female tax 
      1. Tax value along with Taxable income ,Basic tax , Surcharge,cess etc will be shown
      2. You can change the Taxable Income and can get other result autometically
      3. Same can be done for EMI/Loan/Interest  related calculation and  FD RD maturity amount
      4. To use Spell word type" =spell(cell number or any figure)",figure will be converted in to words.suppose you have typed "=spell(100) " then it will be shown as Rupee One hindered Only "
      Spell(MyNumber, MyCurrency, MyCurrencyPlace, MyCurrencyDecimals, MyCurrencyDecimalsPlace)

      where
      MyNumber = Numeric Value you need to convert into words
      MyCurrency = Name of your Currency - i.e. Dollar for USA
      MyCurrencyPlace = Prefix or Suffix the currency, use "P" for Prefix and "S" for Suffix
      MyCurrencyDecimals = Name of your Currency Decimals - i.e. Cent for USA
      MyCurrencyDecimalsPlace = Prefix or Suffix the currency decimals, use "P" for Prefix and "S" for Suffix

      Currency inputs are optional and you will not need to input currency details in case you are using it for Indian Currency. Still this can be used for any currency provided you give currency inputs.

      Convert currency option originally developed By CA Yogesh Gupta

      Open for more suggestions : Hope you will found all things in order ,if not then kindly left your comment below.Further we are open to add more shortcuts which is beneficial for public at large ,so please leave comment what more we  should add in short cuts .

      Report error:Please leave comment for any errors /bugs and problem faced in installation and removal of program from excel, we will try to correct them as soon as possible.

      Disclaimer :Every care has been taken to make this program error free ,however Install this program at your own risk ,we are not responsible for any calculation mistakes

      Visit again & download again :Please visit this page again after few days as we will update this program/addin on the basis of the feedback from the viewers.

      Copyright: This program is protected by copyright to Simpletaxiindia team and no person can upload this program on other website/forum without written permission from us.Further no person can amend this program and redistribute it.However if you like this addin then you can share the link of this page to your friends ,Sharing button given below.

      6 comments: Post Yours! Read Comment Policy ▼
      PLEASE NOTE:
      We have Zero Tolerance to Spam. Chessy Comments and Comments with Links will be deleted immediately upon our review.

      1. Its Surely help in increase of my efficiency or productivity.

        ReplyDelete
      2. Dear Sir,
        Welcome
        ust I have three minutes before your web sight open.
        xcel Addn to calculate IT subject very very important and
        most helpful. Please keep it your good service world level
        famous pray for god bless all of you and thanks to GOOGLE teams all.
        Thanks and regards
        yours truly,
        V.GIRIDHARAN
        rajalakshmi.apk@gmail.com
        giridharanvenkatasubramanian@gmail.com
        19.08.2013

        ReplyDelete
        Replies
        1. Thanks Giridharan Ji,Please provide your feed back on the utility about user experience and what more should be added in it

          Delete
      3. Good Job Boss. Pl Keep posting Excel utility Files like this. I have one doubt regarding deduction of Tax Rebate. Pl confirm whether tax rebate will be deducted Rs. 2000 from (Total Tax calculated + Cess amount on Total Tax) (or) From (Total tax) then Cess will be calculated after deducting Tax rebatei.e. Cess on (Total Tax - Rs. 2,000).

        ReplyDelete
        Replies
        1. We assume cess is to be calculated on basic tax + surcharge -Rebate

          Please read

          faq on 87A


          Delete