The layout
- Years across, items down. Each column is a year, oldest on the left. Each row is one item (revenue, costs, growth). This is the usual analyst layout. Careful: the SEC's statement tables put the newest year first, so when you copy from them, put the years back in oldest-first order.
- Inputs one colour, formulas another. An input is a number you typed in from a source. A formula is a cell that calculates from other cells. The common convention: inputs in blue, formulas in black. Then you can see at a glance what's data and what's math.
- Never type a number inside a formula. Put a growth rate or discount rate in its own cell and point to it. Change it once and everything updates.
- Write down the source and date next to the inputs.
Four formulas to know
Year-over-year growth (YoY): how much bigger this year is than last year.
=C2/B2-1
CAGR, the compound annual growth rate from lesson 03:
=(end/start)^(1/periods)-1
Or the built-in version: =RRI(periods, start, end).
NPV (net present value): the present value of all future cash, minus what it costs today. Each amount of money going in or out in a given year is called a cash flow. You did this by hand in lesson 02. Positive NPV means the cash coming back is worth more today than the cost.
=NPV(rate, future cash flows) + today's cash flow
Careful: the NPV function treats the first value you give it as one year away. So leave today's cost out of the brackets and add it separately (it's negative, because you pay it).
IRR (internal rate of return): the discount rate that makes the NPV exactly zero. Think of it as the yearly return the investment earns. =IRR(range) takes all the cash flows, including today's cost.