How does the interest rate start?

Rate function

Returns a double that indicates the interest rate per period of an annuity.


Rate(ZZR, RMZ, BW [, ZW ] [, F ] [, Estimate ] )

The syntax of the function rate has the following arguments:




Required. Double-Value indicating the total number of payment periods for the annuity. For example, if you make monthly payments on a four-year auto loan, your loan will have a total of 4 * 12 (48) payment periods.


Required. Double-Value that indicates the payment to be made per period. The payments usually include the loan amount and interest and do not change during the term of an annuity.


Required. Double-Value that indicates the present value, i.e. today's value, of a series of future payments or incoming payments. For example, if you take out a loan on a car, the total of that loan to the lender is equal to the present value of the monthly payments you make on the car.


Optional. Variant value that indicates the final value or account balance that should be reached after the last payment. The final value of a loan is e.g. B. 0 euros, as this is its value after the last payment. However, if you want to save 50,000 euros in 18 years for the education of your children, the final value is 50,000 euros. If this argument is missing, 0 is assumed.


Optional. Variant-Value that indicates when payments are due. Use 0 if payments are due at the end of the payment period or use 1 if payments are due at the beginning of the period. If this argument is missing, 0 is assumed.


Optional. Variant-Value that indicates the value you estimate of rate is returned. Becomes Estimate not specified, an estimate of 0.1 (10 percent) is assumed.


An annuity is a series of constant cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings deposit).

For all arguments, amounts of money that are paid out (such as savings) are represented by negative numbers and amounts of money that are received (such as dividends) are represented by positive numbers.

rate is calculated with iteration. The calculation starts with Estimate on and the rateFunction calculates until the result is accurate to 0.00001 percent. if the rate-Function does not find a result within 20 runs, an error occurs. If your estimate for "Estimated" is 10 percent and the rateFunction fails, try a different value for Estimate.

Query example



SELECT FinancialSample. *, (Interest ([TermInYears] * 12; - [MonthlyRePayment]; [LoanAmount]; 0,0,0.1) * 12) AS InterestRate FROM FinancialSample;

Returns all fields from the FinancialSample table and the annual interest rate based on TermInYears, MonthlyRePayment, and LoanAmount, and displays the results in the Interest column.

VBA example

Note: The following examples show the use of this function in a VBA (Visual Basic for Applications) module. For more information on working with VBA, choose Developer reference in the drop-down list next to Search and enter one or more terms in the search field.

In this example the rate-Function used to calculate the interest rate on a loan; given are the total number of payments (TotPmts), the sum of the loan payment (Payment), the current value or repayment of the loan (PVal), the future value of the loan (FVal), a number that indicates whether the payment was made on The beginning or the end of the payment period is due (PayType) and an estimate of the expected interest rate (Guess).