Calculating Interest Rates with Excel

by

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

by

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

by

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…]

SQL Challenge: Cross-Country Scoring (Solution)

by

I introduced the challenge of Scoring a Cross Country match in a 2005 article.

This article provides some SQL Solutions to parts of the Challenge.

1) Matching Teams:

In a four-way match each combination of teams, each team is scored against the others:

Teams: Ephrata, Manheim Township, Conestoga Valley, McCaskey

There are 6 pairs: 3+2+1

  1. Ephrata vs Manheim Township
  2. Ephrata vs Conestoga Valley
  3. Ephrata vs McCaskey
  4. Manheim Township vs Conestoga Valley
  5. Manheim Township vs McCaskey
  6. Conestoga Valley vs McCaskey

2) How do we generate these team pairings with SQL?

/* Get Pairs of Teams */
select 
	t1.team_name as t1, 			
	t2.team_name as t2
from 
	teams as t1
	cross join	/* Cross join two teams: generates all possible combinations */
	teams as t2
where
        /* 'greater than' fix to make sure that teams don't race against themselves or their inverse */
	t1.team_name > t2.team_name	
order by
	t1.team_name, t2.team_name;

3) How to we generate each runner’s points?

select 
	rank() over (order by place) as points,	  
	team_pairs.team1, 
	team_pairs.team2, 
	race_results.runner_name,
	teams.team_name, 
	race_results.place, 
	race_results.time 
from 
  ..

4) Combine Together Using “With-Statement”

/* Add the Race Results to the Team Parings */
with team_pairs as (		
	/* Team Pairs */
	select 
		t1.id as id1,
		t1.team_name as team1, 
		t2.id as id2,
		t2.team_name as team2
	from 
		teams as t1 
		cross join						
		teams as t2
	where
		t1.team_name > t2.team_name			/* could also use teams.id but I didn't want you to think the ids are meaningful */
	order by
		t1.team_name, t2.team_name

)
select 
	rank() over (order by place) as points,		
	team_pairs.team1, 
	team_pairs.team2, 
	race_results.runner_name,
	teams.team_name, 
	race_results.place, 
	race_results.time 
from 
	team_pairs					   /* team_pairs is the 'named' variable from the above with statement */
	left outer join race_results 			   /* this could change to an'inner join' but I want to see all teams, evem if no runners */
	  on 	team_pairs.id1 = race_results.team_id or
	  		team_pairs.id2 = race_results.team_id
	inner join teams
	  on teams.id = race_results.team_id

/* 	There's a lot of data returned.  Let's focus in on one match: Ephrata vs Manheim Township for which we at least have 4 runners. 
	We can remove the filter once we have a single match worked out */
where
	(	team_pairs.team1 = 'Ephrata' and
		team_pairs.team2 = 'Manheim Township'
	)
	or 
	(	team_pairs.team2 = 'Ephrata' and
		team_pairs.team1 = 'Manheim Township'
	)

order by 
	race_results.place;


	/* 	We can add another with statement and sum up the points for each team, grouping by team1, and team2
		We also have to figure out how we're going to solve the 6th and 7th runner rule.
		I think we can do that with a where clause that limits the top 7 runners, 
		which may require a second rank column and an order by statement, but I'm fuzzy on what I did before
	*/

 

View More: