Guides

Finance, accounting & Excel guides

One question per guide, answered with a worked example, the formula in Excel and the mistakes to avoid. Then practise it in a lab that checks your answer.

Excel formulas

Lookups, conditional sums and schedules, explained on finance data.

SUMIFS Examples: Sum with Multiple Criteria in Excel

SUMIFS examples with real data: two criteria, a date range, greater than, wildcards, "not equal" and COUNTIFS, plus the mistakes that return 0.

5 min read

XLOOKUP vs VLOOKUP: Differences and When to Use Each

XLOOKUP vs VLOOKUP side by side: exact match defaults, lookups to the left, not-found values, approximate matches and compatibility, with a worked example.

5 min read

Loan Amortization Schedule in Excel: PMT, IPMT & PPMT

Build a loan amortization schedule in Excel with PMT, IPMT and PPMT: payment, interest, principal and balance per month, with a worked example.

5 min read

How to Make a Waterfall Chart in Excel (2 Methods)

How to make a waterfall chart in Excel: the built-in chart with Set as Total, and the stacked-column method for older Excel. Example with formulas.

7 min read

How to Make a Histogram in Excel: Bins, Chart & COUNTIFS

How to make a histogram in Excel: the built-in chart with bin width and overflow bins, a COUNTIFS bin table, FREQUENCY and the ToolPak. Excel example.

7 min read

Excel Dashboard Tutorial: KPI Cards, Charts & a Month Picker

Excel dashboard tutorial in six steps: plan the KPIs, build KPI cards with INDEX/MATCH and SUMIFS, a month picker, trend and target charts, sparklines.

6 min read

SQL JOIN Explained with Excel: INNER vs LEFT JOIN

SQL JOINs explained with Excel: an INNER and a LEFT JOIN rebuilt with XLOOKUP, why unmatched rows vanish, NULL as "Unknown" and the duplicate-key trap.

5 min read

LINEST in Excel: Multiple Regression with a Worked Example

How to run a multiple regression in Excel with LINEST: coefficients in reverse order, standard errors, t stats, p-values, R² and a forecast. Excel example.

5 min read

Haversine Formula in Excel: Distance from Latitude/Longitude

The haversine formula in Excel: great-circle distance between two latitude/longitude points in miles or km, step by step, with a ready-to-copy formula.

4 min read

Nested IF vs IFS in Excel: Examples and When to Use Each

Nested IF vs IFS in Excel with a worked example: grade bands, commission tiers, why the order of tests matters, the TRUE fallback and XLOOKUP.

5 min read

NETWORKDAYS and WORKDAY in Excel, with Holidays

NETWORKDAYS counts working days, WORKDAY returns a due date. Excel example with a holiday list, NETWORKDAYS.INTL weekends and the off-by-one trap.

6 min read

Law of Cosines in Excel: Find a Side or an Angle

Law of cosines formulas for Excel: the third side from two sides and an angle, any angle from three sides with ACOS and DEGREES, and the RADIANS trap.

5 min read

Extract Text Between Two Characters in Excel (MID + FIND)

How to extract text between two characters in Excel with MID and FIND: brackets, the 2nd and 3rd hyphen, @ and a dot, TEXTBEFORE/TEXTAFTER. Excel example.

6 min read

INDEX MATCH with Multiple Criteria in Excel (+ XLOOKUP)

INDEX MATCH with multiple criteria in Excel: the MATCH(1,(…)*(…),0) formula, a version without Ctrl+Shift+Enter, XLOOKUP and SUMIFS. Worked example.

5 min read

Conditional Formatting Formula Examples in Excel (10 Rules)

Ten Excel conditional formatting formula examples: highlight a row, overdue dates, top 3, below average, duplicates, banded rows, blanks. Worked example.

6 min read

XIRR vs IRR in Excel: Why the Results Differ (Example)

XIRR vs IRR in Excel: why IRR ignores dates, how XIRR uses them and which one to report. Worked Excel example with irregular cash flows and an XNPV check.

6 min read

SUMPRODUCT with Multiple Criteria: AND, OR & Partial Text

SUMPRODUCT with multiple criteria in Excel: AND by multiplying, OR without double counting, partial text, calculated criteria. Worked example in a sheet.

