FIREdreamer,
Sure. As you can tell, I enjoy talking about my spreadsheet ;-). I'll give you a general high-level outline and walkthrough; from there feel free to ask any clarifying questions you want, either on this thread or via PM.
I use an Excel spreadsheet.
Each row in the spreadsheet represents a particular month: row 1 in the spreadsheet represents today, row 2 in the spreadsheet represents one month from now, row 3 in the spreadsheet represents two months from now, etc.
Each column in the spreadsheet represents a particular piece of data that I need to use or calculate to get to my FIRE date. Here are most of my column headings with a description of what they are:
General - date :: This is the date that the row in the spreadsheet represents.
General - my age :: This is how old I will be on that date.
General - kid #1 age :: This is how old my oldest kid will be on that date.
General - kid #2 age :: This is how old my second kid will be on that date.
General - kid #3 age :: This is how old my third kid will be on that date.
Contributions - 401(k) contribution :: This is what my contribution to my 401(k) will be that month.
Assets - checking :: This is what my checking account balance is or will be on that month.
Assets - savings :: This is what my savings account balance is or will be that month.
Assets - taxable account :: etc.
Assets - traditional IRA
Assets - Roth IRA
Assets - 401(k)
Assets - kid #1 college fund
... there are several more that I'll leave out for simplicity's sake.
Liabilities - Mortgage :: This is what my mortgage balance is or will be that month.
Liabilities - Non-mortgage debt :: This is what my non-mortgage debt (credit cards and student loan) is or will be that month.
Liabilities - kid #1 college expenses NPV
...
Net worth :: Calculated based on the above assets minus the above liabilities.
Spending - Quicken :: What I am currently spending monthly based on my actual expenses for the previous six months. This comes from a separate tab.
Spending - Mortgage Interest :: What my mortgage interest is or will be for the given month.
Spending - Net :: Actual monthly spending minus mortgage interest. (This column is included because I assume I'll pay off my house before retirement and my mortgage balance is already being accounted for in the Liabilities section above.)
FIRE - Ratio :: This is a calculated field that represents my net monthly spending divided by my net worth for the given month. I multiply by 12 to annualize it.
FIRE - Age :: This is a calculated field. If the FIRE ratio for the current month is greater than 4% and the FIRE ratio for the following month is less than 4%, then this field shows my current age that month; otherwise it is blank.
The first row of my spreadsheet is special because it is actually where I am: the date is today's date, my age is my current age, my assets and liabilities are at their current values, etc. These values are either pulled from my net worth spreadsheet (on a separate tab within the same Excel file) or are calculated via formulas.
The second and subsequent rows are generally calculated from the cells above them and my assumptions (which come from yet a third tab in the same Excel file). For example, my mortgage balance for future months can be calculated based on the previous months value, the interest rate, and the payment. The date for next month is just the previous row's date plus one month. My future 401(k) balance is equal to the previous month's value plus my monthly contribution plus an amount of growth based on my assumed rate of return.
It may be helpful to know that you can compound monthly by using the formula (1+APR)^(1/12)-1 - that will convert an annual APR into the appropriate monthly amount.
There are some other Excel tricks as well in there. Hopefully that gives you the general outline. Again, let me know if you have questions...
2Cor521
)...
