Sunday, March 31, 2013

Retirement Planning with Excel, Part 1: How Assumptions Impact Cash Flows and Wealth

Two posts ago, I asked the question: Do you save enough for retirement? Based on our example "Popoy," it seems that saving 32% of a 30,000-a-month income would not be enough to cover retirement expenses.

Click image to enlarge
In the graph above, using our example we plot annual income (blue bars) and annual expenses (red bars) against the left vertical axis; to help with our analysis, we also plot accumulated income (green line), accumulated expenses (purple line), and total wealth (blue line) on the right vertical axis.

The graph shows what happens before and after you retire/stop working: before retirement, as income is greater than expenses, you accumulate wealth; after retirement, you stop earning and continue spending, which depletes your wealth. We see here that at around age 75, Popoy's wealth will have been depleted to zero, so he would have too look for alternative sources of income to finance his expenses for the rest of his life. Suffice it to say, the objective of any retirement or financial planning exercise it to be able to achieve zero or more total wealth at the end of the planning horizon.

We also pointed out in the previous post that our example lacks details that would make it more realistic. Let's take a look at some factors that some of you suggested (thanks for the inputs, everyone :)):
  • Inflation and interest on Popoy's savings (Anonymous)
    • Unless we want to look at real (i.e., constant-price) cash flows, we need to take price increases every year into account.
    • "Parking" savings in an interest-bearing instrument would boost income to a certain degree
  • Health and medical expenses (Sean)
    • Our example only considers basic, recurring expenses. As a person gets older, the likelihood of incurring substantial medical expenses increases.
  • Post-retirement lifestyle (mac)
    • A person's lifestyle of choice pretty much determines his or her level of expenses. In our example, we assumed that Popoy will maintain the lifestyle that he has today.
Apart from these, we also need to consider the following factors in our analysis:
  • Annual income increase
    • We can reasonably assume that income will increase by some rate every year, even if we don't get promoted.
  • Retirement pension
    • Apart from any retirement bonus provided by our employer, we should at least get a small pension from SSS or GSIS. For example, from the SSS website, members are entitled to a maximum pension of 7,500 per month, fixed.
  • Current wealth
    • People who have already started saving before age 30 would have a certain amount of "beginning" wealth.
We can thus incorporate the above factors in our analysis by using realistic assumptions. I made a simple "dynamic" Excel template where you can change inputs and see how cash flows and total wealth change correspondingly. You may download the file here.

For Popoy, let's use the following assumptions:

Age now: 30
Current monthly after-tax income: 30,000 
Monthly income multiplier: 13
Annual income increase (%): 4%
Monthly retirement pension: 7,500 
Current monthly expense: 22,000 
Annual inflation: 4%
Current wealth (net assets): 0
Annual investment return (%): 5%

Which leads to this:

Click image to enlarge
In this second graph, we see the profound effect that rising prices can have on wealth. Compared to the previous example, while bankruptcy occurs significantly later (80 years old vs. 75)  the wealth deficit at the end of the planning horizon is much worse (11.4 million pesos compared to 2.8 million). 

Using this tool, we see that Popoy needs to do something to make ends meet after he retires. If the goal is zero total wealth at age 85, what alternatives are available to Popoy and what set of inputs can lead to this goal? Also, remember that in our example, Popoy is single and is responsible only for his personal expenses. How would his future cash flows be affected if he is married, if he has kids or other dependents? How would cash flows change if our unit of analysis is a household rather than an individual? Are you happy with what you see if you plot your personal circumstances into the template?

If you have time in the next couple of days, please play around with the template, and see you can give some suggestions in the comments section below. We'll explore possible answers next week. 

Related Posts Plugin for WordPress, Blogger...