The Excel RATE Function

Related Functions:

Function Description

The Excel Rate function calculates the interest rate required to pay off a specified amount of a loan, or to reach a target amount on an investment, over a given period.

The syntax of the function is:

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

Where the arguments are as follows:

nper	-	The number of periods over which the loan or investment is to be paid.
pmt	-	The (fixed) payment amount per period.
pv	-	The present value of the loan / investment.
[fv]	-	An optional argument that specifies the future value of the loan / investment, at the end of nper payments. If omitted, [fv] takes on the default value of 0.
[type]	-	An optional argument that defines whether the payment is made at the start or the end of the period. The [type] argument can have the value 0 or 1, meaning: 0 - the payment is made at the end of the period; 1 - the payment is made at the start of the period. If the [type] argument is omitted, it takes on the default value of 0 (denoting payments made at the end of the period).
[guess]	-	An initial estimate at what the rate will be. If this argument is omitted, it will take on the default value of 10% (=0.1). (Note this is only a value for Excel to start off working with - Excel then uses an iterative procedure to converge to the correct rate).

Cash Flow Sign Convention:

Note that, in line with the general cash flow sign convention, cash outflows are represented by negative numbers and cash inflows are represented by positive numbers. This is seen in the examples below.

Rate Function Examples

Example 1

In the following spreadsheet, the Excel Rate function is used to calculate the interest rate, with fixed payments of $1,000 per month, to pay off in full, a loan of $50,000 over a period of 5 years. The payments are to be made at the end of each month.

Formula:

	A
1	Monthly interest rate to clear a loan of $50,000 with payments of $1,000 per mth over 5 yrs (payments made at end of each mth):
2	=RATE( 60, -1000, 50000 )
3	Yearly Interest Rate:
4	=12*A2

Result:

	A
1	Monthly interest rate to clear a loan of $50,000 with payments of $1,000 per mth over 5 yrs (payments made at end of each mth):
2	0.62%
3	Yearly Interest Rate:
4	7.42%

In the above example:

As the payments are made on a monthly basis, the number of periods must be expressed in months (5 years = 60 months).
As the payments are outgoing, the pmt argument is a negative value.
The returned interest rate is a monthly rate. This can be converted to an annual interest rate by multiplying by 12 (as shown in cell A4).

Example 2

In the following spreadsheet, the Excel Rate function is used to calculate the interest rate required to save $20,000, over 2 years, with a starting value of zero, and monthly savings of $800. The payments are to be made at the start of each month.

Formula:

	A
1	Monthly interest rate required to save $20,000 over 2 yrs, with an investment of $800 per mth (payments made at start of each mth):
2	=RATE( 24, -800, 0, 20000, 1 )
3	Yearly Interest Rate:
4	=12*A2

Result:

	A
1	Monthly interest rate required to save $20,000 over 2 yrs, with an investment of $800 per mth (payments made at start of each mth):
2	0.33%
3	Yearly Interest Rate:
4	3.90%

In the above example:

The payments are made on a monthly basis, so the number of periods is expressed in months (2 yrs = 24 mths).
The payment is input as a negative value to show that this is an outgoing payment.
The returned interest rate is a monthly rate, which can be converted to a yearly interest rate by multiplying by 12 (see cell A4).

Further examples of the Excel Rate function are provided on the Microsoft Office website.

Rate Function Errors

If you get an error from the Excel Rate function, this is likely to be one of the following:

Common Errors

#NUM!	-	Occurs if the function fails to converge to a solution. (This may be because you have failed to use the cash flow convention of negative numbers to represent outgoing cash flows and positive numbers to represent incoming cash flows. Alternatively, you may need to provide an initial 'guess' to the function.)
#VALUE!	-	Occurs if any of the supplied arguments are non-numeric.

Also, the following problems are encountered by some users:

Common Problem No. 1

The result from the Excel Rate function is much higher or much lower than expected.

Possible Reason

When calculating monthly or quarterly payments, users sometimes forget to convert the interest rate or the number of periods to months or quarters.

Solve this problem by ensuring that the nper argument is expressed in the correct units. I.e.:

months = 12 * years
quarters = 4 * years

Common Problem No. 2

The result from the Excel Rate function appears to be the value 0 or appears as a percentage but shows no decimal places.

Possible Reason

This problem is often due to the formatting of the cell containing the function.

If this is the case, the problem will be fixed by formatting the cell to show a percentage, with decimal places.

To do this:

Select the cell(s) to be formatted as a percentage.
Open up the 'Format Cells' dialog box using any one of the following methods:
- Right-click on the selected cell or range and select the Format Cells ... option from the right-click menu;
or
- Click on the dialog box launcher in the Number grouping within the Home tab of the Excel ribbon (see rightabove);
or
- Use the keyboard shortcut CTRL-1 (i.e. Select the CTRL key and while holding this down, select the "1" (one) key).
Within the 'Format Cells' dialog box:
- Make sure that the Number tab at the top of the dialog box is selected.
- Select Percentage from the Category list on the left side of the dialog box.
  This will cause further options to appear on the right hand side of the control box, which allow you to select the number of decimal places that you want to be displayed (see rightabove).
- Once you have selected the number of decimal places that you want to display, click OK.

Return to the Excel Financial Functions Page
Return to the List of All Built-In Excel Functions