Interest Rate Function

How to use the Interest Rate function in excel to determine the interest on a financial calculation

• 02:03
• Skill Level: 5
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Interest Rate Function

The RATE function calculates the interest rate generated by an annuity. In this segment you will learn how to activate and use the function.

In this example we have a Capital amount which is paid back over a period of 12 months with a monthly payment of this, and what we want to figure out is what the interest rate charge is.
We can use the RATE function, so what we can do is
• click in the cell
• activate the Function Wizard
• and find the RATE function which would sit in Financial
• say Ok
• it asks us how many Periods are involved we can click on 12 as we know its 12 months
• we click on the Payment and we know this is the payment being made
• PV asks us for the present value you put in the capital amount
• if there was a Future value or a residual value we would put the number in there
• and the Type is asking is the payment at the beginning of the period, if it is put in a 1
• or omit if its at the end of a period or put in a 0,
• we’ll put in a 0
• and when we click Ok
• you get this.

It is important to remember that this interest rate is based on the units in the Number of Periods,
• so in this case we have 12 months, therefore this is a monthly interest rate,
• and to become a annual interest rate you need to multiply the formula by 12.

As a proof hidden here, using the Group and Ungroup feature, is a little amortisation table and what we’ve got
• is here is the opening balance,
• here is the interest charged each month based on the interest rate we’ve calculated
• and the repayment given the closing balance
• and you’ll see that it correctly over the 12 month period goes to 0 based on the interest rate we have calculated.