Ok lar

Aiyoyo

No need spoonfeed to this extent gua? Basic Excel also need to guide meh?

Excel masterclass here

Distribution does NOT bring down your cost.

When got distribution/unit split, leave the value column empty or put a zero. Only insert the additional units received - no cost, no income

After u inserted new rows, u have to edit/check the XIRR formula cell to make sure that it includes the newly added rows within the range of cells.

Went to my friend's mum funeral wake

<
See my avatar?

Bonds...anyone?

http://www.bloomberg.com/news/2015-01-22/w...bt-markets.html

U must have just invested about 3 months ago

3-m return 12.5%

But 1-year return only 9.6%, lose out to Aberdeen Islamic Aladdin World and CIMB Global Titanic

hohoho

U all sort it out first, when finalised already baru summon me to amend Post #1

Easy saja

Insert a row (as usual, make sure within range of the XIRR formula)
If buy is negative value, sell is positive value (think...RECEIVE money). Switch out also same, positive value
Under column "units", negative value, tolak out the units sold

RM50? One bucket beer dah habis

My Excel mana ada calculate cost punya

Mine is all like this:

Buy (100)
Buy (100)
Sell 50
Buy (500)
Distribution 0
Current value ~ total units x current NAV

What is your RM1,293.66?

FSM uses weighted average method to calculate cost

I never bothered with the cost, so don't ask me for specifics

This post has been edited by Pink Spider: Jan 23 2015, 11:36 AM

aku taktau formula ABS

ask the creator polarbear?

aiks

bro, your worksheet got 1 very fundamental error!

E.g.
Invest/pay RM100
Sales Charge 2%
NAV price RM1.0000

Units received would be
= RM100 / (1+2%) / RM1.0000
= RM98.04 / RM1.0000
= 98.04

U cannot take amount - (amount x SC)
the figure churned out is wrong!

Maybe it's easier to illustrate this way...

NET amount invested + sales charge = amount payable
Think of SC as a commission
U agree on a Selling Price
U have a commission % rate
The commission is calculated on the SP
The amount payable is SP + commission

And make sure u round each and every formula to 2 decimals!

i.e.

Net amount invested formula: round(gross investment / 1 + SC rate,2) = round(100/1.02,2) = RM98.04

Units entitled: round(net invested/NAV price,2) = round(98.04/1.0000,2)

Another problem is, while most fund houses round to nearest 2 decimals, CIMB-Principal always round DOWN to the nearest 2 decimals for units.

E.g.
Net invested (after deduct SC) RM98.04
NAV price bought RM0.5497
Units entitled 521.4893617021277
They would round DOWN to 521.48

Ok, now go scratch your head

woonsc now u know why?

This post has been edited by Pink Spider: Jan 23 2015, 03:14 PM

Wow...this almost can be a fool-proof worksheet liao

Ada salah lagi!

(1) Sales Charge calculation salah
E.g. Amount invested RM1,020, SC 2%, SC would be RM1,020 / (1 + 2%) x 2% = RM20
With your formula I got RM20.40

The amount u pay/invest is always AFTER SC

Remember this formula...

net investment x (1 + SC rate %) = amount paid

Sekian terima...wait!

Ada lagi satu...and this one is a FUNDAMENTAL ERROR some more

(2) Selling of units
When u sell funds (with the exception of CMF), u indicate how many UNITS to sell, NOT how much money u want to receive!
So, it should be

sale proceeds = round (units sold x NAV price,2)

And what if got redemption fee like AmDynamite Bond?

sale proceeds = round(round(units sold x NAV price,2) x (1 - redemption fee %),2)
e.g. sell 1,000 units at NAV price RM0.5000, redemption fee 1%
1,000 x 0.5 x 0.98 = RM490

(3) Need one more transaction type - PLATFORM FEE for bond funds
Platform fee of 0.05% per quarter for some bond funds would most likely be deducted from CMF, by selling some units of CMF
E.g. platform fee charged RM10 for RHB-OSK Asian Total Return Fund
FSM would automatically generate a sell RM10 worth of CMF transaction to pay the platform fee

So, for CMF, it's routine, just treat it as a normal sale transaction.

For the bond fund concerned, it's...LAGI STRAIGHTFORWARD!
Units - leave empty
Price - leave empty
Amount - just manually key in the amount (make sure it's a NEGATIVE value, cos it's a cost to u) of platform fee actually charged by FSM (FSM would e-mail u every quarter for this). This I also cannot come out with a formula. Go FSM website read their FAQ on bond fund platform fee to know why...

I dunno how this would affect SUM(ABS) formula. This u go test and see...


Ok, here's your weekend assignment! Tata~

This post has been edited by Pink Spider: Jan 24 2015, 09:35 AM

SOME funds will charge redemption fees...

e.g. AmDynamic Bond got no sales charge, no platform fee, so they charge 1% redemption fee

some equity funds charge 1% redemption fee if u sell less than x no. of months after u invested

Later claim OT from Unker Looi

Aiyoyo, kena balik from u pulak. Yes, I salah

Tau pun!

FSM would deduct RM10 platform fee by selling from your CMF holdings

BUT, if u don't have enough in CMF, they would deduct the RM10 from your "best-performing bond fund" by selling enough units to raise the RM10

That's why I ask u, don't bother with formula for this one.

Just insert "platform fee" as another transaction type
Just enter the amount charged manually

Then at CMF side, just treat it as a normal sale transaction

FSM would e-mail u like, a few days in advance before they charge. So, it's best to maintain some $$$ in CMF for them to deduct platform fees for bond funds.

This post has been edited by Pink Spider: Jan 24 2015, 11:27 AM