Here's the problem:

John believes that the average American will be in debt forever. He decides to create a table that demonstrates this. He thinks that given 60 months (5 years) to get out of debt and the annual percentage rates 1-5 and 7-15 the percentage of the balance required to be paid monthly would be far beyond the average consumer's ability to repay.
Given the following graph, create an excel formula that when the monthly payment is entered, displays the annual percentage rate.
monthly percentage of balance/apr
1.75-1.79%/1%
1.80-1.84%/2%
1.85-1.89%/3%
1.90-1.94%/4%
1.95-1.99%/5%
2.00-2.04%/7%
2.05-2.09%/8%
2.10-2.14%/9%
2.15-2.19%/10%
2.20-2.24%/11%
2.25-2.29%/12%
2.30-2.34%/13%
2.35-2.39%/14%
2.40%/15%
>2.40%/16%

To create an Excel formula that displays the annual percentage rate (APR) based on the monthly payment, you can use the VLOOKUP function. Here's how you can do it:

Step 1: Set up your table with the ranges of monthly percentage of balance and the corresponding APRs.

```
A B
1 Monthly % of Balance APR
2 1.75-1.79% 1%
3 1.80-1.84% 2%
4 1.85-1.89% 3%
5 1.90-1.94% 4%
6 1.95-1.99% 5%
7 2.00-2.04% 7%
8 2.05-2.09% 8%
9 2.10-2.14% 9%
10 2.15-2.19% 10%
11 2.20-2.24% 11%
12 2.25-2.29% 12%
13 2.30-2.34% 13%
14 2.35-2.39% 14%
15 2.40% 15%
16 >2.40% 16%
```

Step 2: In a cell, enter the monthly payment for which you want to calculate the corresponding APR.

Step 3: In another cell, use the following formula to retrieve the APR for the given monthly payment:

```
=VLOOKUP(A17, A2:B16, 2, TRUE)
```

In the formula above, A17 is the cell reference for the monthly payment you entered in Step 2. A2:B16 is the range where your table is located.

When you enter a monthly payment in Step 2, the formula will lookup and display the corresponding APR in the cell where you entered the formula.

Note: Make sure to format the cell displaying the APR as a percentage to get the correct formatting.

Remember, this formula assumes that the table is sorted in ascending order based on the monthly percentage of balance.