Method of calculating monthly interest rate and annual interest rate by using rate function

First, Rate function syntax

function function: Returns the interest rate for each period of an annuity. The function rate is calculated by iterative method, and may have no solution or multiple solutions.

function syntax: RATE (nper,pmt,pv,fv,type,guess)

Parameter introduction:

NPer is the total investment period (i.e. total payment period for the investment);

PMT for each period payment amount

PV is present value (principal)

FV for future value

Type Specifies whether the payment time for each period is at the beginning or the end of the period (1). 0 is the end of the term).

Ii. Conditions of assumption

If deposited in the Bank 3600 Yuan deposit, and the future monthly deposit of 500 yuan, in advance of 10 years to make the amount of deposit reached 100000 Yuan, calculate its monthly interest rate and annual interest rate.

Condition Analysis:

Investment period (year): 10, corresponding to B2 cell

Amount paid at end of month:-500, corresponding to B3 cell

Cash:-3600, corresponding to B4 cell

Future value: 100000, corresponding to B5 cell

Three, the rate function uses the method

Calculated monthly interest rate: =rate (B2*12,B3,B4,B5) function returns 0.67%

Calculated annual interest rate: =rate (B2*12,B3,B4,B5) *12 function returns 8.09%