I'm not a statistician, but I am incredibly interested in personal finance, budgeting, and investment management.

I've been building a large spreadsheet of my personal expenses, savings, and investments. I have a large table that predicts investment returns year-over-year, up until age 65.

Currently the table models 10% return every year until age 65, but in reality, investments will become much more conservative over time (more CDs, bonds, etc.), so returns will decrease.

I could just create a linear formula starting at age 26 (current age), whereby every year until 65 the estimated rate of return decreases, until it eventually plateaus around 1-2% at age 65.

However, I think it would be a longer, decaying downward slope instead of a linear drop in returns.

Anyone have ideas as to how to model this? Something like exponential decay or smoothing?

Here's an image of my spreadsheet so you can see what I'm currently doing.

enter image description here


It appears that you are mostly interested in financial planning. Thus, you might be interested in Quantext Portfolio Planner Quantext Portfolio Planner, which is quite good at making long-term projections and charts regarding running out of personal retirement savings, and which assets may result in the greatest wealth. I have seen their code used for "commercial" rebalanced portfolio projections in quantitative finance(QF). Hoadley is another Excel-based portfolio planner that's pretty good - and has a lot of other economic modeling add-ons for Excel. (if you sent your message to the developers of Quantext and Hoadley, I am sure you would receive a very informative response -- which may expand your horizons).

Another dimension in the space of QF is the Markowitzian Theory of Portfolio Optimization, which focuses on e.g. quarterly rebalancing of a portfolio for maximizing returns, $\mu$, and minimizing volatilty, $\sigma$ -- see the efficient frontier. By far, the best portfolio optimization for personal use I have used is Smartfolio -- but this is mostly for rebalancing assets and risk projection (VaR). Last is FinAnalytica, if you want to step up to commercial grade.

| cite | improve this answer | |
  • $\begingroup$ Looks a little heavy for me, but I appreciate the direction. I'll do some research to see how this might help! I appreciate your input! $\endgroup$ – Jon Jan 5 '16 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.