Time Value of Money with Increasing Contributions

[bbeat65]

I am looking to create an Excel Spreadsheet that will allow me to see (as an Advisor) the total value of Assets Under Management an Advisor would have, if they attracted an investor every month, with a certain monthly contribution.

So, let's say - I find an investor every month, to contribute $100 every month.
That means - After month 5, I will have $500 per month going in to investments (from 5 different investors). After 10 months, I will have $1000 per month, etc. And, after 5 years, I will have $6000 per month (60 months X $100 per month).

What I would like to use as variables are: 1. monthly contribution found (eg $100 per month client, every month)
2. Number of Years (obviously need to convert to months, I think)
3. Annual compound interest rate

I would like to see the end, compounded balance of Assets accumulated (under my management), after the number of years, and monthly amount set up, at assumed interest rates.

It would be nice to have it set up, where it shows a list of the balances, at the end of each year also, if that is not too difficult. And, just to throw a bit more toughness (probably for a real PRO), if the chart was run for (say) 30 years, if the number of clients found was only for 10 years, would it be possible to show at the end of the 10th year, to just have the balance continue to compound, without adding new investors.

A bit about me...I am going to be using this for a training presentation about time and consistency for several advisors. I am a novice Excel user, and enjoy coming up with spreadsheets for projections, etc. This one has me TOTALLY BAFFLED. I appreciate any help I can get.

 

[retired007geek]

Baffled,

You can use the Future Value function to calculate this very easily. It doesn't lay it out over time but it does provide the final answer. Just adjust the values in B1-B3 and the answer shows up in B4.

B4 Formula: =FV(ROUND($B$3/12,6),$B$2*12,$B$1,,1)*-1

 

[AliGW]

Please use more descriptive and useful thread titles in future that give a clue to the help or solution required. The title you have used will be utterly useless in a Google or site search, so anyone seeking similar help in the future will not be able to benefit from any help offered in this thread. It would be appreciated if you would change the thread title accordingly.

 

[bbeat65]

Baffled,

You can use the Future Value function to calculate this very easily. It doesn't lay it out over time but it does provide the final answer. Just adjust the values in B1-B3 and the answer shows up in B4.

B4 Formula: =FV(ROUND($B$3/12,6),$B$2*12,$B$1,,1)*-1

View attachment 7408

Thank you retired007geek. I have many tools to project future value of a steady monthly investment, at assumed interest rates. What really has me baffled is that I would like to invest $100 in month 1, and increase it by $100 every month (so, month 2 would invest $200 plus the initial investment - month 3 would invest $300, plus the (already invested $300, etc.)

I want to show new representatives, the effect of consistently finding clients to invest with them. So, if they found one client each month (for example) to invest $100 per month, by the time 5 years went by, (their) clients (total) would be investing $6000 per month (60 months X $100). I would like to adjust the interest rate, amount of contribution, and time frame.

Thank you

 

[bbeat65]

Thank you retried007geek. I understand all that (I think). MY challenge (and I guess I didnt explain it very well), is that I cannot find the formula to increase the monthly investment by 100 (in your example).

[FONT=&quot]I am trying to project the effect of a new representative growing a clientele / book of assets / client base, by consistently getting new clients to invest with them.[/FONT]
[FONT=&quot]So, lets say a representative gets 1 client the first month that invests $100 monthly. Thats $100 per month the he is investing on his entire client base's behalf.[/FONT]
[FONT=&quot]If they go and get another client in month 2, that invests $100 per month - now the representative has $200 per month going in to his client base. They are 2 totally different clients, and they continue to invest their $100 per month.[/FONT]
[FONT=&quot]The 3rd month the representative gets another to invest $100 per month, and so on. By the time 5 years are up (60 months), the representative has 60 clients, each investing their $100 per month. So, the representative has $6000 per month going in to his clients accounts.[/FONT]
[FONT=&quot]Of course, I know this is not how it works in the real world, since some invest $1000 per month, some $25 per month, etc. My goal is to show that consistently adding new clients at different monthly contribution amounts will build a large client base and significant assets under management over time. Even small amounts add up over time, right?[/FONT]
[FONT=&quot]So, here is where I struggle with the formula...[/FONT]
[FONT=&quot]Columns[/FONT]
[FONT=&quot]A monthly amount acquired each month (this is the client eg. 1 new client each month, that will contribute a certain amount of money)[/FONT]
[FONT=&quot]B years that the representative acquires the new clients (remember the clients increase by 1, each month)[/FONT]
[FONT=&quot]C interest rate the investment will grow at[/FONT]
[FONT=&quot]D Total amount calculated by inputting the other 3 variables[/FONT]

[FONT=&quot]I can calculate the time value of a constant amount of money, at a constant rate of return, over a period of time. My struggle is to find the formula that allows me to increase the amount on a monthly basis. [/FONT]

[FONT=&quot]eg. Month 1 - $100[/FONT]
[FONT=&quot]Month 2 - $100 new contribution plus add new $100 contribution (total $300 invested so far)[/FONT]
[FONT=&quot]Month 3 - $100 from month 1, $200 from month 2, now $300 from month 3 (total $600 invested)[/FONT]
[FONT=&quot]Month 4 - $100 from month 1, $200 from month 2, $300 from month 3, now $400 from month 4 (total $1000 invested)[/FONT]

[FONT=&quot]If I put $200 in column A, then it would be $200, plus $200, plus $200, etc[/FONT]
[FONT=&quot]If I put $300, then $300, plus $300, etc[/FONT]

[FONT=&quot]After 10 years (at $100 per new client), the representative would be responsible for 120 clients each investing $100 every month, and $12000 per month would be invested (again, by 120 different clients)

[/FONT]I can tell that you are very well versed in the formulas, etc. I am a novice, so I am not. I do use Excel quite a bit, and can calculate quite a few things, with trial and error, the internet, and help of others. I just cannot seem to get the answer to this.

[FONT=&quot]Thank you for your help, and I look forward to your feedback[/FONT]

 

[bbeat65]

Thank You AliGW, and Pecoflyer. You two seem to know what you're doing on here. I do sense that you are a bit of a clique, and not interested in having any compassion for someone that is trying, but has no Edit Button. I have spent a lot of time looking, but to no avail. Anyway, If I am mistaken about the purpose of this site, please let me know. If there are people on here that can help, then I look forward to their responses.

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

I want to try to accomplish this in 4 columns
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 guess we were all here at one point, weren't we?

Also, I hope that the title changed as a result of this.

Thank you,

Brian

 

[Pecoflyer]

Thanks for changing your title. I forgot to mention that you have to do it in your first post ( that's what search engines will index).
I did it for you so we can go on with finding a solution for you
Cheers

 

[bbeat65]

Thank you. I am serious when I say that there is no edit on the first post. I dont know why - there just isnt. I appreciate your help, and hope we are back on the "right foot", as I try to figure this out.

I certainly appreciate the experts on here, and their help. I know there is a somewhat simple solution, and hope one of those experts can help.

 

[Pecoflyer]

Thanks for pointing that out. I'll PM you on this matter so I can forward the problem to admin

 

[AliGW]

I do sense that you are a bit of a clique, and not interested in having any compassion for someone that is trying, but has no Edit Button.

Absolutely not the case - please don't make assumptions. I asked you to tell us what you wanted to change the title to and, had you done so, would have changed it for you. You then PM'd me saying that you wanted the title changed, but still did not say what it should be changed to. I see it has now been changed.

Our job here is to ensure that members are using the forum correctly and following the rules: please don't take things personally. With a sensible thread title, you are more likely to get the help you need, so it's for your benefit.