How to Calculate APR in Excel
(Easy Examples)

Have multiple borrowing options at your disposal but don’t know which one to choose?

Calculate the APR for each option to know which option optimizes the borrowing cost for you.

APR is a standard measure to express the annual borrowing cost of any lending arrangement. Easy to calculate and a commonly used financial analytic measure 🚀

To understand the concept of APR, the formula that runs behind it, and how to calculate it in Microsoft Excel, get your practice workbook for this tutorial and follow me till the end of this article.

Table of Contents

What is APR and its Formula

APR stands for the Annual Percentage Rate.

This is one equalized yearly rate for your loans and borrowings that expresses what you’re paying against the loan every year.

It gives you an annual borrowing rate for any loan type – this makes the choice between multiple borrowing options easier and more informed 💡

For example, if you want a loan of USD 10,000 and you have three options:

1. Bank A offers it at 10% interest per annum and an initial administrative fee of $500.
2. Bank B offers it at 12% interest per annum and a loan signing cost of $200.
3. Bank C offers it for 15% interest per annum.

Which one’s better? It’s not possible to tell just by looking at these rates and costs. To choose the best borrowing plan, an easy way is to calculate the annual percentage rate for it.

Comparing the annual borrowing rate for each of these loans makes the decision simple – choose the lowest rate.

Formula

You need to add up the total interest and the other expenses you’re paying on a loan and see it as a proportion of the total loaned amount.

To find the annual rate for it, divide this percentage by the number of years for which you’ve sought the loan 📆

Calculating APR in Excel

There are two ways how you can calculate APR in Excel, and in this section, I am going to show you both these methods.

Manual Calculation

The below image explains the terms of a loan arrangement.

It includes an initial cost of $500 and then annual interest payments at the rate of 8% each year for 3 years.

Let’s see what annual percentage rate is paid on this loan.

Step 1) Calculate the total interest paid on this loan over the tenure of this loan.

For this, we will multiply the loan amount by the annual percentage rate to find the yearly interest. Since the loan stands for three years, we will multiply this interest by 3 to find the total interest paid over 3 years.

Step 2) Add the initial cost to the total interest expense.

This means that the total amount you pay on this loan over 3 years is $12,500.

Step 3) See the total expense as a proportion of the loan amount.

That’s 0.25.

Step 4) To see it as a percentage, go to the Home Tab > Number Group > Click on the percentage icon.

The decimal will be converted into a percentage.

Step 5) Divide this percentage by the number of years over which the loan is spread to calculate the annualized percentage of expense.

The APR comes out as 8.33%.

It is easy and simple to calculate APR in Excel.

However, here is an important point to bear in mind 🤔

If you have a loan arrangement that contains only interest expense with no other fees/costs, the annual interest rate will be equivalent to the annual percentage rate.

For explanation, if this loan arrangement didn’t have an initial cost of $500, the APR would have been 8%.

Same as the annual interest rate.

Since the entire difference is caused by the other fees, you can also see the other fees as a proportion of the total amount of the loan.

Step 6) Divide it by the number of years of loan tenure.

This spreads the fees over three years in the form of an annual rate.

Step 7) Add it to the annual interest rate to find the annual percentage rate with the following formula:

Comes down to 8.33% again.

Pro Tip!

There can be many costs associated with a lending arrangement – honestly, this depends on the type of loan or credit. Out of all such costs, generally, APR includes the following:

• Interest cost (the markup paid on loans)
• Origination Fees (Upfront fees charged by lenders for processing a loan application)
• Points (In mortgage lending, points represent the fees paid to the lender at closing in exchange for a lower interest rate)
• Broker Fees (fees paid to a mortgage broker to find a loan or seal a loan deal for you)
• Other Closing Costs (other closing costs that are to be included in APR calculation include appraisal fees, title insurance, and any additional fees)
• discounts or Rebates (In addition to fees, lenders might also offer you discounts and rebates, these are also to be encountered in APR calculation as they reduce your borrowing cost)

Bottom Line – there cannot be an exhaustive list of costs that you must or must not include in the APR calculation. However, reviewing the Truth in Lending Act (TILA) disclosure provided by lenders will help you know the specific costs associated with your lending contact.

This disclosure outlines all the costs associated with borrowing. Based on this, you can then calculate the APR for your loans making sure you’re not missing out on anything.

Using the PMT and RATE function

Another way to calculate APR in Excel is by using the PMT and RATE functions.

Let’s go with the same loan arrangement as above 📝

We know the loan bears an 8% interest and is to be paid back through 3 equal installments, but what is the annual loan repayment to be made against this loan?

Step 1) Use the PMT function to find the annual equal loan repayment to be made against this loan.

The PMT function calculated the annual loan repayment to be made against this loan arrangement of $50,000 at 8% annual interest rate for 3 years as:

This means, the borrower will pay $19,402 each year to pay off this loan. This payment includes the repayment of the principal amount of the loan plus the 8% periodic interest rate.

Step 2) Find the APR for this loan arrangement using the RATE agreement as follows:

We have specified the nper (number of payment periods) as 3, and the annual payments as $19,402.

For the present value of the loan, we have specified $50,000 less the initial cost of $500.

Pro Tip!

To get a loan of $50,000, we had to bear an initial cost of $500.

On net, we received only $49,500 (the net cash inflow) 💰

The payments of $19,402 that we will make each year for 3 years will be against $49,500 and not against $50,000. This, accordingly means that we are paying a higher cost than the mere 8% interest cost.

Based on these inputs, Excel computes the annual rate on this loan as below.

It computes the APR as 8.56%.

Pro Tip!

Note that the APR computed by the PMT and RATE functions will always be higher than the one computed by the manual APR formula.

Although this difference will not be very huge, it will always be there.

This is because the manual APR calculation ignores the impact of compounding and repayments. While we calculate the total interest expense on the loan, we take it as $50,000 8% * 3 years.

However, you’ll not be paying 8% on $50,000 for all three years. After 1st year, $19,402 would have been paid (this includes $4000 interest and $15,402 principal repayment).

So, in the next year, you’d pay 8% interest on the outstanding amount of $50,000 – $15,402 = $34,598.

And so on. The RATE function takes this factor into account whereas the manual APR calculation doesn’t 🧐

Moreover, the RATE function accounts for the initial fee of $500 being paid on day 1, whereas the manual APR calculation assumes it is paid uniformly and equally over three years. This is why the APR computed through both methods will be slightly different.

Conclusion

This guide teaches you how to calculate APR in Excel through multiple methods. Without a doubt, APR is a financial analysis metric that helps you evaluate multiple lending options to choose the right one for you 💪

In Excel, you can calculate it by devising a formula manually or by using a combination of Excel’s financial functions. Some of my favorite and most useful financial functions from Excel include the Future Value Function, the Net Present Value (NPV) function, and the Present Value (PV) function.