Scc2301 Ch4 L07 Stocks

Stocks Lecture 7 This lecture is part of Chapter 4: Investing in the Company

Today’s Lecture What are Stocks How can we find information about Stocks How can we use Excel to keep track of our Stocks

Why Stocks In order to setup a business or invest into new business one needs money! Don’t we all …. The natural question is of course: Where can we get that money for our earth-shattering idea. One option would be to go to a bank …

Why Stocks … but two problems are immediate. A) Banks do not really like promises, they like solidity B) Banks charge interest, and that can be very significant. Would there be any better ways to get money?

Why Stocks If you would want to set up a small company, you could ask some friends or family members to take a stake in return for partial ownership of your company. Hey. That’s quite a good idea! You get money on a promise and you will not have to pay any interest. In return, if you are successful, your ‘stakeholders’ will get a share of the profits. Great! A somewhat more formal implementation of this is the issuance of stock.

Why Stocks When a company is setup, one can issue a certain number of Stock certificates. These certificates represent the partial ownership of the company. The certificates then can be sold to investors who hope that the company (and thus the stock certificate) will increase in value. Naturally, the detailed process is quite tricky and there are many laws governing this but the principle is very simple.

Why Stocks Often the founder of a company buys all or a big part of the issued stock certificates. The founder then owns the certificates, but the company has the money. There’s an important issue here. Companies that issue stocks are considered legally independent entities. They therefore act and are treated like they were a thinking person. We are quite used to this. We e.g. say: “I bought this from Creative” (Of course you didn’t, you bought it from the sales person).

Why Stocks Just being able to buy the stock certificates when they are issued is not very practical.

Hence the Stock market was invented so that investors can trade (or sell/buy) stock certificates. To do so a stock needs to be ‘listed’ and fulfill certain conditions (hence this is only done for not-so-small companies). Trading on the stock market can be difficult though which is why there are brokers who help you do the trading for a commission.

Why Stocks It’s interesting to note that stock certificates are actual pieces of paper. In principle, when you buy a stock certificate you are entitled to get this piece of paper. When the company whose stock you are buying is not listed on any stock market, you will get the certificate. For listed companies, however, the moving around of the physical certificates is a big hassle so most investors will never ask for them and leave them stored in a central place.

Why Stocks As always, a great place to find info is on the web: Exchanges: • http://www.nyse.com • http://www.nasdaq.com • http://www.simex.com.sg/ Brokers: • http://www.etrade.com • http://www.poems.com.sg/

Stocks and Excel Now let us assume that somehow you can buy and sell stocks and that you want to keep track of your investments. Let’s start simply.

Stocks and Excel A 2 3 4 5 6 7 8 9 10 11 12 13 14 15

B

C

My 1st portforlio

Date 03/01/00 28/02/00 17/04/00 10/10/00

Action Buy Buy Buy Sell

D

E

F

G

H

Transactions Year 2000

Stock Quantity Stock Price YHOO YHOO MCD YHOO

2 2 10 3

216 158 35 60

Total Price

Total

432 316 350 -180

432 748 1098 918

=IF(C11 = "Buy",E11*F11,-E11*F11) =SUM($G$8:G11)

Stocks and Excel This is nice, but what if we make a typo in column C? Fortunately the formula can be improved. A 2 3 4 5 6 7 8 9 10 11 12 13

B

C

D

E

F

My 1st portforlio

Transactions Year 2000

Date

Action

Stock

Buy Buy Buy Sell Sell

YHOO YHOO MCD MCD YHOO

03/01/00 28/02/00 17/04/00 04/09/00 10/10/00

G

H

Quantity Stock Price Total Price 2 2 10 5 3

216 158 35 28 60

432 316 350 -140 -180

Balance 432 748 1098 958 778

=IF(OR(C8 = "Buy",C8 = "Sell"),IF(C8 = "Buy",E8*F8,-E8*F8),"error") What a Monster! No, actually it’s quite straightforward.

Stocks and Excel I guess buying Yahoo during the internet bubble was not such a good idea. This reminds me of: A reporter asks Richard Branson (the flamboyant founder of Virgin): “How do you become a Millionaire?” What do you think? And he replies ……….

Stocks and Excel “Start as a Billionaire and buy an Airline!”

Perhaps I should paraphrase … “Start as a Billionaire and become an Investor”

Stocks and Excel The spreadsheet we just made can neatly keep track of our transactions but of course it doesn’t reflect at all the current value of our portfolio. In that sense it’s a rather useless spreadsheet. So, let’s add a second sheet to keep track of this.

Stocks and Excel On this sheet we can enter the valuation of the portfolio. A 2 3 4 5 6 7 8 9 10 11 12 13 14 15

B

C

D

E

My 1st portfolio

Valuations

Date

Qty

03/01/00 28/02/00 17/04/00 04/09/00 10/10/00

This hurts!!!

F

YHOO Price Value 2 4 4 4 1

216 158 123 104 60

432 632 492 416 60

G

Qty

10 5 5

H

I

MCD Price

Value

35 28 28

J

K

Total

350 140 140

432 632 842 556 200

But how much?

Stocks and Excel On the one hand we can of course say it hurts by 778 – 200 = 578 dollars. True … but since we’ve been buying and selling stocks, the absolute amount doesn’t necessarily mean much. Somehow, it would be good if we could keep track of our performance more accurately so that we can compare ourselves to others. May be we didn’t do so badly…

Stocks and Excel Solution: We could treat our portfolio like a mutual fund and work with ‘virtual shares’. Sounds complicated! But as previously, perhaps with Excel, taking things step by step it won’t be so bad. Let’s start.

