why do textbooks and excel use AER/12 for monthly rate

KAGY

Don't know if I should post this in maths, business or computer forums, but I'm just wondering why most text books, websites and even excel state that when you're compounding interest to a different period to annual, you divide the annual rate by the number of periods in the year.
e.g. if the AER was 6%, use 6%/12 = 0.5%, however this is obviously too big if you compound over the full year
I worked out the actual rate should be r=(1+AER)^(1/12) -1 = 0.487%

I can see that text books to JC might simplify, Is it just that the % error of 0.16% in this case is negligible to most businesses?

How this came about was that my mortgage spreadsheet and the bank statement have been off by about €15 per year (if I recall correctly )

ray giraffe

"Effective and nominal interest rates allow banks to use the number that looks
most advantageous to the consumer.

When banks are charging interest, they advertise the nominal rate, which is lower and does not reflect how much interest the consumer would owe on the balance after a full year of compounding.

On the other hand, with deposit accounts where banks are paying interest, they generally advertise the effective rate because it is higher than the nominal rate.

Therefore, if you were to borrow money at 8 percent APR and immediately deposit it in an account at 8 percent APY, the deposit account will have less money at the end of the year than you owe on the debt."

Source: www.csun.edu/~ghe59995/docs/Interpreting%20Nominal%20&%20Effective%20Interest%20Rates.pdf

KAGY

I admittedly only skimmed through your source, but that's confirming what I say, they are using r/n for the interest rate when compounding over a different period.
The AER/APR  is what the banks have to display, but as we know interest is normally compounding monthly. APY is just used in the US AFAIK
Let's say the monthly interest rate was 0.5%, therefore compounding over 12 months the AER is (1.005)^12 -1= 6.17% assuming no other charges etc.
If we use what is taught, working backwards we should use 6.17/12 in the FV() formula. 0.514% =/= 0.5 %
If the principle was €1000 that's €1.80 you'd be out after one year!
If we're talking about credit card intrest rates, the different would be even higher.
So am I just using the FV function wrong or for the wrong application?

MathsManiac

You are right.

These terms have technical legal definitions which can vary from country to country, but in Europe, AER and APR must be calculated in accordance with your understanding of it. (It can get more complicated with more complex payment arrangements, but the fundamental idea is that the APR is the rate that makes sum of the present values of all repayments equal to the sum of the present values of all advances, calculated using the 'statutory formula'.)

If a bank is charging 0.5% per month and compounding it monthly, they are not legally allowed to advertise this as an APR of 6%. Furthermore, they are not allowed to advertise any rate other than the APR, according to the Competition and Consumer Protection Commission:

Advertising credit sales

There are strict rules governing how you advertise consumer credit in Ireland. Advertisements must conform to the Consumer Credit Act 1995 and must include:

• The APR (annual percentage rate). The display of any other rate of interest is not allowed. The APR must be as prominent as any other figure shown

https://www.ccpc.ie/business/credit-intermediaries/advertising-credit-facilities-authorising-credit-intermediaries/