RATE function

The RATE function in Excel calculates the interest rate per period of an investment or loan, given the present value, future value, the number of periods, and the payment amount. It is commonly used in financial analysis to determine the interest rate on loans or investments based on known cash flows.

Syntax

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

Parameters

1. nper: The total number of payment periods in the investment or loan.
2. pmt: The payment made each period; it cannot change over the life of the investment or loan. Usually, this is a negative number, representing outflow (such as loan payments).
3. pv: The present value, or the initial investment or loan amount (usually entered as a negative number because it is an outflow of cash).
4. [fv] (optional): The future value, or the desired cash balance you want after the last payment. If omitted, it is assumed to be 0 (default value), which is common for loans.
5. [type] (optional): The timing of the payment:
   • 0 (or omitted): Payments are made at the end of the period (default).
   • 1: Payments are made at the beginning of the period.
6. [guess] (optional): Your guess for the rate. If omitted, Excel assumes 10% as the starting point for iteration.

How It Works

The RATE function uses an iterative process to calculate the interest rate, attempting to match the present value and future value with the given payment amount and number of periods. It uses the following general formula:

$$PV = \frac{PMT}{(1 + r)} + \frac{PMT}{(1 + r)^2} + \cdots + \frac{PMT + FV}{(1 + r)^{nper}}$$

Where:

• PMT is the payment amount,
• r is the rate per period (what the function is solving for),
• nper is the number of periods,
• FV is the future value,
• PV is the present value.

Example

Let’s say you want to find the interest rate on a loan with the following details:

• The loan amount (present value) is $10,000,
• The loan will be paid off in 5 years,
• The monthly payments are $200,
• The loan has no remaining balance after 5 years (future value is 0).

You can calculate the interest rate with the following formula:

=RATE(5*12, -200, 10000)

In this case:

• 5*12 represents 5 years with monthly payments, so there are 60 periods.
• -200 represents the monthly payment amount (negative because it’s an outflow).
• 10000 is the loan amount (positive, because it’s the amount you borrow).
• The default values for fv and type are assumed as 0 and 0, respectively.

Important Notes

• The payment (pmt) should be entered as a negative number for outflows (loan payments, investments) and a positive number for inflows.
• If the future value (fv) is not provided, it defaults to 0, which is typical for loans.
• The type parameter defaults to 0, meaning payments are assumed to be made at the end of the period unless specified otherwise.
• Guess is optional. If you don’t provide it, Excel starts with a default guess of 10%. Providing a closer guess can speed up the calculation.

Summary

The RATE function helps calculate the interest rate per period on loans or investments. It is especially useful when you know the loan amount, payment amount, number of periods, and future value, but want to determine the interest rate. This function is often used in mortgage or loan calculations.