# A to Z of Excel Functions: The RATE Function

22 July 2024

*Welcome
back to our regular A to Z of Excel Functions blog. Today we look at the RATE function. *

** **

**The
RATE function**

If you have
ever been involved calculating financials, you will appreciate interest is a
fundamental aspect. Annuities often need
to be calculated, *i.e.* regular, periodic payments of identical amounts
earning a similar rate of return.

Perhaps the easiest way to think of it is as follows:

- Let’s assume interest is set at 10% (and we will assume this is after tax)
- Something that is invested at $100 this year will increase by 10% next year,
*i.e.*be valued at $110 - Something that is invested at $100 this year will increase by 10% over the next two years,
*i.e.*be valued at $121 - Something that is invested at $100 this year will increase by 10% over the next three years,
*i.e.*be valued at $133.10.*etc.*

Note that
all of these valuations are for a *point* of time __not__ a *period*. This is a common mistake in modelling.

The **RATE **function returns the interest rate per period of such an annuity. **RATE** is calculated by iteration and can
have zero [0] or more solutions. If the
successive results of **RATE** do not converge to within 0.0000001 after 20
iterations, **RATE **returns the *#NUM!* error value.

The **RATE **function has the following** **syntax:

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

The **RATE** function has the following arguments:

**nper:**this is required and represents the total number of payment periods in an annuity**pmt:**this is also required. This is the payment made each period; it cannot change over the life of the annuity. Typically,**pmt**contains principal and interest but no other fees or taxes. If**pmt**is omitted, you must include the**pv**argument**pv:**this argument is also required. This is the present value, or the lump-sum amount, that a series of future payments is worth right now. If**pv**is unspecified, it is assumed to be zero [0]**fv:**this argument is optional and represents the future value, or a cash balance, you want to attain after the last payment is made. If**fv**is omitted, it is assumed to be zero [0] (the future value of a loan, for example, is 0). If**fv**is omitted, you must include the**pmt**argument (which is a weird thing to say given it is required!)**type:**this is also optional. The**type**should either be zero [0] or one [1] and indicates when payments are due. If**type**is omitted, it is assumed to be zero [0]

**guess:**another optional argument. Your guess for what the rate will be. If you omit the**guess**, it is assumed to be 10%. If**RATE**does not converge, try different values for**guess**.**RATE**usually converges if**guess**is between zero [0] and one [1].

Make sure that you are consistent about the units you use for specifying **guess** and **nper**. If you make monthly
payments on a four-year loan at 12% annual interest, use 12%/12 for **guess** and 4*12 for **nper**. If you make
annual payments on the same loan, use 12% for **guess** and 4 for **nper**.

For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend receipts, is represented by positive numbers.

Sometimes, the numbers aren’t quite what you expect for this function. That’s because Microsoft uses the following relationship to be consistent across its related financial functions:

Please see my example below:

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day. *A full page of the function articles can be found here. *