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
Required: Yescashflows
Required: YesguessMIRR
Required: Yescashflows
Required: Yesfinance_rate
Required: Yesreinvest_rateCAGR
Required: Yesbeginning_value
Required: Yesending_value
Required: YesyearsPMT
Required: Yesrate
Required: Yesnper
Required: Yespv
Required: Yesfv
Required: Yespayment_typeIPMT
Required: Yesrate
Required: Yesper
Required: Yesnper
Required: Yespv
Required: Nofv
Required: NotypePPMT
Required: Yesrate
Required: Yesper
Required: Yesnper
Required: Yespv
Required: Nofv
Required: NotypeNPER
Required: Yesrate
Required: Yespmt
Required: Yespv
Required: Yesfv
Required: Yespayment_typeRATE
Required: Yesnper
Required: Yespmt
Required: Yespv
Required: Yesfv
Required: Yespayment_type
Required: YesguessCUMIPMT
Required: Yesrate
Required: Yesnper
Required: Yespv
Required: Yesstart_period
Required: Yesend_period
Required: YestypeCUMPRINC
Required: Yesrate
Required: Yesnper
Required: Yespv
Required: Yesstart_period
Required: Yesend_period
Required: YestypeXIRR
Required: Yescashflows_csv
Required: Yesdates_csv
Required: Yesguess
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
IRR | decimal | The rate that makes the net present value zero | IRR(-100, 50, 60, 0.1) |
MIRR | decimal | The modified internal rate of return | MIRR("-100,50,60", 0.1, 0.12) |
CAGR | decimal | The compound annual growth rate | CAGR(100, 200, 5) |
PMT | decimal | The payment for each period | PMT(0.05, 10, 1000, 0, 0) |
IPMT | decimal | The interest part of the payment for the period | IPMT(0.05, 1, 10, 1000) |
PPMT | decimal | The principal part of the payment for the period | PPMT(0.05, 1, 10, 1000) |
NPER | decimal | The number of periods needed | NPER(0.05, -100, 1000, 0, 0) |
RATE | decimal | The rate for each period | RATE(10, -100, 700, 0, 0, 0.1) |
CUMIPMT | decimal | The interest paid between two periods | CUMIPMT(0.05, 10, 1000, 1, 3, 0) |
CUMPRINC | decimal | The principal repaid between two periods | CUMPRINC(0.05, 10, 1000, 1, 3, 0) |
XIRR | decimal | The yearly rate of return on cash flows on irregular dates, with a 365 day year | XIRR("-100,50,60", "46023,46204,46388", 0.1) |
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 | Give every argument it lists |
HTQL-8004 | The rate could not be found (#NUM!), for example IRR with no guess | Pass a guess |
Limitations
IRRneeds itsguess:IRR(-100, 50, 60)without one was refused during our checks.MIRRtakes its cash flows as one text, andIRRtakes them as separate numbers: its last argument is always the guess, soIRR(-1000, 300, 400, 500)reads 500 as the guess and answersHTQL-8004(#NUM!).- Coming:
ROUNDdoes 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,ABSandIFaccept it. CAGRneeds a positive starting value and years above 0:CAGR(0, 200, 5)answersHTQL-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.