# practical

Document Sample

```					Practical 4: Dollar Cost Averaging,
Compounding and Statistical Excel
Functions

Gopalan Vivek
vivek@bic.nus.edu.sg
Objectives
Statistical   functions
– MAX, MIN, AVERAGE, MODE,MEDIAN
Compounding
Dollar
Cost Averaging
Complete questions in Practical 4 given below
http://chaos.nus.edu.sg/Teaching/SCC2301/Practicals/practical1.p
pt
Common Statistical Functions
   MAX
– returns maximum of a range of cell values
   MIN
–   returns minimum of a range of cell values.
   AVERAGE
–   returns average or mean of cell values.
Statistical Functions
   MEDIAN
– Returns middle value of an ordered array.
– Unaffected by the outliers, thus most
appropriate measure of central tendency
when outliers are present in the data
   MODE
– Returns the most frequently occurring, or
repetitive, value in an array or range of
data.
– Not affected by outliers
Check the help for other Statistical functions in Excel
Statistical Functions - Excel Hints
   The arguments should be numbers, names, arrays,
or references that contain numbers.
   If an array or reference argument contains text,
logical values, or empty cells, those values are
ignored; however, cells with the value zero are
included.
   If the data set contains no duplicate data points,
MODE returns the #N/A error value.
   If there is an even number of numbers in the set, then
MEDIAN calculates the average of the two numbers
in the middle.

- Obtained from Excel help
Statistical Functions - ?
1.     What value does AVERAGEA function in Excel
returns ?
2.     What Excel function is used to calculate the third
and fourth largest numbers of the following set
1,4,6,7,25, 28, 8,12, 20, 22, 24 ?

Solve the question no. 1 in the practical 4
http://chaos.nus.edu.sg/Teaching/SCC2301/Practicals/practical_4.html
Power of Compounding

ekly/aa112100a.htm
 http://www.rrsp.org/compounding.htm
 http://www.moneycontrol.com/planning_
desk/powerofc.php
"Compounding interest is the greatest mathematical
discovery of all time". Albert Einstein
Compounding calculations in Excel

FV function is used for the calculation of Compounding
problems in Excel
Formula

- Obtained from Excel help
Arguments
   FV (rate, nper, pmt, pv, type)
– Rate is the interest rate per period.
– Nper is the total number of payment periods in an annuity.
– Pmt is the payment made each period; it cannot change
over the life of the annuity. Typically, pmt contains principal
and interest but no other fees or taxes. If pmt is omitted, you
must include the pv argument.
– Pv is the present value, or the lump-sum amount that a
series of future payments is worth right now. If pv is omitted,
it is assumed to be 0 (zero), and you must include the pmt
argument.
– Type is the number 0 or 1 and indicates when payments are
due. If type is omitted, it is assumed to be 0.

- Obtained from Excel help
FV function Arguments criteria
   Make sure that you are consistent about the
units you use for specifying rate and nper.
–    If you make monthly payments on a four-year loan at 12 percent
annual interest, use 12%/12 for rate and 4*12 for nper. If you make
annual payments on the same loan, use 12% for rate and 4 for
nper.

 cash you pay out, such as deposits to
savings, is represented by negative numbers
 cash you receive, such as dividend checks, is
represented by positive numbers.
- Obtained from Excel help
Solve the question no. 2 in the practical 4
http://chaos.nus.edu.sg/Teaching/SCC2301/Practicals/practical_4.html
Dollar Cost Averaging (DCA) -
definition
   An investment strategy designed to reduce volatility in which
securities, typically mutual funds, are purchased in fixed dollar
amounts at regular intervals, regardless of what direction the
market is moving. Thus, as prices of securities rise, fewer units
are bought, and as prices fall, more units are bought.
http://www.investorwords.com/

As mentioned in your practical web page go to your
favorite search engine and find out more about dollar
cost averaging (DCA)
DCA – Simple e.g.

   A person has invested 100 dollar/month
in stock market by dollar cost averaging.
The share price values of the stocks he bought for 6 months are given as
Month    Price per
Share
1           \$8.20
2           \$8.25
3           \$8.30
4           \$8.35
5           \$8.40
6           \$8.45

   What is the value of his total investment after 6 months ?
Month      Am ount    Price per     # Share      # Total    # Total
Invested     Share       Purchased     Share     value of
Purchased investem e
nt
1         \$100           \$8.20       12.20       12.20     100.00
2         \$100           \$8.25       12.12       24.32     200.61
3         \$100           \$8.30       12.05       36.36     301.83
4         \$100           \$8.35       11.98       48.34     403.64
5         \$100           \$8.40       11.90       60.25     506.06
6         \$100           \$8.45       11.83       72.08     609.07
\$600

Total Amount Invested                  \$600
total value of investment after 6     \$609.07
months
Dollar Cost Averaging
   http://www.westcore.com/InvestorEd/reg-invest.asp
   http://www.datalife.com/mall/pages/examples/EXMP_
DCA.HTM
   http://www.phn.com/planni/planni_featur_dollar.asp
   http://www.cifunds.com/web/straighttalk/straight.jsp?l
ang=ENG&no=5
g/dolcstave.fhtml
U. S. Stock Markets – e.g.

   New York Stock Exchange (NYSE)
   American Stock Exchange (AMEX)
   National Association of Securities Dealers (NASDAQ)

http://faculty.fuqua.duke.edu/~mroberts/Teaching/LectureSlides/Class2_Stocks_Slides_Handout.pdf
International Stock Markets – e.g.

http://faculty.fuqua.duke.edu/~mroberts/Teaching/LectureSlides/Class2_Stocks_Slides_Handout.pdf
Dow Jones Industrial Average

 the average of 30 bull chip stocks hand
picked by the Wall Street Journal
editors – DOW 30
 The dow is an indication of the well
being of the overall market.
 http://www.dowjones.com/
Solve the ‘Effect of compounding’ question (question no. 4) in the