RATE

Syntax

RATE (NPer, Pmt, PV, FV, {Type})

Description

The RATE function returns the rate required to accumulate a future value of FV when the present value is PV, the number of periods is NPer, and the payment is Pmt. If Type is omitted or zero, RATE assumes that the investment is an ordinary annuity. If Type is nonzero, RATE assumes that the investment is an annuity due.

Note:

Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Returns

The RATE function returns the rate required to accumulate a future value of FV when the present value is PV, the number of periods is NPer, and the payment is Pmt. If Type is omitted or zero, RATE assumes that the investment is an ordinary annuity. If Type is nonzero, RATE assumes that the investment is an annuity due.

Note:

Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Example

Suppose that you wish to invest 5000 USD at the end of each year for 10 years. You can create a data cube called RATE_REQUIRED and calculate the rate of return required to earn 100000 USD as follows:

RATE(10, -5000, 0, 100000) = 14.69%

Now suppose that you initially invest 15000 USD in addition to the yearly payments. Use the following formula:

RATE(10, -5000, -15000, 100000) = 7.23%

Finally, suppose that you make the payments at the start of the year. You can use the following formula:

RATE(10, -5000, -15000, 100000, 1) = 6.50%