How to use the Interest Rate function in excel to determine the interest on a financial calculation
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.
Occasionally when you have a spreadsheet that will be used for presentation you may want to neaten it up by either showing zeros in Excel or not showing the zero in cells that have a zero in them. We typically … Continue reading
If I understand the query below correctly, the one idea is to type some words in a cell, and then, while holding the ALT key down, click ENTER. Within the cell you will get another line. Hope this helps. Original query Hi, … Continue reading
The remarkable thing about Adrian's presentation is his ability to further change and adapt the focus of the course as issues arose. He was also able to keep the more skilled delegates challenged...– Alexander Proudfoot Consultants