Empirical Project 5 Working in Excel

Part 5.1 Measuring income inequality

One way to visualize the income distribution in a population is to draw a Lorenz curve. This curve shows the entire population along the horizontal axis from the poorest to the richest. The height of the curve at any point on the vertical axis indicates the fraction of total income received by the fraction of the population, shown on the horizontal axis.

We will start by using income decile data from the Global Consumption and Income Project to draw Lorenz curves and compare changes in the income distribution of a country over time. Note that income here refers to market income, which does not take into account taxes or government transfers (see Section 5.9 of Economy, Society, and Public Policy for further details).

To answer the question below:

  1. In this new tab, make one table (as shown in Figure 5.1) for each country and year (four tables total). Use the country data you have selected to fill in each table. (Remember that each decile represents 10% of the population.)
Cumulative share of the population (%) Cumulative share of income (%)
0 0
10
20
30
40
50
60
70
80
90
100

Cumulative share of income owned, for each decile of the population.

Figure 5.1 Cumulative share of income owned, for each decile of the population.

Excel walk-through 5.1 Creating a table showing cumulative shares

How to create a table showing cumulative shares.

Figure 5.2 How to create a table showing cumulative shares.

The data

We will be using data from Afghanistan and Albania for this example. The data has been copied and pasted into a new tab on the spreadsheet. We will make a cumulative table for Afghanistan in 1980. (The other three tables are made in the same way.)

Figure 5.2a We will be using data from Afghanistan and Albania for this example. The data has been copied and pasted into a new tab on the spreadsheet. We will make a cumulative table for Afghanistan in 1980. (The other three tables are made in the same way.)

Calculate the cumulative share of income using the SUM function.

To calculate the cumulative share of income, we need to add up all the incomes corresponding to that decile and all smaller deciles, and then divide by the sum of all incomes.

Figure 5.2b To calculate the cumulative share of income, we need to add up all the incomes corresponding to that decile and all smaller deciles, and then divide by the sum of all incomes.

Calculate the cumulative share of income using the SUM function.

Decile 2 and the remaining deciles are calculated slightly differently from Decile 1, because we have to also include the incomes of lower deciles in the calculation.

Figure 5.2c Decile 2 and the remaining deciles are calculated slightly differently from Decile 1, because we have to also include the incomes of lower deciles in the calculation.

Calculate the cumulative share of income using the SUM function

You can use this table to plot a Lorenz curve with the first column as the horizontal axis values, and the second column as the vertical axis values.

Figure 5.2d You can use this table to plot a Lorenz curve with the first column as the horizontal axis values, and the second column as the vertical axis values.

  1. Use the tables you have made to draw Lorenz curves for each country in order to visually compare the income distributions over time.

Excel walk-through 5.2 Drawing the perfect equality line

Figure 5.3 How to draw the perfect equality line.

The data

We will use the Lorenz curve for Afghanistan in 1980 as an example. The values we need to plot the perfect equality line are given in cells C9 to C19 (labelled ‘perfect equality line’). You will notice that these values are the same as those in cells A9 to A19, because the perfect equality line is where the horizontal and vertical axis values are equal to each other.

Figure 5.3a We will use the Lorenz curve for Afghanistan in 1980 as an example. The values we need to plot the perfect equality line are given in cells C9 to C19 (labelled ‘perfect equality line’). You will notice that these values are the same as those in cells A9 to A19, because the perfect equality line is where the horizontal and vertical axis values are equal to each other.

Add the required cells to the line chart

For the perfect equality line to show up on the chart, we need to add it as a separate data series.

Figure 5.3b For the perfect equality line to show up on the chart, we need to add it as a separate data series.

Add the required cells to the line chart

Since the values in cells A9 to A19 and C9 to C19 are the same, it doesn’t matter which range of cells you add to the chart. After step 6, the perfect equality line will appear on your chart.

Figure 5.3c Since the values in cells A9 to A19 and C9 to C19 are the same, it doesn’t matter which range of cells you add to the chart. After step 6, the perfect equality line will appear on your chart.

  1. Using your Lorenz curves:

A rough way to compare income distributions is to use a summary measure such as the Gini coefficient. The Gini coefficient ranges from 0 (complete equality) to 1 (complete inequality). It is calculated by dividing the area between the Lorenz curve and the perfect equality line, by the total area underneath the perfect equality line. Intuitively, the further away the Lorenz curve is from the perfect equality line, the more unequal the income distribution is, and the higher the Gini coefficient will be.

  1. Using a Gini coefficient calculator, calculate the Gini coefficient for each of your Lorenz curves. You should have four coefficients in total. Label each Lorenz curve with its corresponding Gini coefficient, and check that the coefficients are consistent with what you see in your charts. (Hint: In the Gini calculator, the income values need to be in a single column, but in the spreadsheet the income values are in a single row. You will need to copy and then paste-transpose each row so that your data is in the correct format to enter into the Gini calculator. See Excel walk-through 2.1 for help on how to paste-transpose.)

