Updated date:

How to Calculate Effective Interest Rate and Discount Rate Using Excel

Author:

Jozef is a lover of all things finance. He also supports efforts to save bees from extinction.

What Is Effective Interest Rate?

A solid understanding of financial terms can help you make thoughtful decisions when managing your money. Without financial literacy, it is easy to accept the wrong retirement plan, pay high loan or mortgage payments, or even accumulate debt.

One of these important terms is "effective interest rate."

Effective Interest

Effective interest is the interest paid on an investment that compounds over some amount of time. This means the effective interest rate is the interest plus the cost of changing monetary value over time.

What's the Effective Interest Rate For?

The effective interest rate is a way of calculating what the real cost of borrowing money from a bank or other financial institution will be. It's the easiest way to compare financial products at their "true cost."

Finding the Effective Interest Rate

This sounds complicated, but fortunately, Microsoft Excel makes difficult tasks easy. Here I show you how to calculate the effective interest rate and the discount rate.

In order to calculate the effective interest rate:

• In Excel, you use the function EFFECT

• Use the formula: = ((1 + nominal interest rate/100/ number of billings) ^ (number of billings) -1) * 100

This option is available in Mortgage Calculator With PMI

The inclusion of tax HOA and insurance in the EIR

annuity-calculator-calculatethe-effective-interest-rate

Useful Finance Terms

I use a few general terms in my explanation. It's useful to have at least a minor understanding of these terms when reading this article.

  • Cost is the price of a given thing.
  • An annuity is the fixed sum of money paid to someone each year.
  • A Refund is the repayment of a sum of money.

Find the Effective Interest Rate Using Excel

In Excel, enter the date and amount of the receipt of the loan, the date and amount of the payment of any charges, and the dates and amounts of all refunds. The following steps are necessary for calculating the effective interest rate using Excel.

  1. Enter the starting data for the calculation of the effective interest rate.
  2. Calculate the number of days of transfer credit to a refund.
  3. Determine the baseline percentage.
annuity-calculator-calculatethe-effective-interest-rate
annuity-calculator-calculatethe-effective-interest-rate

Calculate the Discount Rate

The formula for calculating the discount rate is: ((1 + IR/100) ^ (Days / YearDays)).

IR: Interest rate
Days: Number of days
YearDays: The number of days in a year

Calculate the Discount Rate

  1. Calculate the discount rate.
  2. Calculate discounted value.
  3. Divide amounts by the discount rate. If the number of days is zero, the discounted amount is equal to the baseline.
  4. Add discounted payment (the sum needed for comparison with the transfer).
  5. Calculate the difference between the transfer and the sum repayments. The difference tells us what is the real effective interest rate.

Calculate discounted value

Amounts divided by the discount rate. If the number of days zero, the discounted amount equal to baseline.

annuity-calculator-calculatethe-effective-interest-rate
annuity-calculator-calculatethe-effective-interest-rate
annuity-calculator-calculatethe-effective-interest-rate

Find the Effective Interest Rate

Now everything is ready to find the effective interest rate. We are looking for a percentage, which is the difference close to zero.

Example: 8, 14, 11, 12.5, 11.75, 12.125, 12.3125, 12.219, 12.266...12.239.

The smaller the difference, the more accurate the calculation we have.

That's all!

The Effective Interest Rate

The Effective Interest Rate

Calculate APR (Annual Percentage Rate) Using Excel

The annual percentage rate (or APR) is a term often used synonymously with the effective interest rate, but there is one major difference: APR is calculated using simple interest, whereas effective interest rate includes compound interest.

The Calculation of APR Is Carried Out By:

  1. Calculating the monthly annuity of principle plus costs
  2. Calculating the interest rate of the annuity and the principle (at no cost)
Interest rate calculated through interest rate of the annuity and the principle (at no cost).

Interest rate calculated through interest rate of the annuity and the principle (at no cost).

Calculating the Effective Interest Rate With a Calculator

There are many differences between credit, loan, and mortgage opportunities. Not all of them have the same terms, so it is hard to compare them without doing a little math. An effective interest rate calculator can help you compare loans side by side.

Find Out the Real Cost of Borrowing Money

All information you need for calculation of effective interest rate has to be given by the lender of credit, loan, or mortgage and written in a contract or other documents.

  • The compounding period means the length of time from one payment of the loan or mortgage to the next. It can be monthly, quarterly, or annually.
  • The nominal rate is also called stated interest rate and it is expressed as a percentage per year.

After you have all the necessary information, simply type them into an effective interest rate calculator that can be found online. The lowest interest rate means that a credit, loan or mortgage has lower costs than other financial products, so it is the best and the most inexpensive option for you.

Calculator Soup offers an effective interest rate calculator.

Effective Interest Rate Equation

Effective Interest Rate Equation

DateTransferCostsAnnuityDaysdiscount ratediscounted value

4.4.2013

2.000.00

 

 

 

 

 

4.4.2013

 

15.00

 

0

 

15.00

4.5.2013

 

 

342.14

30

1.009535063

338.91

4.6.2013

 

 

342.14

61

1.019483485

335.60

4.7.2013

 

 

342.14

91

1.029204324

332.43

4.8.2013

 

 

342.14

122

1.039346576

329.19

4.9.2013

 

 

342.14

153

1.049588774

325.98

4.10.2013

 

 

342.14

183

1.059596669

322.90

 

 

 

 

 

 

2000.00

 

 

 

 

 

 

-0.000692

 

 

 

 

 

 

 

effective interest rate

 

 

12.2390

%

 

 

How Much Can I Borrow and How Much Would That Cost Me?

Effective interest rate is a rate that can give you a whole picture of different financial options you have. It is best to find a lender that offers a very low effective interest rate because that means smaller costs for you. The effective interest rate tells you how much you have to pay for your loan and you can use this information for comparing different loans.

Every bank offers a variety of options for borrowing money, but some of them are better than others. You can find out the most suitable one with the help of the effective interest rate!

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

Comments

Jozef (author) from Slovenia on June 21, 2013:

Thanks Aki.

I really appreciate you taking the time to share your insights and feedback.

Aki on June 21, 2013:

You can find EIR at the end by using option "Goal seek" in data tab...

Much easier. :)

Good text, tnx...

Related Articles