Syntax. Rate(Nper,Pmt,Pv,Fv,Type,Guess)
Definition. This function determines the applicable interest rate for compound interest tasks and annuity calculation (repayment calculation according to the principle of annuity repayment).
Arguments
Nper (required) The number of interest periods. It is assumed that possible regular payments (that is, the Pmt argument is greater than zero) take place at the end (or the start) of the interest periods.
Pmt (required/optional, see Note) The amount of the regular payments, which can be interpreted as annuity.
Pv (required/optional, see Note) The start value of one payment direction. For disbursement plans, this is the account balance at the beginning of these considerations; for repayment plans, it is the loan amount.
Fv (required/optional, see Note) The future value that you want to have at the end of a specific time period.
Type (optional) Specifies whether regular payments take place at the end of the periods (Type = 0 or not specified) or at the start of the periods (Type = 1).
Guess (optional) Can be used to start the necessary approximation calculation (see the background information for this function). If no value is entered for Guess, Excel calculates with a value of 10 percent.
Note
At least two of the three arguments Pmt, Pv, and Fv must be specified and not be zero. Their signs correspond to the respective direction of the money flow (see the formula discussed later in this section).
Important
The finance mathematical benefit principle
Payment of the creditor + Payment of the debtor = 0
means that you need to differentiate between disbursements and deposits in regard to the leading sign (borrowing and repayment, investment and disinvestment). Here Excel differs from the benefit principle used in finance mathematical literature:
Payment of the creditor = Payment of the debtor
Background. The five functions PV() = cash value, FV() = future value, PMT() = regular payment, NPER() = interest or payment time periods, and RATE() = interest rate, have the following relationship when the benefit principle in the preceding Note is implemented:
The cash value interest is compounded, and so are the regular payments. Finally, the sum is compared to the future value.
Using one of these functions is equal to the respective basic finance mathematical task: calculation of an unknown variable from the preceding equation, when the other variables are known. The functions thus solve the equation for each of its members. For RATE, an approximation calculation is performed.
Excel starts the calculation as an approximation from the given estimated value and will get to the solution (if it exists) step by step. You can support Excel’s search for the solution by varying the estimated value. However, you cannot force a solution of the equation.
Note
Interest rates are usually specified as annual interest rates. The functions discussed here work correctly only if the periods match the interest rate. With intra-annual interest yield, the annual interest rate is usually distributed equally over the periods: 12 months at one twelfth of the interest rate, 3 months at a quarter, and half a year at half.
Examples. Some of the headings of the following examples reflect the general usage of finance mathematical terms.
Compound Interest Calculation. Assume that an investor wants to have a small financial cushion for retirement and decides to invest a newly received inheritance of $10,000.00 for 15 years at a fixed interest rate. He hopes to get a disbursement of at least $25,000.00. At which interest rate will his plan succeed?
A calculation with
=RATE(15,,-10000,25000)
returns a required annual interest rate of 6.3 percent.
Type B German Federal Savings Bonds. With federal savings bonds of type B, paid interest is reinvested according to the agreed-upon terms. In the example for the FVSCHEDULE() function, there is a calculation that uses RATE() to confirm the yield published by the federal bank.
Annuity Calculation. A 60-year-old has saved $100,000.00 and would like to be paid an additional monthly annuity of $750.00. At which interest rate must each current balance be paid interest, if payments are planned at the beginning of each month for 15 years?
You can determine the required monthly interest rate annuity with
=RATE(15*12,-750,100000,,1)
and get a result of 0.354796 percent (the 1 in the formula represents the beginning of the month). The calculation is based on compound interest within one year. Because compound interest cannot be used for a regular savings account, the result is a little bit theoretical. The specification of the nominal interest (times 12) for a whole year is limited to two decimal places: 4.26 percent p a.. A concrete account must then be kept with a twelfth of this number.
Repayment Calculation (Annuity Repayment). Assume that an investor is able to pay back $1,000.00 per month on a loan (repayment plus interest). What should the interest rate of the loan be, if $175,000.00 were to be paid back within 30 years?
The repayment calculation is an annuity calculation in this case. Using RATE() with
=RATE(30*12,-1000,175000)
returns 0.46316 percent. Unlike a savings account, a mortgage loan uses a monthly interest of a twelfth of the agreed-upon yearly interest rate (nominal interest). However, the interest calculated with RATE() still needs to be multiplied by 12 for the year, and the account kept with the resulting nominal interest of 5.56 percent p.a.. To calculate the interest monthly, a twelfth of this interest rate is used. This results in a residual debt after 30 years.
Important
Using formulas might be different from keeping detailed notes of an account history with deposits and withdrawals because of rounding errors. This happens because the numbers for a savings account are rounded to two decimal places. If you are re-creating such an account with Excel, you should use the function ROUND() for intermediary steps that involve money. Simply limiting the display of the cell value to two decimal places often leads to incorrect results.
The example in the sample files for this function leave you room to experiment with the rounding effects, because the account plans are already prepared.
Investment Calculation. If the annual future surpluses are constant in investment evaluations, you can use the function RATE() for determining the internal capital yield instead of the IRR(). The IRR() section contains details about the internal interest rate method.