CFM Excel Functions & Formulas for Financial Modeling

Question 1

What is the main advantage of using INDEX/MATCH instead of VLOOKUP in a financial model?

Accepted Answer

INDEX/MATCH can return values from columns to the left of the lookup column and does not break when columns are inserted

Answer

VLOOKUP uses a hard-coded column number and can only look to the right. INDEX/MATCH references the return range directly, so it can look in any direction and keeps working when columns are added or removed.

Question 2

A modeler has cash flows that occur on irregular dates. Which function gives the most accurate present value?

Accepted Answer

XNPV

Answer

XNPV discounts each cash flow using its actual date. NPV assumes equally spaced periods and discounts the first cash flow by one full period.

Question 3

What does =EOMONTH(A1,0) return if A1 contains 14-Mar-2026?

Accepted Answer

31-Mar-2026

Answer

EOMONTH returns the last day of the month that is the given number of months from the start date. An offset of 0 gives the end of the same month, 31-Mar-2026.

Question 4

Which function is best for summing revenue for a specific product AND a specific region from a flat data table?

Accepted Answer

SUMIFS

Answer

SUMIFS sums a range using several criteria at once. SUMIF handles only one criterion, and COUNTIFS counts rows rather than summing values.

Question 5

A model calculates =PMT(6%/12, 360, 300000) for a mortgage. Why does the result come out negative?

Accepted Answer

Excel's cash flow sign convention shows payments as outflows when the principal is entered as a positive inflow

Answer

Excel's financial functions treat cash received (the loan principal) and cash paid (the payments) as having opposite signs. A positive present value therefore gives a negative payment.

CFM - Certified Financial Modeler Practice Test

CFM - Certified Financial Modeler Practice Test

CFM Excel Functions & Formulas for Financial Modeling