# Calculate the effective Interest Rate of a Loan in Microsoft Excel

|Most of the time you will find that lenders charge some fees alone with annual interest rate. When these fees are calculated, the effective interest rate goes higher than stated interest rate. That’s why it is necessary to calculate the effective interest rate before making any decision.

With the help of **RATE** function in Microsoft Excel we can find the effective interest rate very easily.

RATE(nper, pmt, pv, [fv], [type], [guess])

Allows you to calculate the effective interest rate in no time.

**nper**is the total number of payments for the loan.**pmt**specifying payment to be made each period. Payments usually contain principal and interest that doesn’t change over the life of the annuity.**pv**specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.**fv**Variant specifying future value or cash balance you want after

you’ve made the final payment. For example, the future value of a loan is $0 because that’s its value after the final payment. However, if you want to save $50,000 over 18 years for your child’s education, then $50,000 is the future value. If omitted, 0 is assumed.**type**Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.

Using this function and calculating Interest rate takes thee must give inputs. Those are

- Total Number of payments period
**nper** - The amount to be paid on each payment
**pmt** - The principal of the loan
**pv**

Make a excel sheet like shown below:

Now enter this formula in the cell **“B3″**

=RATE(B4,-B5,B2)*12

where B4 is the number of periods have to pay, B5 is the amount to pay on each period have to put this value as a **negative value** and B2 is the loan principal. We have to multiply the result with 12 because we want the annual Interest rate

Thats it Now you have the Effective Interest rate in the field **B3**.

Hello Rabin,

How did you calculate the monthly installment? Does it include the monthly nominal interest payment, and if so how did you calculate that.

Check this page Monthly Loan Installment Calculations in Microsoft Excel