Lesson 6 of 6

The analyst's spreadsheet

Step 3 of 6

Worked example

Apple's revenue, FY2022 to FY2026. Apple's fiscal year (its accounting year) ends in late September, so FY2026 means the year ended 26 Sep 2026. Revenue is the money Apple took in from sales before any costs. Figures from SEC XBRL data (Apple's 10-K filings: a company's annual report filed with the US regulator, the SEC; Module 3, lesson 01 introduces it), checked 20 Nov 2026, in billions of dollars:

A B C D E F
1 FY2022 FY2023 FY2024 FY2025 FY2026
2 Revenue ($B) 394.328 383.285 391.035 416.161 478.834
3 YoY growth −2.8% +2.0% +6.4% +15.1%
4 CAGR 4.97%

Row 2 is inputs (blue). Rows 3 and 4 are formulas (black).

Row 3: year-over-year growth. In C3 type =C2/B2-1, then drag it right to F3.

FY2023: 383.285 ÷ 394.328 − 1 = −2.8%
FY2024: 391.035 ÷ 383.285 − 1 = +2.0%
FY2025: 416.161 ÷ 391.035 − 1 = +6.4%
FY2026: 478.834 ÷ 416.161 − 1 = +15.1%

Row 4: CAGR. In B4 type =(F2/B2)^(1/4)-1 or =RRI(4,B2,F2).

Five years of data = four growth periods (22→23, 23→24, 24→25, 25→26)

CAGR = (478.834 ÷ 394.328)^(1/4) − 1
     = 1.2143^0.25 − 1
     = 1.0497 − 1
     = 4.97% a year

Check: 394.328 × 1.0497 × 1.0497 × 1.0497 × 1.0497 ≈ 478.8. It works.

The classic mistake: using 5 instead of 4 because you see five columns. That gives 3.96%, which is wrong. Count the gaps between years, not the years.

Notice what CAGR hides: a smooth-looking 5% a year was really a dip, two slow years, then a big jump. Always look at the YoY row too.

NPV and IRR in two cells. A small, made-up investment: pay $1,000 today, get $400 at the end of each of the next three years. Put the cash flows in B8:E8 (−1000, 400, 400, 400) and the discount rate, 8%, in B9.

=NPV(B9, C8:E8) + B8

By hand: 400 ÷ 1.08     = 370.37
         400 ÷ 1.08²    = 342.94
         400 ÷ 1.08³    = 317.53
         total           = 1,030.84
         minus 1,000     = +30.84

NPV is +$30.84, so at 8% the deal is worth more than it costs. =IRR(B8:E8) gives 9.7%: at that rate the NPV would be exactly zero. IRR (9.7%) is above the 8% rate, which agrees with a positive NPV.