Excel - Microsoft Excel Rate Function

Description

The Excel Rate function calculates the interest rate required to pay off a specified amount of a loan, or 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: 

Cash Flow Convention :

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

Rate Function Examples

Example 1

The following spreadsheet shows the Excel Rate function used to calculate the interest rate required, 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. 

Example 2

The following spreadsheet shows the Excel Rate function used to calculate the interest rate required, to save up $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. 

As in the previous example, as the payments are made on a monthly basis, the calculation is in months. Therefore, the number of periods is 24 months (=2 years), and the returned interest rate is expressed as a monthly rate. which needs to be multiplied by 12 to get the yearly interest rate. 

Rate Function Errors

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

Common Errors 

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 

Many users, when calculating monthly or quarterly payments, 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. : 

Common Problem No. 2 

The result from the Excel Rate function appears to be the value 0. 

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 :