Using the Standard formula find the Interest Rate.

[all4excel]

I am using the standard formula for Compound Interest
PV : PRESENT VALUE
FV : FUTURE VALUE n YEARS HENCE
r : INTEREST RATE OR DISCOUNT RATE
n : NUMBER OF PERIODS
m : FREQUENCY OF COMPOUNDING


Compound Interest [ C.I.n = PV(1 + r/m)m X n ]

Based on this formula if I needed to find the Interest Rate (r)

Then, how do i derive the formula?
------------------------------------
My own calculations:

Rate [ r = ((FV/P X m) - m)^1/m X n ]...

But I am not sure whether this is the correct equation can someone help me on the above...

I have a real life example where my bank is offering me a rate of 5%p.a for a 1 year ( 365 day duration) and the amount is 10,000.

So ,in reality my Maturity amount appreciates to 10,509...

Now I want the formula which can give me the rate of 5%.p.a as my answer.

On similar grounds I would like to find the Compounding frequency (m)..

m = ?

[all4excel]

By using my own equation:
PV : PRESENT VALUE
FV : FUTURE VALUE n YEARS HENCE
r : INTEREST RATE OR DISCOUNT RATE
n : NUMBER OF PERIODS
m : FREQUENCY OF COMPOUNDING

Compound Interest [ C.I.n = PV(1 + r/m)m X n ] X 100 ( to get the r%)

I have a real life example where my bank is offering me a rate of 5%p.a for a 1 year ( 365 day duration) and the amount is 10,000 and the compounding frequency is 4 ( i.e. Quarterly Compounding)

So ,in reality my Maturity amount appreciates to 10,509...

Im getting the answer as 5.09 which is the effectiv yield, however I want to get the answer as 5% which is the actual rate applied...

[all4excel]

Bump no response....

[sglife]

Have you checked the function of "EFFECT" AND "NOMINAL"?

[all4excel]

I did try those functions but to no avail as I dont have the Analysis Pack Installed and as per the example in the Help file it does not incline to what Im looking for..
Can anyone just solve the mathematical equation for me?

r= ?

[all4excel]

Bump no response....

[shg]

Im getting the answer as 5.09 which is the effectiv yield, however I want to get the answer as 5% which is the actual rate applied...

Add 1, take the 4th root, subtract 1, multiply by 4:

=( (1 + 5.09%) ^ (1/4) - 1) * 4 = 5.00%

[all4excel]

Add 1, take the 4th root, subtract 1, multiply by 4:

=( (1 + 5.09%) ^ (1/4) - 1) * 4 = 5.00%

Dear Shg,

Thank you so much, it worked like a charm.......
Now there's a new problem, as I was not able to solve the equation for finding the actual rate for very long time for ease of calculation, I used to always consider this for a duration of 1 year and therefore it was easy getting the Effective yield for 1 year, however when I tried it with more than a year my calculation of getting the effective yield was falling flat....

[ r =( (1 + (Effective yield%)) ^ (1/4) - 1) * 4 = 5.00% ]

I want to use the effective yield via an actual calculation like the above to get the actual rate in your formula...
-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-X-

Data :

Comp Frequency 4
Opening Date 01-Jan-07
Maturity Date 01-Jan-09
Int % 5.00
Amount 10,000
Maturity Amt 11,045
Interest 1,045
YEARS 2 Years
MONTHS 0 months
DAYS 0 days
Period 731 Days

I am really sorry Shg you indeed have helped me but just a humble request what will be the effective yield for the above data so as to be used in your magical equation....


Now Is there any equation to get the Effective yield for any number of days , months , years unlike whole numbers as 1 ,2 ,etc..

An amount can be invested for even lets say 72 days then what would be the effective yield if I only know the PV,FV and duration in terms of dates or the number of days...

[all4excel]

Bump no response....

[shg]

Sorry, I'm not going to get mired in this.

The APR is the interest rate times the number of compunding intervals. So 1%/mo is 12%/yr.

The APY is the compounding rate raised to the power of the number of compounding intervals per period of interest.

This is a basic math problem, not an Excel issue. There is ample information available on the web.

[all4excel]

Dear Shg,

Thanks anyways you did solve my issue..
I am trying my level best to get the info on the net however, not getting the specific one what Im looking for ..So i thought maybe someone on the forum could extend a helping hand...

I completely agree that this issue is not Excel oriented however I presume as you were the one who gave me a solution,if you knew this as well, this would be of great help to me...

Because of your formula I could figure out that there's indeed a lot of calculation needed to be performed to get the final thing..

Excellent...5