How can I get the monthly rate from a compounded annual interest rate?
--
David B

David B wrote:
How can I get the monthly rate from a compounded
annual interest rate?

I assume you mean "the monthly compounded rate from
the annual percentage rate (APR)".

Some people will say: simply divide APR by 12.

But if the compounding frequency is monthly, the
correct monthly rate is =RATE(12,,-1,1.045) for an
APR of 4.5%, for example (1.045 = 1 + APR).

However, even when the rate is specified in monthly
or annual terms, it is often compounded daily. In
that case, the monthly rate will vary depending on
the number of days in the month. A "typical" monthly
rate is =FV(RATE(365,,-1,1.045),30,,-1)-1.

Thanks, your reply was very helpful. I have an example where the annual
compounded rate is 7.3% and this is stated as a monthly rate of 0.592% and I
would like to be able to calculate that. Using your formula gives 0.589%. I
see your point about about daily interest charging, is there any way I can
fine tune the formula to give the 0.592% result?
--
David B


" wrote:

David B wrote:
How can I get the monthly rate from a compounded
annual interest rate?

I assume you mean "the monthly compounded rate from
the annual percentage rate (APR)".

Some people will say: simply divide APR by 12.

But if the compounding frequency is monthly, the
correct monthly rate is =RATE(12,,-1,1.045) for an
APR of 4.5%, for example (1.045 = 1 + APR).

However, even when the rate is specified in monthly
or annual terms, it is often compounded daily. In
that case, the monthly rate will vary depending on
the number of days in the month. A "typical" monthly
rate is =FV(RATE(365,,-1,1.045),30,,-1)-1.

David B wrote:
I have an example where the annual compounded rate
is 7.3% and this is stated as a monthly rate of 0.592%
and I would like to be able to calculate that. Using
your formula gives 0.589%. I see your point about
about daily interest charging, is there any way I can
fine tune the formula to give the 0.592% result?

First, I hasten to note that it is very rare when I can
reverse-engineer the computation and get __exactly__ the
same result as a financial institution. Who knows why!
Perhaps due to "exact" computation based on iteration
and intermediaterouding to their units -- pennies,
"centipennies", etc -- compared to exponential and log
functions that might be used in the implementation of
Excel's present value functions (just a guess).

Second, my gutt reaction was: a difference of 0.003%
is "close enough for government work". But I must
admit that it can make a difference of about $8941 on
an investment of $100,000 over 30 years. Then again,
that difference is less than 1.1%. A 98.9% solution
is usually good enough for most people.

Nonetheless, I can understand your curiosity. I look
for explanations of differences of pennies, just to be
sure I truly understand the process.

At this point, I can only guess ....

I might have misled you by asserting that the APR is
7.3%. Perhaps 7.3% is the nominal rate, and the APR
is actually 7.34%.

The estute reader will note that that is not what the
EFFECT() function would return. The answer might be
that the OP is talking about a loan or something similar,
and the APR includes other fees. For an explanation,
do a google search on "how is apr calculated" (without
the quotations). For example, see
http://www.realestateabc.com/insights/apr.htm .

HTH.

Thanks for your interest and information which is appreciated.
--
David B


" wrote:

David B wrote:
I have an example where the annual compounded rate
is 7.3% and this is stated as a monthly rate of 0.592%
and I would like to be able to calculate that. Using
your formula gives 0.589%. I see your point about
about daily interest charging, is there any way I can
fine tune the formula to give the 0.592% result?

First, I hasten to note that it is very rare when I can
reverse-engineer the computation and get __exactly__ the
same result as a financial institution. Who knows why!
Perhaps due to "exact" computation based on iteration
and intermediaterouding to their units -- pennies,
"centipennies", etc -- compared to exponential and log
functions that might be used in the implementation of
Excel's present value functions (just a guess).

Second, my gutt reaction was: a difference of 0.003%
is "close enough for government work". But I must
admit that it can make a difference of about $8941 on
an investment of $100,000 over 30 years. Then again,
that difference is less than 1.1%. A 98.9% solution
is usually good enough for most people.

Nonetheless, I can understand your curiosity. I look
for explanations of differences of pennies, just to be
sure I truly understand the process.

At this point, I can only guess ....

I might have misled you by asserting that the APR is
7.3%. Perhaps 7.3% is the nominal rate, and the APR
is actually 7.34%.

The estute reader will note that that is not what the
EFFECT() function would return. The answer might be
that the OP is talking about a loan or something similar,
and the APR includes other fees. For an explanation,
do a google search on "how is apr calculated" (without
the quotations). For example, see
http://www.realestateabc.com/insights/apr.htm .

HTH.