The RATE function calculates the interest rate for a loan or investment based on length, payments, and present and future value.
For example, you can use the RATE function to calculate the monthly interest rate for a loan.
Syntax
RATE(Number of periods, Payments, Present value[, Future value] [, Payment timing] [ ,Rate estimate])
Arguments
| Argument | Data type | Description |
| Number of periods (required) | Number | The number of periods that the interest rate is applied to. |
| Payments (required) | Number | The amount paid into the investment each period. |
| Present value (required) | Number | The present value of the investment. |
| Future value | Number | The future value of the investment. |
| Payment timing | Number | Determines whether each payment is made at the start or end of each period. If a payment is made at the start of the period, that period's interest applies to it. You can enter a value of 0 or 1 for this argument. If you enter:
|
| Rate estimate | Number | The estimated interest rate. |
The RATE function returns a number.
Financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.
Additional information
For any values you give the RATE function via an argument, or that the function returns:
- a positive value represents money you receive, such as a dividend or loan.
- a negative value represents money paid, such as a deposit or interest payment.
Excel equivalent function
Examples
For example, an Interest rates module has the Contracts list on columns and line items on rows. The module shows different loan amounts and monthly payments for two customer contracts.
The formula in the Monthly interest rate line item calculates the periodic interest rate for each contract.
| Contract 1 | Contract 2 | |
| Loan amount | $5,000 | $7,500 |
| Monthly payment | -$95.00 | -$135.00 |
| Number of periods | 60 | 60 |
Monthly interest rate
| 0.440039% | 0.255868% |
In this example, the formula in the Annual interest rate line item calculates the yearly interest rate for each contract.
| Contract 1 | Contract 2 | |
| Loan amount | $5,000 | $7,500 |
| Monthly payment | -$95.00 | -$135.00 |
| Number of periods | 60 | 60 |
Annual interest rate
| 5.28047% | 3.07042% |
In this example, the formula calculates the required interest rates for two investments to return a future value.
| Contract 1 | Contract 2 | |
| Number of periods | 12 | 36 |
| Payments | 0 | 0 |
| Current value | -$5,000 | -$7,500 |
| Future value | $15,000 | $25,000 |
Interest rate
| 9.58727% | 3.40092% |
