Calculate APR from ARY

[Ron737]

Anyone:

I can find out what an APY is with Excel,with this function:

=POWER((1+(A1/B1)),B1)-1 where A1 is the Rate and B1 is compounding
frequency.

I put this formula into any cell on a spreadsheet (except A1 or B1). In
cell A1, I put the stated annual interest rate - in decimal format.
For example, if the stated annual percentage rate is 10%, I will type
".10" in cell A1. Then, I put the number of times I compound each
year. For example, for daily compounding I would enter "365" (or
360 depending on the institution) in cell B1.

In the example I've used, I will find that the APY is 10.5156%. In
other words, if you get 10% annually with daily compounding, your APY =
10.5156%. If I change the compounding frequency, you will see how the
APY changes. For example, I might show quarterly compounding (4 times
per year) or the unfortunate 1 payment per year (which just results in
a 10% APY).

Now, what I need, is HELP. I need a formula that will convert the APY
into the APR.

So like the above example, I would like to be able to type in the APY
as 10% and have Excel show the APR of 9.6455%. The reason for this
request is a complete spreadsheet already built with quarterly
compounding. I know that a percentage of 9.6455% APR entered will equal
10% APY. I need a formular to show this results for various ARP's and
compounding frequency's

Thank you for your help

 

[Guest]

Ron737

Assume your same set up except A1 = APY and B1 = compunding frequency.

Then to calcualte APR in C1 use

=((A1 + 1)^(1/B1)-1)*B1

For the record the ^ sign means 'to the power of'. It is the shorthand way
of using =POWER( x,y). I prefer ^ as it is neater and makes code more
readable in my opinion.

Any problems, write back...


Regards

Alex

 

[Dana DeLouis]

So like the above example, I would like to be able to type in the APY

as 10% and have Excel show the APR of 9.6455%.

=NOMINAL(10%,4)

You can use...

=EFFECT(10%,360)

to get your 10.5156% example also.

HTH :>)