CAGR Calculation Sample for Stock N

1. Tabulate your cashflow

2. Sum up all outflow
Total investment: 3224.48+3224.48+4646.38+4377.63 = RM15,472.97

3. Sum up all holding share
Total holding share: 10000+10000+10000+24000+7500 = 61,500

4. Average cost per share: 15472.97/61500 = RM0.2516

5. Market price per share: RM0.530

6. Holding return (appreciation), from 2-Sep-06 to 22-Aug-11: (0.53-0.2516)/0.2516 = 110.65%
or, Holding return (appreciation), from 2-Sep-06 to 22-Aug-11: (32595-15472.97)/15472.97 = 110.65%
7. Sum up all your received dividend
Total dividend received: 290+386.9+585+630+631.8+891.75+889.25+889.25+889.25 = RM6,083.20

8. Holding return (dividend), from 2-Sep-06 to 22-Aug-11: 6083.20/15472.97 = 39.32%

9. Holding return (appreciation+dividend), from 2-Sep-06 to 22-Aug-11: 110.65+39.32 = 149.97%

NPV@15% = XNPV(0.15,F2:F16,A2:A16) =  RM7532.12 (accepted)

IRR = XIRR(F2:F16,A2:A16) = 32.12% p.a.

Financial Planning for a 30s Unmarried Woman

0) Background:

To illustrate how a plan is tailored to a working woman's future expectations, I profile a 33-year-old Catherina, unmarried Singaporean PR. She has an annual income of SGD33,000 and a CPF balance of SGD20,000. She bought a shop lot for MYR250,000 at Kampar and the property is now worth about MYR300,000.

She has saved about SGD10,000 in fixed deposits and has about SGD10,000 invested in unit trusts and cash value of MYR5,000 in participating life insurance bought from her brother.

However, she complained her expanse is too high, which the less saving can not bring her to anywhere. Luckily she has less debt: only an outstanding shop lot mortgage of MYR225,000, for which the mortgage loan is paid MYR1,744.42 in interest and principal every month (=PMT(7%/12,20*12,-225000,0)). Luckily, his brother Hui shares the burden with her about 50-50. In additional, her monthly expenses are about SGD2,342.

She works very hard in order to get more overtime allowance. However, her outflow has over taken her inflow. Luckily, special provident fund does help her to offset the deficit of the net cash flow.

She has no sense of retirement planning. However, she assumes that she will need about SGD2,300 (in today's dollars) every month to sustain her lifestyle after retirement. She also hopes to run her own business 3 years from now e.g. florist, snack stall, giftshop, child nurturing center at Malaysia. To do this, she figures that she will need to raise MYR80,000 (in today’s dollar).

Take assumption of 1SGD=MYR2.5.

1) Working Comment:

Catherina is too dedicated to her work, it doesn't mean bad. But, she can’t work forever like this 7*11. What-if she falls sick suffering from the critical illnesses, then her dream of having her own family or business will be jeopardized. Further more, the nature of her current job leaves her no quality time of thinking how to kick start her own business. For her age typically, she must be at least at the senior executive position, at the average monthly salary SGD3500 (total annual income divided by 12). She needs to focus on the working value that pushes her career up to another level. It is normal that she faces the political resistance as everyone does in the working life.

2) Cash flow analysis:

Catherina has single source income, which is only from her employment. 

Rental, food and shop lot loan are already consuming 68%. It is a heavy life living at Singapore.  

Cost of living comparison between Singapore, Singapore and Kuala Lumpur, Malaysia

	Singapore	Kuala Lumpur	Difference
