liminfo

Excel Finance Reference

Free reference guide: Excel Finance Reference

31 results

About Excel Finance Reference

The Excel Finance Functions Reference is a searchable guide covering 28 essential Excel formulas and techniques for financial analysis and modeling. The Time Value of Money section includes PV, FV, PMT, RATE, NPER, XNPV, and NPV functions with complete syntax, parameter explanations, and worked examples such as mortgage calculations, annuity present values, and irregular-date discounted cash flow analysis.

Investment Analysis entries cover IRR, XIRR for date-specific returns (with a PE fund example yielding 28.74%), MIRR with separate financing and reinvestment rates, WACC calculation using the CAPM formula (Re = Rf + Beta*(Rm-Rf)), full DCF enterprise valuation with terminal value, and payback period computation using cumulative cash flows. Financial Statement Analysis includes ROE/ROA with DuPont decomposition, debt-to-equity and liquidity ratios, EV/EBITDA multiples, FCF (FCFF and FCFE), and EBITDA margin calculations.

The reference also covers Data and Lookup functions critical for financial modeling: INDEX-MATCH with multi-criteria arrays, XLOOKUP with range lookups for tax brackets, VLOOKUP, SUMIFS/COUNTIFS/AVERAGEIFS, OFFSET+MATCH for dynamic ranges, and Data Tables for 1-variable and 2-variable sensitivity analysis. Statistics and Regression entries include LINEST for regression coefficients, FORECAST/TREND, CORREL/COVARIANCE for portfolio analysis, STDEV/VAR for volatility and VaR calculations, NORM.DIST/NORM.INV for option pricing and VaR, PERCENTILE/QUARTILE for valuation ranges, and SLOPE/INTERCEPT/RSQ for stock beta and Jensen alpha.

Key Features

  • Time Value of Money functions (PV, FV, PMT, RATE, NPER, NPV, XNPV) with mortgage, annuity, and irregular-date DCF examples
  • Investment analysis with IRR, XIRR (PE fund 28.74% return example), MIRR, WACC (CAPM-based Re calculation), DCF enterprise valuation, and payback period
  • Financial statement ratios: ROE/ROA with DuPont decomposition, debt-to-equity, current/quick ratio, interest coverage, EV/EBITDA multiples, FCFF/FCFE, and EBITDA margin
  • INDEX-MATCH with multi-criteria lookups, XLOOKUP with range matching for tax brackets, and VLOOKUP for chart of accounts
  • SUMIFS/COUNTIFS/AVERAGEIFS for multi-condition financial data aggregation and OFFSET+MATCH for dynamic auto-expanding ranges
  • Sensitivity analysis using 1-variable and 2-variable Data Tables showing NPV across discount rate and growth rate combinations
  • LINEST regression array with R-squared, F-statistic, and standard errors, plus FORECAST/TREND for revenue projections
  • Portfolio risk analysis: CORREL/COVARIANCE for correlation matrices, STDEV for annualized volatility (daily StdDev * sqrt(252)), NORM.DIST for VaR, and SLOPE for stock beta calculation

Frequently Asked Questions

What financial modeling functions does this reference include?

It covers 28 functions and techniques across five categories: Time Value of Money (PV, FV, PMT, RATE, NPER, NPV, XNPV), Investment Analysis (IRR, XIRR, MIRR, WACC, DCF, Payback Period), Financial Statement Analysis (ROE/ROA, debt ratios, EV/EBITDA, FCF, EBITDA margin), Data and Lookup (INDEX-MATCH, XLOOKUP, VLOOKUP, SUMIFS, OFFSET, Data Tables), and Statistics and Regression (LINEST, FORECAST, CORREL, STDEV, NORM.DIST, PERCENTILE, SLOPE).

How do I calculate WACC in Excel?

Use the formula WACC = (E/V)*Re + (D/V)*Rd*(1-Tc). Calculate the cost of equity (Re) with CAPM: Re = Rf + Beta*(Rm-Rf). The reference provides a complete cell setup example with equity of $600M, debt of $400M, Re of 12%, Rd of 5%, and tax rate of 25%, yielding WACC of 8.70%.

What is the difference between IRR, XIRR, and MIRR?

IRR calculates the internal rate of return for equal-interval cash flows. XIRR handles irregular dates, making it more practical for real investments (the reference shows a PE fund example with 28.74% XIRR). MIRR separates financing cost and reinvestment rate, giving a more conservative estimate since IRR unrealistically assumes reinvestment at the IRR itself.

How do I build a DCF model in Excel?

Project 5 years of Free Cash Flow, calculate Terminal Value as FCF*(1+g)/(WACC-g), then discount both using NPV or XNPV. The reference shows a complete model: 5 years of FCF from $50M to $73M growing at 10%, WACC of 10%, terminal growth of 2%, yielding an enterprise value of approximately $810M.

How do I calculate stock beta using Excel?

Use SLOPE(stock_returns, market_returns) to get beta. The reference example shows a beta of 1.25, meaning 25% more volatile than the market. INTERCEPT gives Jensen alpha (0.2%), and RSQ shows R-squared (0.68, meaning the market explains 68% of the stock variance). Apply beta in CAPM: E(R) = Rf + Beta*(Rm-Rf).

How is Value at Risk (VaR) calculated in Excel?

Calculate daily returns as LN(today/yesterday), find daily volatility with STDEV.S, annualize with SQRT(252), then compute VaR = Portfolio_Value * Ann_Vol * NORM.S.INV(confidence). At 95% confidence, NORM.S.INV(0.05) = -1.645. The example shows a $10M portfolio with 28.57% annual volatility yielding a 1-day VaR of -$4.7M.

What lookup function should I use for financial models?

INDEX-MATCH is recommended over VLOOKUP because it supports left-lookups, is robust to column insertions, and handles multi-criteria with array formulas. XLOOKUP (Excel 365) simplifies this further with built-in error handling and range matching. Use VLOOKUP only for simple right-side lookups in stable table structures.

How do I perform sensitivity analysis with Data Tables?

For 1-variable analysis, set up a row of WACC values with the DCF result formula in the corner, then use Data > What-If Analysis > Data Table with the column input cell pointing to your WACC cell. For 2-variable analysis, add growth rates as a second dimension. The reference shows NPV ranging from $33M to $205M across WACC (8-12%) and growth rate (1-3%) combinations.