Login
Self-Study Courses
Virtual Boot Camps
1:1 Coaching
Corporate Training
On-Campus Training
Free Content
Support/Contact Us
About Wall Street Prep
What is the Annual Percentage Rate (APR)?
Annual Percentage Rate (APR) is defined as the interest rate charged by a lender on a yearly basis, expressed in the form of a percentage.
- What is the definition of annual percentage rate (APR)?
- What is the formula used to calculate the annual percentage rate (APR)?
- What are the steps to calculate the APR on a mortgage loan in Excel?
- How is the APR different from the annual percentage yield (APY)?
Table of Contents
How to Calculate Annual Percentage Rate (APR)
APR represents the estimated cost of the yearly fees associated with a specific type of borrowing.
The annual percentage rate, or “APR”, represents the interest rate paid each year on an outstanding loan amount.
APR is a standard calculation used by lenders designed to help borrowers compare different loan options.
The stated interest rate on a loan is not usually enough on its own to make the right borrowing decision – for instance:
- A loan with a low stated interest rate might come with substantial fees which cannot be neglected.
- A loan with a higher stated interest rate yet lower fee structure could be the preferable option.
APR is a relevant concept for numerous financing scenarios, most notably:
- Consumer and Commercial Loans
- Line of Credit (LOC)
- Mortgages
- Credit Cards
- Auto Loans
Depending on the specific circumstances surrounding the financing, additional fees could include:
- Underwriting Fees
- Origination Fees
- Processing Fees
- Appraisal Fees
APR on Loans Explained
The APR on a loan – a mortgage, for example – marks the total yearly cost associated with borrowing money from a financial institution.
Since more fees beyond just interest expenses are considered in the APR of a loan, the metric provides a more accurate estimation of how much in total that a borrower must pay to take out a loan.
The APR on loans facilitate comparisons across different loan offerings (i.e. for the borrower to pick the cheapest option), yet in actuality, the comparison is not “apples-to-apples” due to several factors:
- Oftentimes, loans tranches can be “taken out” (i.e. repaid in full earlier than scheduled) or refinanced before the date of maturity.
- Standardizing the fees charged by the lender is practically impossible (i.e. different types per financing arrangement).
- Contingencies can be influential factors such as prepayment penalties, conditional fees, and incentive programs.
Credit Card APR
Under the context of credit cards, the APR determines the amount of interest due based on the carrying balance from month to month.
If each monthly bill is paid in full and on time, no interest will be incurred.
Unique to credit cards, interest is calculated daily, meaning that a credit card company charges borrowers by multiplying the ending balance by the APR and then dividing by 365.
The amount of interest charged is subsequently added to the outstanding balance the following day.
In contrast to credit cards, the APR on a loan reflects more than just the interest payments that must be met.
Annual Percentage Rate (APR) Formula
The APR is calculated using the following formula.
APR Formula
- APR = (Periodic Interest Rate * 365 Days) * 100
Where:
- Periodic Interest Rate = [(Interest Expense + Total Fees) / Loan Principal] / Number of Days in Loan Term
To express the APR as a percentage, the amount must be multiplied by 100.
Mortgage Loan APR Calculation Example
Now that we’ve defined the APR concept, we can move on to calculating the APR on a loan in Excel.
To access the APR spreadsheet, fill out the form below:
In our illustrative scenario, let’s say that you’ve taken out a mortgage loan with the following lending terms:
- Mortgage Amount: $200,000
- Lending Term: 30 Years, or 360 Months
- Interest Rate (Annual): 5%
Remember, APR does not just factor in the interest expense, but related fees, too.
- Origination Fee: $1,000
Monthly Payment Excel Formula
Using the “PMT” function in Excel, we can calculate the monthly payment amount.
- Monthly Payment: PMT (Interest Expense / 12, Borrowing Term in Months, Loan Principal)
If we plug in our numbers, we get the following:
- Monthly Payment = PMT ($10,000 / 12, 360, $200,000)
- Monthly Payment = $1,074
APR Excel Formula
The “RATE” Excel function can then be utilized to arrive at our mortgage’s annual percentage rate (APR).
- APR = RATE (Borrowing Term in Months, Monthly Payment, (Loan Principal – Origination Fee)) * 12
Since we already have all the required inputs, the only remaining step is to plug them into the Excel formula.
- APR = RATE (360, $1,074, ($200,000 – $1,000)) * 12
- APR = 5.044%
The APR on the mortgage loan – as shown in the screenshot of the model below – is calculated at approximately 5.0%.
APR vs. Annual Percentage Yield (APY)
One common mistake is to confuse the annual percentage rate (APR) with the annual percentage yield (APY).
To distinguish between the two, APR is interest that you pay on a loan whereas APY is the interest you would expect to earn on an investment.
- APR ➝ Interest Owed
- APY ➝ Interest Earned
APR is also an annualized simple interest rate while the APY calculation considers the effects of compounding.
As a general rule, the higher the interest rate and the fewer compounding periods there are, the greater the discrepancy between APR and APY.
Fixed APR vs Variable APR
The final difference we’ll explain is between a fixed APR and a variable APR:
- Fixed APR: APR remains unchanged throughout the borrowing period.
- Variable APR: APR fluctuates due to being tied to a prime rate.
A fixed APR is thus more predictable than a variable APR, which is a function of the market conditions and the specific benchmark by which its value is influenced.
