Excel > Functions

# RATE Function

The Excel RATE function is used to return the interest rate per period of a loan or investment.

The syntax for the RATE function is:

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

Argument

Purpose

nper

The number of payments for the loan or investment

pmt

pv

The present value, or total amount a number of future payments is worth now

fv

The future value, or total remaining after the last payment has been made.

This argument is optional, and if omitted the total is assumed to be 0

type

When the payments are due. It can be entered as 1 or 0 and is optional. If omitted the value is assumed to be 0

• 0 - Payments are made at the end of the period
• 1 - Payments are made at the beginning of the period

guess

Your guess at what the rate may be. It is optional and if omitted, the guess is assumed to be 10%

The examples below show the RATE function being used to return the interest rate dependent upon different parameters.

Function

Result

=RATE(B4*12,-C4,A4)

3.49%

Interest rate returned on a £5,000 loan paid in £200 monthly payments for 5 years. Payments are made at the end of each period

=RATE(B4*12,-C4,A4,,1)

3.67%

Interest rate returned on a £5,000 loan paid in £200 monthly payments for 5 years. Payments are made at the beginning of each period

=RATE(B4*52,-C4,A4)

4.00%

Interest rate returned on a £5,000 loan paid in £200 weekly payments for 5 years. Payments are made at the end of each period