Use the Rate function in Google Sheets to find the periodic interest rate of an annuity. To make you easily learn this function, I can relate this to a real-life example.
Assume the EMI of my car loan is -$592.86 (shown in negative as its a cash outflow). The car loan amount is $31,700.00 and 5 years (60 months) is the number of periods to pay off the loan in full.
From these inputs, you can find the interest rate of the car loan using the Rate function in Google Sheets.
Before going to the Rate formula related to the above example, let’s see the syntax and arguments of this financial function.
Rate Function Syntax in Google Sheets
RATE(number_of_periods, payment_per_period, present_value, [future_value, end_or_beginning, rate_guess])Essential Arguments (Input Values)
- number_of_periods (Nper)
- payment_per_period (Pmt)
- present_value (Pv)
Nper – the total number of payments to be made | Pmt – The payment made (principal payment [Ppmt] + interest payment [Ipmt]) in each period | Pv – The present value of the annuity.
Optional Arguments
- future_value (Fv)
- end_or_beginning
- rate_guess
Fv – Future value/cash balance after making the final payment | end_or_beginning (0 or 1, 0 by default) Indicates when payments are due, whether at the end of each period or beginning of each period | rate_guess – your estimate of what the rate will be.
How to Calculate Periodic Interest Rate Using the Rate Function in Google Sheets
Let’s take the car loan example here. The input values are arranged as below in Google Sheets.
| A | B | |
| 1 | Car Loan (Pv) | $31,700.00 |
| 2 | Interest Rate (annual) | ? |
| 3 | Number of Periods (Nper [in years]) | 5 |
| 4 | Payment (Pmt [monthly]) | -$592.86 |
Rate Formula:
=rate(B3*12,B4,B1)This would return 0.39% as the periodic (here monthly) interest rate.
You can multiply this interest rate with the loan amount to get the interest amount for the first month.
=31700*rate(B3*12,B4,B1)This formula would return -$122.31 which is the interest payment (Ipmt) of the car loan amount for the first period.
To find the Ipmt for any period, multiply the interest rate (Rate formula output) with the present value of the loan amount (Pv).
You can also use the Rate function in Google Sheets to get the yearly interest rate. Just multiply the above formula with 12.
=rate(B3*12,B4,B1)*12Output – 4.63%. That means the annual interest rate of the above car loan is 4.63%. This you can fill in cell B2 in the above table.
Related Finacial Functions in Google Sheets:
