Looking for an investment calculator but with specific inputs

Heir

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!

zepherin

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.

Korror

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.

Heir

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?

Korror

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

Daenris

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

Heir

Thank you, that's really helpful.