The financial function used to calculate the interest rate per period of an annuity is known as the RATE function.
This function is commonly used in spreadsheet software like Microsoft Excel and financial calculators.
Syntax of RATE function:
RATE(nper, pmt, pv, [fv], [type], [guess])
Where:
- nper = Total number of payment periods in the annuity.
- pmt = Payment made each period; it remains constant over time and includes both principal and interest.
- pv = Present value or the lump-sum amount that a series of future payments is worth now.
- fv = Future value (optional); it is the cash balance you want after the last payment is made. By default, it is 0.
- type = (Optional) Indicates when payments are due: 0 = end of period, 1 = beginning.
- guess = (Optional) Your guess for what the rate will be; if omitted, it is assumed to be 10%.
Application:
The RATE function is particularly useful when:
- You know the loan amount or investment (PV),
- You know the periodic payment amount (PMT),
- You know the number of periods (NPER),
But the interest rate is unknown and needs to be calculated.
This is commonly used in calculating the effective interest rate on home loans, EMIs, pension plans, lease rentals, or any regular annuity-based scheme.
Example:
Suppose you are taking a loan of โน 1,00,000, repayable in 12 monthly installments of โน 9,000 each.
To find the monthly interest rate, you can use:
RATE(12, -9000, 100000)
The result would return the periodic interest rate applicable on the annuity.
Important Notes:
- The payment (PMT) is entered as a negative number because it is a cash outflow.
- RATE returns the interest rate per period; multiply by 12 to get annual rate if payments are monthly.
- It may use iterative calculations, and hence, a precise solution may take multiple attempts.
