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

SHARE:

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.

      COMMENTS

      BLOGGER: 6
      Loading...

      $show=index

      Name

      . SECURITIES (MNP) (TDS)2007-08 ` 01.07.12 01.09.2008 01.10.2012 06.07.2009 07.08.2012 1 PERCENT EXCISE ITEMS LIST 1 sep 1% excise 10 year nsc 10(10C) 1000 court cases judgements supplied to ITO 1000 rupee note 11-2008 11-2010 130 items 14.08.2008 15 august 150 Rs coin 15g 15h 18-12-2009 192(1A) 192A 194 I 194A 194c from 01.10.2009 194H 194j 194LC 1981-2007 2% cst 2% excise 2004 2008 2008-09 FY 2010 206AA 234A 234B 234C 234c interest calculator 234E 23AC 23ACA 23B 24C 24q annexure II 25 paisa coin 271(1)(c) 271B 271H 272B 27A 280 280 challan 281 282 283 29 AUGUST 29.04.2010. 29/08/2008 29/2008 service tax 2g meaning use 3 idiots 30 MARCH 31st March 32-2010 33 of 2009 35(2AA) 35(2AB) 3CD 3g meaning use 40(b) 43B 44 AB EXEMPTED INCOME 44AA 44AB AGRICULTURE 44AB new limit 44AB NON RESIDENT 44ad 46/2012 45/2012 49C 5 day week 5 years post office deposit 5/2011 50 paisa coin 54b 54EC 54ee 54f 54gb 5th paycommission punjab 6 % excise 6 crore advance limit 6/2005 69/2010 6th cpc 6TH PAY COMMISSION 70% of pan etds 7th Central Pay Commission 8 tips to save tax 80C 80ccc 80CCD 80cce 80CCF 80CCG 80d 80DDB 80EE 80G 80GG 80GGA 80GGB 80ggc 80tta 80U 85% of pan etds 86/2008 DATED 13-08-2008 87A rebate 89(1) 89(1) relief calculator 9.5 % rate for private epf 92E 95% of pan etds 98 dated 04/01/08 aadhaar aakash tablet aam admi aar Aayakar Sampark Kendra abatement abolished ACCOUNT PAYEE DRAFT Accounting ACCOUNTING CODE accounting code for services ACCOUNTING FOR GOVT GRANTS accounting standards ACES ADD IN Add-Ins additional relief in budget 2010 adjustment of advance FBT with advance Tax advance rullings advance salary ADVANCE TAX CALCULATOR advance tax challan ADVANCE TAX CUT OFF AMOUNT ADVANCE TAX DATES AFFIDAVIT AG Projects Technologies Ltd RT age 80 addition pension aibea wage revision aibea wage revision latest news aiboa aiboc aiboc wage revision AIG AIR airline ticket booking tds airlines all is well allowances allownces alphabet of inspiration alteration on check allowed alternate minimum tax AMENDED 3CD amendment in companies bill 2011 amendment in gratuity act 2010 amendment in registration service tax AMENDMENT IN SERVICE TAX ON RENT amendment to finance Bill 2010 AMNESTY SCHEME 2007 amt Anna Hazare anna hazzare bill vs govt lokpal bill appeal apply for new pan card against old pan card aqb arbitration arrears of salary arrears of sixth pay ARRERS TAX AS-12 ASBA assessee in default ASSESSMENT REOPENING ASSESSMENT YEAR 2008-09 REFUND atm atm 100 per day atm cash without card atm tips atm wrong debit audit cases itr filing audit limit enhanced Audit Questionaire AUDIT REPORT AUS VS IND.INDIA VS SL Automated teller machine AUTOMATION OF CENTRAL EXCISE AND SERVICE TAX ay 2010-11 bad debts Bank Bank account bank audit bank audit 2012 bank charges BANK SALARY bank strike 06/07/09 and 07/07/09 banking law amendment banknotes banks npa provisioning BARE ACT bare rules BASE RATE BY BANKS bcct BIMAL JAIN biometric pan card black and white form not acceptable black money BLOCK PERIOD blogging bobay refund BONUS bonus share book discount BOOK ON EFILING BOOK REVIEW books of accounts BOOMING INDIAN ECONOMY both House rent allowance both hra and house loan Brass Scrap BRIBE CASE BROKER BSE bsnl broadband bsnl broadband usage bsnl land line sms alert BSR CODES BUDGET 2009 budget 2010 budget 2010-11 budget 2011 BUDGET 2012 budget 2013 BUDGET 2014 BUDGET 2014 CONTEST budget expectations BUDGET HIGHLIGHTS budget live on computer budget live telecast budget notification budget on tv budget speech BUDGET SPEECH DOWNLOAD Budget-2014 Budget-2015 BUDGET-2016 BUDGET08 Business BUSINESS AND PROFESSION MEANING BUSINESS COVERED UNDER 44ab BUY HOUSE C FORM CA CAN ONLY AUDIT MVAT CA CLUB INDIA CA GIRISH AHUJA CA NITIN GUPTA CA PARDEEP JAIN CA ROHIT GUPTA ca services CA sudhir Halakhandi CA Swapnil Munot CA Vikas Khandelwal calculate arrear CALCULATE NEW PAY calculate your emi calculation of tax on salary arrears CALCULATOR CALCULATOR REVISED capital asset capital formation huf Capital gain capital gain account scheme 1988 CAPITAL GAIN INDEX capital gain on repayment of loan capital gain on tenancy rights capital goods capital revenue CAPITAL SUBSIDY CAR LOAN RATE INCREASE carry forward of losses in late return cas cash flow statement CASH PAYMENT DIS ALLOWANCE CASH PAYMENTS EXCEEDING 20000 CASH SUBSIDY cbdt cbec CBI ARRESTED centeral sales tax rate CENTRAL PAY COMMISSION CENTRAL PROCESSING CENTER CENTRALISED PROCESSING OF RETURNS Cenvat CENVAT Credit Rules cgas 1988 challan challan 281 CHALLAN 289 challan correction challan Form 17 CHALLAN STATUS INQUIRY change change in cst rate change in excise duty rates CHANGE IN PAN ADDRESS CHANGE IN PAN DATA change in tds rates in budget changes CHANGES IN SERVICE TAX ACT changes in new itr forms CHANGES IN TDS CHARGES ON CASH PAYMENT OF CREDIT CARD BILL charitable organisation check name from PAN check tds deducted online Cheque cheque bounce cheque payment before due date cheque tender date /realisation date Cheque Truncation CHEQUE VALIDITY child care leave CHILD DEPENDENT PREMIUM CHILD MARRIED PREMIUM child plan childeren name children education allownces chip based atm card cibil cibil for companies CII 2007-08 CII 2008-09 CII 2010 cii 2012-13 CII2011-12 cin CIRCULAR 3/2010 DT 2.03.2010 CIRCULAR 8-2010 CIT v. Emilio Ruiz Berdejo (2010) 320 ITR 190 (Bom.) cloning of atm card clubbing of income COLLECTION CHARGES COLOR SCHEME COMMISSION ON SMALL SAVINGS common error in 2009-10 common error in itr 4-5-6 compa companies bill 2012 COMPANIES RATE COMPANY BILL 2011 company deposit Company Law Settlement Scheme COMPANY REGISTRATION complusory payment of taxes Composition scheme service tax COMPUTER AS FAX MACHINE computer sytem at ito office CONCEPT PAPER configure yahoo mail in outlook express consolidate account statement construction purchase of house Consultancy Service Consumer Price Index continues services CONTRIBUTION TO NEW PENSION SCHEME CONTRIBUTORY PENSION FUND CONVERT FIGURES INTO WORD EXCEL COPARCENER CorpMcash corporation bank correction etds CORRECTION IN PAN DATA correction in section CORRECTION RETURN CORRECTION RETURN.ETDS cost accounting cost audit cost inflation index cost inflation index 2012-13 COST INFLATION INDEX FY 2010-11 COST INFLATION INDEX FY 2011-12 COST OF INDEXATION CALCULATOR court case in entry tax punjab cpc phone number cpf Credit card CREDIT CARD BILL PAYMENT ICICI BANK credit scores cricket team critical illness CROSSED DEMAND DRAFT CRR crr reducred CS DIVESH GOYAL CST CST 3% OR 2 % cst act 1956 cst form CST FORM STATUS cst rate changed CST REDUCE RATE ctt currency CURRENCY TRADING ILLEGAL custom custom changes in budget cusual leave DA MERGE da rate da rate 01.07.2012 da rate january 2013 da rate july 2012 da rate wef 01.01.2012 date extension DDO ASK RENT RECIPT ddt dearness allowance Debit card DEBT EQUITY RATIO debt funds debt trap declared goods DEDUCTION 80C deduction for higher studies deduction on saving bank interst deduction u/s 80DD Deemed income of employee deemed services defective return defence officers defence pay defence pay scales DELAY IN FILING delete ledger in tally DELHI HIGH COURT demat deposit tax of two quarter in single challan depreciation depreciation on car depreciation on commercial vehicle. DEPRECIATION ON INTANGIBLE ASSETS depreciation rate depreciation rate 2009-10 DETAIL AFTER E FILLING DETAIL OF TIN Determination of value DIFFRENT TYPE OF TAXES digital signature din direct payment in bank account DIRECT SUBSIDY direct tax bill direct tax changes in budget direct tax code direct tax code 2009 Disability insurance discussion Paper distribution of salary dividend distribution tax dividend striping dnd do not call. document identification number document ientification number documents required for service tax registration domestic transfer pricing donation dot double taxation dow jones download download 89(1) relief calculator download direct tax code 2012 download form 16A download fvu download idfc INFRA BONDS FORM draft reply drawback rates dtaa dtc DUE DATE AY 2010-11 due date ay 2011-12 due date extended due date extended to 31.08.2012. due date for service tax return due date form 16 due date form 16a due date June due date march tax due date of return 2008-09 due date pf esi due date to deposit tds DUE DATES DUE DATES CALENDAR DUE DATES CALENDER DUE DATES INCOME TAX DUE DATES SERVICE TAX DUE FAMILY PENSION DULICATE PAN DUPLICATE TAN dvat e book Income Tax rules e book on service tax E ERA OF TAXES e filing do and don'ts e filing of audit report e filing processing status e filing software excel free e filing through evc e filing utilities e mode for notice to shareholder e notice to share holder e payment of epf E PAYMENT OF EXCISE DUTY e payment of income tax E PAYMENT OF SERVICE TAX e payment of tds E STAMP DUTY e tutorial for TAN registration E-1 FORM E-1 SALE e-commerce E-FILE SERVICE TAX RETURN e-Intermediary e-payment from friends account E-PAYMENT OF SERVICE TAX e-payment of taxes E-SEVA BY ICAI e-TDS/TCS statements earn from home earned leave Easy Exit Scheme ebay EBOOK EBOOK ON SERVICE TAX economic survey economic survey 2010 economic survey 2011 economic survey 2012 Edible Oils EDITABLE INCOME TAX FORM education cess EDUCATION LOAN eet EFFECTIVE DATE CST RATE REDUCTION effective date of allowncs efiling errors efiling free efiling income tax return efiling of excise return efiling registration problem Electricity act ELECTRONIC FOREX TRADING Electronic Verification Code (EVC) elegent card on holi ELSS EMANAGEMENT OF TAXES EMI CALCULATOR empanelment as branch auditor EMPLOYEE PROVIDENT FUND employee state insurance employee state insurance act 1948 employee state insurance act Limit employer e sewa eMudhra Consumer Services entries r off in 50 paisa entries rounded off i