Looking for Excel Spreadsheet Expert

Zanderman

OP, good luck with your search for a solution to your excel problem.

For what it's worth I think (as you've probably worked out already!) you'd be better asking your question on a forum, help site etc about Excel, spreadsheets (and maths) rather than here.

This is a forum about savings and investments in general, not really geared to queries about spreadsheets and how to use them.

I would echo what others have suggested about the need to learn this sort of thing yourself though - I'm no expert on excel but if I need to do something complex with it I work it out, as I want to be sure that I know how and why it works. Otherwise I'd never actually trust it to be correct.

This is me trying to be helpful by the way, please don't dismiss advice like this as unhelpful, because it really isn't!

bostonerimus

Just add the total deposits for the month to current total and multiply by 1+r.......where r is the monthly return. Repeat. This is just compounding. If you want an analytical solution why are you using excel? This thread makes me so glad that I manage my own money.

tempus_fugit

I'm not sure if this will help but I just spent 10 minutes putting together a basic spreadsheet based on what you have outlined in post #27. If this is not what you are thinking of then apologies as I have obviously misunderstood your requirements.

So, in cell A1 I put "100" as the monthly investment and additional investment every month.

In cells A2 to A13 I put the numbers 1 to 12, representing the 12 months of the first year.

In cell B2 I put the formula "=A1". In cell B3 I put the formula "=B2+$a$1". I then dragged this down to cell B13 so that the formula in cell B4 is "=B3+$a$1", etc.

In cell C2 I put the formula "=B2" and in cell C3 I put the formula "=B3+C2" and then dragged this down to cell C13. This gave the formula in C4 as "=B4+C3" and so on, i.e. adding the previous month's investment plus the new investment each month giving the total so far. The amount in cell C13 should be the total invested over the year.

In cell D1 I put "0.05" as the annual interest (just a notional figure obviously). In cell D2 I put the formula "=C2*$D$1/12", and dragged this down to cell D13. This should give the interest for the total invested for that month. If you then put in cell D14 "=sum($d$2:$d$13) that give the total interest for the year. You can then change the values in cells A1 and D1 to give different investment amounts and different interest rates.

Obviously this is only for one year and to do ten years would involve expanding this for a further nine years. There may be a formula that would work this out more succinctly but it is not really within my abilities or time willing to spend on it to do this. I'm sure if you started a spreadsheet based on the above then you could expand it and work on it to get it to do what you want. As I say, if I have misconstrued your requirements then my apologies.

ruperts

Not sure if I'm interpreting the problem correctly, but it seems fairly straightforward.

Column A is your monthly contributions. So in A1 you put 100. Then in A2 you put =A1+100. Copy that formula that down and it'll give you:
100
200
300
400
500
And so on which represents a new client coming on board every month with a contribution of 100

Then in column B, for the sake of demonstration start with a cumulative total, so in B1 put =A1 to give you 100, then in B2 put =B1+A2, and copy the formula down to give you:
100
300
600
1000
1500
And so on which represents the cumulative sum of all money received by that month

Then to add interest, i would not bother with a seperate column but instead go back into B2 and change it to =(B1+A2)*(1+(0.07/12)), where your interest rate is 7%, and copy it down which will give you
100
301
605
1011
1520
And so on down to row 120 (or wherever you want to end it) where you'll have a monthly contribution of 12k and a final cumulative value of 932k.

If you want variable new contributions or interest rate then you'll just have to add in another couple of columns. If you want the result without the lists of data then it'll take a smarter formula but I'm sure it could be done without macros.

tempus_fugit

You can do it that way as well of course, but keeping a separate column, or at least a reference cell, for the interest means that you can change it as you wish to change the overall result.

JohnRo

Very simply this is I think what you're wanting, obviously the AUM can then be manipulated by interest rates, growth rates, whatever.

Is this about right in terms of what you're aiming to show?

https://docs.google.com/spreadsheets/d/1yru-qFoOFpCUfUGvpEp2mWzOpkW_iyol0mKoXYtra1Q/edit?usp=sharing

bbeat65

Yes it is, JohnRo, and others who came up with very similar responses. I appreciate the time you invested to come up with a solution. I didnt clearly state what I was looking for, I guess.
I dont have a challenge coming up with the answer, if I use many rows. I was hoping to come up with a formula that just used 1 row, and the formula did the rest, without having 240 rows for 20 years (example)

Maybe a better way to explain my challenge is...

I want to try to accomplish this in 4 columns, and 1 row, if possible
1. Monthly amount
2. Annual Compounded Interest Rate
3. Number of years (or months) to run the numbers
4. Total amount accumulated.

I can do a time value of money with a consistent contribution - no problem. As many have said - that is simple. But that is not what I am looking for.

I can also run different rows to come up with the answer (eg. Row 1 $100/mo for 120 months, Row 2 $100/mo for 119 months, Row 3 $100/mo for 118 months, Row 4 $100/mo for 117 months, etc.), and have a long sheet, which I can total at the end.

This gives the same effect as Month 1 invest $100, month 2 ADD $200, Months 3 ADD $300, Month 4 ADD $400, etc., for the number of years (variable) I wish to run it.

I am trying to figure out a formula that does it for me on one line. I feel like there is, and it may be straight forward for someone who is an expert. I cannot figure it out, and am looking for help. I recognize that this was the wrong forum to pose such a question, but since there was so many people that seemed to want to help, I continued. Again, I appreciate the positive feedback, I've received.

tempus_fugit

You probably need a maths expert then rather than an Excel one.

RacingDriver

Try this =((C1*(C1+1))/2)*A1*(1+B1/12)

where A1 = Monthly Amount, B1 = Annual Interest Rate and C1 = Number of Months

Put formula in D1 to give result

Apodemus

bbeat65 wrote: »

Yes it is, JohnRo, and others who came up with very similar responses. I appreciate the time you invested to come up with a solution. I didnt clearly state what I was looking for, I guess.
I dont have a challenge coming up with the answer, if I use many rows. I was hoping to come up with a formula that just used 1 row, and the formula did the rest, without having 240 rows for 20 years (example)

Maybe a better way to explain my challenge is...

I want to try to accomplish this in 4 columns, and 1 row, if possible
1. Monthly amount
2. Annual Compounded Interest Rate
3. Number of years (or months) to run the numbers
4. Total amount accumulated.

I can do a time value of money with a consistent contribution - no problem. As many have said - that is simple. But that is not what I am looking for.

I can also run different rows to come up with the answer (eg. Row 1 $100/mo for 120 months, Row 2 $100/mo for 119 months, Row 3 $100/mo for 118 months, Row 4 $100/mo for 117 months, etc.), and have a long sheet, which I can total at the end.

This gives the same effect as Month 1 invest $100, month 2 ADD $200, Months 3 ADD $300, Month 4 ADD $400, etc., for the number of years (variable) I wish to run it.

I am trying to figure out a formula that does it for me on one line. I feel like there is, and it may be straight forward for someone who is an expert. I cannot figure it out, and am looking for help. I recognize that this was the wrong forum to pose such a question, but since there was so many people that seemed to want to help, I continued. Again, I appreciate the positive feedback, I've received.

Would the "future value" function not do this for you?

FV(rate, nper, pmt, [pv],[type])