# Financial functions – PMT to RATE

### Table of Contents

## 1. How to use the PMT function

**What is the PMT function?**

The PMT function returns the payment amount needed for borrowing a fixed sum of money based on constant payments (annuity) and interest rate.

You can also use the PMT function to calculate the amount to save each period to reach a given sum, based on an interest rate and the number of periods.

**What is the principal?**

The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.

**What is an investment?**

An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.

**What are periods?**

A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.

**What is present value?**

The present value is the initial amount that will earn interest/dividend.

**What is future value?**

The compounded amount after the calculated periods based on the given rate. It measures what a current capital (present value) amount will be worth at a designated future date.

**What are periodic constant payments?**

Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.

**What is a constant interest rate?**

A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.

**What is the number of compounding periods per year?**

The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.

Some common compounding periods:

- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year

**What is compounding?**

Compounding refers to the process of generating more interest from interest that was previously earned. It causes interest to grow exponentially over time.

**Related functions**

Function | Description |
---|---|

PMT(rate, nper, pv, [fv], [type]) |
Returns the payment amount needed for borrowing a fixed sum of money based on constant payments (annuity) and interest rate. |

PPMT(rate, per, nper, pv, [fv], [type]) |
Calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate. |

IPMT(rate, per, nper, pv, [fv], [type]) |
Calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate. |

ISPMT(rate, per, nper, pv) | Calculates the interest paid during a specific period of an investment. |

#### Table of Contents

- PMT Function Syntax
- PMT Function Arguments
- PMT Function example
- Excel loan calculator
- Loan calculator - extra payment
- Calculate running total interest paid
- Calculate running total principal paid
- Calculate the interest saved saved by making extra payments?
- Calculate the months saved by making extra payments?
- I know the loan payment, interest rate, and term. How do I calculate the loan amount?
- Get Excel file

### 1. PMT Function Syntax

PMT(*rate*, *nper*, *pv*, [*fv*], [*type*])

### 2. PMT function Arguments

Rate |
Required. The interest rate. |

nper |
Required. The number of payments. |

pv |
Required. The present value. |

[fv] |
Optional. The future value. Default value is 0 (zero). |

[type] |
Optional. When payments are due, default value is 0 (zero).
0 (zero) : at the end of the period. 1 : at the beginning of the period. |

Think carefully about what period you use (years, quarterly, months, weekly, days or whatever unit) before you specify the number of payments (nper) and the interest rate.

Monthly payments: For a ten-year loan at an annual interest rate of 5 percent use 5%/12 (rate) and 10*12 (nper).

Annual payments: For a ten-year loan at an annual interest rate of 5 percent use 5% (rate) and 10 (nper).

### 3. PMT Function example

The above image shows the annual payment needed for a 10-year $12,000 loan based on a 5 percent interest rate.

Formula in cell C7:

The image above demonstrates how to calculate the monthly savings amount needed to reach 100,000 with a 3% interest rate and 10 years of savings.

Formula in cell C9:

### 4. Excel Loan Calculator

The image above describes a loan calculator that displays payment, amortization or principal, interest, and remaining loan per month. The formulas in column E to I resize appropriately based on the values you enter in C2:C4.

Loan amount |
This is the total amount you borrow, this can be, for example, a mortgage. |

Annual interest |
A percentage that you will pay for the loan annually. |

Years |
For how long the loan will be paid. |

Monthly payment |
This cell is calculated by Excel using the PMT function, don't enter a value here. |

### 4.1 Calculate the monthly payment

Formula in cell C5:

### Explaining formula in cell E4

#### Step 1 - Calculate yearly payment

The PMT function has the following arguments: PMT(*rate*, *nper*, *pv*, [*fv*], [*type*])

rate - interest rate

nper - number of payments

pv - present value

The interest rate is annual. The number of payments is once per year for 40 years. The present value is 400000.

PMT(C3,C4,C2)

