Valuation functions

Put a value on money over time.

Syntax

Write the function's name and its arguments. Rates are fractions, so 5 percent is 0.05. Money you pay out is negative.

PV(rate, nper, pmt, fv, payment_type)
FV(rate, nper, pmt, pv, payment_type)
NPV(rate, values)
EFFECT(nominal_rate, npery)
NOMINAL(effect_rate, npery)
ENTERPRISE_VALUE(market_cap, total_debt, cash)
SLN(cost, salvage, life)
SYD(cost, salvage, life, per)
DDB(cost, salvage, life, period, [factor])
DB(cost, salvage, life, period, [month])
XNPV(rate, values_csv, dates_csv)
NET_BOOK_VALUE(acquisition_cost, accumulated_depreciation)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • PV

    rate

    Required: Yes
  • nper

    Required: Yes
  • pmt

    Required: Yes
  • fv

    Required: Yes
  • payment_type

    Required: Yes
  • FV

    rate

    Required: Yes
  • nper

    Required: Yes
  • pmt

    Required: Yes
  • pv

    Required: Yes
  • payment_type

    Required: Yes
  • NPV

    rate

    Required: Yes
  • values

    Required: Yes
  • EFFECT

    nominal_rate

    Required: Yes
  • npery

    Required: Yes
  • NOMINAL

    effect_rate

    Required: Yes
  • npery

    Required: Yes
  • ENTERPRISE_VALUE

    market_cap

    Required: Yes
  • total_debt

    Required: Yes
  • cash

    Required: Yes
  • SLN

    cost

    Required: Yes
  • salvage

    Required: Yes
  • life

    Required: Yes
  • SYD

    cost

    Required: Yes
  • salvage

    Required: Yes
  • life

    Required: Yes
  • per

    Required: Yes
  • DDB

    cost

    Required: Yes
  • salvage

    Required: Yes
  • life

    Required: Yes
  • period

    Required: Yes
  • factor

    Required: No
  • DB

    cost

    Required: Yes
  • salvage

    Required: Yes
  • life

    Required: Yes
  • period

    Required: Yes
  • month

    Required: No
  • XNPV

    rate

    Required: Yes
  • values_csv

    Required: Yes
  • dates_csv

    Required: Yes
  • NET_BOOK_VALUE

    acquisition_cost

    Required: Yes
  • accumulated_depreciation

    Required: Yes

Examples

A project that costs 1000 today and returns 300, 400 and 500 over three years, valued at an 8 per cent discount rate and at 12 per cent.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
PVdecimalThe present value of the paymentsPV(0.05, 10, -100, 0, 0)
FVdecimalThe future valueFV(0.05, 10, -100, 0, 0)
NPVdecimalThe net present valueNPV(0.1, -100, 50, 60)
EFFECTdecimalThe effective annual rateEFFECT(0.1, 12)
NOMINALdecimalThe stated annual rateNOMINAL(0.1, 12)
ENTERPRISE_VALUEdecimalEquity plus debt less cashENTERPRISE_VALUE(1000, 200, 50)
SLNdecimalThe straight line depreciation for one periodSLN(1000, 100, 10)
SYDdecimalThe sum of years digits depreciation for the periodSYD(1000, 100, 10, 1)
DDBdecimalThe double declining balance depreciation for the periodDDB(1000, 100, 10, 1)
DBdecimalThe fixed declining balance depreciation for the periodDB(1000, 100, 10, 1)
XNPVdecimalThe net present value of cash flows on irregular dates, with a 365 day yearXNPV(0.1, "-100,50,60", "46023,46113,46204")
NET_BOOK_VALUEdecimalThe cost less the depreciation charged: what the asset is carried atNET_BOOK_VALUE(1000000, 300000)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-5004 Not enough paramsAn argument is missing. The message lists the names the function expects, such as rate, nper, pmt, fv, payment_typeGive every argument it lists
HTQL-5005An argument has the wrong type, such as a list where NPV expects separate numbersPass numbers as separate arguments
Limitations
  • PV, FV and the loan functions take every argument, including fv or pv and the payment type. Pass 0 where you have no value.
  • Money you pay out is negative, and money you receive is positive.
  • Coming: ROUND does not accept a finance function's result. ROUND(PV(0.05, 10, -100, 0, 0), 2) answers [HTQL-2001] Expected a numeric value, Func(Finance(TvmPv(0.05, 10.0, -100.0, 0.0, 0))), and so does ROUND(SLN(1000, 100, 10), 2), with the same kind of message. ADD, SUBTRACT, MULTIPLY, DIVIDE, MAX, ABS and IF do accept it, so the examples show the figures as they come.
  • Coming: a finance function's result cannot be an argument of another finance function: WACC(600, 400, CAPM(0.03, 1.2, 0.08), 0.05, 0.3) answers [HTQL-5005] Invalid params for FinanceFunctions: Expected number, got Func(Finance(Capm(0.03, 1.2, 0.08))). Type the value in.
  • NPV discounts its first cash flow by one period, as spreadsheets do: take an outlay made today off the result instead of passing it in.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Keep rates as fractions and periods in the same unit as the rate.
  • Check a result against a case you can work by hand.