Wall Street Prep

Annual Percentage Rate (APR)

Understand the Annual Percentage Rate (APR) Concept

Learn Online Now

Annual Percentage Rate (APR)

In This Article
  • 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)?

Annual Percentage Rate (APR) Definition

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:

  1. A loan with a low stated interest rate might come with substantial fees which cannot be neglected.
  2. 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:

dl
Submitting ...

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.

Globally Recognized Certification Program

Get the Fixed Income Markets Certification (FIMC©)

Wall Street Prep's globally recognized certification program prepares trainees with the skills they need to succeed as a Fixed Income Trader on either the Buy Side or Sell Side.

Enroll Today
Comments
guest
0 Comments
Inline Feedbacks
View all comments
Get the WSP Fixed Income Markets Certification (FIMC©)

Wall Street Prep’s globally recognized certification program prepares trainees with the skills they need to succeed as a Fixed Income Trader on either the Buy Side or Sell Side.

Enroll Today
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.