becomes

PMT(0.013, 40, 400000)

and returns -12887.7516044148.

#### Step 2 - Calculate monthly payment

Divide yearly payment with 12 to calculate the monthly payment.

PMT(C3,C4,C2)/12

becomes

-12887.7516044148/12

and returns -1073.9793003679.

#### Step 3 - Remove sign

The PMT function returns a negative value meaning the amount will be subtracted from your account. The ABS function removes the sign, we don't need the negative sign in our calculations.

ABS(PMT(C3,C4,C2)/12)

becomes

ABS(-1073.9793003679)

and returns 1073.9793003679.

### 4.2 Create a series of numbers representing months

Value in cell E3:

Formula in cell E4:

Copy cell E4 and paste to cells below as far as needed.

### Explaining formula in cell E4

#### Step 1 - Calculate months needed to pay back loan

$C$4*12

becomes

40*12 equals 480.

#### Step 2 - Check if the cell above is equal or larger than the number of months

The parentheses allow us to control the order of operation. We want to calculate $C$4*12 before we evaluate if the value in cell E3 is larger than or equal to.

E3>=($C$4*12)

becomes

E3>=480

becomes

1>=480

and returns FALSE.

#### Step 3 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

The IF function allows us to add 1 to cell E3 if E3 is smaller than or equal to the number of payments needed.

IF(E3>=($C$4*12),"",E3+1)

becomes

IF(FALSE,"",E3+1)

becomes

IF(FALSE,"",1+1)

and returns 2 in cell E4.

### 4.3 Populate column F with payments

Formula in cell F3:

### Explaining formula in cell F3

#### Step 1 - Calculate monthly payment

The cell references below are absolute cell references, they don't change when we copy the cell and paste to cells below.

PMT($C$3,$C$4,$C$2)

becomes

PMT(0.013, 40, 400000)

and returns -12887.7516044148.

#### Step 2 - Divide with 12

PMT($C$3,$C$4,$C$2)/12

becomes

-12887.7516044148/12

and returns -1073.9793003679

#### Step 3 - Check if adjacent cell is not empty

The IF function makes sure that the adjacent cell in column E is not empty, we don't want to continue calculating when there are no more months.

IF(E3<>"",PMT($C$3,$C$4,$C$2)/12,"")

becomes

IF(1<>"",PMT($C$3,$C$4,$C$2)/12,"")

becomes

IF(TRUE,PMT($C$3,$C$4,$C$2)/12,"")

becomes

IF(TRUE,-1073.9793003679,"")

and returns -1073.9793003679.

### 4.4 Calculate amortization

The formula in cell G3 returns 0 (zero) now but will return a number when the formulas in cell H3 and I 3 are populated.

Formula in cell G3:

### Explaining formula in cell G3

Formulas in cell H3 and I3 are explained in the next section. They are populated now in order to explain the calculation in cell G3.

#### Step 1 - Check if remaining loan is larger than monthly payment

The larger than character checks if cell I3 is larger than cell C5. Note that the dollar signs are important here. They make the cell reference to cell C5 absolute meaning it does not change when we copy the cell and paste to cells below.

$C$5<I3

becomes

1073.9793003679<400000

and returns TRUE.

#### Step 2 - Calculate amortization

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF($C$5<I3,-F3-H3,I3)

becomes

IF(TRUE,-F3-H3,I3)

becomes

IF(TRUE,-1073.9793003679-433.333333333333,I3)

and returns 640.645967034566

### 4.5 Calculate interest per month

Formula in cell H3:

### Explaining formula in cell H3

#### Step 1 - Divide annual interest with 12

There a re 12 months in a year, to get the monthly interest we divide the annual interest with 12.

$C$3/12

becomes

0.013/12

and returns 0.00108333333333333.

#### Step 2 - Multiply with loan

$C$3/12*I3

becomes

0.00108333333333333*I3

becomes