Stocks and Excel The beginning is easy: Let’s assume we have 1000 v-shares Virtual Shares: Cool! A 2 3 4 5 6 7 8 9 10 11 12 13 14

B

C

D

E

F

G

H

I

J

K

L M

N

My 1st portfolio Valuations

Date 03/01/00 28/02/00 17/04/00 04/09/00 10/10/00

YHOO Qty Price Value Qty 2 4 4 4 1

216 158 123 128 60

432 632 492 512 60

10 5 5

MCD Price Value

35 28 28

350 140 140

Total Qty 432 632 842 652 200

V-shares Value

1000

0.432

The key thing to realize is that if we add or withdraw money, the value per v-share should remain the same.

Stocks and Excel Let’s try this… A 2 3 4 5 6 7 8 9 10 11 12 13 14

B

C

D

E

F

G

H

I

J

K

L

M

N

O

My 1st portfolio Valuations

Date 03/01/00 28/02/00 17/04/00 04/09/00 10/10/00

YHOO Qty Price Value Qty 2 4 4 4 1

216 158 123 128 60

432 632 492 512 60

10 5 5

MCD Price Value

35 28 28

350 140 140

In/Out

Total Qty

432 316 350 -140 -180

432 632 842 652 200

V-shares Value

1,000.00 2,000.00 3,422.76 2,817.73 1,483.02

0.432 0.316 0.246 0.231 0.135

= N10+(K11*N10)/(L11-K11) Wait a moment! Why are O8 and O9 not the same?

Stocks and Excel Perhaps we should do this in two steps. One line for the valuation of the portfolio before the transaction and one line for the value after the transaction. A 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

B

C

D

E

F

G

H

I

J

K

L

M

N

O

My 1st portfolio Valuations

Date 03/01/00 28/02/00 28/02/00 17/04/00 17/04/00 04/09/00 04/09/00 10/10/00 10/10/00

YHOO Qty Price Value Qty 2 2 4 4 4 4 4 4 1

216 158 158 123 123 128 128 60 60

432 316 632 492 492 512 512 240 60

MCD Price Value

In/Out

Qty 432 316

10 10 5 5 5

35 28 28 28 28

350 280 140 140 140

Total

350 -140 -180

432 316 632 492 842 792 652 380 200

V-shares Value

1,000.00 1,000.00 2,000.00 2,000.00 3,422.76 3,422.76 2,817.73 2,817.73 1,483.02

0.432 0.316 0.316 0.246 0.246 0.231 0.231 0.135 0.135

Indeed, the value of the v-shares before and after the transaction are the same

Stocks and Excel Excellent, but this formula …. = N10+(K11*N10)/(L11-K11) = N10 + K11 / ( (L11-K11) / N10 ) The current value of the portfolio divided by

the current number of v-shares. equals

the current value of the v-shares

Stocks and Excel Excellent, but this formula …. = N10+(K11*N10)/(L11-K11) = N10 + K11 / ( (L11-K11) / N10 ) The added amount divided by equals

the current value of the v-shares

the additional number of v-shares

Stocks and Excel Phew… you’re right, this is actually quite difficult! The point I’m making is: It’s neither the math nor the programming of Excel that’s a problem here. We all can multiply and add. The problem is: we have to know what we want to do. This is very typical of financial analysis so focus on understanding of the problem and don’t be scared by the prospect of complicated math since usually it isn’t!

Now back to our portfolio: From the transactions sheet we know that we lost 778 – 200 = 578 dollars or 74% From the v-shares we know that we lost 0.432 – 0.135 = 0.297 per v-share or 68% Relief, like a good financial commentator I’ll now claim that I exceeded expectations by a whopping 6%! Message: be critical – always know what the numbers say!

Stocks and Excel On this sheet we can enter the valuation of the portfolio. 2 3 4 5 6 7 8 9 10 11 12 13 14 15

My 1st portfolio - Performance Date 03/01/00 28/02/00 17/04/00 04/09/00 10/10/00

Value 432 632 842 556 200

V-shares 0.43 0.32 0.25 0.23 0.14

0.5 0.45 0.4 0.35 0.3 0.25 0.2 0.15 0.1 0.05 0 03/01/00

28/02/00

17/04/00

04/09/00

10/10/00

17/04/00

04/09/00

10/10/00

1000 800 600 400 200

Indeed! These two graphs look completely different.

0 03/01/00

28/02/00

Stocks and Books There are all kinds of books … be aware of their quality! On a related note. Financial Planners are all the rage now. It is surprising to see how many (intelligent) people entrust their money to often poorly qualified individuals. In case of doubt, always go with ‘simple’, ‘big’, ‘well-known’, ‘trustworthy’ like e.g. NTUC, DBS, Schroders. The following three books represent certain types of books. That doesn’t mean I recommend them.

Stocks and Books This is quite a good book I think.

Stocks and Books Another good book. Though, it looks quite flashy.

How do you know that this one is OK? Make your own judgment!

Stocks and Books Well, this book looks similar to the one by Lynch. The topics appear sound enough!

Stocks and Books The book reads reasonable enough… until you consider what he doesn’t tell you. But this gives him away!

Stocks and Books But here’s a book I fully recommend!

Stocks and Books The best and worst performance of stock, bond, Tbills for various holding periods.

Stocks and Books Be aware! This is the nominal chart. I.e. inflation and taxes are not accounted for.

Stocks and Books While less than the nominal numbers, the difference between the various classes is clear.

Key Points of the Day Stocks play a key role in business Excel is a great tool to keep track of financial data Be critical of data as presented – know what they mean You’d better not let me manage your money!