Excel Formulas For Financial Analysts Practice Exam

Question 1 Excel Formulas For Financial Analysts

TEXTJOIN with conditional inclusion?

=COUNTIFS(region, "EMEA", year, 2025, value, ">1000") — all conditions ANDed. SUMIFS(sum_range, criteria_range1, criteria1, ...) — sum_range comes first, a common source of bugs since it differs from SUMIF. =TEXTJOIN(", ", TRUE, IF(A2:A100="Active", B2:B100, "")) entered as a dynamic array — joins only rows where condition is TRUE. NPV discounts the first value by 1 period, the second by 2, etc. A period-0 outflow must be added outside the NPV call, not listed as the first argument.

Question 2 Excel Formulas For Financial Analysts

SLN vs DB vs DDB?

SLN: straight-line (equal each period). DB: fixed declining balance. DDB: double-declining balance. All built-ins for asset depreciation. PV(rate, nper, pmt, [fv], [type]) discounts a single future lump sum or annuity stream. NPV discounts a series of unequal cash flows that already start at period 1; you must add the period-0 outflow manually. In pre-365 Excel, multi-cell array formulas required Ctrl+Shift+Enter. The curly braces {=...} are an artifact, not something you type. CALCULATE([Sales], ALL('Date')) — removes filters from the Date table. Useful for "% of grand total" measures.

Question 3 Excel Formulas For Financial Analysts

Pivot Slicer for interactivity?

Insert → Slicer, bind to a field, place on the dashboard. Slicers filter all connected pivots and charts. Project FCF, discount at WACC, sum, add terminal value: TV / (1+WACC)^n, subtract debt, divide by shares for per-share value. PV(rate, nper, pmt, [fv], [type]) discounts a single future lump sum or annuity stream. NPV discounts a series of unequal cash flows that already start at period 1; you must add the period-0 outflow manually. Each period: beginning balance → interest accrual (rate × avg balance) → mandatory + optional repayments → ending balance.

Question 4 Excel Formulas For Financial Analysts

TEXT for date formatting in formulas?

=CurrentAssets / CurrentLiabilities =YEARFRAC(start, end, basis) — fractional years between two dates with day-count conventions (30/360, ACT/360, etc.). =TEXT(A1, "yyyy-mm-dd") — converts a date serial to a formatted string. Use in TEXTJOIN/concat for filenames and reports. =NPV(rate, value1, value2, ...) + initial_investment — NPV discounts from period 1; include the initial outflow (often negative) separately.

Question 5 Excel Formulas For Financial Analysts

Lock a row in a formula?

=FixedCosts / (Price - VariableCost) — sales volume at zero profit. =RATE(nper, pmt, pv, [fv], [type], [guess]) — solves for the periodic interest rate of an annuity (e.g., implied yield on a loan with known payment). =FV(rate, nper, pmt, [pv], 1) — final argument 1 marks annuity due (payments at start of period). Use absolute: $A$1. Lock only column or only row with single dollar sign.

Question 6 Excel Formulas For Financial Analysts

Excel array formula example?

=(CurrentAssets - Inventory) / CurrentLiabilities — often computed from a financial-statements table. Modern dynamic array: =SORT(UNIQUE(A1:A100)) spills automatically. No more Ctrl-Shift-Enter. =EOMONTH(start_date, months) — last day of the month n months before/after a date. Months = 0 gives this month-end. =EnterpriseValue / LTM_EBITDA — sanity-check vs comparables.

Question 7 Excel Formulas For Financial Analysts

Common circular reference cause?

=LEFT(A1, 3), =MID(A1, 5, 2), =RIGHT(A1, 4) — extract substrings. Combine with FIND/SEARCH for position-aware parsing. DIVIDE(numerator, denominator, alternate) handles divide-by-zero by returning the alternate (often BLANK()). Use instead of / to avoid blank rows in visuals. Interest expense uses average debt, which depends on cash flow, which depends on interest. Resolve via iterative calculation (File → Options → Formulas). =NUMBERVALUE("$1,234.56") parses currency-formatted strings to numbers, accepting locale-specific separators. =VALUE("123") is the simpler legacy form.

Question 8 Excel Formulas For Financial Analysts

Why avoid volatile functions in big models?

=SUM(Jan:Dec!B5) — sums cell B5 across every sheet from Jan through Dec. Sheet order matters. OFFSET, INDIRECT, NOW, TODAY, RAND, RANDBETWEEN recalc on every change — they slow large workbooks. Replace with INDEX where possible. =SLOPE(stock_returns, market_returns) — covariance over variance, equivalent to regression slope. VSTACK stacks ranges vertically, HSTACK horizontally. Useful for stitching monthly extracts into one table.