0.00108333333333333*400000

and returns 433.333333333333.

### 4.6 Calculate remaining loan per month

Formula in cell I3:

Formula in cell I4:

The formula in cell I4 takes the loan in cell I3 and subtracts with the amortization in cell G3.

### 5. Loan calculator - extra payment

The loan schedule shown in the image above allows you to do extra payments, type them in column F and the formula in column G takes care of the calculations.

The only formula that changes compared to the loan calculator above is the formula in column G.

Formula in cell G9:

The formula in cell G9 takes the loan amount in cell G8 and subtracts with the amortization in cell D8 and the extra payment in cell F8.

Copy cell G9 and paste to cells below as far as needed. The other formulas are explained here: Loan schedule calculator

### 6. Calculate running total interest paid

The image above demonstrates a formula in column G that displays the sum of interest paid, in other words, a running total of interest paid.

Formula in cell G8:

### Explaining formula in cell G8

#### Step 1 - Cell reference

Combine an absolute and relative cell reference and you get an expanding cell reference. It grows when you copy the cell and paste to cells below as far as needed.

$E$8:E8 returns 433.33.

#### Step 2 - Sum values

The SUM function adds numbers from a cell range and returns a total.

SUM($E$8:E8)

becomes

SUM(433.33)

and returns 433.33 in cell G8.

### 7. Calculate running total principal paid

The image above demonstrates a formula in column G that displays the sum of principal paid, in other words, a running total of principal paid.

Formula in cell G8:

Use the following formula in cell G8 if you want to include extra payments to amortization total:

Check formula explanation here: Calculate running total interest paid

### 8. Calculate the interest saved by making extra payments?

The formula in cell H8 calculates the saved interest based on the amount of the extra payment specified in cell F8.

Formula in cell H8:

### Explaining formula in cell H8

#### Step 1 - Calculate interest per month

The value in cell C3 is the annual interest. Divide with 12 to get the monthly interest. $C$3 is an absolute cell reference, it stays the same when we copy the cell and paste it to cells below.

$C$3/12

becomes

0.013/12 equals 0.00108333333333333.

#### Step 2 - Multiply with amount

F8*($C$3/12)

becomes

F8*0.00108333333333333

becomes

500*0.00108333333333333

and returns 0.541666666666667.

#### Step 3 - Find first amortization that is zero

The MATCH function returns the relative position of a given value in an array or cell range.

MATCH(0,$D$8:$D$11360,0)

returns 477.

#### Step 4 - Subtract current month with last month

(MATCH(0,$D$8:$D$11360,0)-B8)

becomes

477 - 1 equals 476.

#### Step 5 - Multiply values

F8*($C$3/12)*(MATCH(0,$D$8:$D$11360,0)-B8)

becomes

0.541666666666667*476

and returns 257.833333333333.

#### Step 6 - Check if cell F8 is empty

IF(F8="","",F8*($C$3/12)*(MATCH(0,$D$8:$D$11360,0)-B8))

becomes

IF(FALSE,"",F8*($C$3/12)*(MATCH(0,$D$8:$D$11360,0)-B8))

becomes

IF(FALSE,"",257.833333333333)

and returns 257.833333333333.

### 9. Calculate the months saved by making extra payments?

The formula in cell E4 calculates the saved months based on the amounts of the extra payments specified in column F.

Formula in cell H8:

### Explaining formula in cell H8

#### Step 1 - Calculate total months

$C$4*12

becomes

40*12 equals 480.

#### Step 2 - Calculate months needed to repay the loan

The MATCH function returns the relative position of a given value in an array or cell range.

The MATCH function finds the first cell that equals to 0 (zero) in cell range $D$8:$D$11360.

MATCH(0,$D$8:$D$11360,0)

and returns 476.

#### Step 3 - Subtract total with months needed

$C$4*12-MATCH(0,$D$8:$D$11360,0)

becomes

