Revamped formulas, full credits to Pinky to help me to understand various transaction types & how costs / units are calculated for them.
Added new column as Transacted Amount which accepts both type of input - Amount or Units. This value will be converted accordingly based on transaction type where:
- Buy & Switch Buy will convert Tx. Amt. into RM, with back-calculation of Units Gained.
- Dividend will convert Tx. Amt. into Units ONLY. (no recalculations of fee, nav price, etc)
- Platform Fee will convert Tx. Amt. into RM ONLY. (no recalculations of fee, nav price, etc)
- Sell & Switch Sell will convert Tx. Amt. into Units, with back-calculation of Amount Gained
Reworked Entrance Value formula - to not "double charge" self on incurred fees (the invested amount ALREADY takes into consideration of those fees, so if summing the invested amount PLUS fees incurred it's a double-charge mechanism which is wrong)
Added a few pop-up indicator to tell user "what to do or not to do" in this cell. Added data validation error message to make it easier to understand data validation set into the worksheet.

OT: Didn't notice polarzbearz that he likes IU? Annyeong haseyo!

if i copy paste the columns in the Fund allocation

all will show $B$8.. how to change it?

Walaoeh

semakin canggih liao

I only check the main worksheet, by trying out different transactions

your charts I cannot/dunno check

just looking at polarbearz v1.3 xls file. few suggestions:

1. add some comments on column H, I and J on what's the meaning of SC/RF/MF/Manual adj etc

Errors?:

1. the grouping on the left most column in Investment Details worksheet are not grouped properly according to the funds.
2. In Investment Details worksheet, if a Sell transaction is entered, why is the sample data shown as negative amount value?

I'm trying to add rows according to the steps you've listed in Instructions worksheet, but i keep on getting error message "This operation is not allowed......" Why is it so?

for a few times, when I want to partially sell my funds, I key in the Value in RM how much I want to sell....because I don't know the current precise value of the NAV ....
FSM would sent me the details of the qty deducted to equate the value of the RM specified....few days later....guess i can update the excel file few days late.

can the line above the total line (grey in colour) be deleted?

The annual expense of the fund is what is charged to the fund. This includes the fund manager's annual management fee, and other administrative fees that are incurred in the running of the fund. You do not really need to fork out additional money to pay for the annual management charge to the fund manager. They will actually deduct it from the Net Asset Value of the fund daily, and the published price will take into account of the pro-rated annual management charge.

i'm referring to Line 5 in the section How to add new row in the Instructions worksheet.

I'm compiling the historical data for Cash Management Fund based on the historical record in FSM into your xls file and i found a discrepancy of 1.26 unit/-RM 1.5. I've compared the number of lines/number of units/unit price in FSM and the data i entered into xls file and they match each other. no idea where does this 1.26 unit difference is coming from

the difference is in total line. the formula for Units column is correct. I'm also suspecting it's rounding issue.

I entered -1.26 unit in column J but i dont see total unit (column K) is reducing by the same amount of units.

I entered -1.26 unit in column J but i dont see calculated unit of the row and total unit (column K) is reducing by the same amount of units. any idea why?

    Just ignore the mgmt. fees calculation. Nav published is NETT. except some fund has this EXTRA fees..
Performance Fee and Hurdle Value.
Performance fee of the Target Fund, namely the China Select Fund is 10% of the appreciation in the net asset value per unit during the relevant performance period above the high watermark of the Target Fund.
I got no idea what that is.....
http://www.affinhwangam.com/investment-sol...ina-select-fund

I guess this fees can be exempted from the Excel file like the Mgmt fees.....b'cos I believes this fees deduction will be "hidden" unseen...the published NAV is NETT too...hope I am right.

Unlike normal UT, management fee r charged daily and reflected in the fund NAV. So no matter at what point you exit, you would have paid a pro rated management fee, trustee fee etc...

The fund you mentioned only charged an annual management fee because its performance based. That means all fees r accrued (but not chargevto fund) up until 31 Dec and also they need to determine whether the high watermark has been breach, if yes, manager will be entitled to 10% of the excess. If othwerise, manager gets nothing.

after changing the -1.26 into a detail switch buy/buy row, this manual adj works.

another point to raise...how do i add in the total profit/loss amount for the funds that i've switched out over the years? for eg i switched from fund A to fund B and make a profit of RM1000. how do i add this profit of RM1000 into the portfolio profit?

polarzbearz if u are doing it right, there shouldn't be ANY rounding difference at all.

I also started my worksheet halfway, i.e. I created my worksheet, and then copy and paste all transactions from FSM's historical transactions. My units is exactly the same as FSM summary.

Maybe it's best that jutamind share his historical data with polarzbearz and let him do some testing?

pinkspider, how big is your data size? for me, my CMF is over 100 buy/sell transactions over the years.

polarzbearz PM me your email address for me to send the historical data over to you.

the only drawback for this method is that you will see all your old funds in the single worksheet which is kind of messy if you switch funds over the years.

i'm referring to Line 5 in the section How to add new row in the Instructions worksheet.

I'm compiling the historical data for Cash Management Fund based on the historical record in FSM into your xls file and i found a discrepancy of 1.26 unit/-RM 1.5. I've compared the number of lines/number of units/unit price in FSM and the data i entered into xls file and they match each other. no idea where does this 1.26 unit difference is coming from

polarzbearz if u are doing it right, there shouldn't be ANY rounding difference at all.

I also started my worksheet halfway, i.e. I created my worksheet, and then copy and paste all transactions from FSM's historical transactions. My units is exactly the same as FSM summary.

Maybe it's best that jutamind share his historical data with polarzbearz and let him do some testing?

i agree with you that there's no error in buy/sell transactions. as pointed out by you, it could be the errors in platform sell...

but i'm not going to be anal about this....just did the manual adj.

btw, for the Fund Allocation worksheet, is the Total formula (in row 38) for column E to column T correct? these formulas seem to sum up only from row 8 - 13 only.

     

Their legacy will live on forever!

with that excel file....can see easily where had all the investment currently been allocated....where heavy, too little, how much % and which one making $$, which one can be "dumped" or replaced.....then can discuss about regional exposure.
Know what one currently have first before "moving forward to FY15 strategy and other stuff", knowing them will be more value added b'cos of availed info like where had you invested, where are you gonna invest next, had what you invested made money. 

for some, after learning how to use this excel file...can use it to do presentation to impress       
or to get potential clients  or to enhance believes from current customer base so that continue to get few % from the SC.

as for uncle like me, there is a long way before can do above....still in the in Investment detail worksheet....tried inserting a ROW...then found that "=IF(OR(Table3[[#This Row],[Transaction Type]]="Buy",Table3[[#This Row],[Transaction Type]]="Switch Buy",Table3[[#This Row],[Transaction Type]]="Platform Fee"),"...missing from the cell when compared to other above....
Tried to DRAG down that "Formula" directly from above cell to the one just created that has no formula.....after done 3 Drags..."Error message prompted...then can see a new "Arrowed lines"...
will have to try see how this really works....

the best way is create 10 funds? each?

I think the "better" way is to create 1 worksheet for EACH fund.....there will be almost endless entries.
but this EACH worksheet how to link to the other critical worksheet?

hmmm.. maybe separate both?
one is IRR
another is allocation..

I support..

can or not? easy or not? what are the limitation? can people with limited excel knowledge (like me) benefits from it?
else,,,,more leh chey for polarzbearz...

in the meantime will have to learn how to use.....

my excel also limited..
I really thankful for them.. if got paypal donation button.. i donate liao.. <3

I was very skeptical at first about fundsupermart, it looked like some funky one hit wonder kind of service provider without any backing. That was like 5 -6 years ago, i was still noob (still is but upgraded to newbie status) with unit trust fund.

it was not until I joined UT company, I found out who iFast was. I still didn't believe in Unit Trust at that time until I invested in my first unit trust scheme, PRS. That also, i needed for tax deduction only.

it was only when i joined this thread that i got to know fundsupermart's backer (owener) is iFast.

Eye opening... I like the open platform concept, can swing from tree to tree at my finger tips.

I am like shet, why didn't i join sooner. I guess better late than never.

Good to have another techie investor onboard 
Excel has it's limitations, especially without macro or VBA-coding.

Knowing the limitation, best i could do (for anal-retentive detailed tracking) was to break it down to:
1. Summary page (see by fund & by fund overall performance)
2. Details Held -see by EACH transaction's net profit/loss - simple & CAGR
3. Details Sold / Switched - see by EACH transaction's net profit/loss - simple & CAGR

Hopeful that another PoV can do better, thus i can learn & absorb heheh.

IMHO - the best option is to use MS Access - transactions all in tables data entry, then a click-of-a-button
Query-Reports will show necessary summary and another to show details.

Wow,  That is much BETTER.
Any MS Access sifus wanna try?