How to Calculate APR in Excel (3 Simple Methods)

While running a business you might need to borrow money from banks and financial institutions to grow your organization. These institutions profit by charging the loan taker a certain percentage of the total loan amount. The annual percentage rate (APR) is the total cost the borrower pays to the Bank over a year. This financial calculation might seem difficult to you but not anymore. With accurate data, you can determine the APR in Excel. Today in this article, I am sharing with you how to calculate APR in Excel.

How to Calculate APR in Excel: 3 Simple Methods

In the following article, I have shared 3 simple and easy steps to calculate the Annual Percentage Rate (APR) in Excel.

Suppose we have a dataset of Loan Amount, Interest Rate, Time Period of payment, and Administrative Cost. Now we are going to utilize these values and calculate APR in our workbook.

1. Use Formula to Calculate APR in Excel

In this method, I have used the basic mathematical formula to calculate APR in Excel. Here, without using any function you can determine the APR outcome easily. Follow the steps below-

Step 1:

First, we calculate the “Total Interest” by applying the following formula.
To apply the formula select a cell (C9) and write the formula down-

=200000*(0.06*3)

Where,

The formula stands for, Total Interest = Loan Amount*(Interest Rate*Yearly time period).

Hence, hit the Enter button to get the total interest amount over the principal amount.

Step 2:

In the same fashion, let’s calculate the APR (Annual Percentage Rate).
Therefore, choose a cell (C11) and put the formula down-

=((36000+35000)/200000)/3

Where,

APR = (Total Interest + Administrative/Other Costs)/Loan Amount/Time Period.

Similarly, press Enter to get the annual percentage rate.

2. Combine PMT and RATE Functions to Calculate APR

Using function in Excel you can determine the APR for various loans. The advantage is that you just have to change the cell value if needed and the output will be changed according to your cell value. With the combination of the PMT and RATE functions, you can calculate APR with a single click. To do so-

Steps:

Above all, you need to determine the “Monthly Payment Amount”.
In order to do that, choose a cell (C9) and apply the formula-

=PMT(C5/12,C6,(C4+C7),0)

Where,

The PMT function is a financial function that calculates the periodic payment over an amount in the given string.

Then, click Enter to get the “Monthly Payment Amount”.

Hence, to reach our final destination choose another cell (C11) and apply the formula-

=RATE(C6,C9,(C4-C7),0)

Where,

The RATE function returns a calculated interest amount over a loan.

Finally, we have successfully calculated the APR value using the PMT and RATE function in Excel.

3. Utilize NOMINAL Function to Calculate APR in Excel

Sometimes you will have the “Effective Rate” in your hands. In that case, you can check the APR value for different time periods.

Suppose we have a dataset of various time periods and different Effective Rates over those different Compounded Time Periods. Now we will calculate the Annual Percentage Rate (APR) using the NOMINAL function in Excel.

The NOMINAL function in Excel determines the nominal interest rate over annual interest rate and compounding periods given in a string.

Steps:

First, select a cell (E5) to apply the formula.
Put the formula down in the selected cell-

=NOMINAL(D5,C5)

Hence, hit the Enter button to continue.
Now, pull the “fill handle” down to fill all the cells.

In conclusion, we have calculated the APR for various time compound. Simple isn’t it?

Things to Remember

While using the RATE function in Excel “#NUM! Error” might pop up. In order to avoid these errors don’t forget to put the minus sign (–) before any amount which is paid out.
Sometimes “#VALUE! Error” may occur while using formulas. This happens if any of the values in the arguments are formatted as text not as numeric values.

Download Practice Workbook

Calculate APR.xlsx

Conclusion

In this article, I have tried to cover all the methods to calculate APR (Annual Percentage Rate) in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.

<< Go Back to How to Calculate Interest Rate in Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!