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)
The below EMI Calculator can be download from here RATE Calculator
