Financial Modeling Basics Excel by dim29188

VIEWS: 130 PAGES: 18

More Info
									FIN 735: Financial Modeling

    Lawrence P. Schrenk.
         Instructor
1.2




                   Financial Basics




      Lawrence P. Schrenk      FIN 735: Financial Modeling
1.3


                                 Overview
          Returns
            Holding Period Return
            Annualizing Returns
            Continuous-Time Returns

          Data Sources
            Fred
            EDGAR
            Money          Central

      Lawrence P. Schrenk                   FIN 735: Financial Modeling
1.4


                            Financial Data

          Financial Data can be ambiguous. It can
           be presented
              As a dollar value or a return
                    a return of five percent can be written as either
                    ‘0.05’ or just ‘5’.
              In annual, monthly, daily, etc. form
              form new-to-old or old-to-new



      Lawrence P. Schrenk                         FIN 735: Financial Modeling
1.5


                    Holding Period Return

        The holding period return is the return
         over some period of time.
        We calculate it from prices or levels:


                 NewValue  OldValue
           HPR 
                     OldValue

      Lawrence P. Schrenk          FIN 735: Financial Modeling
1.6


           Holding Period Return (cont’d)
          Stock A sells for $46.99 in January and
           $48.51 in February.


                   $48.51  $46.99
             HPR                   0.032347308  3.23%
                       $46.99


          But note this is the monthly return!

      Lawrence P. Schrenk                FIN 735: Financial Modeling
1.7


           Holding Period Return (cont’d)

        But this is not very useful if we are
         comparing assets with different holding
         periods.
        How do you easily tell whether a
         quarterly or monthly return is better?




      Lawrence P. Schrenk         FIN 735: Financial Modeling
1.8


                      Annualizing Returns
          Returns are typically ‘annualized’ to give
           their equivalent annual return (EAR):

           EAR  (1  rperiod )                            1
                                  mnumberofperiodsinyear


          For our example:

              EAR  (1  0.032347)  1           12


                 0.4652388  46.52%
      Lawrence P. Schrenk                    FIN 735: Financial Modeling
1.9


             Annualizing Returns (cont’d)
          We can also move in the opposite direction:
                                                 1
                                       mnumberofperiodsinyear

                rperiod  1  EAR                             1

          For our example:
                                           1


                  rperiod  1.4652   1
                                          12




                       0.032344724  3.23%
      Lawrence P. Schrenk                                  FIN 735: Financial Modeling
1.10


                             Continuous Time

           In some applications, especially,
            derivatives, it is easiest to use
            continuous discounting/compounding.
            This is to assume that there are an
            infinite number of periods, m, in a year.




       Lawrence P. Schrenk               FIN 735: Financial Modeling
1.11


                 Continuous Time (cont’d)
           The formula is:

                    rperiod ,continuous  e
                                          rcontinuous t
                                                          1

             Note,    that in Excel, this would be:

                                  EXP(r*t) - 1



       Lawrence P. Schrenk                                 FIN 735: Financial Modeling
1.12


                 Continuous Time (cont’d)
           If the annual continuous time rate is 10%,
            then the semi-annual, continuous rate is:

              rcontinuous ,semi annual  e   0.10*.5
                                                        1
                     0.051271  5.13%


       Lawrence P. Schrenk                    FIN 735: Financial Modeling
1.13


                 Continuous Time (cont’d)
           Finally, we can convert the discrete annual
            rate to the continuous annual rate as follows:

            rdiscrete,annual  e                         1
                                   rcontinuous ,annual


               or
            rcontinuous ,annual  ln 1  rdiscrete ,annual 
       Lawrence P. Schrenk                           FIN 735: Financial Modeling
1.14


                 Continuous Time (cont’d)
           An annual, continuous rate of 10% is
            equivalent to:

         rdiscrete,annual  e  0.10
                                      1  10.52%



       Lawrence P. Schrenk              FIN 735: Financial Modeling
1.15


                                 Summary

           You should be able to
             Convert        dollar returns to holding period
              returns,
             Holding period returns to EAR (and vice
              versa), and
             Use continuous-time returns and convert
              between continuous and discrete returns.



       Lawrence P. Schrenk                      FIN 735: Financial Modeling
1.16


                              Data Sites

           FRED
             http://research.stlouisfed.org/fred2/
             Data
                          rates
                  Interest

                  CPI, PPI

                  Banking Data

                  Macroeconomic Data




       Lawrence P. Schrenk                 FIN 735: Financial Modeling
1.17


                         Data Sites (cont’d)
           Edgar
             http://www.sec.gov/edgar.shtml
             SEC   files since 1993
             Millions of forms

           EdgarScan
             http://edgarscan.pwcglobal.com/servlets/ed
              garscan
             A better interface/search engine.


       Lawrence P. Schrenk               FIN 735: Financial Modeling
1.18


                         Data Sites (cont’d)

           Money Central
             http://moneycentral.msn.com/
             An  MSN/Microsoft Product
             Company Data
             Easy Download of Stock Prices
             You do need to install some of their
              software


       Lawrence P. Schrenk               FIN 735: Financial Modeling

								
To top