I've been working on constructing an Excel spreadsheet that will calculate an APR (for loans in the US). In researching which built-in formula to use, I've seen websites refer to the "effective rate," the "internal rate of return," and the APR. Could someone help me understand the difference between these terms?

4 Answers 4


Very quickly, the difference between them depends on which side you are coming from (ie as the lender or borrower).

From this source, I found these quick definitions:

Internal rate of return for a cashflow is the discount rate at which the net present value is zero.

Annual Percentage Rate (APR) is the lender's IRR for a mortgage.

IRR is what a lender would actually make on a loan, and is often applied as a standard, annualized way to compare investment returns.

APR is the rate charged to borrowers wanting to take out a loan. You see this most often on car loans.

The effective rate is what you are actually paying/getting payed. This website explains it's relationship to other rates. It looks like the terms are largely inter-changeable, but usage depends greatly on context.

Hope this helps!


Wikipedia gives effective interest rate as an alternative name for IRR. It lists two differences between APR and effective interest rate.

  1. the effective interest rate generally does not incorporate one-time charges such as front-end fees;
  2. the effective interest rate is (generally) not defined by legal or regulatory authorities (as APR is in many jurisdictions)

There are two further differences between the IRR and APR. One is that IRR is the rate taking compounding into account, while APR does not take compounding into account. The other difference is the focus: APR is generally the input, while IRR is the output. That is, with APR one is given a rate, and uses it to find the payments, while with IRR one is given the payments, and uses them to find what rate corresponds to the payments. Because of this, it's possible for there to be more than one possible IRR, if two different discount rates result in the same set if payments.


APR and IRR are identical calculations to find the annualized time value of paid or earned, respectively. You can use the following to calculate IRR at with cashflows terminating on any given day...

Daily formula to solve for XIRR

In order to calculate the XIRR variable in the equation above, you then use the Newton-Raphson numerical calculation to optimizing between f(xirr) and 'f(xirr) as follows:

Numerical optimization to solve for XIRR above


The term APR is a term defined by law. Because federal and state judges in different areas have issued rulings that include or exclude different things, two identical transactions can have different APRs in different states, districts, or court circuits. While that will not be true for simple consumer loans, it can vary for certain types of collateral such as mortgages.

When it was first used, Congress intended the APR to be identical to the IRR or the effective rate, which are usually synonyms. The problem is that the law is not adequately specific. Several things can cause them to differ. The primary issue, however, is when a bank requires a borrower to do something as a condition of extending credit. The question would be is the cash flow associated with that "something" equivalent to being interest, even if it is not paid to the bank? The second difference can be due to timing.

The internal rate of return is based on the date a cash flow happens. The APR is based on the date of a loan, generally. If a cash flow has to happen prior to the date of the loan, the APR treats it as happening at the closing of the loan or may ignore it entirely.

For example, you may be required to buy automobile insurance prior to being approved for a car loan. The insurance premium would not be considered to be interest as part of the loan. It would be a part of the internal rate of return, however, if you would not have purchased automobile insurance had there been no loan.

This is an odd case because it is really an opportunity cost. If you had wanted the automobile insurance and would have purchased it anyway, then it would not be an element of the calculation of the internal rate of return unless you were including fuel and maintenance and so on in your calculations. That would be performed as an estimate of the true cost to own. However, if you were required to produce a cash flow as a condition of the extension of credit, then it should be included in IRR but not APR. The statute excludes insurance on the collateral.

Another example where IRR would differ from APR is when the interest rate is subject to change. The law and court decisions specify how the APR will be calculated under a variety of terms and conditions. While Internal Rate of Return is a complex discussion if the interest rate floats, especially if there are caps, floors and ceilings, it is improbable that the best method to calculate IRR will happen to match the statutory, regulatory and rulings based approach of APR.

Finally, APR is only valid when the borrower makes all payments exactly in the manner specified in the contract. It may not be possible to make a payment if it falls on a weekend or federal holiday. Likewise, if the borrower fully intends to repay the loan early, though they may not say that to the lender, the the IRR calculation will quite possibly vary from what the APR may be.

I will give two examples for this one.

The first is when some form of up front fee is a part of the loan, such as a documentation fee of $50. If you plan to borrow $1000 for 36 months with a $50 documentation fee, that is equivalent to borrowing $950 but owing $1000 plus interest. That $50 is really an interest charge. It will be reflected in the APR, but if you pay the loan off early, you do not get a rebate of the $50 so the bank's IRR will be higher than had you paid the loan according to its terms.

The second case where it will vary is when the statutory or regulatory method of calculating interest is not equivalent to simple compounding. If you were to borrow money under a rule of 78s loan and pay it off early, your interest rate would be the highest in the first month and the IRR would only fall to the APR on the last payment on the last day. If you pay it off early, the banks IRR would be higher than the APR.

If you have never experienced what are called "precomputed loans", they are loans where the amount of interest owed depends only on the date of the payoff and does not depend on the specific balance at a particular time. The interest is often also earned on Day 1 instead of day 28, 29, 30 or 31.

As an example, imagine you borrowed money under a rule of 78's loan. If the interest totalled $78, then $12 of interest would be earned on day 1. If it were a 30 day month, then $11 of interest would be earned on day 31. That would continue, 12,11,10,9,8,7,6,5,4,3,2,1 on the first day of each month until it was paid off. It doesn't matter how much you pay, as long as you meet the minimum terms of the contract. The interest stops only because the entire balance has been paid in full. If you make double payments, it does not impact the amount of interest due, except to the extent you pay the loan off early.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .