Guide using excel XIRR

I am sure you are always interested to compute the returns of your investment. However, most of the time investors compute only their absolute returns of investment. For example, if you invest $10000 and a few months later your investment became $15000, therefore your absolute returns is 50%. But aren't you interested to know your actual internal rate of return (IRR) or compounded annual growth rate (CAGR) of your short or long term investment?

Take for example the following scenario of an investment made by you:

Date, Transaction type, Amount

10 Jan 06, Buy stock X, $8000

14 Mar 06, Buy stock X, $6000

28 Aug 06, Receive dividends from stock X, $300

20 Aug 07, Receive dividends from stock X, $400

12 Feb 08, Sell stock X, $23500

Taking the dividends portion into account as well, how do you calculate the IRR or CAGR of your investment like the above example? It is not a straight forward task to calculate IRR or CAGR using calculator. Thanks to Mircosoft, we can make use of XIRR function from excel to do the computation. I will guide through with you on using this XIRR function.

Step 1: Open a new excel worksheet and change the format of column A to an accounting type with $ symbol or else the data is not valid later on

Step 2: Fill all cell values exactly as shown in the image above. A bracket denotes negative, payment or investment amount forked out. A positive value denotes amount received from investment or current invesment holding

Step 3: Select cell D8 then click fx -> select Financial in category box -> select XIRR -> click OK

If you can't find XIRR function, do the following steps:

1. On the Tools menu, click Add-Ins
2. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK
3. If necessary, follow the instructions in the setup program

Step 4: When a function arguments window pops up, type A2:A6 for Values and B2:B6 for Date. Then click OK

Step 5: Computed result of 0.317 will be shown on cell C8. To view the number as a percentage, click on cell D8 -> click Cells on the Format menu -> click the Number tab -> select Percentage in the Category box

The IRR or CAGR for this example turns out to be 31.7%. It is more meaningful than simply computing its absolute returns. You can also follow the same steps if you want to calculate the IRR of your expenses and incomes as long you take note of a negative sign for each cash outflows and a positive sign for each cash inflows. I hope you find this guide useful and if you think that there is any error, please do let me know.

STI expected report date

Stock	Expected Report Date	Stock	Expected Report Date
CAPITALAND	4/25/2008	OLAM INTER	5/15/2008
CAPMALL TRUST	4/18/2008	SEMBCORP INDUSTRY	5/12/2008
CITY DEVELOPMENT LTD	5/12/2008	SEMBCORP MARINE	4/30/2008
COSCO CORP	4/30/2008	SIA ENGINEERING	5/11/2008
DBS GRP HLDGS	5/7/2008	SIA LTD	5/13/2008
FRASER & NEAVE	5/9/2008	SGX	4/8/2008
GENTING INTL	5/13/2008	SPH	4/10/2008
HONGKONG LAND	8/7/2008	SINGTEL	5/14/2008
JARDINE C&C	5/15/2008	ST ENGINEERING	5/2/2008
JARDINE STR 500	8/15/2008	STARHUB	5/8/2008
KEPPEL CORP	4/24/2008	THAI BEVERAGE	-
KEPPEL LAND	4/16/2008	UOB LTD	5/6/2008
NOBLE GROUP	5/8/2008	WILMAR INTL	5/14/2008
NOL LTD	5/14/2008	YANGZIJIANG	5/15/2008
OCBC	5/7/2008	YANLORD LAND	5/14/2008

Brokerage comparison

Currently I am holding brokerage accounts with POEMS, DBS Vickers, Lim & Tan, Citibank and AmFraser. In this post, I decided to do a brief comparison on each one of them.

Please do take note that these reviews may not be comprehensive enough as I don't really have a chance to make full use of them. I am writing based on my personal exploration and experience. I would appreciate if anyone can add on to their reviews. In the mean time, I will update this post if I can think of anything.

POEMS
About
Phillips' Online Electronic Mart System, or more commonly known as POEMS, is the pioneer for Singapore's online share trading. Established by Phillip Securities Pte Ltd in 1996, it has undergone tremendous changes since then to better fulfill your needs whilst harnessing the power of modern technology. The furious pace of change has meant that new products and services are continuously being evolved, with unsuitable products discarded whilst promising ones being improved upon. From the first generation of POEMS in 1.44 inch diskettes to the entire POEMS system being made available on the Internet, the idea is to allow you with better control of your own trades and be equipped with the essential tools to make a better investment decision. If you are our POEMS clients who have been with us since the beginning, we would like to thank you for your continuous support all this while and make POEMS what it is today. If you are not a POEMS client, do not hesitate any further and check out what POEMS can do for you now.

Screenshot



Pros

1. I would say POEMS provides an almost complete solution for traders with a wide range of financial services offered like stocks and shares, unit trusts, CFD, futures, treasury bills, etc
2. The only brokerage that gives investor an option in allowing their funds left in cash management account to be automatically invested in mmf yielding higher return
3. It is one of the few brokerages that offers mobile trading using a handphone
4. The only brokerage that gives access to SGX unit share market. You can sell your odd lot shares with POEMS
5. The loading speed of this trading platform is very fast

Cons

1. For new users, they may find difficulty to navigate around the not so friendly user interface. Even at times, I may get lost while navigating
2. I find the rewards not that useful as you can only redeem trading or market related items. On top of redeeming with points, you still have to top up in cash for most of the items

DBS Vickers
About
DBS Vickers Online is committed to offering investors in Asia superior experiences in self-directed investing. DBS Vickers Online provides self-directed investors with a broad range of global investment services through multiple channels, including the Internet, call centres, and customer centres. Through us, customers can invest in stock markets in Singapore, Hong Kong, the United States and Canada as well as UK and B-shares listed on the Shanghai and Shenzhen Markets. They can also choose from a variety of investment products that may suit their different needs. In addition to equities, we offer fixed income products, mutual funds and unit trusts, as well as opportunities to participate in IPOs and IPO private placements. What's more, we provide customers with real time market information and professional tools and research to make informed and educated investment decisions. Our operations provide investors in both Singapore and Hong Kong with the local offerings that best meet their needs.


Screenshot



Pros

1. The user interface is very nicely designed. You can easily navigate all the pages with minimal clicks
2. I find Clarity a very comprehensive and useful tool to help investors in doing their stock research. You can do stocks screening with this tool based on your fundamental or technical input requirements
3. You can view each counters complete historical financial data like balance sheet, income statements and cash flow in one page
4. You can access to all DBS Vickers research reports on each counters if available
5. There is also a nice charting tool. It is as good as you can get with any other professional charting software like chartnexus

Cons

1. The loading speed of this trading platform is very crippling slow. It is the slowest among all brokerages that I reviewed
2. Clarity tool is only available after SGX market closes
3. Your money left in the trust fund yields very low return

Lim & Tan
About
Lim & Tan Securities Pte Ltd was incorporated in Singapore on 1 June 1973, commencing activity as a member of the Singapore Exchange (SGX) that year. Lim & Tan Securities main activity is stock broking and the main focus of the company is the retail market. Believing in providing cutting-edge technology to its clients, Lim & Tan Securities was amongst the first to introduce Internet Share Trading in Singapore in October 1998. In Jan 2000, Lim & Tan Securities is again proud to be amongst the first in Asia to introduce V.A.S.T., the Voice-Activated Stock Trading system. V.A.S.T. is positioned, not as a product on its own, but rather to complement internet share trading under its online trading brand name, limtan.com.sg.

Screenshot


Pros

1. I like the reward system with this brokerage. You can choose whether to accept rewards in terms of NTUC linkpoints or their in-house points system. The former is more practical to me
2. You have free access to Netresearch Asia, an established third party research company
3. As a complement to online trading, you can trade through telephone with their V.A.S.T, an interactive voice response system

Cons

1. Your money left in the trust fund yields very low return
2. You need to inform your remisier or broker if you want to withdraw money from the trust funds
3. On high resolution display, I find the buttons on the top right too small to click

