Thanks for the revised version....

pin this in the first post

My love~ <3   

just checking/need to confirm....
Fund A invested amount is RM 1000, it has 10% in M'sia
the rest of the funds has no m'sia.
Total amount in portfolio is RM 10000.
in the chart, the % allocation in M'sia will show 10% or 1%?

what does line item do?   

Done!

Thanks...just need to confirm before I study how to use....

Hope your worksheet is flawless ar

Cos I got no time to check, so the credit and complaints, if any are all yours

When new users want to ask question about how to use in FUTURE....any links to contact you for guidance?

The value you entered is not valid
A user has restricted values that can be entered into this cell.. T.T

the ammount cell

let's say i just start to use this..
So do i put in my whole current negative value in now?   

can at NOTE picture appears when "clicked" in that cell before entering value? note with wording like "Put - in front of value paid, - in front of value sold"?
if can...compile all changes and update next time....Else....would make pink troublesome leh-che.

Based on this year Transaction right?
Cause last year i did a lot 

 
nice to know that no change in the links are required....just make update/revision so much simple
btw, no idea what are those....

  now I know who else to ask when there is something weird in the .xls 

better this way to avoid beginners putting in wrong figure. 

polarzbearz good job... 

for your line item column, my suggestion is probably to beautify it. use actual terms instead of 1, 2. you can leave '2' blank anyway...

1 replace with "Current Value" (ikut bahasa FSM). Haha.

i tried insert new row kan..   
always insert at the top..   

  i got it.. thx!

if distribution kan?
  then the percentage will differ from FSM percentage?

using excel we add the distribution back as cost.. but FSM doesnt??   

polarzbearz

I think instead of using your initial allocation as to determine your geographical allocation of your unit trust portfolio, IMO it’s better to use CURRENT FUND VALUE to determine. That’s what you are holding right now and where your money is NOW or as at latest fact sheet.

hohoho

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

Once put on the field, field tested, IMPROVEMENT suggestion can be made and the app can be revised.
Wow! now pity polarzbearz  have think and work on it to make improvement changes..
At times,..it is for users like me that can "talk easy..want this want that"..unknown of the programmer

don't worry uncle, i actually just suggesting to him to make changes, shouldn't affect the actual user. i'm super free at work, so took the time to analyse it, if any mistakes la. main priority is of course still trying to make life easy. otherwise, too complicated, defeats the purpose of the spreadsheet.

woonsc i would do it for free, if i have the expertise to create an almost flawless and user friendly spreadsheet.

i remember looking at wong sifu spreadsheet that he shared, walao, 

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?

polarzbearz

I think instead of using your initial allocation as to determine your geographical allocation of your unit trust portfolio, IMO it’s better to use CURRENT FUND VALUE to determine. That’s what you are holding right now and where your money is NOW or as at latest fact sheet.

hohoho

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

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?

Updated user instructions
Fixed formula to calculate units (thank you Pinky),
added manual adjustment to cater for ""Round Down"" scenario for CIMB funds (thank you Pinky)
Added drop-down list for transaction type (Buy, Switch Buy, Dividend, Switch Sell, Sell)
added new data validation where:
- Buy & Switch Buy must have negative value
- Dividend MUST not have any value (zero)
- Sell & Switch Sell must have positive value
fixed fund allocation table to pick up investment values from CURRENT VALUE instead of ENTRANCE VALUE (thanks adele123)

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~

Ada salah lagi!
(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

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

(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~

By default, the bond fund platform fee will be deducted from your available balance in the Cash Management Fund. Should there be insufficient balance in the Cash Management Fund, the bond fund platform fee will be deducted in the form of units from the best performing fund in the following sequence:

Cash Management Fund >> Best Performing Fixed Income Fund >> Best Performing Equity Fund >> Best Performing Fixed Income Fund with Redemption Fee >> Best Performing Equity Fund with Redemption Fee

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.