Periodic Rate Functions in Excel
Excel has several dozen financial functions that are useful for calculating interest rates and other components of financial equations. The "RATE" function is the pre-written formula that calculates periodic rate. For it to work, the other arguments in the equation must be constant. If you need to calculate some other component of an annuity or have a more complex scenario, choose a different Excel financial formula or write your own.
Other People Are Reading
-
How to Insert the "RATE" Function
-
To insert the "RATE" function in a cell on your Excel spreadsheet, click the "Formulas" tab. Then press the "Insert Function" button. Type "rate" in the search box and click "Go." Then select "RATE" and click "OK." Alternatively, type "=rate" in the spreadsheet cell and select "RATE" from the drop-down list that appears.
"RATE" Formula and Arguments
-
If you entered "RATE" from the "Formulas" tab, a "Function Arguments" box will appear. If you typed it in yourself, you will see the arguments suggested underneath the cell. The required arguments are "nper," "pmt" and "pv." Optional arguments are "fv," "type" and "guess." "Nper" is the total number of payment periods in an annuity. "Pmt" is the constant payment made each period, including principle and interest, and should be entered as a negative number. "Pv" is the present value of a series of future payments. "Fv" is the future value you want to attain after the last payment. Excel assumes "fv" is "0" if you leave it blank. "Type" indicates when payments are due; entering "0" or leaving this argument blank means that payments are due at the end of the period, while entering "1" means that payments are due at the beginning of the period. "Guess" is your guess at what the rate will be; if you leave this blank, Excel assumes it is 10 percent.
-
Example: Calculating Monthly Periodic Rate
-
Suppose you wanted to calculate the monthly interest rate on a car loan for $8,000. You pay $200 per month for four years. The key to making this equation work is being consistent about units. The period of the loan is four years, but since you make monthly payments, "nper" should be 4x12, or 48. "Pmt" is -200 and "pv" is 8000. You do not need to enter any of the optional arguments since the future value on a loan is zero, it doesn't matter when payments are due in this scenario and a guess isn't necessary. The formula should read "=RATE(48,-200,8000)." The calculated monthly interest rate is 1 percent.
Example: Calculating Annual Periodic Rate
-
The "RATE" function can calculate the annual percentage rate on an $8,000 car loan with monthly payments of $200 over four years. There are 48 payments of $200 totalling $800. Enter these figures into the "RATE" formula and then multiply by 12 since there are 12 months in a year. The formula should read "=RATE(48,-200,8000)*12. The calculated percentage rate is 9.24 percent.
Writing a Custom Formula
-
If the "RATE" formula doesn't calculate exactly what you need, such as if you have a compounding payment, write a custom function. For example, to calculate the annualized interest rate for a compounding investment, use "=((fv/pv)^(1/n)^m)-1." All of the arguments are the same as in the "RATE" formula, with the addition of "n," which is the total number of compounding periods, and the addition of "m," which is the total number of compounding periods per year. As with the "RATE" formula, "pv" should be a negative number for this formula to work.
Related Functions
-
If you know the periodic rate but want to calculate another value in the "RATE" formula, use related Excel functions including "PV," "PMT," "FV" and "NPER." Other closely related functions are in Excel's "Financial Functions" category.
-
References
- Photo Credit Jupiterimages/Photos.com/Getty Images