Restaurants	[Edit]	[Edit]
Meal, Inexpensive Restaurant	9.83 S$	3.86 S$	-60.74 %
Meal for 2, Mid-range Restaurant, Three-course	39.68 S$	19.96 S$	-49.69 %
Combo Meal at McDonalds or Similar	6.57 S$	4.37 S$	-33.43 %
Domestic Beer (0.5 liter draught)	7.04 S$	3.87 S$	-44.94 %
Imported Beer (0.33 liter bottle)	8.21 S$	4.76 S$	-42.01 %
Coke/Pepsi (0.33 liter bottle)	1.26 S$	0.75 S$	-40.58 %
Water (0.33 liter bottle)	1.10 S$	0.48 S$	-56.01 %
Markets	[Edit]	[Edit]
Milk (regular), 1 liter	2.88 S$	2.07 S$	-28.12 %
Loaf of Fresh Bread	2.14 S$	1.15 S$	-45.92 %
Eggs (12)	2.37 S$	1.68 S$	-29.05 %
Fresh Cheese (1kg)	8.95 S$	7.24 S$	-19.14 %
Chicken Breasts (Boneless, Skinless), (1kg)	13.58 S$	5.52 S$	-59.33 %
Water (1.5 liter bottle)	1.84 S$	0.91 S$	-50.51 %
Bottle of Wine (Mid-Range)	22.77 S$	13.99 S$	-38.54 %
Domestic Beer (0.5 liter bottle)	5.41 S$	2.85 S$	-47.38 %
Imported Beer (0.33 liter bottle)	5.89 S$	3.77 S$	-35.98 %
Pack of Cigarettes (Marlboro)	10.99 S$	3.77 S$	-65.64 %
Transportation	[Edit]	[Edit]
One-way Ticket (local transport)	1.60 S$	1.02 S$	-36.06 %
Monthly Pass	46.79 S$	32.48 S$	-30.57 %
Taxi (5km within center)	8.86 S$	3.76 S$	-57.55 %
Gasoline (1 liter)	1.79 S$	0.78 S$	-56.26 %
Volkswagen Golf 1.4 90 KW Trendline (Or Equivalent New Car)	107,626.59 S$	71,008.77 S$	-34.02 %
Utilities (Monthly)	[Edit]	[Edit]
Basic (Electricity, Gas, Water, Garbage)	155.11 S$	53.42 S$	-65.56 %
1 min. of Prepaid Mobile Tariff (no discounts or plans)	0.26 S$	0.18 S$	-33.60 %
Internet (4 Mbps, Flat Rate, Cable/ADSL)	36.69 S$	44.19 S$	+20.43 %
Sports And Leisure	[Edit]	[Edit]
Fitness Club, Monthly Fee for 1 Adult	121.40 S$	61.90 S$	-49.01 %
Tennis Court Rent (1 Hour on Weekend)	10.38 S$	8.29 S$	-20.11 %
Cinema, International Release, 1 Seat	8.99 S$	5.21 S$	-42.07 %
Clothing And Shoes	[Edit]	[Edit]
1 Pair of Levis 501	129.65 S$	104.55 S$	-19.36 %
1 Summer Dress in a Chain Store (Zara, H&M, ...)	91.94 S$	93.97 S$	+2.21 %
1 Pair of Nike Shoes	127.79 S$	92.89 S$	-27.31 %
1 Pair of Men Leather Shoes	125.23 S$	93.42 S$	-25.40 %
Rent Per Month	[Edit]	[Edit]
Apartment (1 bedroom) in City Centre	2,505.23 S$	385.89 S$	-84.60 %
Apartment (1 bedroom) Outside of Centre	1,498.61 S$	247.58 S$	-83.48 %
Apartment (3 bedrooms) in City Centre	5,399.45 S$	845.45 S$	-84.34 %
Apartment (3 bedrooms) Outside of Centre	3,308.39 S$	578.60 S$	-82.51 %
Buy Apartment Price	[Edit]	[Edit]
Price per Square Meter to Buy Apartment in City Centre	16,816.86 S$	1,684.70 S$	-89.98 %
Price per Square Meter to Buy Apartment Outside of Centre	8,549.38 S$	1,117.20 S$	-86.93 %
Salaries And Financing	[Edit]	[Edit]
Median Monthly Disposable Salary (After Tax)	3,301.10 S$	1,404.76 S$	-57.45 %
Mortgage Interest Rate in Percentanges (%), Yearly	2.43	4.56	+87.66 %

Currency : SGD
Source: http://www.numbeo.com/

3) Networth Analysis

4) Financial Ratio

a) Liquidity Ratio = Cash, or near cash/Monthly expenses
	8.54	Month		good
(Comment: More than meet the benchmark band of 3 – 6 months of expenses)
b) Liquid Assets to Net Worth
	32.22%
(Comment: higher liquidity – may be a bit easy or more flexible in converting or restructuring assets in case of an immediate investment opportunity)
c) Solvency Ratio = Net Worth/Total Assets
	58.56%
