A question about compound interest - Hi guys. I am building a calculator in Excel to give a rough idea of how you would have done, based on past performance, if you invested a certain amount each month into the S&P 500 for the past 18 years and I wanted to know if I calculated it correctly?

So, I looked at the Google chart for the S&P, went back to July 2005 and checked how much it had risen from then to today. Turns out it is 272.59%.

I then divided that 272.59% by the 18 years that have passed. Which rounded down was 15.14%. I assume this as the average annualised return.

Then, I divided that 15.14% to give me a monthly return. It was 1.26% per month.

I also decided to pretend I invested £100 each month, every month without fail, regardless of highs or lows. Pure DCA into the index fund every month. (I used GBP because it's my local currency, and I am not going to adjust for FX exchange rates just to keep it simple, so you can use USD, Yen or what ever)

To make my calculations after finding out those key figures, I then simply did the following:

I figured out how much those gains translated into each month by multiplying the total invested amount by the 1.26% figure we worked out earlier. Which for the first month, in the first year, looked like this: £100 \* 1.26 = £1.26.

Then, once the second month rolls around, I would add in my next bit of money I put in (£100) to the S&P 500 and added the monthly gains to the invested total. This gave me a total of £201.26. (£100 initial investment in month 1, then month 2 I add another £100 and count the £1.26 gains: 100 + 100 + 1.26)

After this I repeated the cells all the way for the remaining 11 months in the first year and continued to repeat until I got to 18 full years total.

What it ended up looking like is this.

July 1st 2005:Invested = £100.Gained = £0.Total = £100.

Today:Invested = £21,700.Gained = £90,776.56.Total = £112,476.56

Sorry if I rambled or made it weird or confusing. I'm not amazing at explaining things.

I just want to know simply, looking at the S&P 500's past performance based on the start date I gave, up to today (02/07/2023) assuming I continually invested £100 each and every month, would I have ended up with roughly £112,476.56?

Thanks.

Edit\* Also, is this even how compound interest works?