How to Calculate the APR in Excel – 3 Methods

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:

Where,

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

The dataset showcases different periods of time and different Effective Rates over the Compounded Time Periods. Calculate the Annual Percentage Rate (APR) using the NOMINAL function:

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

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