Reversing POWER Formula

S

soroosj

All,

Could someone with greater algebra skills help me with below formula?

Cell F2 contains 0.5461%
Cell G2 contains =POWER(F2+1,12)-1, which equals 6.7542%

I am trying to create a formula that reverses the above calculation. I
would receive cell G2 data from a system (6.7542%)and would need to
calculate in Excel non-annualized data in cell F2 (0.5461).

I remember tackling something like this many, many years ago but can't
quite summon the required math (perhaps a logarithm)?

Thanks in advance for any ideas.

Regards,
Joel
Using Excel 2003
 
R

Ron Rosenfeld

All,

Could someone with greater algebra skills help me with below formula?

Cell F2 contains 0.5461%
Cell G2 contains =POWER(F2+1,12)-1, which equals 6.7542%

I am trying to create a formula that reverses the above calculation. I
would receive cell G2 data from a system (6.7542%)and would need to
calculate in Excel non-annualized data in cell F2 (0.5461).

I remember tackling something like this many, many years ago but can't
quite summon the required math (perhaps a logarithm)?

Thanks in advance for any ideas.

Regards,
Joel
Using Excel 2003
Click to expand...


=POWER(G2+1,1/12)-1

--ron
 
B

Bob Phillips

Hi Joel,

This works for me

=(G2+1)^(1/12)-1

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dana DeLouis

Just to be different...

=NOMINAL(G2,12)/12

Are you trying to calculate effective rate by chance in G2?
HTH :>)
 
B

Bob Phillips

You didn't answer Dana's question. I would be interested to know as well.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dana DeLouis

Hi. What we're trying to say is that your equation may be correct. We just
want to point out that there seems to be a big difference from entering
0.5461% and returning 6.7542%. If you changed your equation slightly to
include the "/12" part...

=POWER(0.5461%+1,12)-1
to this..
=POWER(0.5461%/12+1,12)-1

You would get the same answer as
=EFFECT(0.5461%,12)

which would be 0.5475%.
Again, just thought we would mention it. :>)
 
Top