Question 9 Excel Formulas For Financial Analysts

OFFSET vs INDIRECT?

Both return dynamic ranges, but OFFSET is volatile (recalculates on every change) and INDIRECT evaluates text as a reference. Prefer INDEX for performance. =LOOKUP(2,1/(A:A<>""),A:A) Revenue → COGS → Gross Profit → OpEx → EBITDA → D&A → EBIT → Interest → Taxes → Net Income → +D&A → −ΔWC → −CapEx → FCF. =TotalDebt / TotalEquity

Question 10 Excel Formulas For Financial Analysts

Ctrl+Shift+Enter legacy CSE?

In pre-365 Excel, multi-cell array formulas required Ctrl+Shift+Enter. The curly braces {=...} are an artifact, not something you type. IFNA traps only #N/A (preserves other errors so you notice bugs). IFERROR traps all error types — riskier because it hides formula mistakes. =T.DIST.2T(ABS(r)*SQRT((n-2)/(1-r^2)), n-2) with r = PEARSON(...) — two-tailed p-value for correlation significance. =SYD(cost, salvage, life, per) — sum-of-years'-digits; front-loads depreciation.

Question 11 Excel Formulas For Financial Analysts

Data Tables (Sensitivity Analysis)?

Build a grid of inputs varying along rows/cols; Excel computes the output cell for each combination. =TotalDebt - CashAndEquivalents — subtract all interest-bearing cash to avoid inflating equity value. RunningPeak = MAX(returns so far); Drawdown = (value - RunningPeak)/RunningPeak. Implement in a helper column or with SCAN/MAX in M365. =A2# references the entire spill range of the formula in A2. Cleaner than OFFSET for dynamic ranges.

Question 12 Excel Formulas For Financial Analysts

Protection: lock formulas but allow inputs?

=NETWORKDAYS(start, end, [holidays]) — counts weekdays between two dates, optionally excluding a holiday range. =NPV(rate, value1, value2, ...) + initial_investment — NPV discounts from period 1; include the initial outflow (often negative) separately. PV(rate, nper, pmt, [fv], [type]) discounts a single future lump sum or annuity stream. NPV discounts a series of unequal cash flows that already start at period 1; you must add the period-0 outflow manually. Format cells → uncheck Locked on input cells, then Review → Protect Sheet. Users can only edit unlocked cells.

Question 13 Excel Formulas For Financial Analysts

FORMULATEXT to display a formula?

=FORMULATEXT(A1) — returns the formula text in A1 as a string. Good for documentation tabs in complex models. =DATEDIF(DOB, TODAY(), "Y") — uses the hidden DATEDIF function. Pair with "YM" and "MD" for finer precision. =EBIT / InterestExpense =(CurrentAssets - Inventory) / CurrentLiabilities — often computed from a financial-statements table.

Question 14 Excel Formulas For Financial Analysts

ACCRINT for accrued interest?

=WORKDAY(start_date, days, [holidays]) — returns the working day n days from start, skipping weekends and holidays. =ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis]) — interest accrued since last coupon. =AVERAGEX(Sales, Sales[Amount]) — averages over a derived table, e.g., per-customer or per-day averages that ordinary AVERAGE can't produce. VLOOKUP: looks up by leftmost column, returns a column to the right; can't go left.
XLOOKUP: any direction, exact match default, IFNULL built in.

Question 15 Excel Formulas For Financial Analysts

Debt-to-equity?

NPV discounts the first value by 1 period, the second by 2, etc. A period-0 outflow must be added outside the NPV call, not listed as the first argument. =TotalDebt / TotalEquity =EOMONTH(start_date, months) — last day of the month n months before/after a date. Months = 0 gives this month-end. SUM aggregates a column. SUMX iterates a table and evaluates an expression per row — e.g., SUMX(Sales, Sales[Qty] * Sales[Price]) for revenue.

Question 16 Excel Formulas For Financial Analysts

FILTER function syntax?

=YEARFRAC(start, end, basis) — fractional years between two dates with day-count conventions (30/360, ACT/360, etc.). OFFSET, INDIRECT, NOW, TODAY, RAND, RANDBETWEEN recalc on every change — they slow large workbooks. Replace with INDEX where possible. Each period: beginning balance → interest accrual (rate × avg balance) → mandatory + optional repayments → ending balance. =FILTER(array, include, [if_empty]) — spills rows where include is TRUE. Example: =FILTER(A2:D100, B2:B100="Closed").

