EMI Calculator

My friend is taking a loan to buy a house and he asked me what will be my EMI. He knew following figures and was curious to know his EMI

Loan Amount
Annual Interest Rate
Loan Period - Years

I quickly calculated EMI using PMT function in excel. This triggered me an idea of making a EMI calculator and sharing it with all of you.


Equated Monthly Installments are commonly knows as EMI. As per business dictionary EMI is "Hire purchase, lease, or loan -repayment installments that are constant in amount, and are usually collected in advance as post-dated checks"

Download EMI Calculator Excel file to know your EMI

Input your figures to find out resultant EMI. Use spin buttons to fine tune your results

In next post I will discuss how to make EMI Calculator. This will be about adding Spin Buttons ActiveX controls to worksheet and PMT function

Excel Formula : Subtotal Excel

Most of the time in our reporting we need to do subtotals followed by Grand total at the end of report. I have seen many people using SUM function for subtotals in between and then adding individual cells for calculating grand total at the end. There is nothing wrong with this method if you use it very carefully so that you do not miss any item while calculating grand total at the end.

However better approach is to use Excel Subtotal function for calculating Subtotal and Grand Totals.



Image above is showing column B with the Subtotal formula and resultant values with this formula. You may notice that when we use subtotal function for calculating Grand Total, it ignores the values resulting from subtotal function within its range. This method ensures that we do not miss any item while doing Grand Totals for our reports thus higher accuracy.

This becomes more helpful when you edit your report after some time to insert another subtotal. You do not need to remember to correct the final formula calculating the Grand Total.

Another advantage of using Subtotal function is that you need not to recalculate your formulas when used with filteres. This function ignores the values hidden by the filters and considers the values in the visible rows only.

Subtotal can be used for various type of calculations based on what do you want this fucntion to do for you. Syntex for this function is

SUBTOTAL(function_num, ref1, ref2, ...)

Following function numbers can be used in Subtotal function

I have used "9" in my example above for sum you can change it as per your required working.

Function_num (Incl hidden values)	Function_num (Excl hidden values)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Format Numbers : Excel Currency Format

You can add currency symbol to your numbers in excel.

Open Number Format dialog box using Format Cells option or just press Ctrl+1

1. Click Accounting
2. Choose currency symbol from drop down

In case you do not get your currency symbol from drop down, add your symbol using custom formating option in the same dialogbox
1. Click Custom
2. Type - "Rs "#,##0_);[Red]("Rs "#,##0)

I use "Rs " to show numbers in Indian Currency. You can replace it with your symblol.

Pivot Tables : P&L reporting for Multi Location Organisation

I have recently started a series via guest posts on pointy haired dilbert famous excel blog also known as PHD or Chandoo.org. This blog is run by Chandoo a excel blogger and Microsoft excel MVP. Most of you must be knowing chandoo, those who do not know about him can know more about chandoo.

In this series we are covering on how to manage Profit and Loss (P&L) account reporting for Multi Location organization.

During this series our aim is to learn how we can do our P&L reporting on various dimensions with few clicks.

We are using Pivot Tables for our reporting purpose and will setup P&L report of a Retails chain with multiple locations divided into various regions.

Topics coverted in this series are :

1. Data sheet structure for Preparing P&L using Pivot Tables
2. Preparing P&L Pivot Table using Data sheet
3. Adding Calculated Fields to Pivot Table P&L
4. Exploring Pivot Table P&L Reports
5. Preparing Quarterly and Half yearly P&L using grouping option
6. Budget V/s Actual report using Pivot

Do not think that series is only about the Profit and Loss Account. This series is also about PivotTables. We will cover many of PivotTable tricks during our series. I hope you will be able to use those tricks elsewhere also.

Follow this series on PHD, I am sure that at the end of this series you will be able to do your P&L reporting on various dimentions with just few clicks.

Excel VBA : Deploy Macros Found Elsewhere

Today I am sharing various ways to add macros found elsewhere to a Excel file where that macro is to be deployed. Will be covering 6 ways to add macro code to your excel file.

1. Excel VBA : Add code to a Regular Module
2. Excel VBA : Add code to WorkBook Module
3. Excel VBA : Add code to Worksheet Module
4. Excel VBA : Copy Module from Existing Workbook
5. Excel VBA : Copy Forms from Existing Workbook
6. Excel VBA : Import code from .bas file

Excel VBA : Add code to a Regular Module

-Copy code from the source
-Open the workbook where you need to deploy this code
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Click Insert > Module (Shortcut ALT+I+M)
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is

See screencast below for Alternate method using right click menu through mouse.


Excel VBA : Add code to WorkBook Module

Some macros are event based macros and get activated as soon as particular event happens. Events which are universally applicable to the entire workbook are called workbook events. These macros will run once a particular event happens in a particular workbook. Typical example of such macros are Auto Open / Auto Close macros

-Copy code from the source
-Open the workbook where you need to deploy this
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Double Click on ThisWorkbook object
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is

See screencast below


Excel VBA : Add code to Worksheet Module

Some of event based macros get activated as soon as particular event happens on a particular worksheet within the workbook. These are the macros which will run on a event in particular worksheet. Typical example of such macros are Worksheet Select / Worksheet Change macros

-Copy code from the source
-Open the workbook where you need to deploy this
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Double Click on Worksheet object (Choose particular worksheet where you want to deploy the code)
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is

See screencast below


Excel VBA : Copy Module from Existing Workbook

You may have the code in existing workbook (Source) with you or recently downloaded from Internet. Following steps will deploy it to new workbook (Target)
-Open both the Workbooks (Source and Target)
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Select the source module
-Drag it to the Target workbook

See screencast below


Excel VBA : Copy Forms from Existing Workbook

Steps and requirement is similar to copy code from existing workbook

-Open both the Workbooks (Source and Target)
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Select the Form to be copied
-Drag it to the Target workbook

See screencast below


Excel VBA : Import code from .bas file

Some time you may get code in downloadable file with extention BAS. This is visual basic file .bas , you can import this to your workbook.

-Open your Workbook
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Click File > Import (CTRL+M)
-Choose downloaded VB file (*.BAS , *.FRM, *.CLS)
-Click Open

See screencast below