Calculate Interest Rate in Google Sheets

Financial choices play a critical role in the development and execution of corporate strategies and plans. In daily life, we also face a slew of financial choices. For instance, suppose you’re applying for a loan to purchase a new automobile. It will undoubtedly be beneficial to discover the actual interest rate you will be required to pay your bank. Google Sheets has the RATE function for such circumstances, which is specifically intended to calculate the interest rate for a certain term.

Google Sheets Rate Function

RATE is an Google Sheets financial function that calculates the interest rate on an annuity over a specified time. The function calculates iteratively and may return zero or more solutions.

The following is the syntax:

=RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess])

Where:

  • number_of_periods (needed) – the total number of payment periods, which may include years, months, and quarters.
  • payment_per_period (mandatory) – the set monthly payment amount that cannot be adjusted throughout the annuity’s life. It typically includes principle and interest but excludes taxes.
  • present_value (needed) – the present value of the loan or investment, i.e. its current worth.
  • future_value (optional) – the future value, that is, the cash balance you desire to have after the previous payment. If omitted, the default value is 0.
  • end_or_beginning (optional) – specifies the manner in which payments are made: 0 or omitted (default) – payment is due at the end of the term
  • guess (optional) – your best guess for the rate. If left blank, it defaults to 10%.

Note:

  • The RATE function performs trial and error calculations. If it does not reach a solution after 20 iterations, it returns a #NUM! error.
  • By default, interest is computed on a per-payment basis. However, as seen in this example, you can get an annuity solve for interest rate by multiplying.
  • While the RATE syntax specifies present_value as a mandatory parameter, it may actually be removed if the future_value argument is included. Typically, this syntax is used to calculate the interest rate on a savings account.
  • In most circumstances, the guess parameter may be deleted since it serves just as a beginning value for an iterative method.
  • When computing RATE for various periods, ensure that the values for number_of_periods and guess are constant. For instance, if you’re making yearly payments on a two-year loan with an annual interest rate of 4%, use 2 for number_of_periods and 4% for estimate.
  • If you’re making monthly payments on the same loan, use 2*12 for the number_of_periods  and 4% /12 for the estimate.

RATE formula Examples

In this example, we’ll look at the easiest way to create a RATE formula in Google Sheets for the purpose of calculating interest rates.

Assume you borrowed $20,000 and are required to repay it in full over the following three years. You intend to pay three annual payments of $7200 each. How much will interest cost on a yearly basis?

calculate interest rate1

Please note that the yearly payment (pmt) is specified as a negative figure since it is an outgoing cash payment.

Assuming that the payment will be paid at the end of each year, we may omit or change the parameter to the default value. Also deleted are the two optional parameters and guess.

As a consequence, we get the following straightforward formula:

=RATE(B1,B2,B3)

calculate interest rate1

If the payment must be recorded as a positive integer, include the negative sign exactly before the pmt parameter in the formula:

=RATE(B1,-B2,B3)

calculate interest rate1

Now that you’re familiar with the fundamentals of utilizing RATE in Google Sheets, let’s look at a few particular use scenarios.

Determine a Loan’s Monthly Interest Rate

Given that the majority of installment loans are repaid monthly, it may be advantageous to know the monthly interest rate, correct? This is accomplished by providing an adequate number of payment periods to the RATE function.

Assume the loan is to be repaid in monthly installments over a two-year period. We multiply two years by twelve months to get the total amount of payments (2*12=24).

calculate interest rate1

The remaining parameters are listed below:

  • number_of_periods (number of periods) in A1: 24
  • payment_per_period (monthly payment) in A2: -1000
  • present_value (load amount) in A3: 20000

Assuming the payment is due at the end of each month, the monthly interest rate may be calculated using the well-known formula:

=RATE(B1,B2,B3)

calculate interest rate1

Calculate the interest rate on a monthly basis in Google Sheets

If your source data contains the number of years required to repay the loan, you may do the multiplication inside the number_of_periods argument:

=RATE(B1*12,B2,B3)

calculate interest rate1

Calculate annuity for Interest rate on Monthly Payments

Continuing our example, how do you calculate the yearly interest rate on monthly payments? Simply multiply the RATE value by the number of periods in a year, which in our instance is twelve:

=RATE(B1,B2,B3)*12

calculate interest rate1

Calculate Quarterly Interest Rate

Assume the loan is to be repaid in quarterly installments over a two-year period. We multiply two years by 4 quarters to get the total amount of payments (2*4=8).

To begin, the entire number of periods is converted to quarterly:

number_of_periods: 2 (years) multiplied by 4 (quarters per year) equals 8.

Then, compute the quarterly interest rate in cell B4 using the RATE function:

=RATE(B1,B2,B3)

calculate interest rate1

If you want to get the yearly interest rate based on quarterly interest rate, you still need to multiply the quarterly interest rate by four, using the following formula:

=RATE(B1,B2,B3)*4

calculate interest rate1

Related Functions

  • Google Sheets RATE function
    The google sheets RATE function returns the interest rate per payment period of an annuity.The syntax of the RATE function is as below:= RATE(number_of_periods, payment_per_period, present_value, [future_value, end_or_beginning, rate_guess])….
Related Posts

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed ...

Check Cell If Contains One of Many with Exclusions

In Microsoft Excel Spreadsheet or google sheets, when cells contain multiple strings, how can we accomplish this task if you want to check whether these cells contain more than one given string and exclude other given strings? In this article, ...

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Cell Contains Number

This article will talk about how to check if a given cell contains any numbers in Microsoft Excel Spreadsheet or Google Sheets. How to quickly check if a cell contains a number? When you have a small number of cells ...

Categorize Text With Keywords

If you are looking for the simplest approach to categorize the text with keywords, then look no further because you have just landed on the right blog post where you will get to know a formula and its usage to ...

Sidebar