Periodic Rate Functions in Excel (2024)

Microsoft Excel 2010 can be a huge help for a business when it comes to crunching the businesses numbers. Excel has a number of financial functions revolving around the periodic interest rate, which business owners may occasionally need to determine on certain investments. These functions save considerable time over computing the numbers manually and ensure that the final result is accurate.

The Periodic Interest Rate

Interest rates are generally given as an annual percentage rate (APR) regardless of the actual period of interest accrual on the loan. For more accurate tracking of the loan, the periodic interest rate is needed, which is simply the interest rate that is applied to the loan once every period. For example, a loan with a six percent APR but which compounds monthly has a periodic interest rate of 0.5 percent. To convert the APR to a periodic rate in Excel, simply place "=.06/12" into a cell but change the ".06" to your APR and the "12" to the number of periods per year.

The Excel RATE Function

While converting an APR to a periodic rate is fairly simple, figuring out the periodic rate based on other factors is a bit more difficult. To make it easier, Excel includes the RATE function with which you can figure out the periodic rate using information other than the APR. Begin by placing "=RATE(" into a cell on your spreadsheet and then enter the three required arguments and the three optional arguments, if desired, in order. Separate each argument with a comma and to skip an argument altogether just place two commas in a row.

RATE Syntax

The RATE function has six arguments, which are listed below. The optional arguments are in brackets:

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

The "nper" argument is the number of payment periods in the annuity. The "pmt" argument is the payment that is made each period and needs to be entered as a negative number. The "pv" argument is the present value of the annuity. The "fv" argument is the future value of the annuity and should only be used when figuring out the periodic rate on an investment that will end with a cash value. The "type" argument is either a 1 or 0, with 0 symbolizing that payments come at the end of the period and 1 symbolizing that payment comes at the beginning of the period. Zero is the default value if the argument is omitted. The "guess" argument is your best guess at what the interest rate will be. Excel uses iteration to determine the periodic rate, so it will run its calculation numerous times using the "guess" value as its starting point and slowly narrow down the result. The default "guess" is "0.1," so if your formula returns an error try different values between 1 and 0 for the "guess" argument.

For example, an $12,000 loan with 36 payment periods, a $550 periodic payment, payments coming at the beginning of the period, and a guess of four percent would be entered as follows:

=RATE(36,-550,12000,,1,0.04)

This will return a periodic interest rate of 3.21 percent. Excel may by default round this to the nearest whole percentage point, so you will have to right-click the cell, choose "Format" and then click the "Up" arrow to increase the number of decimal points Excel will display.

RATE Without Knowing the Periodic Payments

The RATE function can also be used to determine the periodic interest rate when the payment that is made each period is unknown. This can only be used when the end result of the annuity will have a future value and you must enter a number for the "fv" argument is you skip the "pmt" argument. Also, the "pv" value must be entered as a negative number.

For example, a $10,000 investment with payments stretching over 36 periods and a final value of $22,000 would be entered as follows:

=RATE(36,,-10000,22000)

This will return a periodic interest rate of 2.21 percent. Note that the "type" and "guess" arguments were omitted and were thus set to their defaults of "0" and "0.10" respectively.

References