Question 17 Excel Formulas For Financial Analysts

Implied share price from EV?

=LOOKUP(2,1/(A:A<>""),A:A) Right-click date row → Group → choose Months/Quarters/Years. =(EnterpriseValue - NetDebt - Preferred) / DilutedShares =SYD(cost, salvage, life, per) — sum-of-years'-digits; front-loads depreciation.

Question 18 Excel Formulas For Financial Analysts

Power Pivot vs Power Query?

Formulas → Trace Precedents draws arrows from cells feeding into the selected formula. Trace Dependents shows the reverse. Critical for auditing models. Power Query: shape/load data.
Power Pivot: build a relational model + DAX measures for analysis. =(PRICE(sett,mat,coup,yld+0.0001,freq,basis)-PRICE(sett,mat,coup,yld,freq,basis))/10000 — dollar value of a 1bp move. Insert → Slicer, bind to a field, place on the dashboard. Slicers filter all connected pivots and charts.

Question 19 Excel Formulas For Financial Analysts

Calculate cumulative interest paid over a loan?

=CUMIPMT(rate, nper, pv, start_period, end_period, type) =INDEX(returnRange, MATCH(lookupValue, lookupRange, 0)) — more flexible than VLOOKUP. Value Field Settings → Show Values As → Difference From / % Difference From. Three-section sheet layout: Inputs (yellow) → Model (black formulas) → Outputs (summary).

Question 20 Excel Formulas For Financial Analysts

Monte Carlo simulation in Excel?

Generate N random inputs with =NORM.INV(RAND(), mean, stdev), copy a model that produces an output, and record results. Data Table or VBA can iterate. =HYPERLINK("#'P&L'!A1", "Open P&L") — sheet names with spaces or & require single quotes. =INDEX(returnRange, MATCH(lookupValue, lookupRange, 0)) — more flexible than VLOOKUP. =TEXTJOIN(", ", TRUE, IF(A2:A100="Active", B2:B100, "")) entered as a dynamic array — joins only rows where condition is TRUE.

Question 21 Excel Formulas For Financial Analysts

USERELATIONSHIP for role-playing dimensions?

Re = Rf + β*Mkt + s*SMB + v*HML — Fama-French 3-factor extension. #,##0_);(#,##0) — accounting style: positive numbers right-aligned, negatives in parentheses. YoY% := DIVIDE([Sales], CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))) - 1 CALCULATE([Sales], USERELATIONSHIP(Sales[OrderDate], 'Date'[Date])) — activates a non-default relationship between fact and dimension.

Question 22 Excel Formulas For Financial Analysts

EBIT to EBITDA bridge in formulas?

VSTACK stacks ranges vertically, HSTACK horizontally. Useful for stitching monthly extracts into one table. SUMIFS(sum_range, criteria_range1, criteria1, ...) — sum_range comes first, a common source of bugs since it differs from SUMIF. ROUND rounds to nearest. ROUNDDOWN truncates toward zero. ROUNDUP away from zero. Use MROUND for nearest multiple. EBITDA = EBIT + D&A — add back the period's depreciation and amortization. Watch for impairment add-backs.

Question 23 Excel Formulas For Financial Analysts

Terminal value: Gordon growth?

CALCULATE([Sales], 'Date'[Year] = 2025) — evaluates the measure under a modified filter context. Foundation of almost every advanced DAX measure. =FIND("-", A1) gives the position of the first "-". =LEN(A1) gives total length — used together to split names or IDs. Use a distinct fill color (e.g., yellow) for input cells; black for formulas; blue for cross-sheet references. =FinalFCF * (1+g) / (WACC - g)

Question 24 Excel Formulas For Financial Analysts

Best practice: hardcoded inputs?

Use a distinct fill color (e.g., yellow) for input cells; black for formulas; blue for cross-sheet references. Connect to a source (CSV/SQL/API), transform with steps that re-run on refresh — automate repetitive cleaning. CALCULATE([Sales], ALL('Date')) — removes filters from the Date table. Useful for "% of grand total" measures. =Inventory / (COGS / 365)

Question 25 Excel Formulas For Financial Analysts

Difference between NPV and PV?

=SLOPE(y_range, x_range) and =INTERCEPT(y_range, x_range) — quick OLS coefficients. RSQ returns R². PARALLELPERIOD rolls to the start of the period (e.g., -1 YEAR gives the full prior year). DATEADD shifts by exact days, preserving partial periods. =@A1:A10 returns the value from the row of the formula — a way to opt out of spill behavior in a single cell. PV(rate, nper, pmt, [fv], [type]) discounts a single future lump sum or annuity stream. NPV discounts a series of unequal cash flows that already start at period 1; you must add the period-0 outflow manually.

Question 26 Excel Formulas For Financial Analysts

SWITCH vs nested IF?

CALCULATE([NetFlow], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))) — running total across the visible date range. =YEARFRAC(start, end, basis) — fractional years between two dates with day-count conventions (30/360, ACT/360, etc.). =CONVEXITY(settlement, maturity, coupon, yld, frequency, [basis]) =SWITCH(value, 1, "Low", 2, "Med", 3, "High", "Other") — cleaner than nested IFs when comparing one value to a list.

Question 27 Excel Formulas For Financial Analysts

Custom number format for negatives in red?

Custom format: #,##0,;-#,##0,;0 — one trailing comma divides display by 1000. ROUND rounds to nearest. ROUNDDOWN truncates toward zero. ROUNDUP away from zero. Use MROUND for nearest multiple. CALCULATE([NetFlow], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))) — running total across the visible date range. #,##0;[Red]-#,##0;0 — positive ; negative (red, minus sign) ; zero. Three sections separated by semicolons.

Question 28 Excel Formulas For Financial Analysts

LET function benefit?

=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) — bond yield to maturity given clean price. =EnterpriseValue / LTM_EBITDA — sanity-check vs comparables. SUM aggregates a column. SUMX iterates a table and evaluates an expression per row — e.g., SUMX(Sales, Sales[Qty] * Sales[Price]) for revenue. =LET(x, A1*2, y, x+5, y*3) — names intermediate results; faster, cleaner, easier to debug.

Question 29 Excel Formulas For Financial Analysts

LAMBDA for custom reusable functions?

Modern dynamic array: =SORT(UNIQUE(A1:A100)) spills automatically. No more Ctrl-Shift-Enter. =LAMBDA(x, x*2)(5) or define a named LAMBDA in Name Manager. Replaces UDFs for many use cases. =LOOKUP(2,1/(A:A<>""),A:A) TEXTSPLIT(text, delim) (M365) or use Power Query / Text to Columns.

Question 30 Excel Formulas For Financial Analysts

CHOOSE function for scenario picks?

PARALLELPERIOD rolls to the start of the period (e.g., -1 YEAR gives the full prior year). DATEADD shifts by exact days, preserving partial periods. =PMT(rate/12, n_months, -principal) =LEFT(A1, 3), =MID(A1, 5, 2), =RIGHT(A1, 4) — extract substrings. Combine with FIND/SEARCH for position-aware parsing. =CHOOSE(scenario_index, case1, case2, case3) — pick the nth argument. Common in Data Table / scenario-driven models.

Question 31 Excel Formulas For Financial Analysts

Free cash flow to firm?

Division by zero. Wrap: =IFERROR(A1/B1, 0). EBITDA = EBIT + D&A — add back the period's depreciation and amortization. Watch for impairment add-backs. =EBIT*(1-tax) + D&A - ΔWC - CapEx — unlevered cash flow available to all capital providers. Discount each cash flow by an extra 0.5 year: =NPV(rate, FCF1/1.5, FCF2/2.5, ...) — or use XNPV with dates in the middle of each year.

Question 32 Excel Formulas For Financial Analysts

DV01 / PV01 estimate?

=SUMPRODUCT((region="EMEA")*(value)) — multiplies the boolean (1/0) by the value, then sums. Pre-365 portable equivalent of SUMIFS. =(PRICE(sett,mat,coup,yld+0.0001,freq,basis)-PRICE(sett,mat,coup,yld,freq,basis))/10000 — dollar value of a 1bp move. CALCULATE([Sales], 'Date'[Year] = 2025) — evaluates the measure under a modified filter context. Foundation of almost every advanced DAX measure. =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) — bond yield to maturity given clean price.

Question 33 Excel Formulas For Financial Analysts

Beta from returns series?

NPV discounts the first value by 1 period, the second by 2, etc. A period-0 outflow must be added outside the NPV call, not listed as the first argument. Interest expense uses average debt, which depends on cash flow, which depends on interest. Resolve via iterative calculation (File → Options → Formulas). Formulas → Evaluate Formula — steps through nested calculations one operator at a time. Indispensable for debugging NPV/IRR chains. =SLOPE(stock_returns, market_returns) — covariance over variance, equivalent to regression slope.

