Due to a security update, you may have to reset your password. Don’t panic, nothing has gone wrong and your password is safe. If you don’t have access to that email, send Tube a message at [email protected] More info here: https://status.vanillaforums.com/incidents/2zdqxf3bt7mj
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.

Looking for an investment calculator but with specific inputs

HeirHeir Registered User regular
For my kiddos' 529 plans, I contribute a set amount each month. On a yearly basis, that monthly amount increases by a set amount. I'm having a hard time finding a calculator (or writing up an excel formula) that will help me estimate a general amount I'll have saved up in ~15 years or so. I also have an end goal in amount of money I wish to have saved up for each child for college.

Here's an example of what I'm trying to figure out:

Conservatively estimating a 5-6% rate of return.
First year, I contribute $100 a month
Second year: $200 a month
Third year: $300 a month

And so on and so forth. Eventually I'd assume that monthly amount would cap out so I don't save too much.

For the life of me I can't find a good calculator for such a thing where I have an end amount in mind, and a set yearly interval where I increase the amount contributed. Almost everything I find only assumes an x% increase in contributions, which isn't really relevant here.

Any help here is appreciated for sure!

camo_sig2.png

Posts

  • zepherinzepherin Registered User regular
    edited December 3
    Year 1 1200
    Year 2 2400
    Year 3 3600
    Etc

    When you get to year 15 you are up to 18000. Is that something you can handle?

    Doing it by hand at 5% you’ll end up with roughly ~$213,830 for 6% ~$232,654

    That doesn’t seam quite right, I’ll double check from my laptop tomorrow, it’s hard to keep it straight while I’m shitting with a cellphone.

    zepherin on
  • KorrorKorror Registered User regular
    You can always take the result of (15 years at $100 a month) + (14 years at $100 a month) + (13 years at $100) and so on.

    Battlenet ID: NullPointer
  • HeirHeir Registered User regular
    zepherin wrote: »
    Year 1 1200
    Year 2 2400
    Year 3 3600
    Etc

    When you get to year 15 you are up to 18000. Is that something you can handle?

    Doing it by hand at 5% you’ll end up with roughly ~$213,830 for 6% ~$232,654

    That doesn’t seam quite right, I’ll double check from my laptop tomorrow, it’s hard to keep it straight while I’m shitting with a cellphone.

    I would assume at some point the increase would stop as I would be saving enough to reach the amount I'd want. It also assumes my wife and I slowly increase the amount we make each year as we progress through our careers.
    Korror wrote: »
    You can always take the result of (15 years at $100 a month) + (14 years at $100 a month) + (13 years at $100) and so on.

    This is a great idea, might try that. How would I calculate the 5-6% return on top of that though?

    camo_sig2.png
  • KorrorKorror Registered User regular
    Heir wrote: »
    zepherin wrote: »
    Year 1 1200
    Year 2 2400
    Year 3 3600
    Etc

    When you get to year 15 you are up to 18000. Is that something you can handle?

    Doing it by hand at 5% you’ll end up with roughly ~$213,830 for 6% ~$232,654

    That doesn’t seam quite right, I’ll double check from my laptop tomorrow, it’s hard to keep it straight while I’m shitting with a cellphone.

    I would assume at some point the increase would stop as I would be saving enough to reach the amount I'd want. It also assumes my wife and I slowly increase the amount we make each year as we progress through our careers.
    Korror wrote: »
    You can always take the result of (15 years at $100 a month) + (14 years at $100 a month) + (13 years at $100) and so on.

    This is a great idea, might try that. How would I calculate the 5-6% return on top of that though?

    I assume you have a investment calculator capable of doing that, just add up all the results to get your total. The math is too annoying for me to do by hand but here's me using a investment calculator assuming 15 years, $100 increasing by $100 every year up to $500 in year 5 and %5 return.

    ($100 for 15 years) = $26,729
    ($100 for 14 years) = $24,260
    ($100 for 13 years) = $21,911
    ($100 for 12 years) = $19,676
    ($100 for 11 years) = $17,551
    Sum = $110,127

    Battlenet ID: NullPointer
  • DaenrisDaenris Registered User regular
    edited December 3
    Shouldn't be too hard to set something like this up in excel.

    I set up a sheet with 6 columns (though the first two are just informational)
    Year, Month, Invest, Subtotal, Interest, 0
    The 0 on the last column is just to make the formulas consistent.

    Fill in year and month values as far down as you want to go.
    In the Invest column you enter your monthly investment (this assumes it's added at the beginning of the month).
    In subtotal enter the formula =F1+C2
    In the Interest column enter the formula =(0.05/12)*D2 where 0.05 is 5% interest, so alter as you want to. This represents the monthly interest amount, and so is equivalent to compounding monthly.
    In the 0 column, starting in F2 enter the formula =D2+E2 to get the total amount you'd have at the end of that month.

    Drag all the formulas down as far as you want to go and then play with your monthly investment amount.

    I compared it against this just to confirm my basic calculation was fine. https://www.bankrate.com/calculators/retirement/investment-goal-calculator.aspx with 0 starting value, $100/month at the beginning of the month, 5% interest for 5 years, compounded monthly. That site reports $6,829, while my excel sheet says $6,828.944152


    Daenris on
  • HeirHeir Registered User regular
    Thank you, that's really helpful.

    camo_sig2.png
Sign In or Register to comment.