Question

2. An insurance company insures large number of independent individual houses. The expected average loss for each house for 1 year period is $600, and the standard deviation of the average loss is $100. Let us assume that the average loss follows the normal distribution. Using Normdist() function in Excel, calculate the probability that the average loss will exceed $850.

Answer 1

Given Information:

Mean = μ = $600

Standard deviation = σ = $100

x = $850

Required Information:

Using Excel calculate P(x > $850) = ?

Answer:

P(x > $850) = 0.62%

Step-by-step explanation:

The Microsoft Excel has a built-in function "NORMDIST" that calculates the normal distribution for a given mean and standard deviation.

Syntax:

NORMDIST(x, mean, standard deviation, cumulative flag)

Where x is the value for which you want to find the distribution.

Cumulative flag = TRUE or FALSE

Procedure:

Select the cell where you want to display the result.

In the Formula tab, click the Insert function icon.

Type NORMDIST in the search for a function field and select ok.

Enter the required input parameters x, mean, standard deviation and flag or enter the cell numbers of the input parameters and press ok.

Solution:

P(x > $850) = 1 - P(x ≤ $850)

In MS Excel,

P(x ≤ $850) =NORM.DIST(B2,B3,B4,TRUE)

where B2 = 850, B3 = 600, and B4 = 100 and flag = TRUE

P(x ≤ $850) = 0.99379

P(x > $850) = 1 - 0.99379

P(x > $850) = 0.00621 = 0.62%