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

Calculate APR in Excel

Method 1 – Using a Formula to Calculate the APR in Excel

Step 1:

Calculate the “Total Interest” by using the following formula in C9:

=200000*(0.06*3)

In the formula: Total Interest = Loan Amount*(Interest Rate*Yearly time period).

Use APR Formula to Calculate APR in Excel

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:

=((36000+35000)/200000)/3

APR = (Total Interest + Administrative/Other Costs)/Loan Amount/Time Period.

Press Enter to get the annual percentage rate.

Calculate APR in Excel

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:

=PMT(C5/12,C6,(C4+C7),0)

The PMT function calculates the periodic payment over the amount in the given string.

Combine PMT and RATE Functions to Calculate APR

Press Enter to get the “Monthly Payment Amount”.

Choose another cell (C11) and use the formula:

=RATE(C6,C9,(C4-C7),0)

The RATE function returns the calculated interest amount over the loan.

This is the output.

Combine PMT and RATE Functions to Calculate APR

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 .

Utilize NOMINAL Function to Calculate APR in Excel

Steps:

Select a cell (E5) and enter the formula:

=NOMINAL(D5,C5)

Utilize NOMINAL Function to Calculate APR in Excel

Press Enter.
Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Utilize NOMINAL Function to Calculate APR 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

Calculate APR.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!