Looking for Excel Spreadsheet Expert

aroominyork

I've just checked with heaven and RacingDriver's seat has been reserved.

bigadaj

bbeat65 wrote: »

I believe that was fairly clear in the post. I am not sure if that was a "sarcastic" comment or not. I guess it doesnt really matter what I do, or who I want to share this with, I am just looking for some help. That is why I reached out to this forum. I have never posted to this, so I am not sure of the environment here either. I am not looking for debate; just some kind help. If I am in the wrong forum, please let me know. Thank you for your time.

Yes, it was sarcastic, this is a uk based forum, though being Canadian I thought you might be open to a bit of sarcasm unlike our cousins based to your south.

It just appears to be worrying that you appear to be some sort of advisor, with a requirement to analyse data and plan investments, yet can't do a very simple calculation from an excel sheet.

If you are an authorised advisor then I'd probably expect all this to be based on some bespoke system but still if you are numerate and computer literate it's pretty basic stuff.

atilla

P*ss take guys.

bbeat65

If it's so basic, then send me an excell page completed. I dont think you can, can you? You dont have to, I know, but if you had any idea what the role of an advisor is - then you would know that it is NOT to create spreadsheets to calculate the effects of many investors contributing to a total (advisors) AUM. It is to analyze a clients needs, and make recommendations to help them accomplish their goals. Now - I dont buy in to the pompous, tight assed, reputation that you Brits have. In fact, my ancestors are British a couple of generations back. I only came on here to get help with an excel problem, not be criticized, by someone who doesnt have the desire, or skills to help. I will chalk this up as one Brit, and be sure not to lump "you" all in one bunch. I would be embarrassed if my fellow Canadians were disrespectful, without knowing the facts. Thanks to those that were helpful, tired to help, and/or at least polite.

RacingDriver

bbeat65 wrote: »

If it's so basic, then send me an excell page completed. I dont think you can, can you? You dont have to, I know, but if you had any idea what the role of an advisor is - then you would know that it is NOT to create spreadsheets to calculate the effects of many investors contributing to a total (advisors) AUM. It is to analyze a clients needs, and make recommendations to help them accomplish their goals. Now - I dont buy in to the pompous, tight assed, reputation that you Brits have. In fact, my ancestors are British a couple of generations back. I only came on here to get help with an excel problem, not be criticized, by someone who doesnt have the desire, or skills to help. I will chalk this up as one Brit, and be sure not to lump "you" all in one bunch. I would be embarrassed if my fellow Canadians were disrespectful, without knowing the facts. Thanks to those that were helpful, tired to help, and/or at least polite.

See my proposed solution above, I would send it to you in Excel but I do not see a way of sending attachments through this forum.

bbeat65

Thank you so much, racingdriver. I will try your solution, to see if it gives me the answers I am looking for, when I get a minute (in the next 24 hours). I will certainly let you know how it works out. Once again - Thank you

JohnRo

Not enough information, imo.

Are you trying to create a client ledger?
Are clients permanent?
Are contributions fixed?
Are interest rates fixed?

It sounds like something better suited to a relational database.

If you're just trying to project a current snapshot or make a chart of accumulated/ing value from a number of clients compounding over a number of years into the future at a single or range of interest rates then the sums involved are relatively straight forward but the complexities will escalate if those items are all themselves time sensitive variables.

Audaxer

I think some of the replies on here are pretty harsh, as the OP seems to be genuinely asking for help.

If the solution proposed by RacingDriver, which includes a VBA macro, is needed to give the OP the solution he is looking for, then it is not as straightforward an Excel problem as some of you guys have been suggesting.

Aegis

I'd have though this was simple, something like this:

Columns

• A: Month (start at 1, then previous A + 1)
• B: # new clients (1 in months you are adding clients, 0 where not)
• C: #clients in total (B plus previous C, or just B for the first row)
• $ contribution per client (fixed or variable with inflation, however you want it)
• E: $ contribution total (D * C)
• F: growth on existing balance (0 in first column, growth rate raised to the power of 1/12 * previous G in subsequent rows. Depending on how you set up your growth rate, you may need to deduct the previous G from this figure to strip out the initial balance)
• G: End of month balance (D + E + previous G)

Run for as many rows as you want.

bostonerimus

RacingDriver wrote: »

Ok, I think I understand what you are trying to do. To do this calculation you can use the VBA macro I have written below. If you are unsure how to add the VBA macro to Excel let me know what version of Excel you are using (e.g. 2010, 2016 etc.) and I can try and help.

This assumes that you have three values in your spreadsheet in the following cells:

Monthly Deposit in B1 (e.g. 100)
Annual Interest in B2 (e.g. 2)
Months in B3 (e.g. 12)

The macro then outputs your final value to cell B5

If your values are in different cells, you can update the macro below by changing the parts in red/bold in the format row, column

Sub InterestCalc()
'Tell Excel that the values we are going to use are numbers
Dim MonthlyDeposit As Variant
Dim MonthlyInterest As Variant
Dim Months As Variant
Dim Count As Variant
Dim FinalValue As Variant

'Read in values from spreadsheet;
MonthlyDeposit = Cells(1, 2) 'In format (row, column) (e.g. this is B1)
MonthlyInterest = ((Cells(2, 2) / 100) / 12) + 1 'B2
Months = Cells(3, 2) 'B3

For i = 1 To Months
FinalValue = FinalValue + (MonthlyDeposit * i) 'Add monthly deposit
FinalValue = FinalValue * MonthlyInterest 'Add monthly interest
Next i

Cells(5, 2) = FinalValue 'Output final value
End Sub

Why use a macro when it can be accomplished so simply?