(Comment: Relatively high solvency. Financial stability is ranked as strong)
d) Debt to Asset ratio = Total Liabilities/Total Assets
	41.44%			good
(Comment: allow to be higher if risk profile permits)
e) Saving ratio = Annual Saving/Take Home Pay
	8.63%			no good
(Comment: far to the national saving rate of about 30%. However, in this calculation, savings in insurance policies, car and housing loan repayments on principals are excluded. If included, the cash values of such payments will add to the ratio.)
f) Debt Servicing Ratio = Total Serving Loan/Total Inflow
	13.53%			good

5) Future Expectation

Current Liquid Asset	SGD 20,000.00
Saving Duration	3	year
Expected Today Amount	SGD 32,000.00
Inflation	3%	p.a.
Expected Future Amount	SGD 34,967.26
Investment Rate	10%	p.a.
PMT@ROI=8%	($3,010.42)		Failed as net cash flow < PMT
PMT@ROI=10%	($2,521.83)		Accepted as net cash flow > PMT
Action Item:
1. Seek Hui to invest SGD2522 annually in UT for at least 10%p.a. return

6. Retirement Planning

Step 1(A): Retirement income needed – Target replacement ratio (TRR)
Description: Catherina, 33 years old, she earns SGD 33000 p.a. Her yearly increment is 0.03. She seeks for the feasibility of maintaining her life style during her retirement age.
Age (Now)	33
Age (Retire)	60.00
Increment	3%	p.a.
Annual salary	SGD 33,000.00
His last drawn salary			SGD 71,167.51
Assumption:
TRR(50%, e.g. less commitment)
		50%	SGD 35,583.76
TRR(60%, e.g. child educational funded by own, bad health family members etc)
		60%	SGD 42,700.51
TRR(70%, e.g. loan not clear yet, child educational funded by own, bad health family members etc)
		70%	SGD 49,817.26
Note: Higher TRR produces higher security but high cost too. Thus, another method is studies.
Step 1(B): Retirement income needed – Expenses method
Description: Catherina's outflow is SGD 28102.61 with estimated inflation rate at 0.02 p.a.
Outflow (Now)	(SGD 28,102.61)
Inflation (Low)	2.00%	p.a.
Future annual expenses at age 60 year old.
			SGD 47,967.97
Step 2: Retirement Fund calculation
Let’s take future annual expenses at age 60 year old is SGD 49817.26.
Life expectancy (Malaysian/Singaporean: 75 years old, +10 year as buffer).
Medical development human life is getting more advances. Thus, it will be reviewed from time to time .
Two approaches for the retirement fund calculation: (A) principal liquidation and (B) Principal intact.
Life (Low)	75
Life (High)	85
Life (Decide)	85
ROI	8.00%	p.a.
Inflation	2.00%	p.a.
Step 2 (A): Retirement Fund calculation – Principal Liquidation
Retirement fund (without inflation), 2Ai
			SGD 574,331.13
Retirement fund (with inflation), 2Aii
			SGD 681,896.57
Step 2 (B): Retirement Fund calculation – Principal intact.
Retirement fund (without inflation), 2Bi
			SGD 672,532.99
Retirement fund (with inflation), 2Bii
			SGD 896,710.65
Conclusion: From 2Ai, 2Aii, 2Bi and 2Bii, it is recommended 2Aii, SGD 681896.57  realistically.
Step 3: Retirement Funding Calculation
Insurance/UT agent will based on the SGD 681896.57 to work out the annual payment in order to close the deal fast.
Product ROI	10%	p.a.
No other Resource, PMT p.a.
			(SGD 5,118.96)
			Impossible!
Net Investible Income@33	SGD 20,000.00
Current portfolio return	4.00%	p.a
Improved portfolio return	10.43%	p.a	Solver solved
Retirement Fund	SGD 681,896.57
CPF	2.00%	p.a
Net Investible Income@60@4%			SGD 57,667.37
Net Investible Income@60@10.43%			SGD 291,465.05
CPF@60@2%			SGD 390,431.52
Total Retirement Fund@60@Current Return			SGD 448,098.89
Total Retirement Fund@60@Improved Return			SGD 681,896.57
Shortfall/(Excess)@4%			SGD 233,797.68
Thus, NIA equals required fund, she may stop work after 60 provided her investment return improved from 4% to 10.43%.