Calculating Interest Rates with Excel

Here’s an article I wrote for Microfinance Transparency in February 24, 2010:

Chuck Waterfield and Alexandra Fiorillo, MFTransparency’s CEO and VP respectively, have been doing many presentations about how interest rates can be calculated using our excel tool, but we haven’t yet featured a story on our blog about our data collection process and our corresponding excel tool. Although technical, interest rate calculations are really at the heart of MFTransparency’s mission and calculating accurate interest rates is vital to providing transparent pricing data. So today, I would like to give you a brief demonstration of the IRR and XIRR Excel functions, as a way to provide background for the techniques we’ve used to automate interest rate calculations on our website.

For those of you less familiar with excel, this spreadsheet software offers numerous formulas allowing quick and easy calculations within each spreadsheet. As it is particularly geared towards financial use, there are ready-made formulas specifically meant for calculating interest rates. The most basic (but still powerful) calculation is the internal rate of return.

IRR() : Internal Rate of Return

The internal rate of return formula is capable of taking a cash flow and returning the per-period interest rate. It assumes equal lengths of time between each amount in the cash flow. Let’s first start with a sample spreadsheet of loan payments, and use the IRR function to calculate the interest rate.

Screenshot: Excel IRR function

You can see that by applying the IRR formula, we get an “Internal Rate of Return” for the loan. This IRR can then be multiplied by the number of periods in a year to get the APR. Annual Percentage Rate is the standardized format most commonly used in the United States.

  • APR = IRR * n, where n is the number of payments per year.
  • 24.09% = 0.0200757 * 12

The EIR takes into account the effect of compound interest and can be calculated using the formula. This is the standardized interest rate often reported in European countries:

  • EIR = ((1+IRR)^n) -1)
  • 26.94% = ((1+ 0.0200757)^12)-1)

The IRR function is sufficient when there are equal (or near equal) periods between repayments, but what about when repayments occur irregularly? Prior to Excel 2007, there was no easy solution… but thankfully the wizards at microsoft have now provided us with a solution:

XIIR(): accounts for actual payment dates

As mentioned above, the XIRR function is useful for loans with irregular repayment schedules, and is only available in newer versions of Excel (2007) and in recent versions of Open Office.

Excel 2007 XIIR function

Notice that the XIRR function takes into account the payment dates in addition to the payment amounts. It actually provides us with the EIR (so annualized interest rate WITH compounding) for the cash flow in question. We won’t get into the math behind this, but suffice it to say that this formula is powerful, and a significant step up in allowing accurate calculations of interest rates. If you download the sample spreadsheet and play with the numbers, you can see the effect that an early first payment and a short month (February) have on the XIIR result.

  • better EIR = Excel XIIR()
  • XIRR() = 32.16%

So XIRR is a more accurate way to calculate the interest rate because it takes into account both actual payment dates and the effect of compound interest.

These tools are what allow MFTransparency to calculate accurate interest rates that are comparable between MFIs, despite different/irregular repayment schedules, additional fees, etc. So while they may seem mundane, they are actually the crux of transparent pricing!

In a future post, I’ll explain how software programmers can efficiently add advanced Excel 2007-like XIRR calculations to their software programs.

Further Information

For those of you who would like to know more about interest rate calculation, I encourage you to check out the following:

The next post in this series, “Calculating Interest Rates Using Newton’s Method” is a more advanced version of this article that explains the algorithm behind the XIIR formula, and how this technique can be applied in programming languages like C#, python, or Java.

P.S. For investors, Excel’s XIRR feature can also be used to calculate a Personal Investment Rate of Return, which is more relevant than the values that appear in a fund’s prospectus because it takes into account the investor’s actual purchase history.

Calculating interest rates using Newton’s method

Here’s a post I wrote for Microfinance Transparency on March 5, 2010

In a previous blog post, I described how to use a spreadsheet like Microsoft Excel to calculate interest rates. Again, interest rate calculations are at the core of MFTransparency’s ability to provide accurate data that can be compared across various products offered by numerous MFIs. In the last post we looked at Excel’s IRR and XIRR functions and concluded that XIRR is more accurate because it takes into account the actual payment dates of the loan and thus allows us to calculate annualized interest rates even with irregular repayment schedules.

But for the more technical among us, I realize that even this may not be sufficient. Today I’m going to demonstrate how to write a computer program that is as accurate as Excel 2007’s XIRR function. This article is likely to be of less broad interest, but it provides transparency into how we will calculate interest rates for future data collection trips; and it may be useful for MFIs that wish to automate interest rate calculations for a larger data set than can be handled with Excel.

Let’s start with the EIR formula and describe two techniques.

  • EIR = cf*(1+rate)^n

cf = cashflow, n=number of periods/year

Simple Guess and Check

The first technique is to make a guess about the interest rate and then run the numbers through the EIR formula to see how close you are. You then iterate, guessing somewhere in the middle of your previous guesses, or widening your area by doubling. The advantage of this technique is that it is simple and it gets you the right answer eventually (or at least fairly accurately given enough guesses).

Newton’s method

A more advanced way to solve the EIR formula is to use Calculus. It’s still a “guessing” technique, but it is much more efficient and elegant.

It’s easier to visualize this technique if we draw a graph and plot an initial guess, with the goal of finding the point on the graph where it crosses the x axis.