480 - 476 equals 4.

### 10. I know the loan payment, interest rate, and term. How do I calculate the loan amount?

The image above demonstrates a formula in cell C6 that calculates the loan amount based on an interest rate, the number of months needed to repay the loan and the monthly payment.

Formula in cell C6:

The PV function calculates the present value of a loan.

PV(rate, nper, pmt, [fv], [type])

### Useful links

PMT function - Microsoft support

Excel Formula Coach

Payment Calculator

## 2. How to use the PPMT function

**What is the PPMT function?**

The PPMT function calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate.

**What is the principal?**

The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.

**What is an investment?**

An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.

**What are periods?**

A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.

**What is present value?**

The present value is the initial amount that will earn interest/dividend.

**What is future value?**

The compounded amount after the calculated periods based on the given rate. It measures what a current capital (present value) amount will be worth at a designated future date.

**What are periodic constant payments?**

Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.

**What is a constant interest rate?**

A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.

**What is the number of compounding periods per year?**

The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.

Some common compounding periods:

- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year

**What is compounding?**

Compounding refers to the process of generating more interest from interest that was previously earned. It causes interest to grow exponentially over time.

**Related functions**

Function | Description |
---|---|

PMT(rate, nper, pv, [fv], [type]) |
Returns the payment amount needed for borrowing a fixed sum of money based on constant payments (annuity) and interest rate. |

PPMT(rate, per, nper, pv, [fv], [type]) |
Calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate. |

IPMT(rate, per, nper, pv, [fv], [type]) |
Calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate. |

ISPMT(rate, per, nper, pv) | Calculates the interest paid during a specific period of an investment. |

### PPMT function Syntax

PPMT(*rate, per, *nper*, *pv*, [fv], [type]*)

### PPMT function Arguments

rate |
Required. The interest rate. |

per |
Required. The period. |

nper |
Required. The total number of periods in an annuity.
Examples: |

pv |
Required. Present value. |

[fv] |
Optional. Future value, default value is 0 (zero). |

[type] |
Optional. When payments are due.
0 - End of period, default value. |

Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10 year period.

### PPMT function example

The image above shows the PPMT function in cell E3 calculating the principal for the second month for a 10 year loan of 100 000.

Formula in cell E3:

### PPMT function not working

### How is the PPMT function calculated?

## 3. How to use the PRICEMAT function

**What is the PRICEMAT function?**

The PRICEMAT function calculates the price per $100 nominal value of a bond that pays interest at maturity.

**What is the nominal value for a bond?**

The nominal value, also called the par value or face value, is the amount a bond is issued and redeemed for by the bond issuer. It's the stated value of the bond.

**What is interest?**

Interest refers to the periodic coupon payments made by a bond issuer to bondholders over the bond's lifetime. However, the PRICEMAT function works for bonds that pay interest at maturity.

**What is bond maturity?**

Bond maturity is the date when a bond's term ends, at which point the issuer must repay the bond's par value and any final interest due to bondholders. A 30-year bond issued today will have a maturity date 30 years from now.

### PRICEMAT function Syntax

PRICEMAT(*settlement, maturity, issue, rate, *yld*, [basis]*)

### PRICEMAT function Arguments

settlement |
Required. The bond's settlement date. |

maturity |
Required. The bond's maturity date, in other words, when it expires. |

issue |
Required. The bond's issue date. |

rate |
Required. The bond's interest rate at date of issue. |

yld | Required. The bond's annual return. |

[basis] | Optional. Day count basis.
0 (zero) - US (NASD) 30/360 (default value) 1 - Actual/actual 2 - Actual/360 3 - Actual/365 4 - European 30/360 |

**What is the bond's settlement date?**

A bond's settlement date is the date when the trade is finalized and the bond is delivered to the buyer in exchange for payment to the seller. Settlement for US treasury bonds is usually T+1 day.

**What is the bonds issue date?**

A bond's issue date is the original date when the bond was first offered and sold to investors on the primary market by the issuing entity. The bond's coupon payment schedules are aligned with the issue date.

**What is the bond's interest rate at date of issue?**

The interest rate at a bond's issue date, also called the coupon rate, is the annual interest rate the bond will pay throughout its lifetime based on the bond's par value. A 5% coupon rate means the bond will pay 5% of par annually.

**What is the bond's annual return?**

A bond's annual return is the gain or loss in value over a one year holding period plus any interest income. It factors in both bond price appreciation/depreciation and the interest coupons received.

### PRICEMAT function example

Formula in cell C10:

### PRICEMAT function not working

Settlement, maturity, issue, and basis are shortened to integers.

The PRICEMAT function returns:

#NUM! error value if

*rate *< 0

*yld* < 0

*basis* < 0

*basis* > 4

*settlement* >= *maturity*

#VALUE! error value if

*settlement*, *issue* or *maturity* is not a valid date. Use the DATE function in the PRICEMAT function to avoid errors. Example DATE(2010, 11, 5) is 5th November, 2010.

### How is the PRICEMAT function calculated?

The following formula demonstrates how the PRICEMAT function calculates.

B |
Days in year. |

DSM |
Days from settlement to maturity. |

DIM |
Number of days from issue to maturity. |

A |
Number of days from issue to settlement. |

## 4. How to use the PV function

The PV function calculates the net present value for an investment or loan. PV stands for present value.

**What is an investment?**

An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.

**What are periods?**

A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.

**What is present value?**

The present value is the initial amount that will earn interest/dividend.

**What is future value?**

The compounded amount after the calculated periods based on the given rate. It measures what a current capital (present value) amount will be worth at a designated future date.

**What are periodic constant payments?**

Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.

**What is a constant interest rate?**

A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.

**What is the number of compounding periods per year?**

The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.

Some common compounding periods:

- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year

**Related functions**

Function | Description |
---|---|

RATE(nper, pmt, pv, [fv], [type]) | Returns the interest rate per period of an annuity |

PV(rate, nper, pmt, [fv], [type]) | Returns the present value of an investment. |

FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment. |

PDURATION(rate, pv, fv) | Returns the periods needed for an investment to reach a future value. |

### What's on this webpage

- PV Function Syntax
- PV Function Arguments
- PV Function example
- What is the formula behind the PV function?
- How is the result of the PV function calculated in detail?
- What amount is needed if I want x in future value, the interest rate is constant, and the number of periods is given?
- What amount is needed if I want x in future value, the interest rate is constant, the payment amount, and the number of periods are given?
- How much can I borrow if I can pay x per month and the interest is y? (Link)
- Get Excel file

### 1. PV Function Syntax

PV(*rate, nper, pmt, [fv], [type])*

### 2. PV Function Arguments

rate |
Required. The interest rate per period. If nper is years then the interest rate is per year. Divide the interest rate with 12 if you use months in the nper argument. |

nper |
Required. The total number of payment periods in an annuity. |

pmt |
Required. The payment amount per period. Interest and principal is often included in this amount but not other fees or taxes. The pmt argument is not used if you use the [fv] argument. |

[fv] |
Optional. The future value. |

[type] |
Optional. The number 0 or 1 and indicates when payments are due. |

### 3. PV Function example

Formula in cell C7:

### 4. What is the formula behind the PV function?

Formula:

Formula in cell C7:

### 5. How is the result of the PV function calculated in detail?

The PV function in cell C6 calculates the present value of an annuity loan or the amount you can borrow when the interest rate is 5% per year and the loan is paid back in ten years. In other words, it calculates how much the loan is based on a given interest rate, how much you pay per period, and the amount you pay per period.

An annuity loan is paid back for a specific time range at a given interest rate with the same amount for all payments.

The table in cell range E2:H12 shows interest and principal for each period based on values in cell range C2:C4. The total principal (or amortization) paid is the same as the present value in cell C6, which is needed to pay back the entire loan.

Formula in cell C6:

The result in cell C6 is the present value of the loan based on the interest rate, periods, and payment amount.

The formula in cell G3 calculates the interest included in each payment.

Formula in cell G3:

The principal is what's left after the interest is paid, it is calculated in cell h3.

Formula in cell H3:

The formulas in cell G15 and H15 adds the amounts and return the total of interest and principal respectively.

Formula in cell G15:

Formula in cell H15:

### 6. What anount is needed if I want x in future value, the interest rate is constant, and the number of periods is given?

The image above demonstrates how to use the PV function when you want to know the amount needed for an investment when the interest rate is specified, and the number of periods are given.

Formula in cell C7:

Note that the pmt argument can be empty when you specify the fv (future value) argument in the PV function.

The table, shown in the image above, displays the start amount and the interest for each period. The total accumulates the interest (column F) and the fv (future value) is reached after the given period. The table is not needed for the calculation, it is only there to show that the numbers add up and are correct.

See cell F13, it is equal to the future value in cell C5.

Formula in cell F3:

Copy cell F3 and paste cells below.

Formula in cell G3:

Copy cell G3 and paste cells below.

### 7. What amount is needed if I want x in future value, the interest rate is constant, the payment amount, and the number of periods are given?

The image above shows what amount is needed if you save x amount for each period to reach a future value [fv) given a constant interest rate.

Formula in cell C7:

The table in cell range E2:H13 shows that the numbers add up to the future value, specified in cell C5, after the given time of periods based on a constant interest rate.

The table is not needed to calculate the present value of the investment, the PV function is all that is needed.

Formula in cell F3:

Copy cell F3 and paste to cells below as far as needed.

Formula in cell G4:G13:

Formula in cell H4:

Copy cell H4 and paste to cells below as far as needed.

### Useful links

PV function - Microsoft support

Excel Formula Coach - find the present value of a loan

Present Value Calculator

## 5. How to use the RATE function

**What is the RATE function?**

The RATE function returns the interest rate per period of an annuity.

**What is an investment?**

**What are periods?**

**What is present value?**

The present value is the initial amount that will earn interest/dividend.

**What is future value?**

**What are periodic constant payments?**

**What is a constant interest rate?**

**What is the number of compounding periods per year?**

Some common compounding periods:

- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year

**What is compounding?**

Compounding refers to the process of generating more interest from interest that was previously earned. It causes interest to grow exponentially over time.

**Related functions**

Function | Description |
---|---|

RATE(nper, pmt, pv, [fv], [type]) | Returns the interest rate per period of an annuity |

PV(rate, nper, pmt, [fv], [type]) | Returns the present value of an investment. |

FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment. |

PDURATION(rate, pv, fv) | Returns the periods needed for an investment to reach a future value. |

### RATE function Syntax

RATE(*nper*, *pmt*, *pv*, [*fv*], [*type*], [*guess*])

### RATE function Arguments

nper |
Required. The total number of payment periods in an annuity. |

pmt |
Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument. |

pv |
Required. The present value — the total amount that a series of future payments is worth now. |

[fv] |
Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument. |

[type] |
Optional. The number 0 or 1 and indicates when payments are due. |

[guess] |
Optional. Your guess for what the rate will be. |

### RATE Function example

Formula in cell E3:

### RATE Function not working

The RATE function uses an iterative process (repeated calculations) to find the interest rate result, this means it may have more than one possible solution for the rate.

The RATE function will display the #NUM! error value if after trying 20 times at narrowing down the rate via repeated guesses and the results are still not consistent within 0.0000001

### How is the RATE Function calculated?

The math formula behind the RATE function is:

Rate = (Fv/Pv)^{(1/nper)}

Fv - future value

Pv - present value

nper - periods

### Excel categories

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form