Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@. Skimlinks & other affiliated links are turned on

Search
  • FIRST POST
    • bbeat65
    • By bbeat65 23rd Sep 17, 4:21 PM
    • 17Posts
    • 2Thanks
    bbeat65
    Looking for Excel Spreadsheet Expert
    • #1
    • 23rd Sep 17, 4:21 PM
    Looking for Excel Spreadsheet Expert 23rd Sep 17 at 4:21 PM
    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.
Page 3
    • Apodemus
    • By Apodemus 26th Sep 17, 7:11 PM
    • 958 Posts
    • 769 Thanks
    Apodemus
    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.
    Originally posted by bbeat65
    Would the "future value" function not do this for you?

    FV(rate, nper, pmt, [pv],[type])
    • RacingDriver
    • By RacingDriver 26th Sep 17, 7:12 PM
    • 60 Posts
    • 191 Thanks
    RacingDriver
    Would the "future value" function not do this for you?

    FV(rate, nper, pmt, [pv],[type])
    Originally posted by Apodemus
    I think the FV function only works with a fixed value each month rather than the value increasing each month
    • ruperts
    • By ruperts 26th Sep 17, 7:50 PM
    • 677 Posts
    • 1,116 Thanks
    ruperts
    Definitely in the realm of Excel 'tricks' here then because as far as I'm aware there's not a simple mathematical or excel formula capable of dealing with increasing contributions.

    Essentially I think you're going to have to cycle through each month individually somehow. You might be able to write a massive formula that does this (ie one monthly calculation, repeated over and over again with the different contributions drawn from multiplying a reference cell), but that would be so long winded i'm sure it would defeat the object.

    You might also be able to do something with iterative calculations, which will allow you to increase the monthly contribution within one cell and then set the number of iterations to the number of months you want to run it for. I'd need to play around with it to see exactly how that would work though.

    Other than that you're definitely in macro territory I believe.

    You could always cheat by having a hidden data sheet in the background and then draw the key bits of information forward into a presentable summary sheet.
    Last edited by ruperts; 26-09-2017 at 8:31 PM.
    • Apodemus
    • By Apodemus 26th Sep 17, 9:25 PM
    • 958 Posts
    • 769 Thanks
    Apodemus
    .You could always cheat by having a hidden data sheet in the background and then draw the key bits of information forward into a presentable summary sheet.
    Originally posted by ruperts
    Nothing cheating about that, it's often the most sensible way show clear output from complex spreadsheets.
    • RacingDriver
    • By RacingDriver 26th Sep 17, 9:29 PM
    • 60 Posts
    • 191 Thanks
    RacingDriver
    mathematical or excel formula capable of dealing with increasing contributions
    Originally posted by ruperts
    (n(n+1) / 2) X i works (where n is months and i is amount)

    e.g. 3 months starting at £100 and increasing by the same amount each month = (3(3+1) / 2) X £100 = (3(4) / 2) X £100 = 12/2 X £100 = 6 X £100 = £600

    which is the same as £300 + £200 + £100 = £600
    • bbeat65
    • By bbeat65 26th Sep 17, 9:30 PM
    • 17 Posts
    • 2 Thanks
    bbeat65
    Reply to 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
    Originally posted by RacingDriver
    Thank you very much! I think that does it. The way I tried using a row for every month was close, but there is a flaw. It came out 5.2 mil (over 240 months) instead of 5.7mil. I feel like it is in the way I have it compounding (monthly vs annual, or something) Here is the (simple) formula that I used. I just did 1 row for 240 months, 1 for 239 months, one row for 238, etc. If you have a moment, since you seem to really understand this stuff, could you reply, with what I did to have it come out different?

    A B C D E F
    1 8% 240 -100 $58,902.04
    2 8% 239 -100 $58,412.62
    3 8% 238 -100 $57,926.45

    A Row number only
    B interest rate
    C number of months the $100 is invested
    D amount invested ($100)
    E I didnt enter any value for E

    Formula =FV(B8/12,C8,D8,E8)

    I still look at your formula, and have a tough time "getting it". But, I sure do appreciate the ones, like you, who do. I have a question for you too - how long did it take you to figure that out? Just curious
    Last edited by bbeat65; 26-09-2017 at 9:39 PM.
    • RacingDriver
    • By RacingDriver 26th Sep 17, 9:39 PM
    • 60 Posts
    • 191 Thanks
    RacingDriver
    Thank you very much! I think that does it. The way I tried using a row for every month was close, but there is a flaw. It came out 5.2 mil (over 240 months) instead of 5.7mil. I feel like it is in the way I have it compounding (monthly vs annual, or something) Here is the (simple) formula that I used. I just did 1 row for 240 months, 1 for 239 months, one row for 238, etc. If you have a moment, since you seem to really understand this stuff, could you reply, with what I did to have it come out different?

    Month # Interest Rate Months Monthly Deposits Initial Deposit Total/Mo at end
    1 8% 240 -100 $58,902.04
    2 8% 239 -100 $58,412.62
    3 8% 238 -100 $57,926.45

    Formula =FV(B8/12,C8,D8,E8)

    I still look at your formula, and have a tough time "getting it". But, I sure do appreciate the ones, like you, who do. I have a question for you too - how long did it take you to figure that out? Just curious
    Originally posted by bbeat65
    I don't think the FV function works for this as FV only works with a fixed payment made each period, whereas you want it to increase each month.

    All my formula is doing is working out the addition of the number of months. So 12 months gives 12+11+10+9+8+7+6+5+4+3+2+1=78. Then from there it times 78 by £100 (A1), to give £7,800 and then it adds the monthly interest which is B1/12 (assuming B1 is the annual interest rate). I have included 1+B1 in the formula as otherwise the result would just be the amount of interest earnt in the month, minus the original deposits.

    In answer to how long it took me to figure out, not long... I new that n! gives increase number multiplied e.g. 3! = 3 X 2 X 1 so a quick google found the formula for additional increases, 3+2+1
    • ruperts
    • By ruperts 26th Sep 17, 10:00 PM
    • 677 Posts
    • 1,116 Thanks
    ruperts
    I'm not sure that formula works tbh. It seems to only add interest once, on the total amount contributed, whereas I think it should be adding interest at each period.

    Based on 240 months at 8% per annum with contributions rising by £100 each month I get a total of £5.329m doing it manually. Whereas RacingDrivers formula only gives me £2.911m.
    Last edited by ruperts; 26-09-2017 at 10:05 PM.
    • bbeat65
    • By bbeat65 26th Sep 17, 10:38 PM
    • 17 Posts
    • 2 Thanks
    bbeat65
    I'm not sure that formula works tbh. It seems to only add interest once, on the total amount contributed, whereas I think it should be adding interest at each period.

    Based on 240 months at 8% per annum with contributions rising by £100 each month I get a total of £5.329m doing it manually. Whereas RacingDrivers formula only gives me £2.911m.
    Originally posted by ruperts
    Hmmm! When I did it manually, I came up with 5.294M, and when I used RacingDriver's it came up with 5.784M. Funny how that works. I like RacingDrivers formula, and will use it. What I am using it for does not require any specific compounding, or anything. I think that must be where I went wrong, or where we differ. It's more the concept I am looking for. Thank you two for your help, and feedback. I am sure some day, I will become well versed in Excel too. And, maybe I will figure out how or why the numbers differ slightly.

    Have a great day, or night I guess! (I believe its about 10:30pm there).
    • bbeat65
    • By bbeat65 26th Sep 17, 10:41 PM
    • 17 Posts
    • 2 Thanks
    bbeat65
    I agree RacingDriver. That's why I had to have 240 rows to come up with the number 5.294 million (or whatever it was). Which was still a bit off from yours. I am not sure why. Your formula, and work is much appreciated, and I will use it. It's much simpler to use, once it's been entered. Thank you
    • bbeat65
    • By bbeat65 26th Sep 17, 10:47 PM
    • 17 Posts
    • 2 Thanks
    bbeat65
    Hmmm! When I did it manually, I came up with 5.294M, and when I used RacingDriver's it came up with 5.784M. Funny how that works. I like RacingDrivers formula, and will use it. What I am using it for does not require any specific compounding, or anything. I think that must be where I went wrong, or where we differ. It's more the concept I am looking for. Thank you two for your help, and feedback. I am sure some day, I will become well versed in Excel too. And, maybe I will figure out how or why the numbers differ slightly.

    Have a great day, or night I guess! (I believe its about 10:30pm there).
    Originally posted by bbeat65
    OOPS! Maybe I spoke too soon. I just noticed that I had 12% in RacingDrivers Formula. It came out to 4.82M after 240 months.
    • RacingDriver
    • By RacingDriver 27th Sep 17, 5:57 PM
    • 60 Posts
    • 191 Thanks
    RacingDriver
    Yes, I don't think my formula is compounding correctly. Back to my macro solution then...
    • bbeat65
    • By bbeat65 27th Sep 17, 7:31 PM
    • 17 Posts
    • 2 Thanks
    bbeat65
    Help!
    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
    Originally posted by RacingDriver
    I am embarrassed to say - I havent got a clue how to do this. It is way over my head. I am using Excel 2016. If you have some free time, and want to help a novice - thats great. The way I did it, by using 1 row per investment, resulted in 240 rows (as you know), and about 7 pages, if I want to print it. It would be amazing to have it accomplish the same thing in 1 row, and be able to change the amount, rate, years/months, and still come out with the correct answer.
    • RacingDriver
    • By RacingDriver 27th Sep 17, 7:35 PM
    • 60 Posts
    • 191 Thanks
    RacingDriver
    I am embarrassed to say - I havent got a clue how to do this. It is way over my head. I am using Excel 2016. If you have some free time, and want to help a novice - thats great. The way I did it, by using 1 row per investment, resulted in 240 rows (as you know), and about 7 pages, if I want to print it. It would be amazing to have it accomplish the same thing in 1 row, and be able to change the amount, rate, years/months, and still come out with the correct answer.
    Originally posted by bbeat65
    1) Open Excel

    2) Press Alt+F11 to open VBE

    3) Click "ThisWorkbook" on the left hand side

    4) Click Insert -> Module and paste my code above
    • bbeat65
    • By bbeat65 27th Sep 17, 7:52 PM
    • 17 Posts
    • 2 Thanks
    bbeat65
    Omg
    I open Excel, and hit ALT+F11, and my screen gets dimmer...lol. I am feeling pretty lame. I guess I have a lot to learn.
    • RacingDriver
    • By RacingDriver 27th Sep 17, 8:01 PM
    • 60 Posts
    • 191 Thanks
    RacingDriver
    Example here https://files.fm/u/mfj277bn
    • Aegis
    • By Aegis 28th Sep 17, 12:26 AM
    • 4,789 Posts
    • 2,909 Thanks
    Aegis
    1) Open Excel

    2) Press Alt+F11 to open VBE

    3) Click "ThisWorkbook" on the left hand side

    4) Click Insert -> Module and paste my code above
    Originally posted by RacingDriver
    Good thing you're not putting anything malicious in there! I once sent someone some code they insisted I wrote for them, and I coded it to do what the wanted but about 1% of the time to scream "Nooooooooo" when the file was closed. That seemed like a fair price for the work I did!
    I am an Independent Financial Adviser
    Anything I say on the forum is for discussion purposes only and should not be construed as personal financial advice. It is vitally important to do your own research before acting on information gathered from any users on this forum.
    • bbeat65
    • By bbeat65 2nd Oct 17, 4:29 PM
    • 17 Posts
    • 2 Thanks
    bbeat65
    Awesome!
    Thank You Racing Driver. I dont know how you did it. Some day hope to. But - it works, and I am grateful.
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

5,057Posts Today

9,569Users online

Martin's Twitter