Check Sheet Logo
Check Sheet

RATE

The RATE formula returns the interest rate per period of an annuity. This formula is often used in financial analyses to calculate the rate of return on an investment. It assumes that payments are made at regular intervals and that the interest rate remains constant throughout the duration of the annuity.

Usage

Use the RATE formula with the syntax shown below, it has 3 required parameters and 3 optional parameters:

=RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess])
Parameters:
  1. number_of_periods (required):
    The total number of payment periods in the annuity. This number must be greater than 0.
  2. payment_per_period (required):
    The payment made each period in the annuity. This number must be negative, representing outgoing payments.
  3. present_value (required):
    The present value of the annuity. This number must be negative, representing outgoing payments.
  4. future_value (optional):
    The future value of the annuity, after the final payment is made. If omitted, the future value is assumed to be 0.
  5. end_or_beginning (optional):
    Whether payments are made at the end or beginning of each period. If omitted, payments are assumed to be made at the end of each period. Enter 0 for payments made at the end of each period, or 1 for payments made at the beginning of each period.
  6. rate_guess (optional):
    An optional guess at the interest rate per period. If omitted, Google Sheets will use a default value of 0.1 (10%).

Examples

Here are a few example use cases that explain how to use the RATE formula in Google Sheets.

Calculate mortgage payments

The RATE formula can be used to calculate the interest rate on a mortgage. By inputting the number of payments, the payment amount, and the present value of the loan, the formula can determine the interest rate of the mortgage.

Evaluate investment returns

Investors can use the RATE formula to determine the expected return on investment for various annuities. By inputting the number of periods, payment per period, present value, and future value, the formula can determine the interest rate necessary to achieve the desired return on investment.

Calculate bond yields

The RATE formula can be used to calculate the yield on a bond. By inputting the number of payments, payment amount, present value, and future value, the formula can determine the yield on a bond given its purchase price.

Common Mistakes

RATE not working? Here are some common mistakes people make when using the RATE Google Sheets Formula:

Incorrect order of arguments

The RATE formula requires the arguments to be in the correct order. Make sure to enter them as number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess].

Missing arguments

Make sure to include all required arguments when using the RATE formula. You must include number_of_periods, payment_per_period, and present_value, and you can optionally include future_value, end_or_beginning, and rate_guess.

Wrong data type

The RATE formula requires certain arguments to be numeric values. Make sure you are not accidentally passing in text or other non-numeric values.

Incorrectly specifying end_or_beginning

If you include the end_or_beginning argument, make sure you specify it correctly. It should be either 0 or 1, where 0 means payments are due at the end of each period, and 1 means payments are due at the beginning of each period.

Incorrect rate_guess

If you include the rate_guess argument, make sure it is a reasonable estimate for the rate. If the formula returns an error, try adjusting the rate_guess until you get a result.

Related Formulas

The following functions are similar to RATE or are often used with it in a formula:

  • PV

    The PV function in Google Sheets calculates the present value of a regular payment stream or a lump sum amount, based on a constant interest rate. It is commonly used in financial analysis to determine the value of investments or loans. This function returns a negative value, as it represents money flowing out from the user.

  • FV

    The FV function calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.

  • PMT

    The PMT formula in Google Sheets is a financial function that calculates the periodic payment required to fully pay off a loan or investment based on a constant interest rate and a fixed number of payments. It is commonly used to determine loan payments, mortgage payments, and annuity payments.

  • NPER

    The NPER function calculates the total number of payment periods required to pay off an investment based on a constant payment amount, a fixed interest rate, and the present value of the investment. It is commonly used in financial planning and investment analysis.

Learn More

You can learn more about the RATE Google Sheets function on Google Support.