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.

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.

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:

• Example Spreadsheet: examples from this blog entry
• Effective Interest Rate, by William Tucker (pdf)

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.

Citigroup Center

Engineering crisis of 1978 (from Wikipedia)

Due to material changes during construction, the building as initially completed was structurally unsound. William LeMessurier‘s original design for the chevron load braces used welded joints. To save money, Bethlehem Steel changed the plans in 1974 to use bolted joints, which was accepted by LeMessurier’s office but not known to the engineer himself.^[22] Furthermore, according to The New Yorker, LeMessurier originally only needed to calculate wind loads from perpendicular winds under the building code; in typical buildings, loads from quartering winds at the corners would be less.^[22][159] In June 1978, after an inquiry from Princeton University engineering student Diane Hartley, LeMessurier recalculated the wind loads on the building with quartering winds.^[159][f] He found that, for four of the eight tiers of chevrons, such winds would create a 40 percent increase in wind loads and a 160 percent increase in load at the bolted joints.^[22]

Citicorp Center’s use of bolted joints and the increased loads from quartering winds would not have caused concern if these issues had been isolated from each other. However, the combination of the two findings prompted LeMessurier to run tests on the structural safety.^[103] The original welded-joint design could withstand the load from straight-on and quartering winds, but a 75-mile-per-hour (121 km/h) hurricane force quartering wind would exceed the strength of the bolted-joint chevrons.^[99] With the tuned mass damper active, LeMessurier estimated that a wind capable of toppling the building would occur on average once every 55 years.^[162][161] If the tuned mass damper could not function due to a power outage, a wind strong enough to cause the building’s collapse would occur once every 16 years on average.^[162] LeMessurier also discovered that his firm had used New York City’s truss safety factor of 1:1 instead of the column safety factor of 1:2.^[99]

LeMessurier debated how to address the issue before ultimately contacting Stubbins’s lawyer. LeMessurier then contacted Citicorp’s lawyers, the latter of which hired Leslie E. Robertson as an expert adviser.^[163] Citicorp accepted LeMessurier’s proposal to weld steel plates over the bolted joints, and Karl Koch Erecting was hired for the welding process.^[104] Very few people were made aware of the issue, besides Citicorp leadership, mayor Ed Koch, acting buildings commissioner Irving E. Minkin, and the head of the welder’s union.^[99][104] Starting in August 1978, construction crews installed the welded panels at night. Officials made no public mention of any possible structural issues, and the city’s three major newspapers had gone on strike.^[88][104] The work continued despite the threat of Hurricane Ella several weeks after the repairs started.^[99][164] Repairs were completed in October 1978, before the media resumed publishing. LeMessurier claimed a wind strong enough to topple the building would only occur once every 700 years.^[88][165] Stubbins and LeMessurier covered all of the repair costs, which were estimated to be several million dollars.^[165] Since no structural failure occurred, the work was only publicized in a lengthy article in The New Yorker in 1995.^[88][160]

William LeMessurier-The Fifty-Nine-Story Crisis: A Lesson in Professional Behavior

Citicorp Center | NYC skyscraper saved by a student’s question

The Citicorp Center repair is a classic engineering case study of how mistakes must be avoided in engineering and construction of public works. A skyscraper in New York City needed a unique structural system. While reviewing the design a student (named Diane Hartly) asked a question that made the engineer realize that a mistake had been made. There is a daring race to make the repairs for the building collapses. The video gives the details and then discusses how the engineer handled the situation.

Princeton Engineering Student

In a 2011 New Yorker talk, Malcolm Gladwell described the central role “legitimacy” plays in motivating people.  Previously, political theorists had focused on “deterrence theory” that treats people as rational actors who decide whether to follow the law based upon a weighing of the pros and cons of compliance.

Protesting Illegitimate Authority

Gladwell cites NYU Professor Tom Tyler’s work on “legitimacy”, and argues that people will fight to the death and even go on hunger strikes against an authority they feel is illegitimate, despite overwhelming penalties that deterrence theorists assume would be effective.

Gladwell identifies 3 factors in establishing legitimacy:

1. Does the authority grant one standing and listen to one’s petitions?
2. Is authority administered with neutrality or is there one set of rules for one group and a different system for others?
3. Is the system trustworthy — does it follow well-defined rules that are sensible and are not subject to arbitrary change?

How Reform Efforts go Wrong:

Society celebrates civil rights leaders such as Martin Luther King and police reformers such as Jack Maple, but society often fails to recognize how easily reform can falter if succeeding leaders do not follow in the original spirit.

In this article I originally wanted  to also include an evaluation of the role “legitimacy” plays in the right’s perception of the anti-racism movement, but this post is already quite long so this post will focus solely on policing.

In the following post, I summarize a number of sources that suggest that while the policing reforms of the 1980s were innovative and effective, the system mutated into an irrational system of “broken-windows policing” that disproportionately harasses and alienates minorities.

When one asks why do minorities not comply with police, I believe that generally an important factor is American policing’s weak legitimacy.

How Policing Reform turned into “broken windows” policing

There is an excellent 2-part podcast series by Reply-all that chronicles how desperate the crime problem was in New York City in the 1980s and how one policeman rescued the city and transformed policing on a nation-wide basis. Unfortunately his reforms were warped into the system that is today referred to as “broken windows” policing.

Please listen to these two podcasts because they are a major influence on my thinking about broken-windows policing.

1. The Crime Machine, Part I (please listen)
2. The Crime Machine, Part II (please listen)

The Reply-all podcast reports that in the 1980s, when New York City was in crisis, the New York City police only cared about crime that affected white people or rich people.

Police wouldn’t even investigate a theft that was less than $10,000 ($32,000 in today’s dollars).  It was during this crisis that a transit-cop named Jack Maple created an innovative COMSTAT database that allowed police to identify the most prolific criminals and treat every crime seriously.

Leadership cares more about image than problem solving

Sandra Bland: Arrested for Resisting Arrest

Dashcam video of Sandra Bland’s arrest

1. The Texas police don’t seem to have have the nerve to include the video immediately before the stop where the police officer sets a trap to use a pretext for pulling her over.
2. The police officer never responded to Bland’s request when asked for a reason why he was arresting Bland.  He was either too zoned-in in demanding she comply or he knew he couldn’t justify charging her with failure to signal.
3. The officer later gave the rationalization that she was arrested for resisting arrest (a catch-22).

Sandra Bland’s Phone Video of her Own Arrest

How Representative are Video Accounts?

1. Standing: Do police give citizens a fair hearing and articulate legitimate reasons that support their actions rather than declaring that they have the authority to order citizens around and force them into compliance?
2. Neutrality: Do police conduct themselves in a way that persuades citizens that there is a standard of law that applies equally to the police themselves and to all other people?
3. Trustworthiness: Is the law arbitrary or likely to change?

One can argue about whether the intention of broken windows policing is racist, but I would argue that is better to first deal with the fact that American policing (like many other American institutions) has legitimacy problems.

White Americans who have a long history of condoning rebellion should not expect minorities to treat them as meek and polite Canadians would.  Rather, White Americans should expect legitimate resistance to contemporary policing philosophy.  The challenge is to channel legitimate frustration and anger into productive dialog, reconciliation, and reform.