scc2301 ch4 l07 stocks
TRANSCRIPT
-
8/14/2019 Scc2301 Ch4 l07 Stocks
1/37
Stocks
Lecture 7
This lecture is part of Chapter 4:
Investing in the Company
-
8/14/2019 Scc2301 Ch4 l07 Stocks
2/37
Todays Lecture
What are Stocks
How can we find information about Stocks
How can we use Excel to keep track of our Stocks
-
8/14/2019 Scc2301 Ch4 l07 Stocks
3/37
Why Stocks
In order to setup a business or invest into new business
one needs money!
Dont 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
-
8/14/2019 Scc2301 Ch4 l07 Stocks
4/37
Why Stocks
but two problems are immediate.
A) Banks do not really like promises, they like solidityB) Banks charge interest, and that can be very
significant.
Would there be any better ways to get money?
-
8/14/2019 Scc2301 Ch4 l07 Stocks
5/37
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. Thats 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.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
6/37
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.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
7/37
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.
Theres 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 didnt, you bought it from the sales
person).
-
8/14/2019 Scc2301 Ch4 l07 Stocks
8/37
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 onlydone 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.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
9/37
Why Stocks
Its 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.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
10/37
Why Stocks
As always, a great place to find info is on the web:
Exchanges:
http://www.nyse.comhttp://www.nasdaq.comhttp://www.simex.com.sg/
Brokers:
http://www.etrade.comhttp://www.poems.com.sg/
http://www.nyse.com/http://www.nasdaq.com/http://www.simex.com.sg/http://www.etrade.com/http://www.poems.com.sg/http://www.poems.com.sg/http://www.etrade.com/http://www.simex.com.sg/http://www.nasdaq.com/http://www.nyse.com/ -
8/14/2019 Scc2301 Ch4 l07 Stocks
11/37
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.
Lets start simply.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
12/37
Stocks and Excel
A B C D E F G H
2
3 My 1st portforlio Transactions Year 2000
4
5
6 Date Action Stock Quantity Stock Price Total Price Total
7
8 03/01/00 Buy YHOO 2 216 432 432
9 28/02/00 Buy YHOO 2 158 316 748
10 17/04/00 Buy MCD 10 35 350 1098
11 10/10/00 Sell YHOO 3 60 -180 918
12
13
14
15
=IF(C11 = "Buy",E11*F11,-E11*F11)
=SUM($G$8:G11)
-
8/14/2019 Scc2301 Ch4 l07 Stocks
13/37
Stocks and Excel
A B C D E F G H
2
3 My 1st portforlio Transactions Year 2000
4
5
6 Date Action Stock Quantity Stock Price Total Price Balance
7
8 03/01/00 Buy YHOO 2 216 432 432
9 28/02/00 Buy YHOO 2 158 316 748
10 17/04/00 Buy MCD 10 35 350 1098
11 04/09/00 Sell MCD 5 28 -140 958
12 10/10/00 Sell YHOO 3 60 -180 778
13
=IF(OR(C8 = "Buy",C8 = "Sell"),IF(C8 = "Buy",E8*F8,-E8*F8),"error")
This is nice, but what if we make a typo in column C?
Fortunately the formula can be improved.
What a Monster! No, actually its quite straightforward.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
14/37
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 .
-
8/14/2019 Scc2301 Ch4 l07 Stocks
15/37
Stocks and Excel
Start as a Billionaire and buy an Airline!
Perhaps I should paraphrase
Start as a Billionaire and become an Investor
-
8/14/2019 Scc2301 Ch4 l07 Stocks
16/37
Stocks and Excel
The spreadsheet we just made can neatly keep
track of our transactions but of course it
doesnt reflect at all the current value of ourportfolio. In that sense its a rather useless
spreadsheet.
So, lets add a second sheet to keep track ofthis.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
17/37
Stocks and Excel
A B C D E F G H I J K
2
3 My 1st portfolio Valuations
4
5 Total6 Date Qty Price Value Qty Price Value
7
8 03/01/00 2 216 432 432
9 28/02/00 4 158 632 632
10 17/04/00 4 123 492 10 35 350 842
11 04/09/00 4 104 416 5 28 140 556
12 10/10/00 1 60 60 5 28 140 200
13
14
15
YHOO MCD
On this sheet we can enter the valuation of the portfolio.
This hurts!!!But how much?
-
8/14/2019 Scc2301 Ch4 l07 Stocks
18/37
Stocks and Excel
On the one hand we can of course say it hurts
by 778 200 = 578 dollars.
True but since weve been buying and
selling stocks, the absolute amount doesnt
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 didnt do so badly
-
8/14/2019 Scc2301 Ch4 l07 Stocks
19/37
Stocks and Excel
Solution:
We could treat our portfolio like a mutual fundand work with virtual shares.
Sounds complicated! But as previously,
perhaps with Excel, taking things step by stepit wont be so bad.
Lets start.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
20/37
Stocks and Excel
A B C D E F G H I J K L M N
23 My 1st portfolio Valuations
4
5 Total
6 Date Qty Price Value Qty Price Value Qty Value
7
8 03/01/00 2 216 432 432 1000 0.432
9 28/02/00 4 158 632 632
10 17/04/00 4 123 492 10 35 350 842
11 04/09/00 4 128 512 5 28 140 652
12 10/10/00 1 60 60 5 28 140 200
13
14
YHOO MCD V-shares
Virtual Shares: Cool!
The beginning is easy: Lets assume we have 1000 v-shares
The key thing to realize is that if we add or withdraw
money, the value per v-share should remain the same.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
21/37
Stocks and Excel
A B C D E F G H I J K L M N O
2
3 My 1st portfolio Valuations
4
5 In/Out Total
6 Date Qty Price Value Qty Price Value Qty Value
7
8 03/01/00 2 216 432 432 432 1,000.00 0.432
9 28/02/00 4 158 632 316 632 2,000.00 0.316
10 17/04/00 4 123 492 10 35 350 350 842 3,422.76 0.246
11 04/09/00 4 128 512 5 28 140 -140 652 2,817.73 0.231
12 10/10/00 1 60 60 5 28 140 -180 200 1,483.02 0.135
13
14
YHOO MCD V-shares
Lets try this
= N10+(K11*N10)/(L11-K11)
Wait a moment! Why are O8 and O9 not the same?
-
8/14/2019 Scc2301 Ch4 l07 Stocks
22/37
Stocks and Excel
A B C D E F G H I J K L M N O
2
3 My 1st portfolio Valuations4
5 In/Out Total
6 Date Qty Price Value Qty Price Value Qty Value
7
8 03/01/00 2 216 432 432 432 1,000.00 0.432
9 28/02/00 2 158 316 316 1,000.00 0.316
10 28/02/00 4 158 632 316 632 2,000.00 0.316
11 17/04/00 4 123 492 492 2,000.00 0.246
12 17/04/00 4 123 492 10 35 350 350 842 3,422.76 0.246
13 04/09/00 4 128 512 10 28 280 792 3,422.76 0.23114 04/09/00 4 128 512 5 28 140 -140 652 2,817.73 0.231
15 10/10/00 4 60 240 5 28 140 380 2,817.73 0.135
16 10/10/00 1 60 60 5 28 140 -180 200 1,483.02 0.135
17
YHOO MCD V-shares
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.
Indeed, the value of the v-shares before and after the transaction
are the same
-
8/14/2019 Scc2301 Ch4 l07 Stocks
23/37
Stocks and Excel
Excellent, but this formula .
= N10+(K11*N10)/(L11-K11)
the current number of v-shares.
The current value of the portfolio
the current value of the v-shares
= N10 + K11 / ( (L11-K11) / N10 )
divided by
equals
-
8/14/2019 Scc2301 Ch4 l07 Stocks
24/37
Stocks and Excel
Excellent, but this formula .
= N10+(K11*N10)/(L11-K11)
the current value of the v-shares
= N10 + K11 / ( (L11-K11) / N10 )
The added amountdivided by
the additional number of v-shares
equals
-
8/14/2019 Scc2301 Ch4 l07 Stocks
25/37
Stocks and Excel
Phew youre right, this is actually quite difficult!
The point Im making is:
Its neither the math nor the programming of Excel thats
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 dont be scared by the
prospect of complicated math since usually it isnt!
-
8/14/2019 Scc2301 Ch4 l07 Stocks
26/37
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 Ill now claim that
I exceededexpectations by a whopping 6%!
Message: be critical always know what the numbers say!
-
8/14/2019 Scc2301 Ch4 l07 Stocks
27/37
Stocks and Excel
2
3 My 1st portfolio - Performance4
5
6 Date Value V-shares
7
8 03/01/00 432 0.43
9 28/02/00 632 0.32
10 17/04/00 842 0.25
11 04/09/00 556 0.23
12 10/10/00 200 0.1413
14
15
0
200
400
600
800
1000
03/01/00 28/02/00 17/04/00 04/09/00 10/10/00
0
0.05
0.1
0.15
0.2
0.25
0.3
0.35
0.4
0.45
0.5
03/01/00 28/02/00 17/04/00 04/09/00 10/10/00
On this sheet we can enter the valuation of the portfolio.
Indeed! These two graphs
look completely different.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
28/37
Stocks and Books
There are all kinds of books be aware of their
quality!
On a related note. Financial Planners are all the ragenow. 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 doesnt mean I recommend them.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
29/37
Stocks and Books
This is quite a good book I think.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
30/37
Stocks and Books
Another good book. Though, it looks quite flashy.
How do you know that this
one is OK?
Make your own judgment!
-
8/14/2019 Scc2301 Ch4 l07 Stocks
31/37
Stocks and Books
Well, this book looks similar to the one by Lynch.
The topics
appear
sound
enough!
-
8/14/2019 Scc2301 Ch4 l07 Stocks
32/37
Stocks and Books
The book reads reasonable enough until you consider
what he doesnt tell you. But this gives him away!
-
8/14/2019 Scc2301 Ch4 l07 Stocks
33/37
Stocks and Books
But heres a book I
fully recommend!
-
8/14/2019 Scc2301 Ch4 l07 Stocks
34/37
Stocks and Books
The best and
worst
performance ofstock, bond, T-
bills for various
holding periods.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
35/37
Stocks and Books
Be aware!
This is the
nominalchart. I.e.
inflation and
taxes are not
accounted
for.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
36/37
Stocks and Books
While less than
the nominal
numbers, thedifference
between the
various classes
is clear.
-
8/14/2019 Scc2301 Ch4 l07 Stocks
37/37
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
Youd betternotlet me manage yourmoney!