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


predetermined factory overhead ratewrite off bad debt allowance methodsales ledger templatesum of years digits depreciation calculatordirect materials used formulapresent value annuity factorreconciling control accountsdefine daybookcalculating markupsdepreciation spreadsheetbookkeeping assessment testfob destination prepaidthe accounting for cash discounts and trade discounts arefree printable ledger balance sheetaccruals accounting entriespredetermined factory overhead ratediscount amortization scheduleprepaid insurance asset or liabilitypost closing journal entriesretained earnings debit or credit balancewhat is contra entry explain with exampleannuity function in excelperpetual inventory record examplecash book entry exampledifference between markup and profit marginbookkeeping assessmentwarranty expense journal entryaccounting petty cashcommon size analysis income statementjournal entries for trading securitieswhat is the difference between adjusting entries and correcting entriesrate implicit in the leasejournal entry of provision for doubtful debtsbookkeeping and accounting notesnpv functionfifo examplesmarkup calculationsbookkeeping agreementshipping method fobdefine deferred incomeinterest coverage ratio exampleledger book formatreducing balance loan calculator excel downloadamortization spreadsheet templatehow to calculate activity ratiothe retained earnings statement showstabel pvstandard costing variance formulassalaries expense debit or creditusing excel for bookkeepingimprest petty cash fundincome statement unearned revenuesales ledger control account reconciliationproject completion method of accountingshrinkage inventoryblank ledger templatesmall business bookkeeping templatevertical analysis in accountingtotal direct materials cost variance formuladifference between allocation and apportionmentallowance for doubtful accounts is what type of accounthow to calculate quick ratiobond discount amortizationdebtors and creditors ledgeradjusting entry for accrued interestcomparative income statement formatadjusted and unadjusted trial balanceintangible assets accounting treatmentvertical analysis for balance sheetaccounting quiz questions and answerspercent complete formulafair value adjustment journal entrydouble declining balance depreciation method formulaformula of accumulated depreciationaging of accounts receivableasset turnover ratio calculatorhow to find contribution margin ratioexcel pvcommon size statement value of inventory formulapurchase ledger examplecvp formulaseffective interest amortization calculatorzero based budgeting disadvantagesweighted average cost of inventory formuladelivery term fob