Stop Copy-Pasting Excel Errors — Use This Google Sheets NPV/IRR Evaluator
Every week I see consultants and small business owners waste hours on manual DCF calculations. They open Excel, forget to update the discount rate, miss terminal values, or accidentally shift a row. Then they present a wrong decision to their team.
Here's a better way: a pre-built Google Sheets evaluator that does NPV, IRR, Payback Period, and Profitability Index with zero macros. I built one for myself, and now I'm sharing the framework.
What's Inside
Tab 1: Quick Calculator
Just cash flows in rows 4 to 23, discount rate in B3, and formulas do the rest:
-
=NPV(B3,C4:C23)+C4(proper NPV including initial investment) -
=IRR(C4:C23) - Payback using cumulative logic
- Profitability Index = PV of inflows / initial outlay
Tab 2: Side-by-Side (Project A vs B vs C)
Perfect for capital budgeting decisions. All three NPVs, IRRs, paybacks, PIs in one view.
Tab 3: Sensitivity Table
A 5x5 grid varying discount rate ±2% and terminal growth rate ±1%. Conditional formatting turns cells green/red — instantly see risky scenarios.
Tab 4: Dashboard
Bar chart comparing NPVs, sparkline for cumulative cash flow, and a summary tile: Accept if NPV>0 and IRR>discount rate.
Real Example
$100k investment, $25k/year for 5 years, 10% discount:
- NPV = -$5,224 → Reject
- IRR = 8.8% → below hurdle
- Payback > 5 years
Get the Template
Instead of rebuilding it, grab the fully editable copy here:
[gumroad template link]
This saved me 3 hours last quarter. Adapt it for your next project.
Top comments (0)