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.

RATE(Number of periods, Payments, Present value[, Future value] [, Payment timing] [ ,Rate estimate])

ArgumentData typeDescription
Number of periods (required)NumberThe number of periods that the interest rate is applied to.
Payments (required)NumberThe amount paid into the investment each period.
Present value (required)NumberThe present value of the investment.
Future valueNumberThe future value of the investment.
Payment timingNumber

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:

  • 0, payments are made at the start of each period
  • 1, payments are made at the end of each period
Rate estimateNumberThe estimated interest rate.

The RATE function returns a number.

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.

Financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

RATE

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 1Contract 2
Loan amount$5,000$7,500
Monthly payment-$95.00-$135.00
Number of periods6060

Monthly interest rate

RATE(Number of periods, -Monthly payment, Loan amount)

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 1Contract 2
Loan amount$5,000$7,500
Monthly payment-$95.00-$135.00
Number of periods6060

Annual interest rate

RATE(Number of periods, -Monthly payment, Loan amount) * 12

5.28047%3.07042%

In this example, the formula calculates the required interest rates for two investments to return a future value.


Contract 1Contract 2
Number of periods1236
Payments00
Current value-$5,000-$7,500
Future value$15,000$25,000

Interest rate

RATE(Number of periods, Payments, Current value, Future value)

9.58727%3.40092%