Document Sample
practical Powered By Docstoc
					Practical 4: Dollar Cost Averaging,
Compounding and Statistical Excel

  Gopalan Vivek
Statistical   functions
      Cost Averaging
Complete questions in Practical 4 given below
Common Statistical Functions
   MAX
    – returns maximum of a range of cell values
   MIN
    –   returns minimum of a range of cell values.
    –   returns average or mean of cell values.
Statistical Functions
    – 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
    – 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
   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
Power of Compounding

"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

          - Obtained from Excel help
   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
    – 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

         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
Dollar Cost Averaging (DCA) -
   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.

     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
                                 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 ?
DCA - Answer
   Month      Am ount    Price per     # Share      # Total    # Total
             Invested     Share       Purchased     Share     value of
                                                  Purchased investem e
     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

 Total Amount Invested                  $600
 total value of investment after 6     $609.07
Dollar Cost Averaging
U. S. Stock Markets – e.g.

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

International Stock Markets – e.g.

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.
Solve the ‘Effect of compounding’ question (question no. 4) in the
                 practical 4 and submit your answers

Shared By: