**RATE Function (Table of Contents)**

## RATE Function in Excel

Let’s assume an example. Ram wants to take a loan/borrow some amount of money or wants to invest some money from a financial company XYZ. The company needs to do some financial calculations like the customer has to pay something to the financial company against loan amount or how much customer needs to invest so that after some time he/she can get this much amount of money.

In these scenarios, Excel has the most important function “RATE” which is the part of a financial function.

### What is RATE Function?

A function which is used to calculate the interest rate for paying the specified amount of a loan or to get the specified amount of an investment after some period of time is called RATE function.

### RATE Formula

Below is the RATE Formula:

RATE function uses below arguments

**Nper:** The total no. of periods for the loan or an investment.

**Pmt:** The payment made each period and this is a fixed amount during the loan or investment.

**Pv:** The current (Present) value of a loan/an investment.

**[Fv]:** That’s the optional argument. This specifies the future value of the loan /investment at the end of total no. of payments (nper) payments.

4.5 (1,457 ratings)

View Course

If don’t provide any value, then it automatically considers fv=0.

**[type]:** This is also an optional argument. It takes logical values 0 or 1.

1 = If payment made at the beginning of the period.

0 = If payment made at the end of the period.

If don’t provide any value, then it automatically considers it as 0.

**[guess]:** An initial guess for what the rate will be. If don’t provide any value, then it automatically considers this as 0.1 (10% ).

### Explanation of RATE Function:

RATE Function is used in different-different scenarios.

- PMT (Payment)
- PV (Present Value)
- FV (Future Value)
- NPER (No. of periods)
- IPMT (Interest payment)

**How to use RATE Function in Excel?**

RATE Function is very simple to use. Let us now see how to use RATE function in Excel with the help of some examples.

### Example #1

You want to buy a car. For this, you apply for a loan of $5,000 from the bank. The bank provides this loan for 5 years and fixed the monthly payment amount of $150.60. Now you need to know the annual interest rate.

Here, we have the following information available:

=RATE (B4,B3,-B2)

Here the result of the function is multiplied by 12, gives the annual percentage rate. B2 is a negative value because this is outgoing payment.

=RATE (B4,B3,-B2)*12

The annual percentage rate will be:

**PMT (Payment)**

This function is used to calculate the payment made on every month for a loan or an investment on the basis of fixed payment and constant rate of interest.

PMT Formula:

### Example #2

You want to buy a house which costs $350,000. To buy this, you want to apply for a bank loan. The bank offers you the loan on 18% annual interest rate for 10 years. Now you need to calculate the monthly installment or payment of this loan.

Here, we have the following information available:

The interest rate is given annually, hence divided by 12 to convert to a monthly interest rate.

=PMT (B14/12,B13,-B12)

The Result will be:

**PV (Present Value)**

This function calculates the present value of an investment or a loan taken at a fixed interest rate. Or in other words, it calculates the present value with constant payments, or a future value, or the investment goal.

### Example #3

You have made an investment which pays you $200,000 after 18 years at an annual interest of 9%. Now you need to find out that how much to be invested today so that you can get a future value of $200,000.

Here, we have the following information available:

=PV (B23,B22,0,B21)

Result is:

The result is in negative numbers as it’s the cash inflow or incoming payments.

Let’s take one more example of PV function.

### Example #4

You have taken a loan for 5 years that has a fixed monthly payment amount of $150.60. The annual interest rate is 5%. Now you need to calculate the original loan amount.

Here, we have the following information available:

=PV (5/12,5*12,-B17,0)

Result is:

This monthly payment was rounded to the nearest penny.

**FV (Future Value)**

This function is used to determine the future value of an investment.

FV Formula:

### Example #5

You invest a certain amount of money $35,000 at 6% annual interest for 20 years. Now the question is: with this investment, how much will we get after 20 years?

Here, we have the following information available:

=FV (B3,B4,0,-B2)

Result is :

**NPER (No. of periods)**

This function returns the total no. of periods for an investment or for a loan.

NPER Formula:

### Example #6

You take a loan of amount $5,000 with a monthly payment amount of $ 115.43. The loan has a 5% annual interest rate. To calculate the no. of periods, we need to use NPER function.

Here, we have the following information available:

=NPER (B3/12,B4,-B2,0)

This function returns 47.869 i.e. 48 months.

**IPMT (Interest payment)**

This function returns the interest payment of a loan payment or an investment for a specific time period.

IPMT Formula

### Example #7

You have taken a loan of $30,000 for one year at an annual interest rate of 5%. Now to calculate the interest rate for the first month, we will use the IPMT function.

Here, we have the following information available:

=IPMT(B3/12,1,B4,-B2)

Result is:

### Things to Remember about RATE Function

There are two factors commonly used by the finance industry – Cash outflow & Cash inflow.

**Cash outflow: **The outgoing payments are denoted by negative numbers.

**Cash inflow: **The incoming payments are denoted by positive numbers.

### Recommended Articles

This has been a guide to Excel RATE function. Here we discuss the RATE Formula and how to use RATE function along with practical examples and downloadable excel template. You can also go through our other suggested articles –