Software

Ask something
Discussion Locked

Excel: determining an interest rate

0 helpful
13 replies

by abcs86

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Known information:
Present Value
Future Value
Number of Periods

What I need to know:
Given the above, what's the interest rate?

13 replies

by chitosunday

=rate(number of periods,future value,- present value)

Note: Cash inflows is positive while cash outflow should be negative). Hence if present value is your investment , then it should be in negative amount.

by chitosunday

Stressjunkie,
Why don't you try my formula using live data so you can see what is the result. You don't need a knowledge in calculus to see it. It's just plain
excel financial function. Of course, you can do it the hard way, using math formula but why re-invent the old fashioned wheel when you have the latest improved wheel on hand.

by brendanh

I need a formula and i can't figure it out.. Im making a rate sheet with premiums.
example: A premium of $616.00 for the first $15,000. If i was to add ($22/1000 excess of $15,000) how could i get it to figure that out for me.

Coverage Single Unit Premium

$200,000 $880.00 $616.00
The first $15,000 ($22/1000 excess of $15,000)

If somone could help me that would be great.

by abcs86

I had tried this one before, but I always get #NUM! which, according to the Help, is the result of Excel stopping at 20 iterations...to me, this happening makes no sense. What's the value (no pun intended) of this function if this is the result?

by stress junkie

I don't have much confidence in answer 1. The poster used commas between terms. I've never seen a comma used as an operator in arithmetic expressions.

I believe that this is a calculus problem.

You might be able to get some help from your local bank customer service person if they're not busy. Otherwise I would consult a business oriented math book.

by stress junkie

Okay. I have to admit that I was ignorant. As the comment in Answer 1 says this is an exercise in Excel. I didn't notice that part of the original question, and I don't know Excel very well.

I apologize to the author of Answer 1.

by misscrf

Look in Excel help for INTRATE it is a worksheet function. I could paste the entire help thing here, but it would be so much easier if you look at it and test out their example.

NOMINAL may also help. I found this by looking up "determine interest rate" in help.

by misscrf

Also, check out the what if anaylsis tools. They may be useful in this scenario where one variable is missing.

by abcs86

Yep, I should have mentioned that I've tried these functions. I've even checked Microsoft and Walkenbach sites, but nothing.

by misscrf

I don't know if you have enough information. I mean for example, if you have the present value of a car at 12,000 and the future value at 15,000 with 36 payments, you can figure that 12,000/36 = 333 1/3 and 15,000/36 = 416 2/3
ok, wait, that doesn't do it, lol.
hmmm.....
3000 is the total amout which would be ....
25% interest, right?
So if we say that the payments are 36 payments, the rate would be.....wouldn't it still be 25% or is that just my ignorance of interest rates?
Let me think here ( this is fun...)
well 25% / 36 months is .699999999 Can you tell me if any of this points to the formulas you are trying to achieve?

Also, have you tried goal seeking? You could tweak it to say that you want to know what the interest rate would have to be for a value with x periods to have a future value that you already have....

I was thinking about that one earlier.

by chitosunday

Num# results when you forgot to put "-" on amount that is your cash outflow. Which is your cash outflow, your investment or your payments for loan. Another cause of num# is when the period is abnormally too large to get a result .

by chitosunday

Another cause of #num is abnormall too large payments which excel cannot produce a result, try putting a guess rate like this:
=RATE(C5,C4,C3,,,0.3)

This discussion is closed to new replies.