Step 7: terminal value and firm value (lesson 06)
B20 PV of years 1–5 =SUM(I14:I18) → 27,919
B21 terminal value =G19/(B5-B6) → 10,199 ÷ 0.046 = 221,717
B22 its value today =B21*H18 → 221,717 ÷ 1.086^5 = 146,775
B23 firm value =B20+B22 → 174,693
The terminal value is 84% of the firm value.
Step 8: the equity bridge at 30 Aug 2026 (lesson 07)
B24 equity value =B23+B7-B8 → 174,693 + 21,301 − 6,162 = 189,832
B25 value per share =B24/B9 → 189,832 ÷ 445.129 = $426.47
Leases are left out.
Step 9: an event after the balance sheet
On 10 Nov 2026 Costco declared a $30 special dividend, $13,294M ($30 × 443.129M shares), payable 4 Dec 2026. That cash leaves the company:
B26 dividend 13294
B27 equity after dividend =B24-B26 → 176,538
B28 per share =B27/B9 → $396.60
The 19 Nov close of $1,093.83 still carries the dividend, so compare like with like: $426.47 against $1,093.83, or $396.60 against about $1,063.83 after it. Either way the model gives under 40% of the price.
What the gap means
The template sits about 15% low on its own (lesson 07); this gap is about 60%, so most of it isn't the tool. In this model the price needs an operating margin of about 8.9% every year (more than twice today's), or a 5.7% cost of capital (a beta near 0.15), or 52 years of 9% growth. So the reading is lesson 09's: the price assumes much more than Costco's history, perhaps more membership-fee income or growth for far longer than five years. That's what the method can tell you; whether the market is right is a question for your evidence, not for the model.