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