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:
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]||-||[fv] takes on the default value of 0.If omitted,|
|[type]||-||[type] argument is omitted, it takes on the default value of 0 (denoting payments made at the end of the period).If the|
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.
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.
In the above example:
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.
In the above example:
Further examples of the Excel Rate function are provided on the Microsoft Office website.
If you get an error from the Excel Rate function, this is likely to be one of the following:
|#NUM!||-||(This may be because you have failed to use the cash flow convention of negative numbers to represent outgoing payments and positive numbers to represent incoming payments. 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:
The result from the Excel Rate function is much higher or much lower than expected.
When calculating monthly or quarterly payments, users sometimes forget to convert the interest rate or the number of periods to months or quarters.
|months = 12 * years|
quarters = 4 * years
The result from the Excel Rate function appears to be the value 0 or appears as a percentage but shows no decimal places.
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. You can format a cell as a percentage in one of the following ways:
The easiest way to change cell formatting is to select the cell(s) to be formatted and then select the percentage button from the 'Number' group on the Home tab of the Excel ribbon (see below):
If you have an older version of Excel, that does not have the ribbon, you can change the formatting of an Excel cell via the following steps: