Finance functions

Overview

Use this category when you need standard financial calculations such as present value, future value, annuity payments, discounting, and investment appraisal. These functions are typically used for cash flow modeling, financing schedules, valuation, and depreciation.

Start here if…

• You want to discount cash flows (NPV / PV).

• You want an investment return metric. (IRR)

• You want annuity-style calculations (payments, periods, rate).

• You need depreciation logic per period.

Not here if…

• You want rollforward logic, running totals, or projections → see Rollforward & time series

• You want numeric helpers such as rounding, min/max, logs → see Math & numeric

Mental model

• Most finance functions assume a rate per period and a time series of payments/cash flows.

• Results are sensitive to:

  • the time granularity (month vs year),

  • the sign convention (inflows vs outflows),

  • and whether inputs are complete (missing values can break calculations).

Common patterns

• Discount a series of cash flows → NPV('DiscountRate', 'CashFlow')
  Use when you want net present value at a discount rate.

• Compute internal rate of return → IRR('CashFlow')
  Use when you want the discount rate that sets NPV to zero.

• Calculate a periodic payment → PMT('Rate', 'Periods', 'PresentValue')
  Use for annuity payments (loan repayment, leasing, savings plans).

• Solve for number of periods → NPER('Rate', 'Payment', 'PresentValue')
  Use when rate and payment are known and you want the duration.

• Solve for the rate → RATE('Periods', 'Payment', 'PresentValue', …)
  Use when periods and payments are known and you need the implied interest rate.

• Compute present value → PV('Rate', 'Periods', 'Payment', …)
  Use to discount an annuity-like stream into a present value.

• Compute future value → FV('Rate', 'Periods', 'Payment', …)
  Use to project an annuity-like stream into the future.

• Model depreciation per period → DEPRECIATION('InitialValue', 'DepreciationTime')
  Use when you want straight-line depreciation over time.

Choosing between similar functions

• NPV vs PV

  • Use NPV to discount a cash flow series into a single net present value.

  • Use PV for present value of an annuity-style stream with structured parameters.

• IRR vs NPV

  • Use NPV when the discount rate is known and you want the value.

  • Use IRR when the value target is NPV = 0 and you want the implied rate.

• PMT vs PV/FV

  • Use PMT when you want the periodic payment.

  • Use PV/FV when you want present or future value given payment and rate.

• NPER vs RATE

  • Use NPER when the rate is known and you want the number of periods.

  • Use RATE when the number of periods is known and you want the implied rate.

Pitfalls & troubleshooting

• Rate scaling errors: if results are off by a large factor, confirm your rate is per period (monthly vs yearly).

• IRR instability: IRR needs cash flows with at least one sign change (mix of inflows and outflows).

• Missing values: if results are missing, spot-check whether inputs are missing at the same intersections (missing values often propagate).

• Too much dimensionality: if cash flow nodes have extra dimensions (Product/Region/etc.), validate one representative slice first before rolling up.

• Depreciation timing mismatch: if depreciation looks shifted, confirm the depreciation time aligns with your project’s time granularity.

Related sections

• Math & numeric rounding and helpers often used around finance results

• Rollforward & time series project cash flows and build time-based schedules

• Compare periods growth metrics for financial KPIs

• Formula basics evaluation order, constants, and notation

• Troubleshooting guide missing values and unexpected results