Beta distribution in Excel: uncertain shares, rates and percentages
In short: an uncertain percentage, such as a renewal rate. It always stays between 0% and 100%; two numbers set where it centres and how sure you are.
The beta distribution describes an uncertain share, rate or percentage: a value that must stay between 0 and 1, such as a renewal rate, a market share or a yield. Two shape numbers, alpha and beta, set it; its mean is alpha / (alpha + beta). In Excel, =BETA.INV(RAND(), alpha, beta) draws from it. A renewal rate with alpha 8 and beta 2 averages 80.0%, with a P10 of 63.2% and a P90 of 93.9%.
What a beta distribution is
A normal distribution would let a percentage drop below 0% or exceed 100%. A beta distribution cannot: it lives on the interval from 0 to 1, and its two shape numbers bend it to almost any hump inside. The larger alpha is compared with beta, the closer the values sit to 1; the larger both are, the narrower the hump.
How to read the chart: each line is one pair of alpha and beta. Where a line peaks is where the percentage most likely lands; a narrow peak means more certainty.
Formulas: mean, peak, standard deviation
| Quantity | Formula and example |
|---|---|
| Mean | α / (α + β) = 8 / 10 = 80.0% |
| Peak (mode) | (α − 1) / (α + β − 2) = 87.5% |
| Standard deviation | √(α β / ((α + β)² (α + β + 1))) = 12.1% |
| Chance below 60% | =BETA.DIST(0.6, 8, 2, TRUE)= 7.1% |
Setting alpha and beta from a mean and a standard deviation
Few people think in alpha and beta. If you can say “about 80%, give or take 10 points” (a mean of 0.8 and a standard deviation of 0.1), the parameters follow: α + β = mean × (1 − mean) / sd² − 1 = 15, then α = mean × 15 = 12 and β = 3. That beta is narrower than the one above (standard deviation 12.1%): its P10 – P90 runs from 66.3% to 91.9%. A useful reading of α + β: it behaves like the number of observations behind the estimate, so a rate seen in a few cases gets a wide beta and one seen in hundreds a narrow one.
The beta distribution in Excel
BETA.INV(probability, alpha, beta, [A], [B]) returns a percentile, and with RAND() a random draw: =BETA.INV(RAND(),D16,F16). The optional A and B stretch the distribution onto another interval, such as 20 to 60 days, which is how a PERT distribution is drawn: a PERT is a beta stretched from min to max. BETA.DIST(x, alpha, beta, TRUE) gives the chance of a value below x.
| Cell | Formula | Value |
|---|---|---|
| Random number u (I16) | =RAND() | |
| Draw (J16) | =BETA.INV(I16, | |
| Draw in one cell | =BETA.INV(RAND(), | |
| Mean (K16) | =D16/(D16+F16) | 80.0% |
| P10 (L16) | =BETA.INV(0.1, | 63.2% |
| P90 (M16) | =BETA.INV(0.9, | 93.9% |
Check the numbers: exact values against 10,000 draws from Excel and from xellstorm
| Exact | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Mean | 80.0% | 79.9% | 80.0% |
| Standard deviation | 12.1% | 12.1% | 12.1% |
| P10 | 63.2% | 62.6% | 63.2% |
| P50 (median) | 82.0% | 81.9% | 82.0% |
| P90 | 93.9% | 93.8% | 93.9% |
Excel draws with RAND(), fully at random; xellstorm spreads its draws evenly over the range (Latin Hypercube sampling), so its statistics land closer to the exact values.
When to use a beta distribution
- Rates and shares: conversion, renewal and default rates, market shares, yields, the share of a budget spent on time.
- Any bounded quantity with a shape you want to control, stretched onto its interval; for a min, most likely and max, PERT does this for you.
Entering a beta distribution in xellstorm
- Make the cell an input and, on the Distributions step, choose Beta. Type a and b (alpha and beta); loc is the lower end and scale the width of the interval, 0 and 1 for a share.
- With past rates, “From data” in the side panel fits a beta to values between 0 and 1; for another interval, type it under “Beta: known range” and refit.
“Import from workbook” converts =BETA.INV(RAND(), alpha, beta), with or without A and B, into a beta input, and add-in models convert from RiskBeta, RiskBetaGeneral, VoseBeta and PsiBeta.
Questions
How do I generate beta-distributed random numbers in Excel?
Use =BETA.INV(RAND(), alpha, beta), for example =BETA.INV(RAND(), 8, 2) for a share averaging 80.0%. Add a lower and upper limit as the fourth and fifth arguments to draw on another interval.
What do alpha and beta mean?
Their ratio sets the centre and their sum sets the certainty. The mean is α / (α + β); the larger α + β, the narrower the distribution. α = β gives a symmetric shape around 50%; α = β = 1 is uniform between 0 and 1.
How do I choose alpha and beta from a mean and standard deviation?
Compute k = mean × (1 − mean) / sd² − 1, then α = mean × k and β = (1 − mean) × k. For a mean of 0.8 and a standard deviation of 0.1: k = 15, α = 12, β = 3. The standard deviation must be below √(mean × (1 − mean)), or no beta exists.
Related
- Guide
Which probability distribution should I use? A guide for Monte Carlo models
Find the right probability distribution in two questions: PERT, triangular, normal, lognormal, Weibull, Poisson, binomial, beta and more, with what each is for and its Excel formula. - Guide
PERT distribution in Excel: formula, mean and when to use it
A PERT is a beta distribution from min to max with mean (min + 4 × likely + max)/6. The Excel BETA.INV formula, and PERT vs triangular. - Guide
Binomial and Bernoulli distributions in Excel: yes-or-no events and counts out of n
A Bernoulli distribution is one yes-or-no event, such as a risk that occurs or not; a binomial counts the yeses out of n tries. Excel formulas, BINOM.DIST and BINOM.INV, and risk registers.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.