The Excel RATE function has the syntax shown below.

RATE(Nper, Pmt, PV, FV, Type, Guess)

**Arguments used in the Excel RATE function**

Nper = Number of periods (n)

Pmt = Periodic payment

PV = Present Value

FV = Future Value

Type = 0 or 1 depending on whether payment (Pmt) is at the end or start of a period

Guess = optional guess

## Use of the Excel RATE Function

The Excel RATE function is used to calculate the discount rate (i) in time value of money calculations.

For example, it can calculate the interest rate on a loan given the value of the loan, the term and the periodic payments, it can be used to calculate the interest rate earned on a savings account, the return on an investment, or the interest rate needed to generate annuity payments from a lump sum investment.

## Interest rate on a Loan

The Excel RATE function can be used to calculate the interest rate on a loan.

If a loan of 30,000 is to be repaid in equal monthly installments of 2,000 at the end of each month over a period of 5 years, then the interest rate is given by the Excel RATE function as follows:

Nper = 5 x 12 = 60 months Pmt = -600 PV = 30,000 FV = 0 (not used) Type = 0 (payment at the end of each month) Guess = (not used) Interest rate = RATE(Nper, Pmt, PV, FV, Type, Guess) Interest rate = RATE(60,-600,30000,0,0) Interest rate = 0.618% per month

It should be noted that the payment is negative as it represents a cash flow out.

The Excel RATE function shows that a 5 year loan of 30,000 with payments of 600 at the end of each month has a monthly interest rate of 0.618%. This is equivalent to an annual nominal rate of 0.618% x 12 = 7.420%

Note that payment (Pmt), and the number of periods (Nper) must relate to the same period length, in this case monthly, and that the answer provided will be the interest rate for that period length.

In this case the Guess argument has been left blank in which case Excel by default inserts 10%. The Excel RATE function works by iteration (trial and error), and therefore if an answer is not generated, changing the value of the Guess argument can help find a solution.

A similar calculation can be made if the payments are at the beginning of each month by setting the Type argument to 1. In this case the interest rate is given by RATE(60,-600,30000,0,1) = 0.641%

## Loans with a Balloon Payment at the end

Sometimes loans are made on the basis that regular periodic payments will be made together with a final balloon payment at the end of the term.

For example, if the loan terms are the same as the previous example except that a balloon payment of 6,000 is made at the end of the term, then the Excel RATE function gives the interest rate as follows.

Nper = 5 x 12 = 60 months Pmt = -600 PV = 30,000 FV = -6,000 (not used) Type = 0 (payment at the end of each month) Guess = (not used) Interest rate = RATE(Nper, Pmt, PV, FV, Type, Guess) Interest rate = RATE(60,-600,30000,-6000,0) Interest rate = 1.030% per month

The effect of the balloon payment is to increase the calculated discount rate on the loan.

Again if the regular payments are at the beginning of each month the Type argument can be set to 1. Setting the Type to 1 has no effect on the balloon payment (FV), this is always deemed to have been made at the end of the term.

## Interest Rate on a Savings Account

The Excel RATE function can be used to calculate the interest rate on a savings account.

For example, suppose you want to make regular monthly deposits of 300 at the end of each month into a savings account for 5 years, and want to have 22,000 in the account at the end of the five year term. The interest rate the account must earn to achieve this is given by the Excel RATE function as follows:

Nper = 5 x 12 = 60 months Pmt = -300 PV = 0 (not used) FV = 22,000 Type = 0 (payment at the end of each month) Guess = (not used) Interest rate = RATE(Nper, Pmt, PV, FV, Type, Guess) Interest rate = RATE(60,-300,0, 22000,0) Interest rate = 0.660% per month

The account must pay 0.660% per month (nominal annual rate of 0.660% x 12 = 7.920%) in order for the monthly deposits to grow to 22,000 after 5 years.

The payments are paid out by the investor to the savings account, they are therefore cash flows out and shown as negative. The 22,000 will be paid from the savings account to the investor so it is shown as a cash flow in (positive).

Again, if the payments are at the beginning of the month the Type argument should be set to 1.

If the savings account already had a balance before the payments started, then the PV argument can be used to include this. For example, if the account started with a balance of 2,000 then the interest rate needed would be given by the Excel RATE functions as RATE(60,-300, -2000, 22000,0) = 0.289%.

It should be noted that as the 2,000 is an amount which needs to be paid out by the investor to the savings account it is a cash flow out and is therefore negative,

## Compound Annual Growth Rate on an Investment

The Excel RATE function can be used to calculate the compound annual growth rate (CAGR) on an investment.

For example, suppose you make an investment of 10,000 and 5 years later receive 48,000 then the compound annual growth rate on the investment is given by the Excel RATE function as follows:

Nper = 5 years Pmt = (not used) PV = -10,000 FV = 48,000 Type = 0 (not used) Guess = (not used) CAGR = RATE(Nper, Pmt, PV, FV, Type, Guess) CAGR rate = RATE(5,0,-10000, 48000,0) CAGR rate = 36.851% per year

The investment has earned the equivalent of 36.851% compound annual growth rate per year over the 5 year term.

## Excel RATE Function and Annuity Rates

An annuity is a series of regular payments at the end of each period. The Excel RATE function can be used to calculate the annuity rate needed to provide regular payments for a given lump sum investment.

If a lump sum of 300,000 is available to purchase an annuity for 14 years, and regular monthly payments of 3,000 are needed, then the annuity rate is given by the Excel RATE function as follows:

Nper = 14 x 12 = 168 months Pmt = 3,000 PV = -300,000 FV = (not used) Type = 0 (payments at the end of a month) Guess = (not used) Annuity rate = RATE(Nper, Pmt, PV, FV, Type, Guess) Annuity rate = RATE(168,3000,-300000, 0,0) Annuity rate = 0.679% per month

The lump sum to purchase the annuity is shown as a cash flow out, and the payment received from the annuity over 14 years are cash flows in and are therefore positive.

An annuity rate of 0.679% per month (nominal annual rate of 0.679% x 12 = 8.153%) is sufficient to produce a regular monthly sum of 3,000 at the end of each month for 14 years, from an initial investment of 300,000.

## Annuity Due Rates

An annuity due is a series of regular payments made at the **start** of each period. The Excel RATE function can be used to calculate annuity due rate by setting the Type argument to 1. In the above example the annuity due rate is given as follows:

Nper = 14 x 12 = 168 months Pmt = 3,000 PV = -300,000 FV = (not used) Type = 1 (payments at the start of a month) Guess = (not used) Annuity due rate = RATE(Nper, Pmt, PV, FV, Type, Guess) Annuity due rate = RATE(168,3000,-300000, 0,1) Annuity due rate =0.689% per month

## Using the Excel RATE Function with Variable Arguments

Excel is at its most useful when the arguments can be varied without having to enter the Excel function each time a calculation is made. To do this a simple spreadsheet can be set up as shown below.

In this example spreadsheet, the variable arguments are entered in cells B4 to B9, and the Excel RATE function is entered at cell B13 as =RATE(B4,B5,B6,B7,B8,B9). By changing any of the variables in B4 to B9, the discount rate can be recalculated without having to enter the Excel RATE function each time.

This Excel function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

## About the Author

Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.