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
Required: Yesrate
Required: Yesnper
Required: Yespmt
Required: Yesfv
Required: Yespayment_typeFV
Required: Yesrate
Required: Yesnper
Required: Yespmt
Required: Yespv
Required: Yespayment_typeNPV
Required: Yesrate
Required: YesvaluesEFFECT
Required: Yesnominal_rate
Required: YesnperyNOMINAL
Required: Yeseffect_rate
Required: YesnperyENTERPRISE_VALUE
Required: Yesmarket_cap
Required: Yestotal_debt
Required: YescashSLN
Required: Yescost
Required: Yessalvage
Required: YeslifeSYD
Required: Yescost
Required: Yessalvage
Required: Yeslife
Required: YesperDDB
Required: Yescost
Required: Yessalvage
Required: Yeslife
Required: Yesperiod
Required: NofactorDB
Required: Yescost
Required: Yessalvage
Required: Yeslife
Required: Yesperiod
Required: NomonthXNPV
Required: Yesrate
Required: Yesvalues_csv
Required: Yesdates_csvNET_BOOK_VALUE
Required: Yesacquisition_cost
Required: Yesaccumulated_depreciation
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
PV | decimal | The present value of the payments | PV(0.05, 10, -100, 0, 0) |
FV | decimal | The future value | FV(0.05, 10, -100, 0, 0) |
NPV | decimal | The net present value | NPV(0.1, -100, 50, 60) |
EFFECT | decimal | The effective annual rate | EFFECT(0.1, 12) |
NOMINAL | decimal | The stated annual rate | NOMINAL(0.1, 12) |
ENTERPRISE_VALUE | decimal | Equity plus debt less cash | ENTERPRISE_VALUE(1000, 200, 50) |
SLN | decimal | The straight line depreciation for one period | SLN(1000, 100, 10) |
SYD | decimal | The sum of years digits depreciation for the period | SYD(1000, 100, 10, 1) |
DDB | decimal | The double declining balance depreciation for the period | DDB(1000, 100, 10, 1) |
DB | decimal | The fixed declining balance depreciation for the period | DB(1000, 100, 10, 1) |
XNPV | decimal | The net present value of cash flows on irregular dates, with a 365 day year | XNPV(0.1, "-100,50,60", "46023,46113,46204") |
NET_BOOK_VALUE | decimal | The cost less the depreciation charged: what the asset is carried at | NET_BOOK_VALUE(1000000, 300000) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-5004 Not enough params | An argument is missing. The message lists the names the function expects, such as rate, nper, pmt, fv, payment_type | Give every argument it lists |
HTQL-5005 | An argument has the wrong type, such as a list where NPV expects separate numbers | Pass numbers as separate arguments |
Limitations
PV,FVand the loan functions take every argument, includingfvorpvand the payment type. Pass0where you have no value.- Money you pay out is negative, and money you receive is positive.
- Coming:
ROUNDdoes 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 doesROUND(SLN(1000, 100, 10), 2), with the same kind of message.ADD,SUBTRACT,MULTIPLY,DIVIDE,MAX,ABSandIFdo 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. NPVdiscounts 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.