Offering helpful financial and lifestyle advice for everyday Canadians

Justin Prichard wrote this informative piece on APR originally posted on

Annual Percentage Rate (APR)
Overview and How to Calculate APR with Excel

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 when explaining the total cost of borrowing money. 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.

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

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:


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:


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

[end of article]



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Tag Cloud

%d bloggers like this: