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

Calculate Cumulative Totals with Excel SUM Function

Today, through a simple example, we will show you how to use one of the most common-used Mathematical functions in excel, the SUM function, to add up the sum. In our daily life, we keep an account of what we ...

BMI Calculation Formula In Ms Excel

You might have come across a task in which you were assigned to make BMI calculations of the supplied numbers, and you may be looking for an efficient approach to accomplish this process rather than doing BMI calculations manually, by ...

Calculate The Period of Loan or Investment in Excel

In our daily life, most of us will invest or take a loan, so we need to master some simple financial functions to calculate the period needed for loans or investments. If you work as an accountant, you need to ...

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

Basic Price Discount Calculation with Excel VLOOKUP Function

We often encounter product discounts in our shopping. Depending on the level of spending, the mall will offer different percentages of discounts. Usually, the more you spend, the bigger the discount, while the less you spend, the smaller the discount. ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Count Attendance and Absence with Google Sheets COUNTIF

In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Google Sheets COUNTIF function to count the attendance. Using a week as an example, we use the following example to ...

Calculate Average Of Last 5 Or N Values In Columns in Google Sheets
Average last N values in columns in google sheets1

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values in google sheets, then what would you do? If you are new to google sheets, then your first attempt ...

Sidebar