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.

4 Steps in Using Lipper Leaders to Choose Investment Funds

(Thanks to Anonymous for sharing this.)

Lipper Leaders is a free online service under the Thomson Reuters brand that lets users screen and sort investment funds (e.g., UITFs and mutual funds) in various markets. In this post, I will take you through Lipper Leaders, how you can use it to choose a fund that best suits you, and some of its limitations.

1. Choose a fund type and classification

"Universe" pertains to market or country, so for most of us it's the Philippines. 

Under "Asset Type," we have the common types of investment funds: equity, bond/fixed income, balanced (under "mixed assets"). Please note that there are no "alternative" or "commodity" listed funds for the Philippines.

"Classification" refers to sub-types under main fund classes, such as different currency denominations for bond funds and geographic reach for equity funds.

The main "Asset Type" and "Classification" combinations that you should be interested in are the following:

Equity - Equity Philippines = for (peso-denominated) funds that are comparable with the PSEi
Bond - Bond PHP = for peso-denominated bond funds
Bond - Bond USD = for US dollar-denominated bond funds
Mixed Assets - Mixed Asset PHP Balanced = for peso-denominated balanced funds

"Fund Family" lets you choose funds offered by a particular bank. If you're looking for the "best" fund in an asset class, it would be best to leave this as "Any Fund Family."

2. Select a time period

Choices are 10 years, 5 years, 3 years, and Overall. "Overall" means since the introduction of the fund. Since UITFs have only been introduced in 2005 (or thereabouts), you'll only get limited results for "10 years." In choosing a time period, I suggest looking at the results for both "5 years" and "3 years" for consistent top performers.

3. Choose "Lipper Leaders" that match you goals

"Lipper Leaders" is just a fancy term for "selection criteria," or fund characteristics that investors should look at in selecting investment funds. Funds are rated in terms of these Lipper Leaders using a scale of 1 to 5, with 5 being the best for investors.

The five Lipper Leaders are:

Total Return = the total (accumulated) percentage change in the NAVPU of the fund over your chosen time period. A score of 5 indicates a high Total Return.

Consistent return = returns adjusted for volatility or risk. A score of 5 indicates consistently high returns

Preservation = ability of the fund to prevent loss of capital. A score of 5 indicates high capital preservation

Expense = a reflection of the fund's cost structure. A score of 5 indicates low fees and costs

Tax Efficiency = involves effects on taxes, but is limited to US funds

In choosing the "best" fund of a particular asset type, you can just leave the Lipper Leader boxes as "Any" and just sort the results later

4. View and sort results

After configuring the options mentioned above, click "Display Funds" to view the results.

The default view is sorted alphabetically by fund name. You can click on any column header to sort by that criteria. Clicking on the "3 Year Return" header, for example, will sort the funds from the lowest to highest return in the chosen period. Clicking it again will sort the funds from highest to lowest, like this.

You can also get more details about a fund by mousing over the fund name:

One limitation of the system is that it does not have information for fund costs, so you would have to get the information manually from monthly fund reports. In choosing a fund based on performance, please don't forget to consider costs because they do matter (remember that front/back sales loads are not reflected in the NAVPU).

Another feature that I would have loved is the ability to download historical NAVPUs, which are necessary in portfolio construction/asset allocation (UITF.ph is not working anymore, anyone know alternative sources?).

Despite these limitations, Lipper Leaders is still a great way to choose funds from the multitude of offerings in the market. I'm sure that in choosing a fund, you may have other considerations than the ones listed above, such as convenience, customer service, or brand strength; still, looking at total returns is a good way to start.

JUST REMEMBER: Lipper Leaders screens and sorts funds based on past performance, and past performance is not indicative of future results.

Short Answers to Unanswered Questions: Insurance Concerns and BDO vs BPI Part "N"

DEAR INVESTOR JUAN

Dear Investor Juan,

