Looking for Excel Spreadsheet Expert

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 training 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.

bigadaj

You're an adviser?

And you're training people?

bbeat65

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.

Eco_Miser

30 years is 360 months, so once you've set up the columns (How much contributed this month, how much interest (Interest? What are you advising on?), total), you just copy down over 360 rows and read off the figures you're interested in. What's difficult about that?

bbeat65

If you know what you're doing, then it's probably common sense. I just cant get me head around how (if we use $100 per month, for example), in month 1 - I invest $100, in month 2 - I invest $200, in month 3 - $300, etc. And, how that would compound. So after 3 months, there has been $600 invested ($100+$200+$300, and so on). After 6 months, $2100 invested. If this is not clear, or above your knowledge, then I apologize. It makes perfect sense to me...lol. It just may be difficult to explain my challenge. With the greatest respect, and appreciation for your help....

bbeat65

Clearly, I am in the wrong forum. I apologize. This is the first time I have ever posted in ANY forum. I should've looked closer at all the conversations. Have a GREAT DAY everyone!

badger09

Is this the sort of help you're looking for?

http://www.keynotesupport.com/excel-basics/excel-for-beginners-guide.shtml


There are many similar sites which might be useful, depending on what level of experience you have. Try a google search and include the functions you're struggling with in the search term?

bbeat65

Thank you badger09. I apreciate your reply. I have a basic understanding of how Excel works, and how to submit formulas. I am challenged by the "adding 1 new every month" instead of a steady or consistent monthly amount. It's probably a simple solution, but for some reason, I just cant get "my head around it" to figure it out. Maybe its just one of those days. Thanks again.

bostonerimus

Put monthly contribution in one column (A1). In the next column (B1) calculate the compound interest on that contribution (1+rate)*A1. In the next row put in another monthly contribution (A2) and in B2 calculate the compound interest on the total ( A2+B1)*(1+rate). If the rate is in cell A6 for example you'd enter it into the formula using $A$6 so that it doesn't change

This really is trivial stuff.

RacingDriver

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

Alexland

Seriously you are an authorised IFA?