Archive | March, 2012

Planning for retirement using spreadsheet – BEWARE OF PENSION FUNDS

6 Mar

What is your today’s monthly expense?
Answer: 25000 per month
How much you need per month on 25th year from now, when you retire?

Type the following formula in your favourite spreadsheet software.
=FV(0.1, 25, 0, 25000)
It evaluates to “270867.65” per month i.e 32,50,411.78 lakhs per annum.

Let us say we get around 10% per annum for FD to get 32.5 lakh interest you need 3.2 crores.

Now how much you need to save per month to get 3.2 crores upon retirement.

Type the following formula in your favorite spreadsheet software.
=PMT(0.15/12, 25*12, 0, 32000000)
It evaluates to “9865.8” per month on an investment that would give 15% per annum.

BEWARE of pension funds which promise 25000 Per month pension 25 years from now. Which in today’s cost would have a purchasing power of 2300 Rs(assuming 10% inflation).

How to plan for long term expense using spreadsheet?

6 Mar

 

Let us take an example of kids wedding 25 years from now.

Let us assume it costs Rs 8 lakhs for wedding as per today‘s costs.

On an average if we assume an inflation of 10% year on year.

Wedding 25 years later would cost 86 lakhs.

How?

Type the following formula in your favourite spreadsheet software.
=FV(0.1, 25, 0, 800000)
It evaluates to “8667754.75” Rs.

Now how to save this 86 lakhs needed on 25th year via monthly saving.

Type the following formula in your favorite spreadsheet software.
=PMT(0.15/12, 25*12, 0, 8667764.75)
It evaluates to “2672.32” Rs per month for next 25 years on an investment which can give 15% per annum(compunded on an montly basis((15/12)% per month))

.
How can you find an instrument that gives 15%(compunded (15/12)% per month) per annum?

You can choose the one you are convenient with like GOLD, REAL ESTATE.

I *personally* prefer equity *DIVERSIFIED* Value research 5 star rated funds.
(FYI DIVERSIFIED POWER SECTOR FUND IS THE NONSENSE FUND IT IS A RISKY SECTORAL FUND ONLY IN ITS NAME YOU HAVE DIVERSIFIED) By Diversified I mean no sector bias fund.

I invest since 2004 May(Over 7.5 years) via mutual funds. My CAGR annual return is 17.7% per annum as of last week. All my investments were slow monthly accumulations via SIP.

In the last 7.5 years we had so many bubbles and so many bursts but still 17.7%.

Mutual fund/stock investing is like TEST CRICKET MATCH and should not be seen as 20/20 or one day match.

Play like Dravid you have a very high chance of success.

By regularly investing you gain from “Rupee cost averaging”. In fact you should pray your favourite god to make market to FALL so that you can accumulate more units/shares for the same money.

Just monitor on a regular basis by recording this to trackers like ValueResearchonline.com if you see the CAGR to be more than 25% after an year. You can sell 1 year older units and recycle the money.

FYI any gains made after 1 year(long term capital gains) on a equity mutual fund is tax free.

When you make such regular investment for longer time horizon may be when you are 80% of your horizon try selling this units to fixed deposits and other cash assets instead of waiting till the last day of 25th year.

If you have a short term horizon of less than 5 years better stay away from the markets.

Use FV and PMT of your spreadsheet for better financial planning.