Financial functions in Excel

1. Financial Function - Calculate EMI 

You want to take a loan and you want to calculate EMI OR you want to build an EMI calculator in Excel. It is a fairly easy job to do - 

 

You will need to use PMT function for this. It has following structure - 

 

PMT(rate, nper, pv, [fv], [type]) 

 

rate: You rate of interest 

 

nper: No. of payments. Your nper and rate should be on the same scale. i.e if you are 

planning to pay up monthly, the rate in your formula should be monthly only. Generally, 

interest rate is specified yearly i.e. 10.5% per year. This you should divide by 12 to arrive 

at monthly rate. Hence, if you wanted 3 years loan, it means nper would 3x12=36 months. 

If it is quarterly, rate = 10.5%/4 = 2.625% and nper would be 3x4 = 12 

If it is annual, rate = 10.5% and nper = 3 

 

pv: Your loan amount. You will need to put negative value of this in your formula. If you 

don't put negative value, your EMI would be in negative but answer would be same though 

with negative sign.  +ve / -ve PMT requires some explanation though you may choose to ignore. It depends upon your cashflow. If you are taking a loan, hence cash in, hence pv is +ve. But every month, you will have to pay up something, hence cash out. Hence, PMT is -ve. If you are investing, hence 

cash out. Hence pv is -ve. But every month, you will be receiving something, hence cash in. 

Hence, PMT is +ve. 

 

Now what is +ve or -ve is simply your preference. I recommend you should not worry about 

this. 

 

fv: Your remaining value after you finish your installment. Generally, it is 0 as any lender 

will like to recover its money fill. (Default is 0) 

type: 0 - At the end of the period, 1 - At the beginning of the period (Default is 0) 

Also note, fv and type are optional and may not be required in your formula. 

 

The formula used in the below picture is =PMT(B1/12,B2,-B3,B4,B5) 

 

Bonus Tip = If you use ABS function, then there would be no need to put negative value of 

PV. Hence, formula in this case would be =ABS(PMT(B1/12,B2,B3,B4,B5)) 

2. Financial Fuction - Calculate Interest Part of an EMI 

 

Now the EMI for a month = Interest for that month and Principal for that month. IPMT is 

used to calculate the interest portion of your EMI. 

 

Excel defines IPMT as "Returns the interest payment for a given period for an investment 

based on periodic, constant payments and a constant interest rate" 

 

The syntax of IPMT is IPMT(rate, per, nper, pv, [fv], [type]). 

‘ 

rate: You rate of interest 

 

per: Period for which you want to calculate Interest 

 

nper: No. of payments. Your nper and rate should be on the same scale. i.e if you are 

planning to pay up monthly, the rate in your formula should be monthly only. Generally, 

interest rate is specified yearly i.e. 10.5% per year. This you should divide by 12 to arrive 

at monthly rate. Hence, if you wanted 3 years loan, it means nper would 3x12=36 months. 

If it is quarterly, rate = 10.5%/4 = 2.625% and nper would be 3x4 = 12 

If it is annual, rate = 10.5% and nper = 3 

pv: Your loan amount. You will need to put negative value of this in your formula. If you 

don't put negative value, your EMI would be in negative but answer would be same though 

with negative sign. 

 

+ve / -ve IPMT requires some explanation though you may choose to ignore. It depends 

upon your cashflow. If you are taking a loan, hence cash in, hence pv is +ve. But every month, 

you will have to pay up something, hence cash out. Hence, IPMT is -ve. If you are investing, 

hence cash out. Hence pv is -ve. But every month, you will be receiving something, hence 

cash in. Hence, IPMT is +ve. 

 

Now what is +ve or -ve is simply your preference. I recommend you should not worry about 

this. 

 

fv: Your remaining value after you finish your installment. Generally, it is 0 as any lender 

will like to recover its money fill. (Default is 0) 

 

type: 0 - At the end of the period, 1 - At the beginning of the period (Default is 0) 

 

Also note, fv and type are optional and may not be required in your formula. 

 

The formula used in the below picture is =IPMT(B1/12,B2,B3,-B4,B5,B6) 

Also, since Interest will vary every month, hence it makes sense to calculate it for each 

month. Columns H & I carry interest for each month. 

 

Bonus Tip = If you use ABS function, then there would be no need to put negative value of 

PV. Hence, formula in this case would be =ABS(IPMT(B1/12,B2,B3,B4,B5,B6))

3. Financial Function - Calculate Principal Part of an EMI 

 

Now the EMI for a month = Interest for that month and Principal for that month. IPMT is 