6 min read

#N/A Error in Excel: Causes and How to Fix It

Why Excel shows #N/A and how to fix it: trailing spaces, numbers stored as text, missing values, IFNA vs IFERROR, with a worked Excel example.

6 min read

Round to Nearest 0.05 in Excel: MROUND, CEILING & FLOOR

Round to the nearest 0.05 in Excel with MROUND, always up with CEILING or down with FLOOR, plus .99 prices, case sizes and the #NUM! trap. Excel example.

5 min read

Top 10 List in Excel with LARGE + INDEX MATCH (Ties Fixed)

Build a self-updating top 10 list in Excel without SORT: LARGE for the values, INDEX MATCH for the names and a unique rank so ties don't repeat names.

5 min read

YTD Formula in Excel: Year-to-Date with SUMIFS and Dates

YTD formula in Excel with SUMIFS and two date conditions: year-to-date, prior-year YTD with EOMONTH, YoY growth, running YTD. Excel example.

5 min read

Named Ranges in Excel: How to Create Them and Why They Help

Named ranges in Excel explained: create names in the Name box, use them instead of $B$1, naming rules, scope and #NAME? fixes. Excel example.

6 min read

Habit Tracker in Excel: Streak Formula, COUNTIF & Ticks

Build a habit tracker in Excel with a streak formula: COUNTIF per habit, current and longest streak, a success rate without rest days. Excel example.

6 min read

FILTER Function in Excel with Multiple Criteria (AND, OR)

How to use the FILTER function in Excel with multiple criteria: AND with *, OR with +, both together, an if-empty message and SORT. Excel example.

5 min read

UNIQUE Function in Excel: Distinct Lists, Counts & Sorting

How the UNIQUE function in Excel lists distinct values, counts them with ROWS, finds values that appear exactly once and sorts them A to Z. Excel example.

5 min read

LAMBDA Function in Excel: Create Your Own Custom Functions

How the LAMBDA function in Excel creates custom functions without VBA: parameters, inline tests, the Name Manager, LET, MAP and REDUCE. Excel example.

5 min read

Shoelace Formula in Excel: Polygon Area from Coordinates

Find the area of any polygon from its corner coordinates with the shoelace formula in Excel: row by row, in one SUMPRODUCT and with LET. Worked example.

5 min read

Pivot Table with Formulas in Excel: SUMIFS Cross-Tab

Build a pivot table with formulas instead of a PivotTable: a SUMIFS cross-tab with row and column totals, % of row and % of grand total. Excel example.

6 min read

Borda Count in Excel: SUMIF, SUMPRODUCT & Example

Borda count in Excel: count each option by rank with SUMIF, score with SUMPRODUCT, name the winner and compare it with plurality. Excel example.

5 min read

Percent Change Formula in Excel: Increase, Decrease, Errors

Percent change formula in Excel: (new − old) / old, with an Excel example for growth, declines, a zero base, negative numbers and percentage points.

5 min read

CAGR Formula in Excel: Calculate Compound Annual Growth

CAGR formula in Excel with an example: (end/start)^(1/years) − 1, RRI and POWER, why average growth overstates it, projections and annualised rates.

5 min read

Dynamic Calendar in Excel with SEQUENCE and WEEKDAY

Build a dynamic monthly calendar in Excel from one date: the first Monday with WEEKDAY, a 6 × 7 SEQUENCE grid, hidden days of other months. Excel example.

5 min read

Amortization Schedule with Extra Payments in Excel

Add extra payments to a loan amortization schedule in Excel: cap the last payment with MIN, find the payoff month and the interest saved. Excel example.

6 min read

D'Hondt Method Calculation: Step-by-Step Example

How the D'Hondt method allocates seats: divide votes by 1, 2, 3…, take the largest quotients and count seats with LARGE and COUNTIF. Excel example.

5 min read

Weighted Decision Matrix in Excel: Template & Example

Build a weighted decision matrix in Excel: weights that add up to 100%, 0–10 normalised scores, SUMPRODUCT totals and the winning margin. Worked example.

6 min read

VSTACK in Excel: Combine Multiple Sheets and Tables