AmFraser
About
In June 1987, Fraser was converted from a partnership into a corporate entity. The Company's name was changed to Fraser Roach & Co Pte Ltd and it became a wholly-owned subsidiary of Frasers International Pte. Ltd. (FIPL). In July 1991, Arab-Malaysian Merchant Bank Berhad(AMMB) bought a 49% stake in FIPL, thus making AMMB FIPL's largest single shareholder. Following this transaction, Fraser was renamed Fraser Securities Pte Ltd. The ownership structure changed in September 2006 when AMMB, restructured as AmInvestment Bank Berhad under the Amlnvestment Group, bought the remaining 51% stake in FIPL making the Company a wholly owned subsidiary. In July 2007, the Company’s name was changed to AmFraser Securities Pte. Ltd. As at 31 March 2007 AmFraser Securities has a capital base of S$46 million and is now a member of the AmInvestment Group Berhad, which has shareholders' funds of RM2.3 billion. The AmInvestment Group is ranked as the largest investment banking group in Malaysia with interest spanning from merchant banking, commercial banking, stockbroking, consumer finance to fund management. With effect from July 2007, Fraser Securities Pte Ltd will be renamed AmFraser Securities Pte. Ltd.

Screenshot


Pros

1. The loading speed of this trading platform is very fast
2. The user interface is newly designed and is quite user friendly
3. You have free access to Dow Jones News
4. When you trade, there is a "Good till date" instruction for your transaction

Cons

1. There is no loyalty rewards at the moment but there is an online lucky draw conducted monthly
2. You need to inform your remisier or broker if you want to withdraw money from the trust funds
3. There is no shortcut function to list counters by the same initial of either A to Z
4. Your money left in the trust fund yields very low return

Citibank
About
Not available from official website

Screenshot


Pros

1. The user interface is very nicely designed. You can easily navigate all the pages with minimal clicks
2. As your brokerage account is linked with Citibank internet banking, you can manage all your banking, investments and trading accounts with one convenient access
3. When you trade, there is a "Good till date" instruction for your transaction
4. You can instantly transfer money between cash trading and checking accounts
5. You have free access to award winning Smith Barney Citigroup equity research
6. It offers the lowest minimum commission at $22 for online trading

Cons

1. There is no loyalty rewards
2. There is no shortcut function to list counters by the same initial of either A to Z
3. Your money left in the trading account yields very low return

The above are some of the pros and cons I can think of for these brokerages. I have been using POEMS since I started investing. Only recently I decided to switch to Lim & Tan because of the NTUC linkpoints. There is an ongoing promotion if you complete four transactions within six months, you earn $118 cash or 6000 linkpoints. You can see this for more detail.

I have also heard good reviews about Kim Eng trading platform. That shall be the next brokerage I intend to open an account with.

Maximising credit card rebates - Part 2

In one of my previous post, I have shown how I arranged my various bill settlements and expenses across different credit cards in order to maximise cash rebates. I have also introduced the UOB One Card and its benefits. Based on my monthly expenditures, I came to a conclusion that I may or may not be able to reach the minimum spending amount to qualify for cash rebates. So that is why I decided not to use One Card initially.

After some time, I finally decided to apply for the UOB One Card as I am really attracted by the cash rebates. Imagine for a monthly spending of $300, you stand a chance to earn cash rebates of $30 per quarter or $10 per month. Effectively you are earning cash rebates of 3.33% on your spending. So far, I see this to be the highest cash rebates you can receive among various credit cards. Please share if you can find more attractive than that.

So recently I have informed all my billing organisations like Starhub, Singnet, M1 and even EZ-Link to charge future bills and auto top-up under One Card. Towards the end of the month if I am not able to meet the minimum $300 amount, I will just go down to SP Services customer service centre to pay utility bill with One Card. It is a strange thing that you can't setup a recurring instruction to charge your utility bill under One Card but you can still use credit card payment at their customer service centre.

When my spending reaches past $300, I will charge all subsequent transactions under Citibank M1 card which gives me a 0.5% rebate. However the rebate of this card is not in cash. So you can choose to offset M1 bills. I don't really mind as I can still save on my M1 bills.

If any kind soul can suggest a better way to earn cash rebates, please do inform me. I don’t mind giving a small reward for that suggestion. As for now, I am really happy with these arrangements.

Change in Mid Cap constituent