used to calculate the interest portion of your EMI. To calculate the principal part of an EMI, 

you will need to use PPMT. 

 

Excel defines PPMT as "Returns the payment on the principal for a given period for an 

investment based on periodic, constant payments and a constant interest rate." 

 

The syntax of PPMT is PPMT(rate, per, nper, pv, [fv], [type]) 

 

rate: You rate of interest 

 

per: Period for which you want to calculate Principal 

 

nper: No. of payments. Your nper and rate should be on the same scale. i.e if you are 

planning to pay up monthly, the rate in your formula should be monthly only. Generally, 

interest rate is specified yearly i.e. 10.5% per year. This you should divide by 12 to arrive 

at monthly rate. Hence, if you wanted 3 years loan, it means nper would 3x12=36 months. 

If it is quarterly, rate = 10.5%/4 = 2.625% and nper would be 3x4 = 12 

If it is annual, rate = 10.5% and nper = 3 

 

pv: Your loan amount. You will need to put negative value of this in your formula. If you 

don't put negative value, your EMI would be in negative but answer would be same though 

with negative sign. 

 

+ve / -ve PPMT requires some explanation though you may choose to ignore. It depends 

upon your cashflow. If you are taking a loan, hence cash in, hence pv is +ve. But every month, 

you will have to pay up something, hence cash out. Hence, PPMT is -ve. If you are investing, 

hence cash out. Hence pv is -ve. But every month, you will be receiving something, hence 

cash in. Hence, PPMT is +ve. 

 

Now what is +ve or -ve is simply your preference. I recommend you should not worry about 

this. 

 

fv: Your remaining value after you finish your installment. Generally, it is 0 as any lender 

will like to recover its money fill. (Default is 0) 

 

type: 0 - At the end of the period, 1 - At the beginning of the period (Default is 0) 

 

Also note, fv and type are optional and may not be required in your formula. 

 

The formula used in the below picture is =PPMT(B1/12,B2,B3,-B4,B5,B6) 

 

Also, since Principal will vary every month, hence it makes sense to calculate it for each 

month. Columns H & I carry Principal for each month. 

 

Bonus Tip = If you use ABS function, then there would be no need to put negative value of 

PV. Hence, formula in this case would be =ABS(PPMT(B1/12,B2,B3,B4,B5,B6))

4. Financial Function - Calculate Number of EMIs to Pay Up a Loan 

 

You have taken a loan and you know your EMI capability. So, you want to know how many 

months will be taken to pay off a loan completely. 

 

It is fairly easy job to do it in Excel. You will need to use NPER function for this. 

 

Excel defines NPER as "Returns the number of periods for an investment based on periodic, 

constant payments and a constant interest rate." 

 

Syntax of NPER is NPER(rate,pmt,pv,[fv],[type]). 

 

rate: You rate of interest 

 

pmt: EMI (Payment per period). You will need to put -ve value of this in your formula. 

Your pmt and rate should be on the same scale. i.e if you are planning to pay up monthly, 

the rate in your formula should be monthly only. Generally, interest rate is specified yearly 

i.e. 10.5% per year. This you should divide by 12 to arrive at monthly rate. Hence, if you 

wanted 3 years loan, it means nper would 3x12=36 months. 

If it is quarterly, rate = 10.5%/4 = 2.625% and nper would be 3x4 = 12 

If it is annual, rate = 10.5% and nper = 3 

 

pv: Your loan amount. You will need to put +ve value of this in your formula. 

Note - Either PMT or PV should be -ve. Both can't be +ve and -ve at the same time. 

 

+ve / -ve requires some explanation and this can not be ignored. It depends upon your 

cashflow. If you are taking a loan, hence cash in, hence pv is +ve. But every month, you will have to pay up something, hence cash out. Hence, PMT is -ve. If you are investing, hence 

cash out. Hence pv is -ve. But every month, you will be receiving something, hence cash in. 

Hence, PMT is +ve. 

 

fv: Your remaining value after you finish your installment. Generally, it is 0 as any lender 

will like to recover its money fill. (Default is 0) 

 

type: 0 - At the end of the period, 1 - At the beginning of the period (Default is 0) 

 

Also note, fv and type are optional and may not be required in your formula. 

 

The formula used in the below picture is =NPER(B1/12,-B2,B3) 

5. Financial Function - Calculate Interest Rate 

 

You want to take a loan. You know how much loan to take (pmt), you know how many 

months you want to pay up (nper) and you want to know effective rate of interest. 

Excel makes it easy to do. RATE function is the answer for this. 

 

Excel defines RATE as "Returns the interest rate per period of an annuity. RATE is calculated 

