How to Calculate Effective Interest Rate and Discount Rate Using Excel

Updated on August 21, 2019
Usemybee profile image

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

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.

Click thumbnail to view full-size
Enter starting data for the calculation of the effective interest rate.Calculate the number of days of transfer credit to a refund.Determine baseline percentage.
Enter starting data for the calculation of the effective interest rate.
Enter starting data for the calculation of the effective interest rate.
Calculate the number of days of transfer credit to a refund.
Calculate the number of days of transfer credit to a refund.
Determine baseline percentage.
Determine baseline percentage.

The effective interest rate comprises:

See results

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.

Click thumbnail to view full-size
Calculate the Discount RateCalculate the Discount ValueAmounts Divided by the Discount RateThe Sum Needed for Comparison of the Transfer
Calculate the Discount Rate
Calculate the Discount Rate
Calculate the Discount Value
Calculate the Discount Value
Amounts Divided by the Discount Rate
Amounts Divided by the Discount Rate
The Sum Needed for Comparison of the Transfer
The Sum Needed for Comparison of the Transfer

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)

Click thumbnail to view full-size
Interest rate is calculated firstly through the monthly annuity of principle plus costs.Interest rate calculated through interest rate of the annuity and the principle (at no cost).
Interest rate is calculated firstly through the monthly annuity of principle plus costs.
Interest rate is calculated firstly through the monthly annuity of principle plus costs.
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 here.

Effective Interest Rate Equation
Effective Interest Rate Equation
Date
Transfer
Costs
Annuity
Days
discount rate
discounted 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
%
 
 
This table shows how to calculate the effective interest rate.

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

    0 of 8192 characters used
    Post Comment
    • Usemybee profile imageAUTHOR

      Jozef 

      6 years ago from Slovenia

      Thanks Aki.

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

    • profile image

      Aki 

      6 years ago

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

      Much easier. :)

      Good text, tnx...

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, toughnickel.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://toughnickel.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)