Question 34 Excel Formulas For Financial Analysts

IFS function syntax?

Use =$B2="Critical" in a CF rule applied to =$A2:$Z2 — the relative row reference paints the entire row. =EFFECT(nominal_rate, periods_per_year) — converts a nominal rate compounded n times into the true EAR. =CurrentAssets / CurrentLiabilities =IFS(cond1, val1, cond2, val2, TRUE, "default") — readable replacement for nested IFs.

Question 35 Excel Formulas For Financial Analysts

SYD depreciation?

=XLOOKUP(1, (A:A=x)*(B:B=y), C:C) — boolean AND. =FILTER(array, include, [if_empty]) — spills rows where include is TRUE. Example: =FILTER(A2:D100, B2:B100="Closed"). =FV(rate, nper, pmt, [pv], 1) — final argument 1 marks annuity due (payments at start of period). =SYD(cost, salvage, life, per) — sum-of-years'-digits; front-loads depreciation.

Question 36 Excel Formulas For Financial Analysts

COVARIANCE.P vs CORREL?

SUM aggregates a column. SUMX iterates a table and evaluates an expression per row — e.g., SUMX(Sales, Sales[Qty] * Sales[Price]) for revenue. Add-in: File → Options → Add-ins → Solver. Sets an objective cell to max/min/value, subject to constraints on changing cells. Use GRG Nonlinear for smooth models, LP Simplex for linear. =TotalDebt - CashAndEquivalents — subtract all interest-bearing cash to avoid inflating equity value. COVAR / COVARIANCE.P measures directional co-movement. CORREL normalizes to [-1, 1] — usually what you want for diversification analysis.

Question 37 Excel Formulas For Financial Analysts

NPV formula in Excel?

#,##0_);(#,##0) — accounting style: positive numbers right-aligned, negatives in parentheses. =NPV(rate, value1, value2, ...) + initial_investment — NPV discounts from period 1; include the initial outflow (often negative) separately. =ROUNDUP((MONTH(A1) - FiscalStart + 12) MOD 12 / 3, 0) or use INT((MONTH+offset)/3). CALCULATE([Sales], ALL('Date')) — removes filters from the Date table. Useful for "% of grand total" measures.

Question 38 Excel Formulas For Financial Analysts

Approximate YTM with RATE on bond cash flows?

=YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) — bond yield to maturity given clean price. Drag Customer to Values → Value Field Settings → choose Distinct Count (Excel 2013+). Models the COUNT DISTINCT pattern. #,##0_);(#,##0) — accounting style: positive numbers right-aligned, negatives in parentheses. Build a schedule of [-Price, Coupon, Coupon, ..., Coupon+Par], then =RATE(N, Coupon, -Price) — the bond's YTM as if it were an annuity.

Question 39 Excel Formulas For Financial Analysts

INDEX/MATCH pattern?

=SUBTOTAL(9, range) or =AGGREGATE(9, 5, range) =INDEX(returnRange, MATCH(lookupValue, lookupRange, 0)) — more flexible than VLOOKUP. ROE = (NetIncome/Sales) * (Sales/Assets) * (Assets/Equity) — break ROE into margin, turnover, leverage. =SUM(Jan:Dec!B5) — sums cell B5 across every sheet from Jan through Dec. Sheet order matters.

Question 40 Excel Formulas For Financial Analysts

Cumulative balance with DAX?

CALCULATE([NetFlow], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))) — running total across the visible date range. =(CurrentAssets - Inventory) / CurrentLiabilities — often computed from a financial-statements table. =AccountsReceivable / (Revenue / 365) =SLOPE(y_range, x_range) and =INTERCEPT(y_range, x_range) — quick OLS coefficients. RSQ returns R².

Question 41 Excel Formulas For Financial Analysts

IFNA vs IFERROR?

IFNA traps only #N/A (preserves other errors so you notice bugs). IFERROR traps all error types — riskier because it hides formula mistakes. =(PRICE(sett,mat,coup,yld+0.0001,freq,basis)-PRICE(sett,mat,coup,yld,freq,basis))/10000 — dollar value of a 1bp move. Drag Customer to Values → Value Field Settings → choose Distinct Count (Excel 2013+). Models the COUNT DISTINCT pattern. =FORMULATEXT(A1) — returns the formula text in A1 as a string. Good for documentation tabs in complex models.

