The sample dataset showcases Loan Amount, Interest Rate, Time Period of payment, and Administrative Cost. To calculate the Annual Percentage Rate (APR).
Method 1 – Using a Formula to Calculate the APR in Excel
Step 1:
- Calculate the “Total Interest” by using the following formula in C9:
In the formula: Total Interest = Loan Amount*(Interest Rate*Yearly time period).
- Press Enter to get the total interest amount over the principal amount.
Step 2:
- To calculate the APR (Annual Percentage Rate), choose a cell (C11) and enter the formula:
APR = (Total Interest + Administrative/Other Costs)/Loan Amount/Time Period.
- Press Enter to get the annual percentage rate.
Method 2 – Combining the PMT and the RATE Functions to Calculate the APR
Steps:
- To determine the “Monthly Payment Amount”, choose a cell (C9) and enter the formula:
The PMT function calculates the periodic payment over the amount in the given string.
- Press Enter to get the “Monthly Payment Amount”.
- Choose another cell (C11) and use the formula:
The RATE function returns the calculated interest amount over the loan.
This is the output.
Read More: How to Calculate Monthly Interest Rate in Excel
Method 3 – Utilizing the NOMINAL Function to Calculate the APR in Excel
If you have the “Effective Rate”, you can check the APR value for different periods of time .
Steps:
- Select a cell (E5) and enter the formula:
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
Read More: How to Convert Monthly Interest Rate to Annual in Excel
Things to Remember
- To avoid the “#NUM! Error” while using the RATE function, enter a minus sign (–) before any paid amount.
- The “#VALUE! Error” may occur if any of the values in the arguments are formatted as text.
Download Practice Workbook
Related Articles
- How to Calculate Interest Rate from EMI 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
Get FREE Advanced Excel Exercises with Solutions!