Investment functions

Judge a return and plan a repayment.

Syntax

Write the function's name and its arguments. Rates are fractions. Money you pay out is negative.

IRR(cashflows, guess)
MIRR(cashflows, finance_rate, reinvest_rate)
CAGR(beginning_value, ending_value, years)
PMT(rate, nper, pv, fv, payment_type)
IPMT(rate, per, nper, pv, [fv], [type])
PPMT(rate, per, nper, pv, [fv], [type])
NPER(rate, pmt, pv, fv, payment_type)
RATE(nper, pmt, pv, fv, payment_type, guess)
CUMIPMT(rate, nper, pv, start_period, end_period, type)
CUMPRINC(rate, nper, pv, start_period, end_period, type)
XIRR(cashflows_csv, dates_csv, guess)

Parameters and arguments

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

  • IRR

    cashflows

    Required: Yes
  • guess

    Required: Yes
  • MIRR

    cashflows

    Required: Yes
  • finance_rate

    Required: Yes
  • reinvest_rate

    Required: Yes
  • CAGR

    beginning_value

    Required: Yes
  • ending_value

    Required: Yes
  • years

    Required: Yes
  • PMT

    rate

    Required: Yes
  • nper

    Required: Yes
  • pv

    Required: Yes
  • fv

    Required: Yes
  • payment_type

    Required: Yes
  • IPMT

    rate

    Required: Yes
  • per

    Required: Yes
  • nper

    Required: Yes
  • pv

    Required: Yes
  • fv

    Required: No
  • type

    Required: No
  • PPMT

    rate

    Required: Yes
  • per

    Required: Yes
  • nper

    Required: Yes
  • pv

    Required: Yes
  • fv

    Required: No
  • type

    Required: No
  • NPER

    rate

    Required: Yes
  • pmt

    Required: Yes
  • pv

    Required: Yes
  • fv

    Required: Yes
  • payment_type

    Required: Yes
  • RATE

    nper

    Required: Yes
  • pmt

    Required: Yes
  • pv

    Required: Yes
  • fv

    Required: Yes
  • payment_type

    Required: Yes
  • guess

    Required: Yes
  • CUMIPMT

    rate

    Required: Yes
  • nper

    Required: Yes
  • pv

    Required: Yes
  • start_period

    Required: Yes
  • end_period

    Required: Yes
  • type

    Required: Yes
  • CUMPRINC

    rate

    Required: Yes
  • nper

    Required: Yes
  • pv

    Required: Yes
  • start_period

    Required: Yes
  • end_period

    Required: Yes
  • type

    Required: Yes
  • XIRR

    cashflows_csv

    Required: Yes
  • dates_csv

    Required: Yes
  • guess

    Required: Yes

Examples

A 25,000 loan at 7.2 per cent a year over 5 years, paid monthly: the monthly payment and the total repaid.

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
IRRdecimalThe rate that makes the net present value zeroIRR(-100, 50, 60, 0.1)
MIRRdecimalThe modified internal rate of returnMIRR("-100,50,60", 0.1, 0.12)
CAGRdecimalThe compound annual growth rateCAGR(100, 200, 5)
PMTdecimalThe payment for each periodPMT(0.05, 10, 1000, 0, 0)
IPMTdecimalThe interest part of the payment for the periodIPMT(0.05, 1, 10, 1000)
PPMTdecimalThe principal part of the payment for the periodPPMT(0.05, 1, 10, 1000)
NPERdecimalThe number of periods neededNPER(0.05, -100, 1000, 0, 0)
RATEdecimalThe rate for each periodRATE(10, -100, 700, 0, 0, 0.1)
CUMIPMTdecimalThe interest paid between two periodsCUMIPMT(0.05, 10, 1000, 1, 3, 0)
CUMPRINCdecimalThe principal repaid between two periodsCUMPRINC(0.05, 10, 1000, 1, 3, 0)
XIRRdecimalThe yearly rate of return on cash flows on irregular dates, with a 365 day yearXIRR("-100,50,60", "46023,46204,46388", 0.1)
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 expectsGive every argument it lists
HTQL-8004The rate could not be found (#NUM!), for example IRR with no guessPass a guess
Limitations
  • IRR needs its guess: IRR(-100, 50, 60) without one was refused during our checks.
  • MIRR takes its cash flows as one text, and IRR takes them as separate numbers: its last argument is always the guess, so IRR(-1000, 300, 400, 500) reads 500 as the guess and answers HTQL-8004 (#NUM!).
  • Coming: ROUND does not accept a finance function's result, and a finance function's result cannot be an argument of another finance function. ROUND(PMT(0.006, 60, 25000, 0, 0), 2) answers [HTQL-2001] Expected a numeric value, Func(Finance(TvmPmt(0.006, 60.0, 25000.0, 0.0, 0))). MULTIPLY, DIVIDE, SUBTRACT, ABS and IF accept it.
  • CAGR needs a positive starting value and years above 0: CAGR(0, 200, 5) answers HTQL-5005 "Beginning value must be positive".
  • A rate of 0 is allowed in PMT: PMT(0, 60, 25000, 0, 0) divides the loan evenly.
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
  • Give IRR and RATE a guess near the answer you expect.
  • Use PPMT and IPMT together to build a repayment schedule.