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.

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

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

**Similar Readings**

**How to Set Intervals on Excel Charts (2 Suitable Examples)****How to Remove Last Modified By in Excel (3 Ways)****If a Value Lies Between Two Numbers Then Return Expected Output in Excel****How to Move Up and Down in Excel (5 Easy Methods)**

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

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

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

**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

**Create an Organizational Chart in Excel from a List****How to Add Trailing Zeros in Excel (2 Easy Ways)****Calculate WACC in Excel (with Easy Steps)****How to Make Sankey Diagram in Excel (with Detailed Steps)****Make Trial Balance in Excel (with Easy Steps)****How to Add Signature in Excel (3 Quick Ways)****Make Fishbone Diagram in Excel (with Easy Steps)**