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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Easy Methods to Calculate APR in Excel

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.

Calculate APR in Excel


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

Use APR Formula to Calculate APR in Excel

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

Calculate APR in Excel

Read More: How to Calculate Cost of Funds in Excel (with Easy Steps)


Similar Readings


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.

Combine PMT and RATE Functions to Calculate APR

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

Combine PMT and RATE Functions to Calculate APR

Read More: How to Create Money Management Excel Sheet for Trading


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.

Utilize NOMINAL Function to Calculate APR in Excel

Steps:

  • First, select a cell (E5) to apply the formula.
  • Put the formula down in the selected cell-
=NOMINAL(D5,C5)

Utilize NOMINAL Function to Calculate APR in Excel

  • 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?

Utilize NOMINAL Function to Calculate APR in Excel

Read More: How to Multiply Time by Money in Excel (with Easy Steps)


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.

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo