Calculate APR from ARY

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

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



"Ron737" wrote:

> 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
>
>

> 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 :>)
--
Dana DeLouis
Win XP & Office 2003


"Ron737" <rc_watkins@yahoo.com> wrote in message
news:1120219299.895988.26560@g47g2000cwa.googlegroups.com...
> 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
>