Calculate back pay with varying annual interest rates

Say someone is owed a back payment of $500 per month beginning from 1/1/2008 through 12/31/2015. Let's say the applicable annual interest rates are 4.6% in 2008, 5.3% in 2009, 5.1% in 2010, 4.7% in 2011, 4.2% in 2012, 4.1% in 2013, 3.9% in 2014, and 3.7% in 2015. We want to calculate how much the total back benefits would be based on these interest rates, compounded annually. Does anyone know how I can do this calculation? THanks!

Set up a table like this - it is easier since you are compounding annually:

Capture.JPG

All 20,000 people get the same number of years of back pay? Or do you need to take into account one or more dates? And they all get the same monthly amount?

In any event, you can set up a single table of percentages for all of the people, and use 8 or 9 columns of formulas (or how ever many you need) to do the calculation for each row (assuming that your data is in rows). If you post a very small, cleaned up example of the data you have....

You can modify the format of Bernie's table (as he stated in his second post to the thread) so that it is horizontal. Providing the name and monthly salary of each individual is in columns A and B, as indicated by the blue highlight in the attached file. You can fill in the table, indicated in green, for the first person select C3:K3 and double click the fill handle to copy it down 20,000 or so rows.
There are three formulas in the table.
The formula for column C is: =B3*12
The formula for column D is: =C3*(1+D$2)
The formula for columns E through K is: =SUM($C3,D3)*(1+E$2)
Let us know if you have any questions.