Now we will look at other measures of income inequality to see how they can be used with the Gini coefficient to summarize a country’s income distribution. Instead of summarizing the entire income distribution like the Gini coefficient does, we can take the ratio of incomes at two points in the distribution. For example, the 90/10 ratio takes the ratio of the top 10% of incomes (Decile 10) to the lowest 10% of incomes (Decile 1). A 90/10 ratio of five means that the richest 10% of the population earn five times more than the poorest 10%. The higher the ratio, the higher the inequality between these two points in the distribution.

  1. Look at the following ratios:

    • 90/10 ratio = the ratio of Decile 10 income to Decile 1 income
    • 90/50 ratio = the ratio of Decile 10 income to Decile 5 income (the median)
    • 50/10 ratio = the ratio of Decile 5 income (the median) to Decile 1 income.

We will now compare these summary measures (ratios and the Gini coefficient) for a larger group of countries, using OECD data. The OECD has annual data for different ratio measures of income inequality for 42 countries around the world, and has an interactive chart function that plots this data for you.

Go to the OECD website to access the data. You will see a chart similar to Figure 5.4 which show data for 2015. The countries are ranked from smallest to largest Gini coefficient on the horizontal axis, and the vertical axis gives the Gini coefficient.

  1. Compare summary measures of inequality:

OECD countries ranked according to their Gini coefficient.

Figure 5.4 OECD countries ranked according to their Gini coefficient.

The Gini coefficient and the ratios we have used are common measures of inequality, but there are other ways to measure income inequality.

  1. Go to the ‘income inequality’ section of the Our world in data website, and choose two other measures of income inequality that you find interesting.

Part 5.2 Measuring other kinds of inequality

There are many ways to measure income inequality, but income inequality is only one dimension of inequality within a country. To get a more complete picture of inequality within a country, we need to look at other areas in which there may be inequality in outcomes. We will explore two particular areas, focusing on the measures used and their limitations:

First, we will look at how researchers have measured inequality in health-related outcomes. Besides income, health is an important aspect of wellbeing because it determines how long an individual will be alive to enjoy his or her income. If two people had the same annual income throughout their lives, but the one person had a much shorter life than the other, we might say that the distribution of wellbeing is unequal, despite annual incomes being equal.

As with income, inequality in life expectancy can be measured using a Gini coefficient. In the study ‘Mortality inequality’, researcher Sam Peltzman (2009) estimated Gini coefficients for life expectancy based on the distribution of total years lived (life-years) across people born in a given year (birth cohort). If everybody born in a given year lived the same number of years, then the total years lived would be divided equally among these people (perfect equality). If a few people lived very long lives but everybody else lived very short lives, then there would be a high degree of inequality (Gini coefficient close to 1).

We will now look at mortality inequality Gini coefficients for ten countries around the world. First, download the data:

  1. Using the mortality inequality data:
  1. Now compare the Gini coefficients in the first year of your line chart (1952) with the last year (2002).

Excel walk-through 5.3 Drawing a column chart with sorted values

Figure 5.5 How to draw a column chart with sorted values.

Sort the data from smallest to largest Gini coefficient

We will use the Gini coefficients for 1952 as an example. The data has been filtered to show values for the year 1952 only.

Figure 5.5a We will use the Gini coefficients for 1952 as an example. The data has been filtered to show values for the year 1952 only.

Sort the data from smallest to largest Gini coefficient

After step 2, the countries will now be sorted according to their Gini coefficient (from smallest to largest).

Figure 5.5b After step 2, the countries will now be sorted according to their Gini coefficient (from smallest to largest).

Draw a column chart

Now we will make a column chart with the sorted Gini coefficients. After step 5, the column chart will look like the one shown above.

Figure 5.5c Now we will make a column chart with the sorted Gini coefficients. After step 5, the column chart will look like the one shown above.

Change the horizontal axis labels to country names

Now we will change the horizontal axis labels to country names.

Figure 5.5d Now we will change the horizontal axis labels to country names.

Change the horizontal axis labels to country names

After step 8, the horizontal axis labels are now country names.

Figure 5.5e After step 8, the horizontal axis labels are now country names.

Add data labels to the columns

Data labels will make the vertical values easier to see, especially for values that are very close to each other. After step 9, the Gini coefficients will appear in boxes above the columns.

Figure 5.5f Data labels will make the vertical values easier to see, especially for values that are very close to each other. After step 9, the Gini coefficients will appear in boxes above the columns.

Round the Gini coefficients to two decimal places

The chart may be too crowded at first because the data labels are not rounded to two decimal places. If we round the Gini coefficient values, the data labels will change accordingly.

Figure 5.5g The chart may be too crowded at first because the data labels are not rounded to two decimal places. If we round the Gini coefficient values, the data labels will change accordingly.

Add axis titles and a chart title

After step 16, your chart will look similar in style to that of Figure 5.4.

Figure 5.5h After step 16, your chart will look similar in style to that of Figure 5.4.

Other measures of health inequality, such as those used by the World Health Organization (WHO), are based on access to healthcare, affordability of healthcare, and quality of living conditions. Choose one of the following measures of health inequality to answer Question 3:

To download the data for your chosen measure:

  1. For your chosen measure:

Since an individual’s income and available options in later life partly depend on their level of education, inequality in educational access or attainment can lead to inequality in income and other outcomes. We will focus on the aspect of gender inequality in educational attainment, using data from the Our world in data website, to make our own comparisons between countries and over time. Choose one of the following measures to answer Question 4:

To download the data for your chosen measure:

  1. For your chosen measure: