How to use the Interest Rate function in excel to determine the interest on a financial calculation.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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.