# How To Set Up A Formula In Excel For Percentage Annual Percentage Rate Versus Annual Effective Rate – Can You Differentiate?

You are searching about How To Set Up A Formula In Excel For Percentage, today we will share with you article about How To Set Up A Formula In Excel For Percentage was compiled and edited by our team from many sources on the internet. Hope this article on the topic How To Set Up A Formula In Excel For Percentage is useful to you.

Muc lục nội dung

## Annual Percentage Rate Versus Annual Effective Rate – Can You Differentiate?

When a product provider offers an interest rate, it’s not always immediately clear how much you’ll pay when you withdraw the product – or how much you’ll be paid.

Financial firms like to sell complex products. That way, customers don’t know what they’re buying, don’t understand the potential downside risks, and don’t understand the true costs, many of which are expertly hidden in the fine print.

Let’s go to these 2 terms.

Annual Percentage Rate (APR)

• Also known as nominal rate or simple interest rate per annum
• Does not take into account the effect of intra-year compounding
• A financial institution is quoted when they lend money, thus earning interest from customers.
• The main reason is to give customers the impression that borrowing costs less
• Usually applies to loans, mortgages and credit cards
• The APR is always lower than the quoted AER
• APR to AER conversion math equation: AER = (1+APR/n)^n -1

Annual Effective Rate (AER)

• Also known as effective annual rate (EAR), annual percentage rate of return (APY) per year
• Considers the effect of intra-annual mixing
• Quoted by a financial institution when customers deposit money, thus paying customers interest.
• The main reason is to give customers the impression that the customer’s deposits earn more interest
• Usually applied to savings accounts, term deposits.
• The AER will always be higher than the quoted APR if there are two or more annual compounds. The only time AER = APR is when there is no annual compounding,
• Mathematical equation to convert AER to APR: APR = n[(AER+1)^(1/n) – 1]where n = number of times for intra-annual mixing

APR – Credit Card Annual Interest Rate Actual Cost

Let’s say you’re one of those people who constantly spends more than you earn and frequently misses payments, putting yourself in the third tier interest rate range of 17.5% p.a.

Let’s say you have an outstanding balance of \$10,000, so you figure (I’ve done this before) that even if I don’t pay a cent for the next 12 months, I should pay 117.5% x 10,000 = \$11,750 by month 13. Or you think that every month I will be charged a monthly interest of 17.5% /12 = 1.4583%

Not quite that simple. Confused? We confirm the concept with an example below.

Remember that you will receive your credit card statement each month – your outstanding balance and previously accrued interest will carry over to the next month. This means that the compounding period is monthly!

In other words, as you well know, the first month’s outstanding balance plus interest is \$10,145.83. In the second month, 1.4583% interest is charged on the \$10,145.83 carried forward from the first month. Can you see the mixing effect here?

If you want to pay off the outstanding balance after 12 months, you will pay MORE than the advertised 17.5% interest rate due to compounding because 17.5% is actually the APR!

The actual interest rate you pay is the AER. At 17.5% APR the AER is 18.974%!! See APR to AER conversion equation above. Or in cash \$11,897.40 instead of \$11,750.00

Still don’t believe me? Calculate FV in Excel with the following inputs: nper = 12 months, 0.014583 for rate and PV = -10000. Put zero for pmt. The concept is similar.

So why did the banks quote you 17.5%?

Easy because it’s the smaller number between the two. If you are a bank debtor, the bank gives you the hidden impression that you have to pay less than you actually do. It’s marketing – they’re not actually lying to you, just that it’s not the whole truth. You have nothing to blame but your own ignorance. Different countries have different rules and regulations to deal with past unscrupulous practices related to listing rates; however, there is no better insulator against these machinations than sound financial knowledge. If you know of any banks that offer AER instead of AER for credit card interest please let me know – I’m pretty sure their credit card product wouldn’t sell too well even though they’re telling their customers the truth!

AER – the truth is revealed! Annual interest rate on fixed deposits

Suppose \$30,000 is placed as a fixed deposit for 1 month with an announced interest rate of 3 percent per annum. The principal and interest are transferred to the savings account after the end of the term.

Your earned interest would be \$73.97 at the end of the month

You asked why? If it is 3% p.a., the monthly interest rate based on the principal amount of \$30,000 should be (3/12)% x 30,000 = RM75.

The truth is that the 3% APR is actually the AER, which is your total income based on \$30,000, if and only if your monthly interest earned is added to your original principal and carried over to the next month, for a total of 12 months. repeatedly.

Using the Excel function FV, where nper=12, i=0.00246625 and PV=-30,000, you get FV=30,900.00. You will earn interest of RM300 which is 3% of the principal amount.

In other words, you only earn the quoted 3% per annum if and only if your monthly interest is added to the principal and carried over to subsequent months within 12 months. You will NOT actually get 3% p.a. on your principal if the monthly interest is deposited into your savings account every month for 12 months.

Example, 73.97 x 12 = 887.64. That’s just 2.959% of 30,000!

Now using the AER to APR conversion formula above, you get APR = 2.959%, ​​which is exactly 887.64 over 30,000.

In this scenario, it is in the bank’s best interest to quote you the AER, not the APR. They know that if you are a lender, you are looking for the highest possible interest rate to attract you.

Do you feel cheated? Yes. Dodgy marketing? Double yes. Why can’t they just present the facts as they are? How many people who don’t know finance know about it?

Here is a quote I read somewhere:

Other industries cater to loyal customers. Banks do the opposite; reward new customers with the best deals while ignoring their existing ones, no matter how long you’ve been banking with them.

*AER and EAR are essentially synonymous. EAR is the term used to calculate the overdraft.

## Video about How To Set Up A Formula In Excel For Percentage

You can see more content about How To Set Up A Formula In Excel For Percentage on our youtube channel: Click Here

## Question about How To Set Up A Formula In Excel For Percentage

If you have any questions about How To Set Up A Formula In Excel For Percentage, please let us know, all your questions or suggestions will help us improve in the following articles!

The article How To Set Up A Formula In Excel For Percentage was compiled by me and my team from many sources. If you find the article How To Set Up A Formula In Excel For Percentage helpful to you, please support the team Like or Share!

Rate: 4-5 stars
Ratings: 4719
Views: 32566464

## Search keywords How To Set Up A Formula In Excel For Percentage

How To Set Up A Formula In Excel For Percentage
way How To Set Up A Formula In Excel For Percentage
tutorial How To Set Up A Formula In Excel For Percentage
How To Set Up A Formula In Excel For Percentage free
#Annual #Percentage #Rate #Annual #Effective #Rate #Differentiate

Source: https://ezinearticles.com/?Annual-Percentage-Rate-Versus-Annual-Effective-Rate—Can-You-Differentiate?&id=6527052

Bài viết đã được tạo 2736

## How To Use If Formula In Excel With Multiple Conditions Review of Takeoff Software for Estimating Construction

Bắt đầu nhập từ khoá bên trên và nhấp enter để tìm kiếm. Nhấn ESC để huỷ.

Trở lên trên