Start learning with our library of video tutorials taught by experts. Get started
In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
When you evaluate any investment opportunity, most of the time the first thing you want to know is the interest rate. However, there are some investments that are presented to you without that information. What they tell you are the number of periods, the payment each period, the present value, in other words the amount you're borrowing and so on, but they don't tell you the interest rate. However, if you want to find that interest rate using Excel, you can do so using the RATE function. The RATE function has three required arguments and three optional arguments.
I've listed them here in this worksheet. The first required argument is the number of periods and that is simply the number of payments that you're going to have to make. In this case it's monthly. So it's a period of five years multiplied by 12 months per year, and then the payment per period and that is expressed as a negative number because it is an outflow from your account. Next is the present value and that's basically the amount that you're borrowing. In this case it's $45,000. Then we get to the three optional arguments. The first one is the future value which is zero and this is how the accounting format displays a zero.
Type can be one of two values. It can either be zero, in which case you're making your payment at the end of an accounting period, in this case a month, or 1 which means that you're making your payment at the beginning. Both future value and the type, if you don't include them in the function, are assumed to be zero. And then finally you have guess, and this is your guess at the interest rate. If you don't put in a value then Excel uses a value of 10%. So I put in 10%, which is the default just for explanatory purposes.
Okay, so let's go ahead and create the formula. So we'll type =RATE( and then the first argument is the number of periods and that is in cell C3, type a comma, then the payment which is in C4, and again that is a negative number because it's an outflow from your account, comma, and then the present value, the amount you're borrowing, and that is in cell C5, and then we get to the optional arguments which are indicated in the formula tooltip by enclosing them in square brackets.
So we have C5 and then the future value's in C6. We're paying it down to zero. Then we have the type which is when we make our payment and that is going to be in cell C7, and then our guess which in cell C8. So type a right parenthesis, make sure all the arguments are good, and press the Tab key. When we do, we see that the rate is about 1%. If you're ever faced with an investment where you don't know the interest rate but you have this other information, you can use the RATE function to discover it very quickly.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.