How to combine tables from multiple sheets with VSTACK: one header row with DROP, a source column with HSTACK, totals and lookups. Excel example.

7 min read

Accounting & financial statements

Bookkeeping, VAT and the ratios that read a set of accounts.

Trial Balance Example: How to Prepare One Step by Step

A complete trial balance example with 15 accounts: normal balances, debit and credit columns in Excel, how to find errors and what it cannot prove.

5 min read

How to Calculate VAT from Gross: Reverse VAT Formula

Calculate VAT from a gross amount: net = gross ÷ (1 + rate), VAT = gross − net. Excel formulas, examples at 20%, 19% and 5%, and the classic mistake.

5 min read

Cash Conversion Cycle Formula: DSO, DIO & DPO Example

The cash conversion cycle formula (DSO + DIO − DPO) explained with a two-year worked example in Excel, what the days cost in cash, and how to read it.

5 min read

Current Ratio vs Quick Ratio: Formulas and Example

Current ratio vs quick ratio: both formulas, an Excel example of two firms with the same current ratio but very different liquidity, and how to read them.

5 min read

DuPont Analysis Example: ROE in 3 and 5 Steps

DuPont analysis worked example: split ROE into net margin, asset turnover and equity multiplier (plus the 5-step version) for two companies with a 20% ROE.

5 min read

Bank Reconciliation Example: Step by Step with Entries

A bank reconciliation example with deposits in transit, outstanding checks, bank charges, an NSF check, interest and a book error, plus journal entries.

6 min read

Payroll Journal Entries: Examples with Debits and Credits

Payroll journal entries with a worked example: gross pay, withholdings, employer contributions, net pay, paying the liabilities and accrued wages.

5 min read

FIFO vs LIFO vs Weighted Average: Inventory Methods Example

FIFO vs LIFO vs weighted average cost on one example: cost layers, COGS, ending inventory, gross profit and the LIFO reserve, with Excel formulas.

5 min read

Chain Ladder Method Explained: IBNR Worked Example in Excel

The chain ladder method step by step: claims triangle, age-to-age factors, CDFs, ultimate claims and IBNR, with a worked Excel example and pitfalls.

6 min read

IFRS 16 Lease Liability Calculation: Worked Example in Excel

How to calculate the IFRS 16 lease liability: lease payments, discount rate, PV, right-of-use asset and the 5-year schedule. Worked Excel example.

5 min read

Black-Scholes Stock Option Expense: Excel Example

Value an employee stock option grant with Black-Scholes in Excel and turn the grant-date fair value into annual stock compensation expense. Worked example.

5 min read

Days Sales Outstanding (DSO) Formula: Simple and Count-Back

The DSO formula with an Excel example: simple DSO, the count-back method, best possible DSO, days beyond terms and the cash a lower DSO frees up.

6 min read

Days in A/R and Net Collection Rate: Formulas & Example

How to calculate days in A/R, A/R over 90 days and the net collection rate for a medical practice, with an Excel example and the common mistakes.

5 min read

Diluted EPS Calculation Example: Options & Convertibles

Diluted EPS calculation example: treasury stock method for options, if-converted method for bonds and preference shares, antidilution test. Excel example.

6 min read

Statement of Cash Flows: Indirect Method Example

Statement of cash flows with the indirect method: start from net income, add back non-cash items and get the working capital signs right. Excel example.

5 min read

Valuation, investing & credit

Cost of capital, cash-flow valuation, returns and loan capacity.

How to Calculate WACC in Excel: Formula & Example

Calculate WACC in Excel step by step: CAPM cost of equity, after-tax cost of debt and market-value weights, with a worked example (8.95%).

5 min read

How to Build a DCF Model in Excel: Step-by-Step Example

Build a DCF model in Excel: forecast free cash flow, discount it at the WACC, add a terminal value and bridge to value per share. Worked example.

5 min read

How to Calculate IRR in Excel: IRR, XIRR and MIRR

Calculate IRR in Excel with IRR, XIRR and MIRR. Worked example with real cash flows, an NPV check and the mistakes that give wrong answers.

5 min read

Simple LBO Model Example: Sources & Uses, MOIC and IRR

A simple LBO model in Excel: sources and uses, a five-year debt paydown, exit value, MOIC and IRR. Worked example with every formula (2.46x).

5 min read

How to Calculate the Sharpe Ratio in Excel (Example)

Calculate the Sharpe ratio in Excel from monthly returns: AVERAGE, STDEV.S and SQRT(12) to annualise, a two-fund example and the common mistakes.

5 min read

Cap Rate Calculation: Formula, NOI and Worked Example

How to calculate the cap rate: build NOI from rent, vacancy and expenses, divide by price, and turn a market cap rate into a value. Excel example.

5 min read

Debt Service Coverage Ratio (DSCR): Formula & Example

DSCR explained: cash flow available for debt service ÷ interest plus principal. Examples for a business and a rental property, and loan sizing in Excel.

5 min read

Three-Statement Model Example: Linked IS, BS and Cash Flow

A three-statement model example in Excel: a one-year forecast where net income, working capital and capex link IS, BS and cash flow, and the check is 0.

7 min read

Liquidation Preference Example: Participating vs Not

Liquidation preference example in Excel: 1x non-participating, participating and capped preferred at four exit values, with the conversion point.

6 min read

How to Calculate Pip Value: Formula, Lots and Examples

How to calculate pip value for any forex pair: units × pip size, then convert to USD. Yen pairs, USD/CHF, crosses and lot sizes in an Excel example.

5 min read

RSI Formula in Excel: Wilder's RSI(14) Step by Step

The RSI formula in Excel: gains and losses, a simple first average, then Wilder's smoothing and RSI = 100 − 100/(1+RS). Worked example with 22 prices.

5 min read

Graham Number Formula: Calculation, Example and Limits

The Graham number formula √(22.5 × EPS × BVPS) in Excel: where 22.5 comes from, three worked stock examples, the net-net (NCAV) test and margin of safety.

5 min read

Debt-to-Income Ratio Calculation: DTI Formula & Limits

Debt-to-income ratio calculation: front-end and back-end DTI, which debts count, typical US lender limits and the maximum loan, with an Excel example.

6 min read

Impermanent Loss Formula: 2√r ÷ (1 + r) − 1, with Example

The impermanent loss formula for a 50/50 liquidity pool, a table of losses by price change and a $10,000 worked example in Excel, plus the fee break-even.

6 min read

LCOE Formula: Levelized Cost of Energy, Solar Example

The LCOE formula, present value of costs ÷ present value of energy, with a 25-year rooftop solar example in Excel and the one-cell NPV shortcut.

6 min read

Present Worth Analysis Example: Compare Two Machines

A present worth analysis example from engineering economics: two machines with equal lives, P/F factors, NPV in Excel and how the MARR changes the choice.

5 min read

Debt Sculpting in Excel: DSCR-Based Repayment Explained

Debt sculpting explained with an Excel example: debt service = CFADS ÷ target DSCR, debt size as NPV, interest, principal, and the annuity comparison.

5 min read

How to Calculate FFO and AFFO for a REIT, with Example

FFO and AFFO step by step: from net income to FFO per share, AFFO after recurring capex, payout ratios and P/FFO. Worked Excel example of a REIT.

5 min read

Portfolio at Risk (PAR30) Formula, with an Excel Example

How to calculate PAR30 and PAR90: outstanding balance of late loans ÷ gross loan portfolio, with an ageing table built with VLOOKUP and SUMIF in Excel.

5 min read

Trading Expectancy Formula, with an Excel Example

Expectancy = win rate × average win + loss rate × average loss. Excel example with 20 trades, expectancy in R, the break-even win rate and the edge.

5 min read

Credit Card Interest Calculation: Average Daily Balance

How credit card interest is calculated: APR to daily periodic rate, average daily balance, interest for the cycle and the grace period. Excel example.

6 min read

Equity Waterfall with an IRR Hurdle in Excel (Example)

Build a real estate equity waterfall with an 8% LP IRR hurdle and a promote in Excel: hurdle balance, tier cash, LP IRR and the GP promote. Worked example.

5 min read

Tax-Equivalent Yield Formula: Muni vs Taxable Bond Example

Tax-equivalent yield formula with a worked example: federal and state tax, the break-even tax rate, TEY by bracket, muni vs corporate bond. Excel example.

5 min read

Historical VaR in Excel: PERCENTILE.INC Step by Step

Historical VaR in Excel: apply past returns to today's portfolio, read the 95% VaR with PERCENTILE.INC, expected shortfall, 10-day VaR. Excel example.

5 min read

Refinance Break-Even Calculator: Closing Costs ÷ Savings

Refinance break-even calculator in Excel: new payment with PMT, real closing costs, break-even month, net saving and the term trap. Excel example.

5 min read

70% Rule in House Flipping: Formula, Example & Limits

The 70% rule in house flipping: max offer = ARV × 70% − repairs. A worked example, a refined offer from the deal's real costs and when the rule fails.

5 min read

Risk-Weighted Assets Calculation: RWA Example in Excel

How to calculate risk-weighted assets: exposure × CCF × risk weight, credit, market and operational RWA, RWA density and the CET1 ratio. Excel example.

6 min read

13-Week Cash Flow Forecast: Excel Template & Worked Example

How to build a 13-week cash flow forecast: available opening cash, weekly receipts and payments, the cash trough and the revolver need. Excel example.

6 min read

Enterprise Value to Equity Value Bridge: Excel Example

The EV to equity value bridge in both directions: diluted shares with the treasury stock method, debt, cash, NCI and associates. Worked Excel example.

6 min read

Gold-to-Silver Ratio: Formula, z-Score and Ratio Switch

Calculate the gold-to-silver ratio in Excel, compare it with its average using a z-score and test a ratio switch with dealer spreads. Worked example.

5 min read

Business & operations

Costs, inventory, projects, customers and pay.

Break-Even Analysis in Excel: Formula and Example

Break-even analysis in Excel: break-even units and revenue, contribution margin, margin of safety and the volume for a profit target, with an example.

5 min read

EOQ Formula: Economic Order Quantity Example in Excel

The EOQ formula √(2DS/H) with a worked example in Excel: holding cost, orders per year, the cost curve, and why rounding to a pack size is fine.

5 min read

Earned Value Formulas: CPI, SPI, EAC Worked Example

Earned value management formulas with one consistent example: PV, EV, AC, CV, SV, CPI, SPI, EAC, ETC, VAC and TCPI, calculated step by step in Excel.

5 min read

LTV:CAC Ratio: How to Calculate It, With Example

How to calculate the LTV:CAC ratio: fully loaded CAC, LTV from gross margin and churn, CAC payback and the churn limit, in a worked Excel example.

5 min read

Compa-Ratio: Formula, Excel Example and How to Read It

The compa-ratio formula (salary ÷ band midpoint) with an Excel example: lookups to the salary band, range penetration, group compa-ratio and review flags.

5 min read

RevPAR Calculation: Formula, ADR, Occupancy and Example

RevPAR calculation step by step: occupancy, ADR, RevPAR = ADR × occupancy, plus TRevPAR, GOPPAR and the RevPAR index, in a worked Excel example.

6 min read

Food Cost Percentage Formula: Restaurant Worked Example

The food cost percentage formula for a month and for one plate, theoretical vs actual food cost, and menu prices from a target food cost %. Excel example.

5 min read

Markup vs Margin: Formulas, Conversion Table, Example

Markup vs margin explained: both formulas, how to convert one into the other, a price from a target margin and a conversion table, in an Excel example.

5 min read

Cost per Mile in Trucking: How to Calculate It (2026)

Calculate trucking cost per mile: fixed and variable costs, fuel at 2026 diesel prices, deadhead and the break-even rate per loaded mile. Excel example.

5 min read

ROAS vs ROI: Formulas, Break-even ROAS and Example

ROAS vs ROI explained: both formulas, break-even ROAS = 1 ÷ margin, break-even CPA, and why a campaign with a 5.5x ROAS can lose money. Excel example.

5 min read

Freelance Hourly Rate Calculator: The Formula in Excel

How to calculate your freelance hourly rate: revenue needed ÷ billable hours, with overheads, utilisation and a margin. Worked example and Excel formulas.

5 min read

