How to use the Rate function in Excel to calculate loan interest rates is extremely simple

RATE function structure in Excel

Function syntax: =RATE(nper, pmt, pv, [fv], [type], [guess])

In there:

Nper : required argument, is the loan term.
Pmt : required argument, is the payment amount per period.
Pv : required argument, is the loan amount.
Fv : optional argument, which is the cash balance you want to collect after making the final payment.
Type : optional argument, a number 0 or 1 that specifies when the payment is due.
Guess : optional argument, which is your guess about the interest rate.

Note:

The pmt argument includes both principal and interest payments each period. If pmt is omitted, you must include the fv argument .
If fv is omitted, it is assumed to be 0.
If type is 0 or omitted, it means payment at the end of each period.
If type is 1, it means payment time is at the beginning of each period.
If guess is omitted, it is assumed to be 10 percent.

How to use the RATE function

For example, you borrow 100 million from the bank. Every month you have to pay principal and interest of 2 million within 6 years. Request to calculate the interest rate of the loan.

Applying the above function structure, we have the following formula to calculate monthly interest rate:

=RATE(B5*12;-B4;B3)

We need to multiply B5 by 12 because the loan term here is years and the payment amount is monthly.

To calculate the annual interest rate, we only need the monthly interest rate multiplied by 12. Or use the following function:

=RATE(B5;-B4*12;B3)

Isabella Humphrey

Update 04 April 2024