The RATE function in Google Sheets helps you calculate the periodic interest rate of an annuity, such as a loan or investment with equal payments over time.
To make it easier to understand, let’s walk through a practical, real-life example involving a car loan.
Real-Life Example: Car Loan Interest Rate
Let’s say I’ve taken a car loan of $31,700 with a loan term of 5 years (60 months). The monthly EMI (equated monthly installment) is -$592.86 (a negative value to reflect cash outflow).
Using the RATE function, we can calculate the monthly interest rate of this loan.
Syntax of the RATE Function in Google Sheets
RATE(number_of_periods, payment_per_period, present_value, [future_value, end_or_beginning, rate_guess])Required Arguments:
- number_of_periods (Nper) – Total number of payments
- payment_per_period (Pmt) – The amount paid each period (includes principal and interest)
- present_value (Pv) – The current value of the loan or investment
Optional Arguments:
- future_value (Fv) – The cash balance after the last payment (defaults to 0)
- end_or_beginning – 0 if payments are due at the end of each period (default), or 1 if at the beginning
- rate_guess – An initial estimate of the interest rate (optional; Sheets defaults to 10%)
Example: Calculate the Monthly Interest Rate
Here’s how the data might look in your Google Sheet:
| A | B |
|---|---|
| Car Loan (Pv) | $31,700.00 |
| Interest Rate (annual) | ? |
| Number of Periods (years) | 5 |
| Payment (Pmt – monthly) | -$592.86 |
To calculate the monthly interest rate, use the formula:
=RATE(B3*12, B4, B1)This returns a decimal value (0.0039), but Google Sheets automatically formats it as a percentage, displaying it as 0% by default due to rounding.
✅ Tip: Click the “Increase Decimal Places” button on the toolbar to reveal the actual value—0.39% per month.
Calculate First Month’s Interest Payment
To find the first month’s interest payment, multiply this monthly rate by the loan amount:
=31700 * RATE(B3*12, B4, B1)Result: -$122.31, which is the interest portion (IPMT) of the first payment.
Calculate the Annual Interest Rate
To convert the monthly rate to an annual interest rate, multiply by 12:
=RATE(B3*12, B4, B1) * 12Output: 0.0463, or 4.63% annually.
✅ Tip: To display this result as a clean percentage, wrap the formula with TO_PERCENT:
=TO_PERCENT(RATE(B3*12, B4, B1) * 12)This will show the output directly as 4.63%, instead of a decimal.
You can now enter this value in cell B2.
Notes and Tips
- The RATE function in Google Sheets returns the periodic rate, so be sure your inputs (like number of periods and payment frequency) match accordingly.
- Use a negative number for payments (Pmt) to reflect cash outflow, such as a loan repayment.
- You can use optional arguments like
future_valueorend_or_beginningto handle more complex financial scenarios.
Conclusion
The RATE function in Google Sheets is a powerful financial tool to calculate periodic interest rates for loans, investments, and annuities. Once you understand the required inputs—number of periods, payment amount, and present value—you can use it to uncover hidden interest rates in various financial situations.
Related Financial Functions in Google Sheets
If you’re learning about the RATE function, you may also want to explore:
