Compounding Interest + contributions

I understand how to use the compound interest function in excel (=PV*(1+R)^N), but is it possible to add contributions to the formula?

I am trying to create a calculator in excel for my wealth management business. Every 3 months I bring in $1,000,000 in new assets and I charge 1% per year to manage them.

At first, I took the total annual investments (contributions) of $4,000,000 at 1% for a total of $40,000 per year, but soon realized this was incorrect because I didn't start with $4,000,000, I accumulated it over the year, however, I was collecting payment on what I had accumulated during that time.

The ACTUAL earnings by year end, taking the contributions over time, was $25,000 (much different that $40,000!).

The easiest way to get what I wanted was to place 1,000,000 in 4 different cells, let's call them A1, A2, A3 and A4. I times A1 by .0025 (1% divided by 4) so =A1*(.0025) in cell B2, then did the same in cell B3 but added A1 and B1 (initial contribution + interest) and added in to the new contribution and times that by .0025, so (A1+B1+A2)*.0025 and did this for the remaining cells which took me to my total of $25,000.

My question is, is it possible to complete this calculation using an excel formula or custom function?

Thank you, your answers/input are more than appreciated.

Is it something like this you're looking for?

Formula:

Please Login or Register  to view this content.

You nailed it! Thank you so much. Can you explain how it works?

I'd be happy to.

Basically, what you're amounting to is,
1,000,000 going across 4 quarters at 1% p.a. plus
1,000,000 going across 3 quarters at 1% p.a. plus
1,000,000 going across 2 quarters at 1% p.a. plus
1,000,000 going across 1 quarters at 1% p.a.

So it's basically,
1,000,000 * (1+1%/4) ^ 4 plus
1,000,000 * (1+1%/4) ^ 3 plus
1,000,000 * (1+1%/4) ^ 2 plus
1,000,000 * (1+1%/4) ^ 1

which I've simplified as SUMPRODUCT(1,000,000 * (1+1%/4)^{4,3,2,1}) and I used ROW(1:4) in place of {4,3,2,1}.

Thank you for taking your time. I'd be embarrassed to disclose the hours I spent trying to figure a solution.

Not a problem jcrowe. It's just experience that helps deal with these things.

If this resolves your initial query, can you please mark this thread as solved by clicking on Thread Tools above your first post and then Mark This Thread as Solved.