Question 42 Excel Formulas For Financial Analysts

DAX: filter context vs row context?

=IFS(cond1, val1, cond2, val2, TRUE, "default") — readable replacement for nested IFs. Filter context is set by slicers, page filters, and CALCULATE filters — it determines which rows are aggregated. Row context is the current row iterated by a calculated column or iterator (SUMX, FILTER). ROE = (NetIncome/Sales) * (Sales/Assets) * (Assets/Equity) — break ROE into margin, turnover, leverage. Use structured table references (Table1[Column]) — auto-adjust as the table grows.

Question 43 Excel Formulas For Financial Analysts

DATEADD for prior period?

Insert → Slicer, bind to a field, place on the dashboard. Slicers filter all connected pivots and charts. Use =$B2="Critical" in a CF rule applied to =$A2:$Z2 — the relative row reference paints the entire row. CALCULATE([Sales], DATEADD('Date'[Date], -1, MONTH)) — same period one month earlier, smoothing month-length differences. =(CurrentAssets - Inventory) / CurrentLiabilities — often computed from a financial-statements table.

Question 44 Excel Formulas For Financial Analysts

Pivot: show running total?

Project FCF, discount at WACC, sum, add terminal value: TV / (1+WACC)^n, subtract debt, divide by shares for per-share value. =VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) — declining-balance with switch to straight-line. Value Field Settings → Show Values As → Running Total In → choose the date field. =ContributionMargin / OperatingIncome — % change in EBIT from a 1% change in sales.

Question 45 Excel Formulas For Financial Analysts

SUMIFS argument order?

=(PRICE(sett,mat,coup,yld+0.0001,freq,basis)-PRICE(sett,mat,coup,yld,freq,basis))/10000 — dollar value of a 1bp move. SUMIFS(sum_range, criteria_range1, criteria1, ...) — sum_range comes first, a common source of bugs since it differs from SUMIF. COVAR / COVARIANCE.P measures directional co-movement. CORREL normalizes to [-1, 1] — usually what you want for diversification analysis. =DGET(database, field, criteria) — extracts a single value from a table that matches a criteria range. Useful for ad-hoc lookups with multi-condition criteria.

Question 46 Excel Formulas For Financial Analysts

DAX 'time intelligence' for YoY%?

#,##0_);(#,##0) — accounting style: positive numbers right-aligned, negatives in parentheses. =DSO + DIO - DPO — should be minimized to free working capital. YoY% := DIVIDE([Sales], CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))) - 1 =A2# references the entire spill range of the formula in A2. Cleaner than OFFSET for dynamic ranges.

Question 47 Excel Formulas For Financial Analysts

Convert dollar amounts to thousands without changing values?

Each period: beginning balance → interest accrual (rate × avg balance) → mandatory + optional repayments → ending balance. Custom format: #,##0,;-#,##0,;0 — one trailing comma divides display by 1000. =ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis]) — interest accrued since last coupon. =FinalMetric * ExitMultiple — e.g., LTM EBITDA × 8.

Question 48 Excel Formulas For Financial Analysts

Net debt computation?

=TotalDebt - CashAndEquivalents — subtract all interest-bearing cash to avoid inflating equity value. Re = D1/P0 + g — Gordon model on equity cash flows. RunningPeak = MAX(returns so far); Drawdown = (value - RunningPeak)/RunningPeak. Implement in a helper column or with SCAN/MAX in M365. Connect to a source (CSV/SQL/API), transform with steps that re-run on refresh — automate repetitive cleaning.

Question 49 Excel Formulas For Financial Analysts

What-if: two-variable Data Table?

CALCULATE([NetFlow], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))) — running total across the visible date range. =AVERAGEX(Sales, Sales[Amount]) — averages over a derived table, e.g., per-customer or per-day averages that ordinary AVERAGE can't produce. Set up a table with one input along the top row, another down the left column, and a cell with the formula being evaluated. Data → What-If → Data Table. Customers := DISTINCTCOUNT(Orders[CustomerId])

Question 50 Excel Formulas For Financial Analysts

Annuity PV factor shortcut?

EBITDA = EBIT + D&A — add back the period's depreciation and amortization. Watch for impairment add-backs. =EBIT / InterestExpense Three-section sheet layout: Inputs (yellow) → Model (black formulas) → Outputs (summary). = (1 - (1+r)^-n) / r — closed form for equal-payment PV; Excel uses PV(rate, nper, -1).