We start by making an initial guess and then figuring out what the “tangent” line at that point would be. This is the same thing as the derivative of the EIR calculation:

  • EIR = cf*(1+rate)^n
  • f’rate = cf*n*(rate+1)^(n-1) = pink tangent line is the derivative

cf = cashflow, n=number of periods/year

We can then figure out where the tangent line intersects the x axis and use that to make a much more accurate second guess.

The speed advantage the Newton-Raphson method has over a simple guess and check technique is quite remarkable. It is common to be able to achieve a result that matches Excel to within 8 decimal places in 5-10 iterations.

Implementing Newton-Raphson

Fortunately for me, I didn’t have to implement the entire Newton-Raphson algorithm myself because the programming language I use already has a library to do this. I just give it the EIR function and the derivative function and it does the rest.

For the programmers out there, here are a few links to implementations in various languages:

  • C#: uses bisection rather than Newton’s method
  • Java
  • Python

Related Information:

For those of you who would like to know more, please explore the following links:

Next Article:

Calculating Effective Interest Rates Using Cashflow Discounting

Here’s a post I wrote for Microfinance Transparency on October 26, 2010:

(Note: not all formatting carried over from the MFT Blog)

In a previous post, I described the technique that computer programs like Microsoft Excel use to calculate the XIRR (effective interest rate) as a very smart version of “guess and check.” The post on Newton’s Method described how the “guessing” part works, but it did not describe how the computer is able to finally verify when it has the correct EIR figure — the “check” part.

In today’s post, I’m going to describe the process that a computer program uses to generate a discounted cashflow, a method of calculating the value of a cashflow that uses the time-value of money. By adding up the discounted cashflows we are able to determine whether we have the correct EIR.

I’m going to start with a sample loan of $4,825.00 that was disbursed on the 28th of the month but is paid back on the 16th of the month every month for about a year. The fact that the disbursement date is not exactly one month prior to the first repayment means that a simple IRR formula can not be used for accurate results, and the calculation must take all the actual dates into account. The exact details are at right.

How to Check an EIR

The first step in the process is to come up with a guess. For my example, I’ll start with a guess of 30 percent. The next step is then to take that 30% rate and plug it into the discounting formula for each date of the loan:

Discounted cashflow = cashflow * (rate +1)^(-days/365)

This gives us the discounted value of the cashflow for each date period. We then add up all the discounted cashflows to produce a total. This total should be 0 if the rate is correct, indicating that discounted cashflow is equivalent to the nominal cashflow at the specified rate. If it is not 0, we guess again, until we get closer and closer to 0. (For our purposes we decided that 8 decimal places of accuracy is good enough!) Below, you will see that Newton’s method is able to guess the correct EIR in only 5 guesses! [Read more…]

The amazing woman responsible for the Pfizer and Moderna Vaccines

Here’s the amazing story of grit and perseverance about the woman who did the pioneering work for the Pfizer and Moderna vaccine.
Katalin Kariko light corrected.jpeg
Katalin Karikó, born in Hungary
Temple University & University of Pennsylvania

Timeline: Development of Vaccine by Katalin Karikó:

(from Wired Magazine article)

  1. 1955: Katalin Karikó was born in Hungary
  2. 1976: She hears about ideas of using mRNA to target viruses while an undergrad at the University of Szegedin Hungary.
  3. She completes her Ph.D.
  4. 1985: As an immigrant from Hungary, Katalin Karikó immigrated to the US to do research at Temple University.
  5. After a dispute with her boss, Temple University tried to have her deported.
  6. She switched to the University of Pennsylvania, but her research was not considered promising because there were significant challenges in getting the immune system to accept the mRNA that the vaccine uses.
  7. The mid-1990s — She failed to get funding for her work at the University of Pennsylvania and was forced to choose between stopping work on her mRNA research or be demoted from a track to be a full professor.
  8. She chose to be demoted and continue her research.
  9. UPenn’s ultimatum was posed just after she had been diagnosed with cancer.
  10. She persisted and was able to get her research funded with the help of an established immunology professor — Drew Weissman — who she met at the photocopier.
  11. In the early 2000s: she read a study that gave her an idea of how to avoid the adverse immune system reaction that prevented mRNA from being used in vaccines.
  12. 2005: Karikó and Weissman published a study suggesting that there may be a way to avoid the immune reaction.
  13. After publishing their research and patenting it, Karikó and Weissman received no invitations to talk about their work.
  14. But Derrick Rossi, a postdoc at Stanford University noticed their research and created a company called Moderna in 2010 to commercialize the technology.
  15. Karikó and Weissman licensed their technology to a small German company called BioNTech, after five years of trying and failing.  (BioNTech was founded by a Turkish immigrant named Ugur Sahin)
  16. 2013: UPenn refused to reinstate Katalin Karikó as a full professor after demoting her in 1995. She told them she was leaving to go to BioNTech: ”When I told them I was leaving, they laughed at me and said, ‘BioNTech doesn’t even have a website.’”
  17. 2017: Moderna (founded by the Derrick Rossi Stanford postdoc) used the technology Karikó pioneered to develop a Zika virus vaccine .
  18. 2018: The German company Karikó and Weissman licensed their technology to partnered with Pfizer to develop an influenza vaccine.
  19. April 2020: Derrick Rossi’s Moderna received $483 million (£360m) from the US Biomedical Advanced Research and Development Authority to fast-track its Covid-19 vaccine program
  20. Pfizer developed their mRNA vaccine using Karikó and Weissman research, but without government funding.

Read More:

1) Read full “Wired” article: