Scribd Logo
Upload

Welcome to Scribd!

  • 79 views

    ACTL 2111/ACTL5102 Financial Mathematics Excel Tutorial Series

    Uploaded by

    Jingwen Shi
    financial mathematics

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    ACTL 2111/ACTL5102 Financial Mathematics Excel Tutorial Series

    Uploaded by

    Jingwen Shi
    79 views16 pages
    financial mathematics

    Original Title

    Week 5

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    financial mathematics

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    79 views16 pages

    ACTL 2111/ACTL5102 Financial Mathematics Excel Tutorial Series

    Uploaded by

    Jingwen Shi
    financial mathematics

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 16

    Week 5: Random Generator and Statistical Functions

    ACTL 2111/ACTL5102 Financial Mathematics


    Excel tutorial series

    Xinda Yang

    © University of New South Wales (2017)


    School of Risk and Actuarial Studies, UNSW Business School
    xinda.yang@unsw.edu.au

    Week 5:
    Random Generator and Statistical Functions 1

    1
    1/16 Reference: link
    Week 5: Random Generator and Statistical Functions

    1 Goals

    2 Distributions in Excel

    3 Simulation

    4 Statistical analysis

    2/16
    Week 5: Random Generator and Statistical Functions
    Goals

    1 Goals

    2 Distributions in Excel

    3 Simulation

    4 Statistical analysis

    3/16
    Week 5: Random Generator and Statistical Functions
    Goals

    Goals of this tutorial

    By the end of this tutorial, you should be able to


    use Excel to simulate random observations
    perform preliminary statistical analysis

    4/16
    Week 5: Random Generator and Statistical Functions
    Distributions in Excel

    1 Goals

    2 Distributions in Excel

    3 Simulation

    4 Statistical analysis

    5/16
    Week 5: Random Generator and Statistical Functions
    Distributions in Excel

    Normal distribution

    NORM.DIST(x,mean,standard_dev,cumulative)
    mean and standard_dev are parameters of a normal r.v.
    cumulative: TRUE for c.d.f. and FALSE for p.d.f.
    x: the value where the function will be evaluated
    link to official support

    6/16
    Week 5: Random Generator and Statistical Functions
    Distributions in Excel

    Normal distribution

    NORM.INV(probability,mean,standard_dev)
    mean and standard_dev are parameters of a normal r.v.
    probability: the (cumulative) distribution
    link to official support

    7/16
    Week 5: Random Generator and Statistical Functions
    Distributions in Excel

    Normal distribution

    What is the result of


    NORM.INV(NORM.DIST(5,2,3,TRUE),2,3)
    NORM.DIST(NORM.INV(0.8,2,3),2,3,TRUE)

    8/16
    Week 5: Random Generator and Statistical Functions
    Distributions in Excel

    Distributions in Excel

    There are more distributions in Excel


    They can be found at Formulas → More Functoins →
    Statistical
    Most of them allow for both distribution (c.d.f. and p.d.f./p.m.f.)
    and inverse c.d.f. calculations (Poisson is an exception -
    inverse c.d.f. is not included).
    Excel also offer functions that are compatible with older
    versions
    e.g. NORMDIST, NORMINV
    avoid using these since they may not be available in future
    versions

    9/16
    Week 5: Random Generator and Statistical Functions
    Simulation

    1 Goals

    2 Distributions in Excel

    3 Simulation

    4 Statistical analysis

    10/16
    Week 5: Random Generator and Statistical Functions
    Simulation

    Generating uniform random values

    The most basic simulation is to genrate uniform random variables


    RAND(): generate uniform values between 0 and 1
    RANDBETWEEN(); generate uniform values between two
    specified numbers

    11/16
    Week 5: Random Generator and Statistical Functions
    Simulation

    Manual calculation I

    Both RAND() and RANDBETWEEN() functions return a new ran-


    dom value every time the worksheet is calculated
    Formulas → Calculation → Calculation Options: select
    Manual
    This will stop Excel from automatic calculation
    Keep the current results (for error checking, etc)
    Reduce the calculation load (e.g. having thousands of
    simulations and complex calculation)

    12/16
    Week 5: Random Generator and Statistical Functions
    Simulation

    Manual calculation II

    Manually enable the calculation


    update the whole workbook: F9 or use Formulas →
    Calculation → Calculation Now
    update only one worksheet: Formulas → Calculation →
    Calculation Sheet

    13/16
    Week 5: Random Generator and Statistical Functions
    Simulation

    Generating random values of a continuous distribution

    Continuous random variables with closed-form FX−1 can be gener-


    ated via the inverse transformation.
    if U ∼ unif(0,1), then FX−1 (U ) ∼ FX . (you can try to prove this
    yourself !)
    Algorithm
    Determine FX−1 (x )
    Generate random observations, u1 , . . . , un , following the
    Unif(0,1) distribution e.g. via RAND()
    Transform the uniform observations by using the FX−1 (ui ) for
    i = 1, . . . , n

    14/16
    Week 5: Random Generator and Statistical Functions
    Statistical analysis

    1 Goals

    2 Distributions in Excel

    3 Simulation

    4 Statistical analysis

    15/16
    Week 5: Random Generator and Statistical Functions
    Statistical analysis

    Some useful functions

    statistical: SUM, COUNT, AVERAGE, MAX, MIN, ABS,


    MEDIAN, MODE, STDEV.P, STDEV.S, VAR.P, VAR.S
    statistical+logical: SUMIF, SUMIFS, COUNTIF, COUNTIFS,
    AVERAGEIF, AVERAGEIFS
    Note:
    A colon(:) - a range of cells
    A comma (,) - two cells

    16/16

    You might also like