How to Calculate Effective Interest Rate and Discount Rate Using Excel
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
 Mortgage Calculator With PMI  Taxes,Insurance,Down payment
Mortgage Calculator With PMI,Property Taxes,Property Insurance,Extra Payments,Amortization table,EIR,HOA,Biweekly,Accelerated Biweekly,Accelerated Monthly
The inclusion of tax HOA and insurance in the EIR
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.
 Enter the starting data for the calculation of the effective interest rate.
 Calculate the number of days of transfer credit to a refund.
 Determine the baseline percentage.
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
 Calculate the discount rate.
 Calculate discounted value.
 Divide amounts by the discount rate. If the number of days is zero, the discounted amount is equal to the baseline.
 Add discounted payment (the sum needed for comparison with the transfer).
 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.
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!
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:
 Calculating the monthly annuity of principle plus costs
 Calculating the 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.
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  % 


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!
Mortgage Calculator With Points
 Mortgage Discount Points Calculator  Mortgage Calculator
If you've ever applied for a mortgage, you’ve probably heard the term “discount points”, but you may not be familiar with what they are. Mortgage Discount Points are basically extra fees paid at closing in exchange for a lower 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...