RATE

Calculates the interest rate for an annuity.

Syntax:

RATE(numperiods, payment, presentvalue, futurevalue, type, guess)

rate: the (fixed) interest rate per period.

numperiods: the total number of payment periods in the term.

payment: the payment made each period. If futurevalue is given, this may omitted (defaults to 0).

presentvalue: the lump sum payment at the start of the term.

futurevalue: the cash balance you wish to attain at the end of the term (optional - defaults to 0).

type: when payments are made (optional - defaults to 0):

0 - at the end of each period.

1 - at the start of each period (including a payment at the start of the term).

guess: an optional guess of the interest rate (defaults to 10%).

RATE calculates the fixed interest rate needed, so that presentvalue (the value today) followed by a payment each period for numperiods periods results in futurevalue, the value at the end of the term. Interest is assumed to be compounded each period. The calculation uses iteration to solve an equation, with guess as the starting value for the iteration.

If this function returns error Err:523, the iteration has failed to converge - try a different guess, or recheck the parameters.

Example:

RATE(3, -1000, 0, 3152, 0, 10%)

returns approximately 4.98%. You pay 1,000 at the end of each year for 3 years, and receive 3,152.50 at the end of the term. The applicable interest rate was 4.98%.

Application:

Buying a Car

Let's imagine you are buying a used car. The car dealership offers you a financing plan, but you want to calculate the annual interest rate to see if it's a good deal compared to other loans you've been offered.

Here's the information you have:

Loan Amount (Present Value, PV): The price of the car is $15,000. This is the initial lump sum you receive from the bank.
Monthly Payment (Payment, PMT): The dealership's financing plan requires you to make a monthly payment of $300.
Loan Term (Number of Periods, NPER): The loan is for 5 years.
Future Value (FV): After 5 years, the loan will be fully paid off, so the future value is $0.

You want to find the monthly interest rate, and then convert that to the annual interest rate.

The RATE function syntax is typically: RATE(nper, pmt, pv, [fv], [type], [guess])

nper: The total number of payment periods in the annuity.
pmt: The payment made each period.
pv: The present value or the lump sum amount that a series of future payments is worth right now.
fv: The future value, or a cash balance you want to attain after the last payment is made. (Optional, defaults to 0).
type: When payments are due. 0 = end of the period, 1 = beginning of the period. (Optional, defaults to 0).

Let's organize this data into a table.

	Parameter	Value	Description
	A	B	C
1	NPER	5 years×12 months/year=60	Total number of monthly payments.
2	PMT	-300	The monthly payment. This is a negative value because it's an outflow of cash.
3	PV	15000	The initial loan amount. This is a positive value because it's an inflow of cash (the bank gives you the money).
4	FV	0	The loan will be fully paid off.
5	Type	0	Payments are made at the end of each month.

To calculate the monthly interest rate, you would use the following formula: RATE(60, -300, 15000, 0, 0)

The function would return a value of approximately 0.01. This is the monthly interest rate.

To find the annual interest rate, you simply multiply the monthly rate by 12: Annual Rate = 0.01 * 12 = 0.12 or 12%.

This means the car dealership is offering you a loan with an annual interest rate of approximately 12%. Now, you can compare this to the 7% interest rate you were pre-approved for at your bank and make an informed decision to either take the bank loan or negotiate with the dealership.

Result of RATE(60, -300, 15000, 0, 0):

12%