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

  1. Total Number of payments period nper
  2. The amount to be paid on each payment pmt
  3. The principal of the loan pv

Make a excel sheet like shown below:
Calculate the effective interest rate of a loan in Microsoft Excel

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.

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.