What is Rate Function in Google Sheets?
The Rate function in Google Sheets calculates the interest rate of an annuity investment based on periodic payments of a constant amount and the assumption of a constant interest rate. In simpler words, the RATE function in Google Sheets is used to calculate the interest rate per period of an annuity. Here, an annuity refers to a series of equal cash flows at regular intervals over a fixed period.
In the example below, we have a loan of $10,000 for a term of 5 years. Monthly payments are made at $212. We can determine the interest rate using the RATE function in Google Sheets, as shown below.
The yearly interest rate is around 9.9%.
Table of Contents
Key Takeaways
- The RATE function in Google Sheets is a financial function used to calculate the interest rate per period of an annuity investment or for a loan, given the number of periods, periodic payment amount, future value, present value, and payment timing.
- The syntax of the RATE function is as follows =RATE(nper, pmt, pv, [fv], [type], [guess]).
- Here, the type is optional and indicates when payments are due.
- 0: Payments are due at the end of the period.
- 1: Payments are due at the beginning of the period.
- The function uses iterative calculations and may require a guess value to find the correct rate.
- Remember that the rate returned by the Rate function in Google Sheets represents the periodic rate. For annual rates, multiply by the appropriate number.
Rate() Google Sheets Formula
The syntax for the RATE function is as follows:
RATE(numberofperiods, paymentperperiod, presentvalue, [futurevalue, endorbeginning, rate_guess])
Arguments
- number_of_periods – The number of periods for which payments to be made.
- payment_per_period – The amount to be paid per period.
- present_value – The present value of the annuity.
- future_value – (optional) – The future value remaining after the final payment has been made.
- end_or_beginning – (optional) – indicates if payments are due at the end (0) or beginning (1) of each period.
- rate_guess – (optional) – An estimate of the interest rate (0.1 by default).
How to Use Rate Function in Google Sheets?
Now that we have learned the syntax of the RATE function let us put it to practical use. As we know, the RATE function is used to calculate the interest rate for a loan or determine the inflation rate of an investment, which helps us calculate the ROI. Besides, it can be used to find the effective annual rate which takes into account the compounding interest rate. To use the interest RATE function in Google Sheets, enter the details required for the function as follows in your spreadsheet. Let us calculate the interest for a loan of $5000 over five years with monthly payments of $170.
Step 1: Let us enter the details as shown below in a Google sheet.
Step 2: To enter the formula, go to cell B5 and enter the following formula. The syntax is as follows: =RATE(nper, pmt, pv, [fv], [type], [guess])
=RATE(B4,-B2,B1)
The negative sign in the formula indicates the calculation from the perspective of the person taking on the loan. Since it is a cash outflow, he is losing that amount every month causing it to be negative.
Step 3: Press Enter. You get the monthly interest rate for this loan. To get the yearly interest rate, multiply this value by 12.
You get the following result. If it is in decimal form, convert it to percentage from the Google toolbar.
From the Google Menu
You can also use the RATE function from the Google Sheets menu. Select the cell where you want to calculate the rate. Go to the following path and select the Rate function.
Insert->Function -> Financial-> Rate.
Examples
Here, we learn how to use the Google Sheets RATE function with examples to help us understand the concept of the Rate function better.
Example #1 – Calculate mortgage payments
Let us look at an example where we calculate the interest rate for a loan amount of $100,000 and a loan term of 20 years. The monthly payment details are $750.65. The number of payments per year is 12.
Step 1: Enter the details in a Google sheet.
Step 2: Calculate the monthly interest rate in B6 with the following formula after calculating the total number of payments in cell B5.
Cell B5: =B3*B4
Cell B6: =RATE(B5,-B2, B1)
Step 3: To calculate the yearly rate, multiple B6 by 12.
You get the value as shown below.
Calculating the Monthly Mortgage Payment
Step 4: After calculating the interest rate, as shown above, you can use the PMT function to verify the monthly mortgage payment.
For the same example, apply the following function in cell B8.
=PMT(B6, B5, -B1)
As observed, you get the monthly mortgage payment accurately which can be verified from cell B2.
Example #2 – Evaluate investment returns
Now that we have seen the use of the RATE function in Google Sheets in the above example let us evaluate the investment returns using the same. Suppose you have this information regarding an investment.
- Initial Investment (Principal): $20,000
- Periodic Payment: $150
- Number of years: 5
- Total Number of Periods: 60
- Future Value: $35,000
- Payments per Year: 12
Step 1: Enter the above details in Google sheets.
Step 2: In cell B7, enter the following formula. Since it is an investment, it is -B1.
=RATE(B4, -B2, -B1, B5)
Step 3: Calculate the yearly interest rate.
The RATE function calculates the interest rate per period, and by multiplying the periodic interest rate by the number of periods per year, you get the annual interest rate. Thus, you can evaluate investment returns using the RATE function with the effective annual rate of return on your investment.
Example #3 – Calculate bond yields
In this Google Sheets RATE function example, the face value of a bond is $1500. The purchase price of the bond is $1200. The periodic interest payment made is $80, and the total number of periods is ten if we consider semi-annual payments. Let us enter these details in a Google sheet.
Step 1: Let us calculate the rate for the periodic yield as follows.
=RATE(B4, B3, -B2, B1)
Step 2: We get the periodic yield as shown below. Therefore, the yearly yield is as follows.
=B5*2.
Thus, you can calculate the yield to maturity (YTM) of a bond using the RATE function in Google Sheets. It will give you insights into the bond’s annual yield based on details like its purchase price, coupon payments, and face value.
Important Things to Note
- For the last optional argument, rate_guess, you enter a value based on your assumption of what the rate may be. The default value here is 10%.
- The RATE function calculates the interest rate per payment period. You can use it to find the annual interest rate by multiplying it with a suitable number. For instance, if it’s semi-annual, you multiply by two, and if it’s monthly, you multiply by 12.
- The formula should contain a negative number to represent cash outflow. The positive numbers represent cash inflows, and the negative numbers, cash outflows.
- You get the #NUM! Error when the RATE function cannot find a solution.
- Sometimes, the interest rate is calculated to be 0%. It is because the interest rate is less than 1%. As the cell is formatted to not represent decimals, it is rounded to zero. You can use the “increase decimal places” option in the toolbar to get the correct value.
- For cash flows that occur at regular intervals and of the same value, use the RATE function. If the amount changes but the payment is at regular intervals, you can use the IRR function.
Frequently Asked Questions (FAQs)
Some of the common mistakes when using the RATE function include:
• When we use a positive number to represent a cash outflow, you get a #NUM error.
• If any of the values are non-numeric, you will get the #VALUE error.
• You sometimes get a negative interest rate when you forget to convert years to the total number of periodic payments. Its incorrect value leads to mistakes.
• The RATE function is commonly used for determining the interest rate to attain our investment goals during investment planning.
• It is used to find the interest rate for mortgage loans such as purchasing a car or house.
• You can determine the interest rate required for a desired retirement fund or to calculate bond yields.
The RATE function is used for annuities which have constant payments and a constant rate at regular intervals. IRR (Internal Rate of Return) is used for cash flows that do not have constant payments or periods.
Thus, RATE is for constant payments, while IRR is for irregular cash flows.
Recommended Articles
Guide to What is Rate Function in Google Sheets. Here we discuss how to use Rate Function in Google Sheets with its examples. You can learn more from the following articles. –
Leave a Reply