A question from YouTube about the Excel RATE function not appearing to work. The function does work, but since it returns the rate per period instead of the annual rate, the answer provided by the function might appear to be wrong.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1804: It seems like the RATE function does not work.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in from YouTube.
Someone says, hey, I'm trying to use the rate function in Excel it seems like it is not working out right now.
Never use the rate function before today, except for maybe when I wrote about it in excel 2013 in depth and the previous editions of that book.
But, the function I use all the time is the payment function.
So, I'm very familiar with how to do this model here.
So, let me just run through that one because it's similar to the rate function so =PMT.
They want to know the rate and the important thing here is that this is the interest rate per period so we're buying a car 25,995 over 60 months 5.25% interest.
That's an annual interest rate and because this is we're looking for a monthly payment we have to take that rate and divide it by 12 and the pair is 60 that's the number of periods present value that has to be negative because it's money coming out of the bank and I want my payment to be positive.
Finish that formula 493.54.
Okay, so, this is the model that I use over and over and over again and all my power examine our power Excel seminars I get that.
Now, the rate functions those solves something else we know the loan amount, we know the term, we know how much we're paying and we're trying to figure out what the rate is.
So, let's see if we can figure out how that works =RATE (E4, E5, -E3).
The first thing it wants is end pairs so that's up there an E4 comma then it wants the payment.
So, I'll use a positive them out there.
Then it wants the present value and again, I'll make that negative one of those numbers either PMT or PB has to be positive and the other one has to be negative.
So, press ENTER and we're getting zero percent.
So, it seems like the rate is not working at all.
I cycle, you know it should be five and a quarter percent ah, but here's the thing, the rate that we're getting is the rate per period so this is not an annual rate, it is on which means that the answer is going to be 112 of five and a quarter.
So, the answer is there we just aren't showing enough precision in order to get it to work.
Now to get the annual rate you're going to take that rate function and multiply it by the number of periods per year, so in this case I have monthly payments up x 12 =RATE(E4, E5, -E3) *12 if you were doing for example quarterly payments or semi-annual payments you would multiply by 4 or x 2.
Press ENTER 5.25%.
Now we don't need all those decimal places we can get rid of them and we have our answer.
If the payment would change they have a variable payment and you go from 493 up to $527.65 and your new rate Wow is 8.05% it jumped way up.
So, there you go a function that I really haven't used a lot before, but I knew that it had to be similar to the PMT function in that the rate that they're talking about is the rate per period, which always kind of causes problems in calculating loan payments and sure enough, I'm assuming was the reason why the person I YouTube said hey this is not working they forgot to multiply it by the number of periods per year.
Okay, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1804: It seems like the RATE function does not work.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent in from YouTube.
Someone says, hey, I'm trying to use the rate function in Excel it seems like it is not working out right now.
Never use the rate function before today, except for maybe when I wrote about it in excel 2013 in depth and the previous editions of that book.
But, the function I use all the time is the payment function.
So, I'm very familiar with how to do this model here.
So, let me just run through that one because it's similar to the rate function so =PMT.
They want to know the rate and the important thing here is that this is the interest rate per period so we're buying a car 25,995 over 60 months 5.25% interest.
That's an annual interest rate and because this is we're looking for a monthly payment we have to take that rate and divide it by 12 and the pair is 60 that's the number of periods present value that has to be negative because it's money coming out of the bank and I want my payment to be positive.
Finish that formula 493.54.
Okay, so, this is the model that I use over and over and over again and all my power examine our power Excel seminars I get that.
Now, the rate functions those solves something else we know the loan amount, we know the term, we know how much we're paying and we're trying to figure out what the rate is.
So, let's see if we can figure out how that works =RATE (E4, E5, -E3).
The first thing it wants is end pairs so that's up there an E4 comma then it wants the payment.
So, I'll use a positive them out there.
Then it wants the present value and again, I'll make that negative one of those numbers either PMT or PB has to be positive and the other one has to be negative.
So, press ENTER and we're getting zero percent.
So, it seems like the rate is not working at all.
I cycle, you know it should be five and a quarter percent ah, but here's the thing, the rate that we're getting is the rate per period so this is not an annual rate, it is on which means that the answer is going to be 112 of five and a quarter.
So, the answer is there we just aren't showing enough precision in order to get it to work.
Now to get the annual rate you're going to take that rate function and multiply it by the number of periods per year, so in this case I have monthly payments up x 12 =RATE(E4, E5, -E3) *12 if you were doing for example quarterly payments or semi-annual payments you would multiply by 4 or x 2.
Press ENTER 5.25%.
Now we don't need all those decimal places we can get rid of them and we have our answer.
If the payment would change they have a variable payment and you go from 493 up to $527.65 and your new rate Wow is 8.05% it jumped way up.
So, there you go a function that I really haven't used a lot before, but I knew that it had to be similar to the PMT function in that the rate that they're talking about is the rate per period, which always kind of causes problems in calculating loan payments and sure enough, I'm assuming was the reason why the person I YouTube said hey this is not working they forgot to multiply it by the number of periods per year.
Okay, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
