I can handle alot of very basic excel spreadsheet, but I am clueless when it comes to doing formulas and or functions.
I am trying to have 4 sets of returns based upon the S&P 500 open and close for the past 60+ years. That correspond to a financial product.

The first is a daily average which will equal the sum of the market close value for the full year (ie July 26 2012 to July 26 2013 - or closest market day after the 26th) then the average is subtracted from the beginning index value and then the difference divided by the beginning value to result in a percentage. If this is a negative number 0% should be reflected.

The second is monthly average except done on a monthly basis - (ie July 26 2012 + August 26 2012....July 26 2013)/12 = X, X/beginning value = Percentage which is less than 0% should be reflected a 0%

Then MPP - which should be a basic calculation which calculates the percentage change from mos. to mos. (July 26 2012 to August 26 2012) for one year - the resulting percentage will be the sum of the 12 months (positive and negative) - i.e. if from July to July the figures are 1, 2, -3, 4, -5, 6, 7, -8, 9, 10, 11, -12 - the reflected MPP figure would be the sum of these percentage changes or 22%. The catch is there will be a cap on the month upside (none on the downside) - this changes and needs to be something I can manipulate.

The final is the APP which is something I can do fairly easily I think - this is simply the value of the market now-the value 1 year ago/beginning value = % change with a cap that will change.

Any advice will help for these 4 columns! I am attaching the value I have so you can see what I am talking about.
JJEquity Index2.xlsx