Projecting values to future dates and other questions

[kkbmustang]

I have what I am sure is a very basic question. In planning our household documents and spreadsheets we want to look at and build a summary, snapshot and projections of our financial condition at regular intervals.

I have a couple of questions.

1.  How do we calculate and project what our investment balances as they exist now will be in say 5, 10 or 15 years from now.

2.  We were talking about doing a quarterly and annual balance statement for our household as well as a monthly income statement/cash flow statement. Our Mint and YNAB accounts would help with these but we want to make sure we are capturing the right data for tracking our progress. Also, need something that will show us years to FIRE and to semiretirement.

Any suggestions for excel spreadsheets for these? Anything else we should be capturing?  Thanks.

Edited to correct iPad typing errors.

[brewer12345]

1.  This is real tough, because investment returns are extremely volatile unless you invest in the most plebian instruments.  What I have done is to make a return assumption (7% as a very rough bogey) to project, but you always need to keep in mind that this is a very hazy guess and for any given year you will be wrong.

Can't help you with the cash flow, but balance sheet is a lot easier.  I keep an excel sheet with a tab for each account and a master/total tab that rolls everything up into one place.

[NinetyFour]

So if you have an investment of, say $50k, that you think will grow at 7% for, say, 20 years, you would calculate 50,000*(1.07)^20.  That way, you are always multiplying the new balance by another 7%.

[CDP45]

With excel/google docs/open office you can make a table so that if you start with $50k and then add $5k/yr to that in additional principal you can multiply it by your expected investment return. Doesn't do a great job of factoring in for inflation though.

[kkbmustang]

Thanks everyone. We're just trying to get a handle on our investments, etc. I'm trying to project out what we currently have in our 401(k)/IRAs to age 60 to see what it looks like for annual income after applying the 4% SWR. Then I have to figure out what it will be assuming we add to it each year. And, on top of that, I'm trying to figure out whether we should switch (all/some/how much?) to taxable savings as opposed to throwing more in the pre-tax savings. (Most of our investments are in pre-tax vehicles.)

Until I started reading this forum, we just maxed out our 401(k)s and didn't think about anything else.

[aj_yooper]

An addition to a spreadsheet is a simple business calculator, like my HP 10B Business.  It also does cash flow.