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

1
Practical 4 Dollar Cost Averaging, Compounding
and Statistical Excel Functions

• Gopalan Vivek
• vivek_at_bic.nus.edu.sg

2
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/Practical
  s/practical1.ppt

3
(No Transcript)
4
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.

5
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
6
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
7
Statistical Functions - ?

• What value does AVERAGEA function in Excel
  returns ?
• 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/Practic
als/practical_4.html
8
Power of Compounding

• http//mutualfunds.about.com/library/weekly/aa1121
  00a.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
9
Compounding calculations in Excel
FV function is used for the calculation of
Compounding problems in Excel
10
Formula
- Obtained from Excel help
11
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
12
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 412 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/Practic
als/practical_4.html
13
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)
14
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
• What is the value of his total investment after 6
  months ?

15
DCA - Answer
16
Dollar Cost Averaging

• http//www.statelinebusiness.com/501/mony5.htm
• http//www.westcore.com/InvestorEd/reg-invest.asp
  
• http//www.datalife.com/mall/pages/examples/EXMP_D
  CA.HTM
• http//www.phn.com/planni/planni_featur_dollar.asp
  
• http//www.cifunds.com/web/straighttalk/straight.j
  sp?langENGno5
• http//www.ameritrade.com/educationv2/fhtml/learni
  ng/dolcstave.fhtml

17
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/L
ectureSlides/Class2_Stocks_Slides_Handout.pdf
18
International Stock Markets e.g.
http//faculty.fuqua.duke.edu/mroberts/Teaching/L
ectureSlides/Class2_Stocks_Slides_Handout.pdf
19
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/

20
Solve the Effect of compounding question
(question no. 4) in the practical 4 and submit
your answers http//chaos.nus.edu.sg/Teaching/SCC
2301/Practicals/practical_4.html