Excel RATE Function

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 a 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.

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.

excel rate function v 1.0
Excel RATE Function

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.

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

Excel RATE Function November 6th, 2016Team

You May Also Like


Related pages


supplies and supplies expenseni premium bondsunits of output depreciation calculatorfinding the present value of an annuitycompute effective interest rateworking capital turnover ratio interpretationdepreciation half year conventionaccrual spreadsheet templateamortization of bond discountcalculate gross margin in excelcalculating wipmarkup cost formulaaverage contribution margin formulawarranty revenue recognitionwhat is nbv in accountingprepaid expenses in profit and loss accountsingle column cash book formatreversal of deferred tax liabilitydsi accountinghow to calculate percentage markuphow to keep a ledger for a small businessusing npv in excelsample payroll journal entrycash disbursement journal definitionannuity calculation formulaerrors detected by trial balancecontribution margin income statement examplecash flow statement calculator excelbep analysis formulastandard costing in cost accountinggoodwill accounting treatment examplesuspense payment definitionnet present value formula in exceltemplate for time sheetcredit sales in income statementmeaning of absorption costingtrade receivables debtorsprepare multiple step income statementfixed asset depreciation calculatorfifo and lifo in accountinga post closing trial balance will showhow to calculate the irr in excelexamples of petty cash expensesinvoiced meaningaccrued expenses adjusting entriesannuity payment calculatorthe lifo reserve isdebtor collection period formulalabor rate variance examplelease payment calculator excelavailable for sale securities accounting journal entriesfixed assets turnover ratio interpretationconverting preference sharesfiguring gross profithow to calculate the quick ratiodirect labor variancesinventory worksheet templatesample cash voucher templatemulti step income statementsdeclining balance method depreciation calculatorwiki double entry bookkeepingopposite of deferred revenuepersonal and impersonal accountspetty cash balance sheetassets liability and equityaccounting trial balance worksheetbank reconciliation nsf checkcalculating future value of an annuityclosing entries retained earningsaccrued income journal entrypayroll journal entrieswhat is unit contribution marginfreight prepaid meaninghow to book accrued expensesunclassified balance sheetdouble entry records for depreciationaverage accounting rate of return