Total Compensation Calculator: Compare Job Offers in Excel

Calculate total compensation: base + expected bonus + retirement match + benefits + equity. Three job offers compared in Excel, also per hour worked.

5 min read

Scope 1, 2 and 3 Emissions Calculation: Excel Example

How to calculate a carbon footprint by scope: activity data × emission factor, location- vs market-based Scope 2, travel and commuting, totals with SUMIFS.

5 min read

Erlang C Formula in Excel: Calculator with Worked Example

Build the Erlang C formula in Excel: traffic in Erlangs, probability of waiting, service level, ASA and the agents needed for 80/20. Worked example.

6 min read

Net Revenue Retention (NRR) Formula with an Excel Example

The net revenue retention formula with an Excel example: starting MRR, expansion, contraction and churn, NRR vs GRR, and why new customers stay out.

5 min read

Cp and Cpk Formula in Excel: Process Capability Example

Cp and Cpk formulas in Excel with a worked example: within σ from R-bar/d2, Cp, Cpk, Pp and Ppk for a machined shaft, and what 1.33 really means.

5 min read

Van Westendorp Price Sensitivity Meter: Excel Example

Van Westendorp price sensitivity meter in Excel: the four questions, COUNTIF curves, OPP, IPP and the acceptable price range, in a worked example.

5 min read

ACoS vs TACoS: Formulas, Break-Even ACoS and Excel Example

ACoS vs TACoS explained: both formulas, the link to ROAS, break-even and target ACoS from your margin, and how to read the trend. Excel example.

5 min read

Time Charter Equivalent (TCE): Formula and Worked Example

Time charter equivalent (TCE) formula: net freight minus voyage costs ÷ voyage days, slow steaming and a time-charter offer compared. Excel example.

6 min read

Coinsurance Formula: Did ÷ Should × Loss − Deductible

The property coinsurance formula with a worked example: required insurance, the coinsurance penalty, the limit cap and how health plan coinsurance differs.

5 min read

TRIR Calculation: Formula, Example and Excel Calculator

TRIR calculation step by step: recordables × 200,000 ÷ hours worked, a company rate from four sites, a target case limit, DART and LTIFR. Excel example.

5 min read

How to Find a Nash Equilibrium in a Payoff Matrix (Excel)

Find the Nash equilibrium of a payoff matrix step by step: best responses with MAX, the cells where both coincide, and a 3×3 pricing game in Excel.

5 min read

Markov Chain Attribution: Removal Effect in Excel

Markov chain attribution step by step: transition matrix, conversion probability with MMULT and MINVERSE, removal effects, credit. Excel example.

5 min read

Landed Cost Formula: Per-Unit Calculation with Duty & VAT

The landed cost formula step by step: customs value, import duty, broker and port fees, cost per unit, and why import VAT is left out. Excel example.

6 min read

Baker's Percentage Formula: Calculate and Scale a Recipe

Baker's percentage formula with a worked example: each ingredient as a % of the flour, hydration, and scaling a bread formula to any dough weight in Excel.

5 min read

Board Feet Formula: How to Calculate Board Feet

Board feet formula with examples: thickness × width × length ÷ 12, nominal sizes, a lumber list in Excel, price per MBF and the order total with tax.

5 min read

Price Elasticity of Demand from a Log-Log Regression

Estimate price elasticity of demand in Excel with a log-log regression: LN, SLOPE, a forecast with EXP and the profit-maximising price. Worked example.

5 min read

Angel's Share Calculation: Whisky Cask Loss per Year

Calculate the angel's share of a whisky cask: compound loss per year, litres of pure alcohol and bottles left, and years until 20% is gone. Excel example.

5 min read

Engine Displacement Formula: cc, Liters and Cubic Inches

The engine displacement formula from bore, stroke and cylinders in cc, liters and cubic inches, plus compression ratio and overbore. Worked Excel example.

6 min read

Data, tech, science & health

Survey and forecast accuracy, machine learning metrics, IT reliability, cyber risk, chemistry and nutrition maths.

Reading is step one. Doing is step two.

Every guide links to a lab where you build the same calculation in an online spreadsheet and get instant feedback.

Start Excel for Finance — free