Product Pricing Research & Discrete Choice Econometrics
Conjoint Part-Worth & Multinomial Logit Share Formula
Calculate Conjoint Net Part-Worth Utility U_A = alpha_A - beta * Price_A, McFadden Multinomial Logit (MNL) Choice Shares EXP(U_i) / SUM(EXP(U_j)), and IIA Relative Odds Ratio EXP(U_A - U_B).
Convert conjoint part-worth utilities into probabilistic market shares via McFadden’s MNL!
In Choice-Based Conjoint (CBC) pricing simulators, summing feature part-worths minus price disutility gives deterministic utility U_A = Feature_Worth_A - beta_price * Price_A. Instead of a brittle "first-choice winner-takes-100%" rule that jumps discontinuously by $0.01, Nobel laureate Daniel McFadden’s Multinomial Logit (MNL) model adds Gumbel extreme-value unobserved taste shocks to map utilities into smooth choice probabilities across Product A, Competitor B, and the Outside "No-Purchase" Option 0: Share_A = EXP(U_A) / (EXP(U_A) + EXP(U_B) + EXP(U_0))! Crucially, MNL exhibits Independence of Irrelevant Alternatives (IIA): the relative odds ratio Share_A / Share_B = EXP(U_A - U_B) depends ONLY on U_A - U_B—warning analysts of the "Red-Bus/Blue-Bus" proportional-substitution trap when adding near-clone SKUs!
Verify on Feature_Part_Worth_A = 5.00 utils (B2), Monthly_Price_A = $30.00 (B3), Price_Disutility_Slope beta = 0.10 utils/$ (B4 -> Net Utility U_A = 5.00 - 3.00 = 2.00 utils in E2), Competitor_Utility U_B = 1.00 util (B5), and Outside_NoBuy_Utility U_0 = 0.00 utils (B6): denominator sum is EXP(2) + EXP(1) + EXP(0) = 7.3891 + 2.7183 + 1.0000 = 11.1073, yielding Product A Share = 0.6652 (66.52% in E3), Competitor B Share = 0.2447 (24.47% in E4, with 9.00% choosing No-Buy), and Relative Odds A/B = EXP(2 - 1) = 2.7183 (E5)!
| Conjoint MNL Choice Metric (WorthA=5, PriceA=$30, beta=0.10, UB=1, U0=0) | Formula | Verified Output |
|---|---|---|
| Product A Net Utility U_A | Feature_Worth_A - beta * Price_A | 2.0000 utils (1.0000 @ $40) |
| MNL Preference Share (Product A) | EXP(U_A) / (EXP(U_A) + EXP(U_B) + EXP(U_0)) | 0.6652 (66.52%; 0.3333 Equal) |
| MNL Preference Share (Comp B) | EXP(U_B) / (EXP(U_A) + EXP(U_B) + EXP(U_0)) | 0.2447 (24.47%) |
| IIA Relative Odds Share_A / Share_B | EXP(U_A - U_B) (Invariant to U_0!) | 2.7183 (Unchanged @ U_0=2!) |
McFadden Multinomial Logit (MNL) Choice Probability Formula
Validate strictly positive price-disutility slope beta > 0, non-negative price Price_A >= 0, and bounded utility range ABS(U) <= 50 to prevent floating-point overflow!
=LET(wr_a, B2, pr_a, B3, bt_p, B4, u_b, B5, u_0, B6, u_a, wr_a - bt_p * pr_a, IF(OR(pr_a < 0, bt_p <= 0, ABS(u_a) > 50, ABS(u_b) > 50, ABS(u_0) > 50), "Invalid Conjoint Utility", LET(dn_m, EXP(u_a) + EXP(u_b) + EXP(u_0), ROUND(EXP(u_a) / dn_m, 4))))Three-Way Equal-Utility Symmetry & IIA Invariance Checks
Raise Price_A to $40 (so U_A = 1.00) and set U_0 = 1.00: all three options tie at 0.3333 (33.33%) with Odds = 1.0000! Or keep U_A = 2, U_B = 1 and raise U_0 to 2: Share_A drops, yet IIA Odds E5 stays 2.7183!
=IF(ISTEXT(E3), E3, ROUND(EXP(E2 - B5), 4))Try the example
Use the original values or change them to test your understanding.