Looking for Excel Spreadsheet Expert

tempus_fugit

Yeah, a macro is overkill. From what I can tell it's a simple cumulative calculation based on increasing monthly contributions and the interest therefrom, which can all be done by straightforward formulas.

Apodemus

tempus_fugit wrote: »

Yeah, a macro is overkill. From what I can tell it's a simple cumulative calculation based on increasing monthly contributions and the interest therefrom, which can all be done by straightforward formulas.

Yep, all fairly simple for a spreadsheet task. But the OP really needs to work on their own spreadsheet skills rather than "buy-in" a solution, otherwise if they get it wrong, the results will be way out and they might not realise it.

I'd suggest starting by modelling a simple monthly payment into an interest-bearing account or a mortgage scenario first, to learn the process.

Bravepants

I agree with Apodemus. Start simply and build on that. It should be quite straightforward..I'm no Excel expert either but you can learn stuff like this online for free.

I guess some people on here are a bit senstitive to financial "experts" turning up on a forum struggling with a basic financial tool like a spreadsheet.

AndyT678

bbeat65 wrote: »

It is to analyze a clients needs, and make recommendations to help them accomplish their goals.

Absolutely agree but how can you possibly do that without any understanding of basic cash flow forecasting? Surely most of the clients' goals are going to be centered around the ability to spend money at some point in the future on stuff they want?

JustAnotherSaver

bbeat65 wrote: »

I believe that was fairly clear in the post. I am not sure if that was a "sarcastic" comment or not.

This is MSE, there is no other language unfortunately

bbeat65

Ok. 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.
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.
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.
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.
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?
So, here is where I struggle with the formula...
Columns
A monthly amount acquired each month (this is the client eg. 1 new client each month, that will contribute a certain amount of money)
B years that the representative acquires the new clients (remember the clients increase by 1, each month)
C interest rate the investment will grow at
D Total amount calculated by inputting the other 3 variables

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.

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

If I put $200 in column A, then it would be $200, plus $200, plus $200, etc
If I put $300, then $300, plus $300, etc

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)

Thank you for your help, and I look forward to your feedback

bbeat65

Please understand that this is not for a clients presentation.
This has nothing to do with discovering needs, and providing solutions for a client.
Maybe we should change the scenario.
Let's say I am a farmer...
Month 1 I find someone that is going to give me a cow EVERY month. And that cow reproduces an offspring at the rate of 10%. That means that it will have a birth approximately every 7.2 years.
In month 2, I find another person to give me a cow EVERY month, that reproduces at the same rate. Now I have 2 cows, each reproducing at a rate of 10% per year. Please dont report how much you know about the birthing rate of cows. These are my cows, and that is the rate they reproduce.
In month 3, I find another person that gives me a cow EVERY month, and I continue to find a person monthly that gives me a new cow every month for a period of time that I get to choose.
Now the first cow will give birth in approximately 7.2 years, but the one I get in the 60th month, wont give birth until 7.2 years after that. See what I mean?
Let's not get hung up on what the purpose of this is going to be used for. It is simply a calculation to show how many cows I will have (ones I purchased, and ones that gave birth), in whatever number of years I choose. I, believe it only needs to be 4 columns - A # of cows to be added every month, B annual rate at which the herd will grow, C the length of time I continue to add to the herd, and D the calculation. My challenge, for anyone willing to accept it, is to find the formula that increase the number of cows in my herd, monthly, by the initial value in column A.

Thank you for the help (for all those that are trying to help) To the rest, please find another blog to criticize.

AnotherJoe

Op, invest in yourself and sign up for an excel course.

Because with such a poor ability with spreadsheets it would be an embarrassment in a training course when one of your students pointed out an error (or an apparent error) and you were unable to diagnose / answer because you had such a poor knowledge of what is a basic tool for anyone dealing in finance these days.

RacingDriver

As I see it you have two options:

1) Include a row for each month with three columns
i) A = Total Monthly Deposit (e.g. A1 = £100, B1 = SUM(A1: A1) + A1, C1 = SUM(A1:B1) + A1)
ii) B = Annual Interest Rate (e.g. 2%)
iii) C = Ending Balance (e.g. C1 = A1 * (B1/12))

2) If you only want three values in your spreadsheet, and a final balance, instead of separate rows, I think you will need to use a macro to loop through the number of months

Apodemus

bbeat65 wrote: »

Let's say I am a farmer....

That means that it will have a birth approximately every 7.2 years.

I'm glad you are a financial advisor rather than a farmer!

The first spreadsheet I ever did (when I first got Supercalc on a double-floppy Amstrad 1512!) was a breeding-flock model for sheep farms. My first attempt had the flock in a run-away breeding-cycle where the whole country would have been over-run with sheep in a year or two - that taught me about circular references, iteration errors, stack overflows and infinite looping!

You are the one that really knows and understands what you want to achieve and you will benefit greatly from learning for yourself how to do it. It might help if you were to break it all down and write each step out on cards, then you will understand what functions you need to apply to each box.