RATE Function in Excel and 10 Different Formulas with it

The function return with interest rate for every year. It is calculated by the iteration, and can have either zero or even more solutions.

Parameters and Arguments

The syntax of the rate function is – RATE(nper;pmt;pv;fv;type;guess) – and they are working together to return with the interest rate.

Nper: This is crucial to the function, and would be the total number of the payment periods in the annuity.

Pmt: This is required part of the RATE function. It is the payment that is made each period, and cannot be changed throughout the life of the annuity.

PV: Present value is required, and is the total amount that the future payments are currently worth

FV: This is the future value, and is optional. It is the cash balance that the user wishes to attain when the final payment has been made. If it is omitted, then the rate would assume it to be 0.

Type: This is optional, and can only be either 0 or 1. It is an indication of when the payments are due.

Example 1: Simple RATE Formula

We are in need of the interest rate that will make it possible to determine how much we could make from borrowing the client 100000.

Simple RATE Formula

Example 2: Complete Interest Rate Formula

We'd have to decide how much the interest rate would be, when our future goal is specific for the loan that we are lending the client. This is why we need to use a complete rate formula to find rate of the loan.

Complete Interest Rate Formula

Example 3: What about the Previous Loan?

The client has a loan with the company before, and we would now like to know the interest rate, based on that, which is why we need to add that amount of money that the client has borrowed in the past.

What about the Previous Loan

Example 4: Rate plus 7%

We are adding 7 percent to the interest rate.

Rate plus 7

Example 5: SUM and Rate

The client has borrowed different amount of money from the company, and we have decided that once the loan has reached 500000, then the interest rate has to increase with 8 percent. This is why we are using the sum formula with the rate.

SUM and Rate

Example 6: Interest Rate of Payment

We are using the previous data to find the answers that we’d been looking for, and this is best acknowledged through both RATE and SUM. With exactly the same data as previous ones, we would like to acknowledge the interest rate of the data.

Interest Rate of Payment

Example 7: AVERAGE and RATE

It is rare, but we are trying to evaluate a whole new scenario that will help us to find the crazy world that we have found ourselves in. Normally, we do not need the average, but this time, it has become necessary to find answers that help evaluate the scenario.


Example 8: RATE & AND Formula

The situation is quite flexible for how the payments would be made, and our number of total payment, which is why we are using both the RATE & AND formula.

RATE  AND Formula

Example 9: IF, SUM and RATE

We would like to find out the rate of the loan, after borrowing certain amount of money to the client. Normally speaking, we’d have a specific aim for rate, and we would like to know the rate after the loan amount reaches a specific amount of loan. This is why we are using IF, SUM and Rate formulas together to find answers.


Example 10: RATE, AND & SUM

We'd need the data to find out how the whole information would work out, and the only thing to do to find this answer, as the financial engineers say, is to use the rate, and & sum formulas simultaneously to find the answer that we’d need for the success of this acknowledgement.