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.

**Read More: **How to Calculate Interest Rate on a Loan in Excel

### 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 Calculate Monthly Interest Rate 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?

**Read More: **How to Convert Monthly Interest Rate to Annual in Excel

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

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

## Related Articles

- How to Calculate Interest Rate from EMI in Excel
- How to Find Interest Rate in Future Value Annuity in Excel
- How to Create Effective Interest Method of Amortization in Excel

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