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

## 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:

**Bank A**offers it at 10% interest per annum and an initial administrative fee of $500.**Bank B**offers it at 12% interest per annum and a loan signing cost of $200.**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.**

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

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

It computes the APR as 8.56%.

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