Madhu krishna blog: January 2014

Now-a-days many people are Interested to purchase there Movable (or) Immovable Assets through Bank Loans.
Some people face the problem for calculating the Principle Amount & EMI Interest by changing
the EMI Installment periods of your choice for every month.

Now I am showing the different steps by which we can prepare the EMI calculator in EXCEL.

STEP - 1

First you prepare a statement containing the basic details which will be the base to Prepare the EMI Calculator, I have given below the details for purchase of Honda Vehicle as sample.

View large image

STEP - 2

Then create a statement of EMI Calculator as given below with the following columns with No. of rows as your desire.

(below statement contain up to 36 Installments)

1. EMI NO : Indicates the Installment Number.

2. EMI Date : Installment Date monthly basis (usually Installments will be paid for every month)

3. Opening balance : The balance Loan Amount to be paid for each month (The closing balance of previous month)

4. Principal Amount : The principal amount to be paid for each month

5. Closing balance : Closing balance for every month (Opening bal. - principal amount)

6. Interest : Interest charged for each month

View large image

STEP - 3

(a) First start with first row of EMI NO. with entering "1"

(b) Then come to next cell and enter the formula as =if(g17>=1,c17+1," ") and press enter

and then when you click on the formula cell corner of the cell you will find the "+" mark and

then by double click you will get the formula for the hole row.

In the above formula G17 is the cell containing closing balance of that month

and C17 is the starting cell which we have mentioned as "1".

This formula is used for obtaining the complied rows and if the closing balance is "0" it will be blank.And with this we can get the perfect result when we change the no of installments or any other columns in the first statement.

Note: you will obtain the result when we enter the closing balance amount.

View large image

STEP - 4

a. First you type the starting date of EMI Installment, and please take care that the cell

should be in the date format.

b.Then come to next cell of EMI Date and enter the formula as =IF(G17>=1,EDATE(D17,1)," ")

Note : The formula for obtaining the next month of the same date is =EDATE(D17,1)

eg: starting date : 01.01.2014 by applying above formula we will get 01.02.2014.

In the above formula G17 is the cell containing closing balance of that month

and D17 is the starting cell which Contains the starting date.

This formula is used for obtaining the complete row with installment dates

(AND) if the closing balance is "0" it will be blank.

View large image

STEP - 5

As we know the opening balance is the Starting Loan amount , Copy the amount

from the table 1 (in my statement D10) ie., =D10

View large image

STEP - 6

Select the first row (EMI NO ROW) from 1 to end of the row and give the name to the Row, and then press enter to freeze the row, I have given as "EMI".

As given below

View large image

STEP - 7

The formula for obtaining principal amount is =ppmt then press CtrlA

you will get the Function Arguments table as stated below and then you will find the following

1. Rate (Interest rate) : enter as "12%/12" as we require for each month and then press F4 to stand by the given rate

2. Per (EMI NO. ROW) : enter the name which you have given to freeze the Row of EMI No.

3. NPer(No. of Installments): select the installment cell i.e., 36 in the first table and then press F4

4. PV (Present Value) : then select the Loan Amount and press F4

5. Future Value : enter 0

then press "OK"

By this you will get the Principal amount.

Note: do not press F4 for "PER".

View large image

STEP - 8

As you know Closing Balance is the difference between the opening balance and Principal Amount.

By entering the closing balance you will able to find the EMI NO. AND EMI DATE in the second Rows.

View large image

STEP - 9

The formula for obtaining principal amount is =IPMT then press CtrlA

you will get the Function Arguments table and fill up the fields as same procedure stated in calculating the Principal amount.

Then you will get the Interest amount for that month.

By this we have completed with the formulas.

View large image

Then I will show how to fill the remaining cells also.

STEP - 10

As everyone know that the closing balance of last month is the opening balance of the current month.

So, copy the cell containing the Closing balance of the last month as given below.

View large image

STEP - 11

To copy the formulas for the second month select the first column from principal column to interest column and when you place the mouse on the edge of the selected cells as shown below you will find "+" mark so,hold the mouse near "+" mark and then drag the selected cells to next row of three columns.

View large image

STEP - 12

Then again copy the closing balance of previous month (i.e., 2nd month) in the cell of opening balance of the 3rd month and then select the 3rd month opening balance cell and double clink on the "+" mark which you find on the corner of the cell.

Note : you will obtain 0's for the remaining cells as we have not yet fill the cells of principal amount,closing bal, and Interest Amount.

View large image

STEP - 13

Select the 2nd month of the 3 columns of Principal Amount,Closing balance and Interest and then you find "+" mark at the end of the 3 selected columns and then double click , by this we can able to find the full table.

And you can find the Closing balance as "0" for the last installment.

View large image

STEP - 14

You can get the EMI Calculator as per your desired monthly installments,down payment,cost price etc., by changing the No. of Installments, and other amounts in table - 1 except the interest rate i.e., "12%". But you may prepare your own EMI Calculator of any desired rate of interest in table - 1.

By changing the No. of Installments if the period is decreased you may find error as #VALUE! for the remaining cells which are after closing balance is "0".

So, to Format the cell containing the error the following steps to be followed.

This is very useful for preparing other statements also.

1. Select the entire table from EMI No.

2. Then select the conditional formatting in the Home Tab

3. Select the New Rule and then in New Formatting Rule select the Rule Type select

"format only cells that contain".

4. Then in "format only cell with " select "Errors" as given below.

5. Then press the Format button in the New formatting rule select White color in color selection then press "OK" in Format Cells and "OK" in the New Formatting rule.

Then you can find that the error cells changed in white color.

I think this is clear for you for preparation of EMI Calculator.