I’m 24 years old and is earning a decent income. About last month, I came across your blog and I’m glad I did. I just finished reading your all of your posts. Ever since I started reading your blog, I got into serious thinking about my future and finances. I just started opening investments in BDO and even enrolled in EIP. I’m aiming towards financial stability and having counter measures against the unpredictability that future brings. I’m still learning how to effectively manage my finances and make my money grow.

You caught my interest on your last post about insurances. Now I’m thinking of signing up for one. But I don’t really know much about it. First thing, which insurance company should I signup for? What plan should I go for? I’ve read few of Sun Life Financial’s products information but can’t decide. I’m actually quite confused about this one and I’m hoping you could provide your view about this to help me get a sound decision.

I hope you’d be able to shed some light to this. Thank you for taking the time to read my mail.

jbsalts


Dear jbsalts,

Thanks for your support, it means a lot to me.

To answer your questions, first get details about your employer-provided insurance coverage, which I'm sure you already have, from your HR department.

Get more life insurance only if you have dependents and/or you are the breadwinner of the family, and you think your company coverage is not enough.

For health insurance, you can ask your HR department if you can upgrade your company-provided coverage to include your family members if they don't have health insurance yet.

Regarding Sun Life's Financial products and other variable-life instruments, you might want to check out some of my past posts about the topic. In a nutshell, I advice against such insurance and investment hybrids because getting insurance and investing in UITFs separately is usually cheaper.

I hope I was able to help. Good luck!


***

Dear Investor Juan, 

I'm a newbie investor, I've just opened an equity and bond fund in BPI last January and that's before seeing the track record of BDO vs BPI. I'm really tempted to transfer to BDO cause it looks like BDO fund managers are a lot better. The thing that turns me off a bit is the convenience factor. In BPI I can monitor my investment online, and can subscribe and redeem online as well. If I choose too I can also do that regular subscription plan. I was searching the BDO website (I don't have a BDO account) trying to look for any mention of an online facility for investment but I could not find any, so it looks like I can't monitor online and I would need to go to the branch. I guess I'm just being 'tamad' since as you mentioned I can just do go to bloomberg website and see the latest price and do some math everyday to see If what I'm earning (loss)... I guess I just wanted to get your opinion on the convenience factor of having an online facility for BPI, vs no online.

Prince


Dear Anonymous,

I share the sentiment that one important (maybe the only?) benefit of going with BPI is convenience. However, how much that advantage is worth is purely subjective. Like you said, you're tamad, so maybe the convenience is worth more to you than a person who is not so tamad.

To help you decide, maybe see how much better a comparable BDO fund has performed against your BPI UITF in percent terms ever since you started investing, then multiply that by your initial investment to get a peso amount. Then ask yourself: is the extra convenience provided by BPI worth this much? If yes, stick with BPI; if not, move to BDO. It's like if you need to buy a sachet of shampoo and there's a store beside your house that sells it for 6 pesos and another store 5-minutes on foot away sells it for 4 pesos. Is the extra 10 minutes of effort worth the 2 pesos that you save? Only you can answer that.

(Follow up)

Hi Investor Juan, me again. I did some number crunching using the BDO and BPI investment calculator, and If I've done it right (and the calculators are correct)then below are my result comparing with my 50K investment in BPI and BDO. Summary: Dates Covered (Jan-Feb 2013 & 2012-2009) BDO Bond Fund Ave. Yield: 5.496% BPI Bond Fund Ave. Yield: 4.754% Difference: 0.74% Summary: Dates Covered (Jan-Feb 2013 & 2012-2009) BDO Equity Fund Ave. Yield: 30.402% BPI Equity Fund Ave. Yield: 19.652% Difference: 10.749% My Assessment for myself: For the Bond fund: I'll stay with BPI (for now) The 0.74% advantage of BDO on the Bond Fund does not fully out weight the convenience factor of being able check/subscribe/redeem my bond fund in BPI's mobile app anytime I would like to. On the Equity on the other hand... the 10.749% advantage of BDO over BPI for the same amount of time covered (2009-2012 and Jan-Feb 2013) is VERY significant!!! I shall be opening a BDO account this month! :D