How To Calculate Interest Rates with Microsoft Excel

interest rate calculation

Calculate Interest Rate

The Rate function works out the rate of interest implicit in a group of loan or investment terms given the number of periods, the payment per period, the present price, the future worth, and, optionally, the type-of-annuity switch, and also optionally, an interest-rate guess.

If you set the type-of-annuity switch to one, Excel presumes payments happen at the start of the period, following the allowance due convention. If you set the pension switch to zero or you omit the discussion, Excel thinks payments happen at the end of the period following the standard allowance convention.

The function uses the following syntax :  RATE ( nper, pmt, pv, fv, type, guess )

As one example, suspect you need to figure out the implicit IR on an automobile lease for a $20,000 auto that needs 5 years of $250-a-month payments ( occurring as an allowance due ) and also a $15,000 balloon payment.

To do that, presuming you would like to begin with a guess of ten percent, you may use the following formula : =RATE ( 5*12,-250,20000,-15000,1 ) The function returns the price .95%, which is a monthly interest rate of just less than one percent. If you annualize this monthly rate by multiplying it by twelve, you get an equivalent yearly IR of 11.41%. As another example, suspect you wish to figure out the implicit interest rate on a $300,000 property mortgage that needs 30 years of $2000-a-month payments ( occurring as a normal allowance ) but ( thankfully ) no balloon payment.

To do that, presuming you need to start with a guess of ten percent, you may use the following formula : =RATE ( 30*12,-2000,300000 ) The function returns the value .59%, which is an once a month rate of interest of a touch more than half a p.c. If you annualize this monthly rate by multiplying it by twelve, you get an equivalent yearly interest rate of 7.0203%.

A last point : Excel solves the RATE function iteratively starting with the guess debate you provide. ( if you do not provide this optional discussion, Excel uses ten percent. ) If Excel can’t solve the RATE discussion within twenty attempts, it returns the NUM! Blunder . You can try a different guess discussion, that might help because you are telling Excel to start its search from a different ( hopefully closer ) kick off point.