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. 

Philippines Receives Investment Grade Credit Rating for the First Time Ever

IN THE NEWS

Yesterday, Fitch Ratings upgraded the credit rating of the Philippines to "investment grade," from "BB+" to "BBB-".

How did we react to the news? First, by giving each other virtual pats-on-the-backs and high fives.

Good job

Second, by buying stocks like crazy, leading to a 2.74% rise in the PSEi, ~5% price increases for "blue chip" stocks, and more than half of "top losers" being in the green.

But what does the credit rating upgrade really mean for the ordinary Filipino? The Rappler sums it up pretty nicely with this infographic.

One thing we have to watch out for is how fast banks and other financial institutions will adjust interest rates on consumer and household debt. If memory serves me right, they're too slow.

So yeah, the upgrade tells us that there are plenty of reasons to be excited and optimistic. We just have to try to be not too excited and optimistic.

Do You Save Enough for Retirement?

According to this article from Rappler, most likely not.

You can estimate how well you're doing easily enough. With your monthly disposable income (that is, your monthly salary minus taxes, SSS/GSIS, Pag-Ibig, and PhilHealth contributions) and usual monthly expenses, start by computing for how much you can save in one year. For example (all figures in pesos):

Monthly disposable income = 30,000
Annual total income (including 13th month pay) = 30,000 * 13 = 390,000

Monthly expenses:

• Rent and utilities =  10,000
• Food = 300 per day * 30 days = 9,000
• Transportation = 100 per day * 30 days = 3,000
• Total per month = 22,000

Total annual expenses = 22,000 * 12 = 264,000

Total annual savings = 390,000 - 264,000 = 126,000

Implied savings rate = 126,000/390,000 = 32%

Is saving 126,000 per year, or 32% of a 30,000 monthly income, good enough for retirement? Let's see.

Say "Popoy," a person with the above income and expenses, is 30 years old today, is planning to retire at 60, and is likely to live until age 85. Assuming he plans to spend the same amount--22,000 per month--from retirement til the day he dies, how much will his savings at 60 years old take him?

(126,000 per year * 30 years to retirement) / 22,000 per month = 172 months or 14 years

It seems that Popoy's saving habit will only take him 14 years into retirement, and his savings will fall considerably short of his needs. Are you doing better or worse than him?

Of course, the above example is very simplified as it ignores important factors that may alleviate or magnify the savings burden of a person like Popoy. Aside from those used above, what other factors should we consider in order to have a more realistic and useful analysis? I would love to hear your thoughts in the comments section below. At the end of the month, I will show you a model/tool that will help you come up with a better analysis.

Short Answers to Unanswered Questions: Advice for a College Student and Insurance Questions

DEAR INVESTOR JUAN

Dear Investor Juan.

I am Michael, a 2nd year college student and I am planning to invest some of my savings in a bank. What should I do then?

I am still ignorant about these finance stuff but I am really eager to know how to invest.

Thank you for helping me out. 

Michael


Dear Michael,

It's hard to answer your question without knowing specifics, but given that you're a second-year college student, I assume that:

1) You're still living with your parents
2) You don't have a job and your income just comes from allowance

Honestly, at your age and given the above assumptions, it would be best to just save and accumulate as much cash as you can--and yes, a simple savings account will do. Don't worry about not getting high
interest on your savings, that will come in due time, like when you get a job and have more disposable income.

As soon as you get a job after graduating, you can refer to these posts from a while back about getting started with investing:

http://www.investorjuan.com/2011/03/6-steps-guide-for-newbie-investors-part.html
http://www.investorjuan.com/2011/03/6-steps-guide-for-newbie-investors-part_28.html

I hope I was able to help. Good luck!


***

Dear  Investor Juan

I would like to ask your advice regarding HMO’s /Health Insurance. I have read a few articles regarding about it but I am still confused.

When is it necessary to have a health care plan / insurance? Isn't it enough to have a Life Insurance as well as Philhealth? Or would you advice to put the money on some investment instead?

Thank you very much. Your blog has been a lot help in straitening out my financial plan.

God Bless

rocloyd


Dear rocloyd,

When is it necessary to have a health care plan/insurance? In my opinion, we should be covered our entire lives, if possible. Work compensation packages usually include a decent health insurance
coverage, so you don't really have to worry about it if you're employed full time. If you're unemployed or underemployed, however, you have to make sure that you're covered with PhilHealth at least.

Life insurance is also usually a standard non-cash employment benefit. The need for it differs with a person's circumstances, however. For example, a person with a family would need it more than, say, someone who is single and is living independently.

And yes, to make it clear, I strongly suggest that you make sure you're adequately covered first (and have an emergency fund on top of that) before you invest in "risky" assets (such as stocks and
investment funds). Why? In a nutshell, because most likely you can only borrow at a higher interest rate than the return that you can earn from investments, so in case of emergencies and other situations
that require a significant amount of cash, you'd want debt to be your last resort.

I hope that makes sense. Good luck!

Unique Risk, Systematic Risk, and Diversification

PERSONAL FINANCE 101

Risk is one of the things that we should be mindful of when we invest, particularly in terms of the "riskiness" of investment alternatives that are available to us, our capacity to take on risk, and our attitude towards risk. "Investment risk" may be interpreted a number of ways, and one widely-used measure is the volatility or standard deviation of returns. "Risky" investments such as stocks provide highly unpredictable returns, with high standard deviations, while "safe" investments like money market funds have more stable, low-standard deviation, returns. Of course, the trade off is that riskier investments have higher average or expected returns than safer investments. The difference in how "risky" and "safe" investments behave can be seen in the example below, where we plot the stock price movement of Ayala Land (ALI) in the past 5 years and compare it with BDO's Peso Money Market Fund (BDOPMMF).