Following the acceptance levels for the cash acquisition of Ascott Group (Singapore Constituent) by Capitaland (Singapore Constituent) reaching in excess of 90%, Ascott Group will be deleted from the index. Upon deletion, Macquarie MEAG Prime REIT shall take its place. The announcement was made on 26 Feb at FTSE website. For more detail , you can read it from here.

Therefore I shall update all information in this blog accordingly.

Funds transfer: Xtra$aver -> Step-up account

I know many readers are having a problem doing funds transfer from Xtra$aver to their Step-up account through AXS machine. Initially I got a hard time figuring out on how to do it and a few failed transactions resulted. But this morning, I managed to successfully make a transfer. I will post the steps required for the transfer. I shall not reveal too much on the benefits of this transfer as I am yet to verify its outcome. You can read more from the comments on my previous post.

1. From AXS main menu, choose Finance
2. Click on Citibank
3. Click on Citibank S$ Checking / Savings Account
4. Accept the notice you see. Note you can only do a maximum transfer of $2000 per day
5. Enter your 10 digits Step-up account number. You can get the account number on your bank statement. For example, mine is 0516XXXX01
6. Enter the amount you wish to transfer
7. Choose NETS and insert Xtra$aver card
8. Choose Current Account. It will fail if you choose Savings Account
9. Enter your 5 digits ATM pin

Yield curve

While I was at the airport in the morning I went holiday, I visited a book store and came across a book titled The secrets of economics indicators by Bernard Baumohi. This book explains the meaning of all important and key indicators like consumer price index, producer price index, consumer confidence, new home sales, etc for the world’s biggest economy of the world. Understanding these indicators can be essential on how they can affect the market sentiment.

Results from the indicators have a variable range of impact on the stock market. As I was browsing, a topic on US yield curve caught my attention. According to the book, the yield curve has a very significant impact on the stock market. Furthermore it has accurately predicted all the previous recessions except for the year 2000 recession.

The topic on yield curve thus triggers me to know more about it. Basically the curve is a yield plot of US treasury bills across various maturities period at a point of time. The shape of the curve can dictate the state of economy as a whole.

During good economic growth, the curve is an upward sloping. It makes sense that the yield increases as the maturity becomes longer since most investors are putting money in equities or stocks when market is good. However when times are uncertain or economic outlook turns gloomy, many investors are putting their money in safe havens like treasury bills. As quoted from stockcharts website, an inverted yield curve is a sign of tight money and bearish for stocks.

You can monitor the shape of the yield curve at any point of time here. So based on this indicator, are we seeing a recession coming? It certainly doesn’t look so isn’t it?

My stocks portfolio - Feb 2008

I went for holiday for almost a month in Feb but I did manage to monitor the stock market once in awhile. I am happy that I took a break for sometime as the market sentiment has not been that good anyway. In overall my portfolio dropped by -11.55% at the end of Feb which is a slight rebound from the previous month. I am expecting my portfolio to remain in the red for at least a few more months. In the mean time, I am still looking for good buying opportunity if there is.

There is only 1 transaction done for the month of Feb.
Bought ARA, average price at $0.972

After looking at ARA latest financial results, I am really convinced that it is just a matter of time before this stock realises its true value. I am also surprised that ARA is declaring dividend of 3.8 cents a share as it does not mention any dividend payout policy previously. I received total dividends of $109.08 for the month of Feb.

No	Stock	Mode	Unrealised P/L
1	ALLCOREIT	CASH	3-
2	ARA	CASH	2-
3	COURAGE MAR	CASH	1-
4	FIBRECHEM	CASH	2-
5	FSL TRUST	CASH	2-
6	GEN INT	CASH	1-
7	INT-ROLLER ENGG	CASH	1-
8	JAYA HLDG	CASH	3-
9	KIM ENG	CASH	2+
10	MACQ INT INFRA	CASH	2-
11	ROTARY ENGRG LTD	CASH	4-
12	SINGHLDG	CASH	4-
13	TAI SIN	CASH	3-
14	UOB-KAY HIAN	CASH	2-
15	VICOM	CASH	1-
16	BH GLOBAL	CPF	1+
17	COSCOCORP	CPF	4+
18	SIAENGG	CPF	2-
1+ denotes returns of 0 to 10%
2+ denotes returns of 10.01% to 20%