Question

1. A bank has had to repossess 100 homes. Fifty of these repossessed homes are expected to have market values that are less than the outstanding balance on the mortgage. An auditor randomly selects 10 different repossessed homes and records the number of homes which have market values less than the outstanding balance of the mortgage. What is the probability that all of the audited homes will have outstanding balances in excess of their mortgage?

I know for a fact that this is a binomial distribution and that's because it has two different outcomes and independent trials however I am trying to understand finding the probability in excel, can you explain how this is done in excel?

Answer 1

Solution

Let X = number of homes out of a sample of size 10, which have market values less than the outstanding balance of the mortgage. Then, X ~ B(10, p), where p = probability a home has market value less than the outstanding balance of the mortgage.

Given, ‘Fifty of these repossessed homes are expected to have market values that are less than the outstanding balance on the mortgage.’ => p = 0.5 [i.e., 50/100]. Thus, X ~ B(10, p) …….……....................................................................……. (1)

Back-up Theory

If X ~ B(n, p). i.e., X has Binomial Distribution with parameters n and p, where n = number of trials and

p = probability of one success, then, probability mass function (pmf) of X is given by

p(x) = P(X = x) = (ⁿC_x)(p^x)(1 - p)^{n – x}, x = 0, 1, 2, ……. , n …………...................................................................………..(2)

[This probability can also be directly obtained using Excel Function: Statistical, BINOMDIST……………………...…….(2a)

Now, to work out the solution,

All of the audited homes will have outstanding balances in excess of their mortgage => none of 10 homes audited has market values less than the outstanding balance of the mortgage. Hence,

The required probability

= P(X = 0)

= (¹⁰C₀)(0.5⁰)(0.5)^{10 – 0}

= 0.5¹⁰

= 0.00098 Answer

DONE

[Going beyond,

Method to find probability using Excel Function for Binomial Distribution

In Excel Worksheet, click ‘f_X’. Screen displays ‘Insert Function’ In the window ‘Search for a function’ type ‘BINOMDIST’ and click ‘Go’. Under ‘Select a function’, BINOMDIST appears. Click ‘Ok’

Screen displays four windows. Against ‘Number_s’ window, type the X value for which probability is required. Against ‘Trials’ window type the value of n. Against ‘Probability_s’ window type the value of p. Against ‘Cumulative’ window type ‘false’. Required probability, is displayed below the windows against ‘=’ sign.

If cumulative probability is required, type ‘true’ against ‘Cumulative’ window

The above method is illustrated below for P(X = 10) given X ~ B(10, 0.5)

Type ‘10’ against ‘Number_s’ window, ‘10’ against ‘Trials’ window, ‘0.5’ against ‘Probability’ window, and ‘false’ against ‘Cumulative’ window. 0.00097656 is displayed below the windows against ‘=’ sign. This is P(X = 10).

If ‘true’ is typed against ‘Cumulative’ window, display would be 1. This is P(X ≤ 10).

Complete