by iteration and can have zero or more solutions. If the successive results of RATE do not 

converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value." 

Syntax of RATE is RATE(nper, pmt, pv, [fv], [type], [guess]). 

 

nper: Payment periods. Typically in months. 

 

pmt: EMI (Payment per period). You will need to put -ve value of this in your formula. 

Your pmt and rate should be on the same scale. i.e if you are planning to pay up monthly, 

the pmt in your formula should be monthly only. 

 

pv: Your loan amount. You will need to put +ve value of this in your formula. 

Note - Either PMT or PV should be -ve. Both can't be +ve and -ve at the same time. 

 

+ve / -ve requires some explanation and this can not be ignored. It depends upon your 

cashflow. If you are taking a loan, hence cash in, hence pv is +ve. But every month, you will 

have to pay up something, hence cash out. Hence, PMT is -ve. If you are investing, hence cash out. Hence pv is -ve. But every month, you will be receiving something, hence cash in. 

Hence, PMT is +ve. 

 

fv: Your remaining value after you finish your installment. Generally, it is 0 as any lender 

will like to recover its money fill. (Default is 0) 

 

type: 0 - At the end of the period, 1 - At the beginning of the period (Default is 0) 

 

guess: If you omit guess, it is assumed to be 10 percent. If RATE does not converge, try 

different values for guess. RATE usually converges if guess is between 0 and 1. Once again, 

note that if PMT is monthly, then Guess should also be monthly. Hence, if you are giving 

annual interest rate of 12%, guess should be given as 12%/12 = 1%. 

 

Also note, fv, type and guess are optional and may not be required in your formula. 

 

The formula used in the below picture is =RATE(B1,-B2,B3,B4,B5,B6/12)

6. Financial Function – Calculate Compounded Interest 

 

As part of our Mathematics courses in our childhood, we had learned about Compounded 

Interest. The famous formula which we remember is Compounded Balance = Principal x (1+rate)^N 

 

This is a fairly easy job to do in Excel. The formula to be used is FV. 

 

Excel help describes FV as "Returns the future value of an investment based on periodic, 

constant payments and a constant interest rate". 

 

The syntax of FV is FV(rate,nper,pmt,[pv],[type]) 

 

You require only 3 pieces of information for Compounded Balance. 

 

rate: Interest rate on which compounding needs to be done 

nper: Total number of periods for which compounding needs to be done. Now rate and nper 

should be on the same scale. If interest rate is monthly, then nper should be in months. If 

interest rate is quarterly, then nper should be in quarter. If interest rate is annual, then nper 

should be in years. 

 

pv: This is the initial principal and it has to be specified in -ve. (Note, I have already 

discussed significance of +ve and -ve in many previous tips on Financial Functions.) 

 

The formula used in below picture for Monthly 

 

=FV(B1/12,B3*12,0,-B2) 

 

The formula used in below picture for Quarterly 

 

=FV(F1/4,F3*4,0,-F2) 

 

The formula used in below picture for Yearly 

 

=FV(J1,J3,0,-J2)

7. Financial Function – Calculate Effective Interest 

 

You are applying for a loan and an interest rate has been quoted. The interest rate which is 

quoted is called "Nominal Interest Rate". They will quote Nominal Interest Rate in yearly 

terms. Hence, if they quote 12% interest for a loan, this is yearly figure. Now, you generally 

pay EMIs every month. They simply say that you need to pay 1% monthly interest which 

has been derived by annual interest rate / 12 which 12%/12=1% in this case. 

 

But actually interest rate of 1% (nominal monthly interest rate) is compounded every 

month, hence your effective interest rate per year becomes higher. But lending financial 

institutions doesn't quote this higher rate as it will make your loan cost look higher. 

 

To calculate Effective Interest Rate, Excel has provided a function called EFFECT.

Excel describes EFFECT - Returns the effective annual interest rate, given the nominal 

annual interest rate and the number of compounding periods per year. 

 

The syntax of EFFECT is EFFECT(nominal_rate, npery). 

 

Nominal Rate - Annual Interest Rate 

 

npery - Compounding periods in a Year. For monthly payments, it is 12. For quarterly 

payments, it is 4. 

 

In the below picture, the effective interest rate is 12.68% for a monthly payment. This may 

be a small difference for a year or two, but if you take mortgage on housing which is say for 

20 years, this makes hell of a difference. 

 

The formula used is =EFFECT(B1,B2) 

 

Now, if you are making an investment and making monthly payments, you will be getting 

annual return of 12.68% against 12% if you make yearly payment.