Financial modeling in Excel: build your first simple model

In short: a good model keeps every assumption in its own labeled cell, builds each year from the previous one with the same formula, lets you switch scenarios in one place and uses PMT, FV and PV for anything involving interest. Those habits matter more than any advanced function.

What this page covers and what it does not

Professional financial models often link an income statement, a balance sheet and a cash flow statement so that every change flows through all three, and then add a valuation on top. Building one well takes accounting knowledge and a lot of practice, and it is beyond the scope of this page. What you practice here are the foundations every larger model is made of. If you can build a small projection that is easy to check and change, you have the habits a three-statement model needs.

How a simple model is laid out

Part	What goes there	Rule of thumb
Assumptions	Growth rates, prices, cost ratios, interest, tax rate	Inputs only, no formulas, each one labeled with its unit
Calculations	Revenue, costs, profit, loan schedule by year	Formulas only, no typed numbers
Output	Key figures and charts for the reader	References to the calculations, nothing new

In a small model the assumptions can sit in a block at the top of the sheet. In a larger one they get their own tab. A widely used convention is to show inputs in blue font and formulas in black, so anyone can see at a glance which cells they are allowed to change.

Step 1: A three-year revenue projection

The core of most models is a row that grows year by year. Instead of typing a growth rate into each formula, every year refers to the same assumption cell. That makes the model easy to test: change one cell and watch every year move.

Task Revenue in 2026 was 480,000. Project 2027 to 2029 using the growth assumption in F1. In B3, write a formula that builds on the previous year. It is filled down to B5.

	A	B	C	D	E	F
1	Year	Revenue $			Growth p.a.	0.07
2	2026	480000
3	2027
4	2028
5	2029

The dollar signs in $F$1 are what make the formula safe to fill. In a model where years run across columns, you would anchor the row and column the same way, and the lesson on absolute references shows the mixed versions like $B5 and B$2. Costs often follow the same pattern as a percentage of revenue: =B3*$F$2 with the cost ratio in F2.

Step 2: Growth rates and CAGR

To set sensible assumptions you look back first. The year-over-year growth is =B3/B2-1. For the average yearly growth across several years, use the compound annual growth rate: =(B7/B2)^(1/5)-1 for five years of growth. CAGR is the rate that, applied every year, gets you from the first value to the last, which is exactly what a projection row does in reverse. Practice it in the lesson on the CAGR formula, which also covers compound interest.

Step 3: A scenario switch with IF

Nobody trusts a single forecast. A model becomes useful when you can ask "what if growth is lower?" without rebuilding anything. The simplest way is a scenario cell that the formulas read through IF.

Task B1 picks the scenario: 1 for base, 2 for downside. In B7, calculate 2027 revenue from 2026 revenue and the growth rate of the selected scenario. Use IF so the formula takes the right assumption when B1 changes.

	A	B	C
1	Scenario (1 = base, 2 = downside)	1
2
3	Assumption	Base	Downside
4	Revenue growth	0.08	0.02
5	Revenue 2026 $	850000
6
7	Revenue 2027 $

With three cases, nest the conditions or use IFS: =IFS(B1=1,B4,B1=2,C4,B1=3,D4). A cleaner layout puts the active assumptions in their own column, filled with these formulas, and lets every calculation refer only to that column. Add a drop-down list to B1 so only valid scenarios can be chosen.

Step 4: Time value of money with PMT, FV and PV

As soon as a model includes a loan, savings or an investment, money at different times has to be compared. Excel has three functions for that. They all follow the same sign rule: money you pay out is negative, money you receive is positive. That is why the examples below put a minus sign in front of the amount.

Function	Answers	Example	Result
PMT	What is the regular payment on a loan?	=PMT(6%/12,60,-40000)	773.31 per month
FV	What will regular savings be worth later?	=FV(5%/12,120,-300)	46,584.68 after 10 years
PV	What is a future amount worth today?	=PV(8%,5,0,-10000)	6,805.83 today

The most common mistake is mixing periods. With monthly payments, divide the annual rate by 12 and multiply the years by 12. The lesson on the CAGR formula includes a hands-on PMT exercise for a loan payment.

Habits that make a model trustworthy

No typed numbers inside formulas

=B2*1.07 hides an assumption where nobody will find it. =B2*(1+$F$1) shows it in a labeled cell.

One formula per row

Every cell in a projection row should hold the same formula, just shifted. If year three has a special case, it belongs in the assumptions, not in a one-off formula.

Checks you can see

Add cells that should always be zero, such as total of the parts minus the total. A check that is not zero tells you something broke before your reader notices.

Units and rounding

Label every row with its unit, and round only in the output with ROUND, not in the calculations. Rounding early adds small errors in every period.

Clean outputs

Use IFERROR to keep error values out of summary tables and a simple line chart to show how the projection develops across the years.

FAQ