1. Money

Annual Percentage Rate (APR)

Overview and How to Calculate APR with Excel

By

Annual Percentage Rate (APR) is a tool for understanding the cost of a loan, whether it's a credit card or a mortgage. Although APR is not perfect, it gives you a nice standard for comparing interest and fees from different lenders. This page covers the basics of APR, and how you can calculate it.

Why Use APR?

Loans can be confusing. Slick lenders can quote a lot of different numbers that mean different things. They might include certain costs that you're likely to pay, or they might conveniently omit those costs in advertisements and brochures. To reduce confusion, the US Government passed the Truth in Lending Act, and one of the requirements in the Act is that lenders quote APR to potential borrowers.

If you just want an APR calculation without all of the math, use our online APR calculator.

What is APR?

APR allows you to evaluate the cost of the loan in terms of a percentage. If your loan has a 10% rate, you’ll pay $10 per $100 you borrow annually. All other things being equal, you simply want the loan with the lowest APR.

APR Limitations

Unfortunately, all other things are not equal. There are a few important things you should know about using APR.

With credit cards, APR tells you what interest rate you pay, but it doesn’t include the effects of compounding – so in reality you probably pay more than the APR. If you only make small payments on your credit card, you’ll start paying interest not only on the money you borrowed, but you’ll also pay interest on the interest that was previously charged to you. This compounding effect can raise your cost of borrowing higher than you might think. Instead of looking at the APR, APY would be a more accurate description of how much you pay.

In addition, APR for credit cards only includes interest costs - it doesn’t account for the other fees you pay to your credit card company, so you have to research and compare those costs separately. Annual fees, balance transfer fees, and other charges can add up, so a card with a slightly higher APR might be better in some cases (depending on how you use your card). In addition, your credit card might have several different APRs, so you pay different rates for different types of transactions.

With mortgage loans, APR is complicated because it does include more than just your interest charges. Any quotes you get might or might not include closing costs that you’ll have to pay or other payments required to get your loan approved (such as private mortgage insurance). Lenders have the ability to choose whether or not certain items are part of the APR calculation, so you have to look closely if you’re comparing loans.

You can’t simply rely on an APR quote to evaluate a loan. You need to look at each and every charge and expense related to your prospective loan in order to judge whether or not you’re getting a good deal. In addition, look at the bigger picture – you need to know how long you’ll be using a loan to make the best decision. For example, one-time charges up front may drive up your actual cost on a loan – even though an APR calculation might assume those charges are spread out over a longer lifetime (and therefore the APR would look lower).

For more discussion, see Get the Wrong Loan by Comparing APR

APR Example With Excel

APR seems really easy, but it’s amazing to watch the numbers (and your costs!) change with different scenarios.

Assume you will borrow $100,000, and the lender tells you you’ve got a 7% interest rate. You also have $1,000 in closing costs. The APR on a 30 year fixed rate mortgage would be 7.10%.

To test this, try the math yourself. In Microsoft Excel, follow these steps:

Find the monthly payment for loan and closing costs:

=PMT(0.07/12,360,100000)

The format is: PMT(rate,nper,pv,fv,type)

  • .07 divided by 12 is the rate (you’re using a monthly rate to find monthly payments)
  • 360 is the number of periods (payments or months – 30 years here)
  • 100,000 is the present value of your loan (including additional costs)
You should have a result of $665.30.

Next, Solve for the APR:

=RATE(360,-665.30,99000)

The format is: RATE(nper,pmt,pv,fv,type,guess)

  • 360 is the number of periods you pay on the loan (360 months or 30 years)
  • - 665.30 is your payment
  • 99,000 is the present value of your loan (how much you’re actually borrowing)
You should have a result of .592%. This is a monthly rate. Multiply by 12 to get 7.0999%.

Why is the loan amount smaller in the third bullet point above? We need to calculate the rate for this step using an decreased loan balance of $99,000 (the $100,000 "loan" minus the $1,000 in fees required to get that loan).

As you compare loans, you might hear additional terms, such as variable APR and 0% APR. To learn more about different types of loans, see What does APR Mean?

Related Video
What Is APR?
What Is Simple Interest?
  1. About.com
  2. Money
  3. Banking / Loans
  4. Loans
  5. Annual Percentage Rate (APR)

©2014 About.com. All rights reserved.