ALI and BDOPMMF, past 5 years

When we invest in individual stocks, as many investors in the middle of a bull market (such as the one we have now) seem to be very tempted to do, we expose ourselves to two kinds of investment risk: unique risk, risk that is unique to a particular industry or stock; and systematic risk, risk that affects all securities of one type or all securities in a particular market.

Nobel laureate William Sharpe (remember the "Sharpe Ratio"?), in his 1972 paper "Risk, Market Sensitivity, and Diversification," demonstrated that by simply adding more stocks to a stock portfolio, an investor can significantly reduce and eventually eliminate unique risk (or "non-market" or "diversifiable" risk).

A point will be reached, however, where adding another stock will not make a difference anymore. At this point, all that remains is systematic risk, or what Prof. Sharpe refers to as "market risk"; here, the portfolio, for all intents and purposes, is the entire market.

So when we pick stocks, we needlessly subject ourselves to unique risk, and that is not very "prudent" investing. As Prof. Sharpe has demonstrated in his paper, risk-wise we are better off holding the "market" portfolio, or in practical terms, any equity or index fund.

ALI, BDOPMMF, and the PSEi, past 5 years

Warren Buffet was once quoted as saying:

Diversification is a protection against ignorance. It makes very little sense for those who know what they're doing.

So if you can truly and honestly say that you know what you're doing, or if you have access to exclusive (i.e., "insider") information that lets you take advantage of market imperfections, then by all means, pick stocks or day trade or whatever. If you can't, then just buy a low-cost UITF or mutual fund of your choosing and sleep more soundly knowing that systematic risk is all you have to worry about.

Asian Stocks Drop the Most in Eight Months Amid Cyprus Bailout

IN THE NEWS from Bloomberg

From Bloomberg Businessweek

This may be big news for some, and not so big for others

The MSCI Asia Pacific Index dropped by 1.89% today, the most in eight months, just after the announcement of an EU bailout of the island-nation of Cyprus. The bailout was granted under the condition of a one-off tax on bank deposits: 6.75% for deposits of less than 100,000 euros and 9.9% for deposits of more than 100,000 euros.

The PSEi was down 1.78% for the day.

News of the bailout, perhaps not surprisingly, was met with outrage and some degree of panic among the Cypriot public. Numerous accounts of people queuing in front of ATMs to take their money out raise the possibility of a widespread bank run in the coming days.

Be sure to keep your eyes peeled for the latest news about the bailout and its effect on the global economy in the coming days.

Reporting Income and Wealth Inequality: The Right Way or the Highway

I completely agree that income and wealth inequality are serious problems that need to be addressed, or at the very least, discussed. So I wholeheartedly support efforts to make the public more aware of the problem, such as this recent article by Interaksyon. What I don't like is using facts or statistics inappropriately to get one's point across or push a particular agenda.

Case in point.

The article refers to a presentation by former economic chief Cielito Habito which shows that:

In 2011 the 40 richest families on the Forbes wealth list accounted for 76 percent of the country's gross domestic product (GDP) growth. This was the highest in Asia, compared with Thailand where the top 40 accounted for 33.7 percent of wealth growth, 5.6 percent for Malaysia, and just 2.8 percent for Japan.

While I won't go so far as to say that Prof. Habito was wrong (I have the utmost respect for him), I do have questions, and maybe some reservations. How did he arrive at 76%? It is important to ask this question because the statement implies that the rest of the country--the 100 or so million minus the members of the Forbes' Top 40--"account for" 24% of the country's GDP growth.

Going by how the statement was worded, it seems that 76% comes from getting the total wealth of the 40 richest families in the Philippines (based on the 2011 Forbes list) and dividing the sum by the change in GDP in 2011, or some similar wealth-to-income comparison (you can try using the Forbes data and this chart from Google for a ballpark replication). If you think about this method for a moment, you'll realize that it's not appropriate because the total wealth of all people in the Philippines is not equal to the change in GDP, or even to the total GDP in one period. Wealth is not the same as income (one can interpret GDP as the income of a country in a particular period); income is what one earns every period and wealth is essentially accumulated income.

The article further compounds the issue by saying:

According to the Forbes 2012 annual rich list, the two wealthiest people in the Philippines, ethnic Chinese magnates Henry Sy and Lucio Tan, were worth a combined $13.6 billion. This equated to six percent of the entire Philippine economy.

I take it by "Philippine economy," the author meant "GDP." Here, the wealth-to-income comparison--and thus the misuse of data--is clearer. Google quotes the Philippines' GDP in 2011 to be $ 224.75 billion, so 13.6/224.75 = 6%, which is exactly what the author cites as proof for her thesis. But again, the comparison does not make sense! It does not mean that the wealth of the rest of the Philippine population sans Henry Sy and Lucio Tan is 94%!

My point is that if you want to highlight wealth inequality, then do it correctly: compare the wealth of individuals or a group of individuals to the total wealth in the country. You want to discuss income inequality, then compare income to income.

Income and wealth inequality are real problems not just in the Philippines, but in many parts of the world as well. If you want to use statistics to raise awareness about the issues, you owe it to your readers to do it the right way.

Here's one way of doing it.

Or using data from all around the Internet:

Total wealth in the world: $ 200 trillion
The Philippines' share of world wealth: 0.67%
Implied total wealth of the Philippines: $ 1,340 billion
Net worth of Philippines' Forbes' 40 richest: $ 47.43 billion
Percentage of Philippine wealth owned by Forbes' 40 richest: 3.5%

Not as exaggerated as the numbers used in the article, but still fully supports the case for wealth inequality. And this time, 3.5% means something.