<koff><koff> Unker checking in
For the same TIME ENTERED and EXIT (green), yup simple calc for 1 year, same ENTRY and EXIT time, is the CAGR / compounded pa.

For the different TIME IN/OUT/more than or less than 1 year, er.. not calculatable to me using simple calc coz time value of $ is not comparable, thus, best to use Excel's XIRR() function. i'm a calculator baka, too dependant on Excel

This post has been edited by wongmunkeong: Aug 23 2012, 08:36 AM

These are exciting times we're living in - woohoo!
First time in my life i'm excited over the fear and pessimism surrounding MY, SG, CH & US markets coz semi-prepared mar (i don't think anyone's 100% prepared )

When all else is too iffy to make a decisive judgement-call, IMHO, best is to fly-blind & go back to basics (money management, risk management and asset allocation). Just a thought

---

Added on August 23, 2012, 2:52 pmmessage (U've my Yahoo & Skype mar) or PM me lar.
Cannot be that hard to use mar XIRR, UNLESS U only have the HOME EDITION of Excel, which doesn't support XIRR

This post has been edited by wongmunkeong: Aug 23 2012, 02:53 PM

yeah.. the problem is WHEN and HOW DEEP a discount/lelong

er.. AngelOnFire (where's my CO2 fire extinguisher ), i'm no Oracle of Ulu Klang wor, thus opinions & POV only ar

60% Equities (i'm assuming a nice mix of normal stocks + REIT stocks + properties) & 40% Cash (no bond funds or gov bonds?)
Just to share - i too am having a similar mix - give or take 3%+/- as the market gyrates
ie. was 40%ish Fixed Income & 60%ish Equities last 2 months
now is 37%ish Fixed Income & 63%ish Equities due to my programmatic/scheduled investments + crazy SG & MY REITs run up.

Opinions / PoV
1. If U do mutual funds/unit trust (i guess that's why U are posting in this thread/topic), better have some of your Fixed Income in bond funds, which can be SWITCHED to equity funds of your choice to rebalance or going into lelong buying.
Reason: Bond funds generally beats FD or 2.5%pa interest.
For your consideration - my sh*ttiest bond fund transaction is giving me about 4%+pa net now (keep in mind, lose 0.25% straight away when moved into bond fund) VS my best bond fund transaction is showing 7%+pa net profit.
BTW, used to show 9%+pa last year and two (ie. 2011 and 2010).

2. Adding 5% stock exposure every month?!
er.. U taking into account your monthly savings boh as a replenishment to your Fixed Income / Cash?
If every month U taruk 5% into Equities.. i'd say U'd totally be out of ammo in 8 months time.
IF SHTF and lelong in month 10 how? U'd be totally lopsided in your Asset Allocation, with no ammo to scoop up the lelong equities.

3. SG Stocks
I'd still suggest going for REITs and Dividend Stocks in SGX coz it's TAX FREE.
The gross DY% U see is the DY% U get
I'm awaiting for things like APB (ran up way too high during the fight to take over), ARA, CEREBOSPAC, SEMBMAR & M1 to be of value to me.
Only holding REITs now in SGX as it was easier to compute VS the normal stocks above where multi-years' ROEs, D/Es, EPSs, etc. had to be hunted down - not easily available vs US markets.

4. China Stocks
No idea on direct China stocks - way too little solid data like multi-years ROE, D/E, EPS, etc unlike US markets
Personally - i'm value cost averaging & dollar cost averaging into PFES for China exposure
+ waiting for Shanghai Index to fall below 1900 before starting my value scooping (2 buckets, waiting - 1 bucket at a time)

5. General Asset Allocation
During choppy and totally unpredictable times, i'd suggest a 35% to 45% Fixed Income asset allocation (not cash only, mixed cash/bonds/bond funds).
This generates $ for us no matter what happens
AND is also a cache of ammo to be drawn on after SHTF.

Personally - my target in current an environment is to keep about 38%+/- in Fixed Income,
as compared to my normal zzz 33% (1/3 of AA),
and to draw down to 27%+/- for lelong equity binging usage.

Why i'm not targeting 50% or 80% in Fixed Income?
I'm assuming:
+1/5 years kaka happens but to what degree, no idea AND inflation is a near sure thing (as compared to the probability of deflation) thus i need something to keep up and beat it
+the major developed economies seemed to be printing/creating money out of thin air to keep things humming along. Not just US - UK, EU, JP too + CH's "easing monetary policies", thus to me having 80% in Fixed Income is akin to committing inflation suicide
+my emergency buffer of 1 year is NOT part of the Asset Allocation, thus i can draw down my Fixed Income portion of AA quite a bit if forced to

Just my personal thoughts yar, no crystal balls nor 100% gospel truths

---

Added on August 23, 2012, 4:03 pmheheh - yes i did, in end 2008 / 1st quarter 2009 + endish 2011 and yes, i was called crazy even by some investing friends.
well, qualifying it - didn't JUMP in lar, just moved like about 5% to 15% of investable assets into Equities.

This post has been edited by wongmunkeong: Aug 23 2012, 04:03 PM

er.. bro, 2008 fiasco, PBbank fell to $7.50 or less leh.
Nah, not based on my old foggy memory - i actually bought at $7.50

Wei.. mana i said fire ALL CYLINDERS ar? Start "biting" or getting in yes lar, crazy meh ALL CYLINDERS heheh

Perhaps better clarifications:
My "sure buy in / use up at least 80% of my cache for China" is 1,800 and less .
My "yummy.." is at 1,900
My "ooo backside start itching" is less than 2000

Aiya, old unker like that wan lar - got several levels of wants / aims / goals, just like my net worth goals and my investable assets goals

---

Added on August 23, 2012, 4:19 pmAhh.. ChartNexus? U set it for "adjust price for rights issue and stuff"?
FYI - PBBank had some "X for Y shares held" shot out to existing holders somewhere between 2009 and 2011 (sorry ar - memory's a bit old & fuzzy )

Based on my buy/sell - i had PBBank executed:
10/03/2009 Bought $7.500
26/09/2011 Sold $11.780 (trailing stop loss hit during EU fear shake-up)
14.28% (of my cost) net dividend received

Holy cow.. 70(FI) / 30(Eq) and still generating 7%pa?!
Must be your AmDynamic Bond fund pulling up the socks cow cow leh

This post has been edited by wongmunkeong: Aug 23 2012, 04:23 PM

Aiya, such a simple function - make sure yr Excel is NOT the basic or home edition, ie. need to be MS Office Standard or Professional edition, then Skype or YM me lar.
Accumulate goodwill until hit bah kut teh or subway bread, then i withdraw

This post has been edited by wongmunkeong: Aug 23 2012, 06:33 PM

Knowing U (numbers person), U'd do it
To get detailed per transaction's return (each transaction), total fund's returns (each fund) AND total investments' returns (which i think U have now).

(Value - Cost) / years aint too accurate one U've been running several years. Trust me - been there & hit by that before.
CAGR or compounded pa. is the holy grail mar right? Comparable to "no risk" FD rates & even mortgage rates (eg. to pay off mortgage or invest).

Me - i just have the first two only, and can't compute the total coz i keep pumping $ into my "investable assets", screwing up my "total" tracking

This post has been edited by wongmunkeong: Aug 23 2012, 06:42 PM

Whoa.. a professional property flipper
I'm still toe-in/toe-out in properties - just sold my 1st investment property (homes sold 2x liao ), was for rental but since someone is offering to pay me more NOW vs X years accumulated rental+cap appreciation+equity built-up in mortgage, why not Found i dislike landlording - i'm a lazy baka.
Where/when can i pick your brains (Bah Kut Teh / Subway enticement to loosen your tongue) on flipping properties ?

Thanks for the info on SembCorp & CerebosPac - i was wondering what the heck was happening to CerebosPac recently spiking like mad. Dang - why lar, 2 out of 4 or 5 SGX targets gets taken over (chased up already)...

Looks like U & i share similar thinking in terms of hedging our bets due to crazy QEs by central bankers. I hope Pink and the rest of the gang that are hoarding their ammo don't keep their ammo dry too long IF the lelong doesn't happen soon.

---

Added on August 24, 2012, 1:30 amOoo... free food.. when when? (yeah yeah, i'm a cheap bast*rd - free food wor)

Technically, 2007 & 2010 version has more rows and columns, thus less limitations. In addition, XLSX (native SAVE file type of Excel 2007 & 2010) is a compressed file by itself, thus much smaller than XLS.

Yes yes, it's a bloody pain to get used to when moving from Excel 2003 - imagine a power user reduced to googling basic functions and menus (as in where the heck to access them).

---

Added on August 24, 2012, 1:43 amer.. SWITCHING? in short, total value (ie. value SWITCHed OUT from Fund A) becomes total cost of Fund B.
I've 1 worksheet for HELD + 1 for SWITCHED/SOLD (ie. EXITed transaction)

Thus, in when i SWITCH:
a. From SWITCHed Out in HELD worksheet
minus the units and avged cost (if partially SWITCHed out)
or
delete row (if whole transaction was SWITCHed out)

b. In SWITCHED/SOLD worksheet
Capture date of buy transaction,
costs of units (units * average cost of that transaction, keeping in mind, i re-distribute dividend reinvested units to all buy transactions still held )
VS
date of SWITCH/SOLD,
$ gotten for it (the SWITCHed out value)

c. to SWITCHed In in HELD worksheet
new row with date of purchase, total cost & units

er.. i think easier to see screenshots eg BUT i'm currently on my spare PC in my bedroom (insomnia due to over eating heheh), no access to my files. Will snapshot and add here tomorrow.

This post has been edited by wongmunkeong: Aug 24 2012, 01:46 AM

Ah.. cool, U got my gibberish without screenshots

Well, in my situation, i usually (99.99%) SWITCH only between Equity <--> Bond funds, to either lock-in some profits into bond funds OR enter into equity market. Thus the SC "paid" by the first time Equity fund in (loaded units) is not comparable to Bond fund SC
ie. the 3% or 5.5% (or my actual cost of 1.5%+ or 2.75%+) for Equity funds VS 0.25% Bond Funds doesn't my tracking much as Equities' returns <> Bonds' returns.

I guess one will have COMPARATIVE problems when U often SWITCH between Equity to Equity or Bond to Bond funds only.
ie. SWITCHing between same class causes comparison issues during one's review of transactional / fund performances if the 2nd Fund "does not pay SC" as the 1st Fund paid SC and is loaded already.

If i were to accumulate all SCs as some sort of overhead - how to absorb to each transaction and at what basis? especially when Equities' and Bonds' SC varies wildly.
If i don't absorb these overheads, how to calculate CAGR as close as possible? only simple calc & averaging can be done - unless there are some other methods of calculation lar (pls share share - maths baka here).

This post has been edited by wongmunkeong: Aug 24 2012, 08:18 AM

Wah...
Personally i just track my personal investments' CAGR (ie. costs-to-value), not the funds' performance NAV-to-NAV, coz NAV-to-NAV tracked & published by fund houses mar

it is - if i take a specific transaction and date, then map to fund house's data & get FROM.. TO.. CAGR
anyhow, i'd still factor in total costs per transaction, rather than have another set of tracking for cost-value, in my case - lazy lar, too many worksheets liao

it's bloody fun when U see your net worth AND investable assets grow at x.xx% pm
And during yearly reviews.. and in 3 to 5years time, go.. whoa... i didn't know something that simple can have such impact...

Yes yes, i'm a gamer at heart - "hitting the next level" and seeing the "stats grow" monthly, quarterly, yearly, etc. (RPG gaming & levelling up anyone?) is addictive and an incentive for me to keep "plugging along"

er.. withrawals/switching outs doesnt mess (much) from mine coz i've a separate "SOLD/SWITCHED OUT" worksheet to capture realized gains/losses
VS
held & paper gains/losses
- bad habit carried forward from my stock trading days (ie. paper vs realized)

See-food always hungry wan , hyper-metabolism, U won't believe how much i eat a day
See, Pinky know also wielding "the XIRR force" easily now - just need to structure the cash flow out & in/current total value with dates.
Need to mix it with IF() for more powerful and automated leverage <does a Jedi hand wave> heheh - er.. i hope i'm not turning this thread/topic into an Excel tutorial

Yo TakoC,

Dividends/distribution & XIRR().

IF REINVESTED
Me - i redistribute the units into the respective transactions' units
eg.
Distribution 1/3/2012 100 units
a. 1/1/2012 Bought 1000 units with $X,000
b. 1/2/2012 Bought 1000 units with $Y,000
c. 2/3/2012 Bought 3000 units with $Z,000

Thus, the 100 units reinvested will be absorbed into (a) & (b), making my data
a. 1/1/2012 Bought 1000+100*1000/2000 units with $X,000
b. 1/2/2012 Bought 1000+100*1000/2000 units with $Y,000
c. 2/3/2012 Bought 3000 units with $Z,000


IF TAKEN OUT / CASH OPTION
Me - i minus the cost of the respective transactions' costs (ie. these transactions cost me lower as i milked the cash out of them now mar)
Yar yar - this is NOT exactly an accurate tit-for-that as an impact to XIRR.
The more accurate method would be to ADD the $100 to the TOTAL VALUE of the fund.
eg.
Distribution 1/3/2012 $100
d. 1/1/2012 Bought 1000 units with $X,000
e. 1/2/2012 Bought 1000 units with $Y,000
f. 2/3/2012 Bought 3000 units with $Z,000

Thus, the $100 received will be taken out of (a) & (b), making my data
d. 1/1/2012 Bought 1000 units with $X,000-$100*$X,000/($X,000+$Y,000)
e. 1/2/2012 Bought 1000 units with $Y,000-$100*$X,000/($X,000+$Y,000)
f. 2/3/2012 Bought 3000 units with $Z,000

---

Added on August 24, 2012, 6:50 pmautomation of TOTAL CURRENT VALUE AND DATE
eg.
Data entered:
___COL A___; COL B_; COL C___; COL D
__DATE BUY; COST__; UNITS____; CURRENT VALUE
1: 1/1/2011; $X,000; 1000 units; =(C1*NAV)
2: 1/2/2011; $Y,000; 2000 units; =(C2*NAV)
3: 1/2/2011; $Y,000; 2000 units; =(C3*NAV)
4: <empty>
5: <empty>

Thus, in E1, i'll have something like
=IF(B1>0, -B1, SUM($D1: D1) ), which gives me a negative of B1, as cash outflow

copied down col E, thus by E4, i'd have =IF(B4>0, -B4, SUM($D1: D4) ), which automatically gives me the TOTAL CURRENT VALUE of the whole thing

my XIRR would then be going =XIRR(E1:E5, A1:A5)
All the above for 1 fund if i want to know my "per fund" CAGR

Phew.. er.. hope U can grasp by lousy illustration above. Hard to snapshot my spreadsheet to show the above coz a sh*tload of other columns with triggers to SWITCH and how much to SWITCH and stuff, all inter-twined heheh.

This post has been edited by wongmunkeong: Aug 24 2012, 06:50 PM

After Sep? On - heheh, Sep i gotta fly all over for 2 weeks+ for work

Ok - looks like your Excel version DOES support XIRR() coz if not, U won't even get #NUM hehe.

Simple idea
Think like this - cash inflow OR cash outflow?
eg.

8/18/2011 -20,000 (invested in A)
11/20/2011 10,000 (got cash distribution from A)
3/12/2012 0 (huh? this shouldnt be here - kill this)
3/16/2012 -12,000 (invested more in A)
3/30/2012 45,000 (current value of total held in A)

This post has been edited by wongmunkeong: Aug 24 2012, 09:39 PM

See, TakoC also picked it up sup sup water.
Think cash flow -(ve) or +(ve) and total current value at the end

---

Added on August 24, 2012, 9:42 pmand that is why one can couple IF() to automatically set a column used by XIRR to -ve or +ve and also do other funky stuff

IF() can be coupled to SEVERAL NESTED IF() and/or AND() & OR() or both. er.. i think we better take this to the BKT or SUBWAY table before mods come after our butts for turning this Funds thread/topic into an Excel tutorial

Lots of changgih stuff can be automated, even grabbing data from websites and auto updating all your value held / NAVs * units etc.. ok that's my last spiel on Excel

This post has been edited by wongmunkeong: Aug 24 2012, 09:42 PM

heheh - where got Excel directly related ar? Like that, maths and financial calculator "usage / how to" also going to flood over here leh heheh

Anyhow, 2003 boleh do XIRR leh, UNLESS your 2003 at home is HOME/STUDENT or FREE edition. If Standard or Professional edition, sure can wan - just need to add the Analysis ToolPak.

This post has been edited by wongmunkeong: Aug 24 2012, 11:12 PM

NEARING 30 = not young?
That means 40s and 50s = ancient dinos ar?
Kaka, let's hunt this youngster down and punk him

eek.. me thinks we created some sort of Excel snowball here.. soon Excel classes held in LYN virtually, using simulations of savings, planned investments & even EPF simulations

This post has been edited by wongmunkeong: Aug 25 2012, 12:07 PM