I. Intro
The RATE function returns the interest rate for an investment or loan with equal phased payments.
The RATE function is based on an iterative method and can have no solutions or many solutions. The #NUM! error value is returned if the results don't converge to 0.0000001 after 20 iterative calculations.
Note: The iterative method is a mathematical process that makes an initial estimate and searches for a series of approximate solutions to solve problems (generally mathematical equations or simultaneous equations).
II. About the function
- Formula: =RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess])
- Parameters:
- number_of_periods (required): The number of payments to be paid.
- payment_per_period (required): The amount to be paid per period.
- present_value: The current value of the loan.
- future_value (optional): The future value, or the cash balance you would like to receive after the last payment.
- end_or_beginning: Determines whether payments are due at the end or beginning of each period (0 for end, 1 for beginning)
- rate_guess (optional): Your estimate of the interest rate.
- Example: =RATE(12,-100,400,0,0,1%)
- Note:
- The RATE function assumes repayments in every end period.
III. Steps
Use the RATE function
- Select a cell and enter =RATE.
- Enter the parameters in the cell. For example: = RATE(12,-100,400,0,0,1%).
- Press Enter to display the result, which is 23% in this example.
250px|700px|reset
Delete the RATE function
Select the cell with the RATE function, and press Delete.
IV. Use case
Finance: Calculate the interest rate
You can find the effective interest rate of a loan or investment with equal periodic payments by using the RATE function.
- Formulas used below:
- =RATE(B2*12,B3,B4)
- =B6*12
- About the parameters:
- In the example below, the number of payments (4) is in years, so we need to multiply it by 12 to get the monthly number.
- The second parameter is payment_per_period, which is 200. Since it is an expense, this value is negative
- The third parameter is the present value of the loan, which is 8000.
- The three parameters above are required. In the example below, the future value, payment type, and rate guess that we have omitted are optional and can be added according to your needs.
- To get the annual interest rate, multiply the monthly interest rate by 12.
250px|700px|reset