CAGR Analysis on Singtel

Building on my previous post, I would like to extend the model to a more bearish stock to see how the results are. I will be using Singtel here for my analysis and below is the CAGR for Singtel across different possible entry points on the entire time frame. (for more details on this implementation, please check my previous post)

A quick observation shows that CAGR was generally around 5% which can be attributed to the depressed levels during the 2009 periods. CAGR then plummeted below 0 around 2014-2015 periods and stayed negative aside for the anomaly there which is attributed to the 52-week low of 2.8 levels which is a distance from its current price of 2.94. This is inclusive of the dividends distributed over the period.

The point which I want to make here is that the time of entry point is not the only important factor if the stock itself is not faring well. Indeed, Singtel has not been performing up to standard since the entry of the 4th telco in Singapore. The picture we have here is definitely not as rosy as the previous example I used (Capitamall trust).

Let us look at a comparison between CAGR of the two for clearer analysis.

We see that the CAGR of Capitamall has been generally superior over Singtel, lest the 2018 periods of volatility. This further reinforces the point that finding a fundamentally sound stock which can dish out a constant stream of dividends is more important than ever.

For those who are interested in the technical details of this implementation, I downloaded the data from Yahoo Finance and used Excel VBA to calculate the ending value (with dividends) which I can in turn calculate the CAGR. I wish I could have built a more robust model without much hardcoding but I guess this is a good starting point as my time is rather limited nowadays.

I am putting the Excel VBA here and who knows, maybe someone will build something much better than me? Hopefully you will have fun with this too!