Financial functions

Analytica User GuideOther FunctionsFinancial functions

These functions can be accessed under the Definition menu Financial command, or in the Object Finder dialog, Financial library. The function names and parameters match those in Microsoft Excel, where they are equivalent. Of course, the Analytica versions support array abstraction, which makes them more flexible.

Parameters: The same parameters occur in many of the financial functions. These parameters are described here. Dollar amounts for both parameters and return values of functions are expressed as the amount you receive. If you make a payment, the amount is negative. If you receive a payment, the amount is positive.

rate	The interest rate per period. For example, if periods are months, the rate should be adjusted to the monthly rate, not the annual rate (e.g., `8%/12`, or `1.08^(1/12)-1` with monthly compounding).
nPer	Number of periods in the lifetime of an annuity.
per	The period (between 1 and nPer) being computed.
pv	The present value of the annuity. For example, for a loan this is the loan amount (positive if you receive the loan, negative if you are the lender).
fv	The future value of the annuity. This is the remaining value of the annuity after the final payment. In the case of a loan, for example, this is the balloon payment at the end (positive if you are the lender, negative if you pay the balloon amount). This parameter is usually optional with a default value of zero.
pmt	The total payment per period (interest + principal). If you receive payments, this is positive. If you make payments, this is negative.
type	Indicates whether payments are due at the beginning or end of each period.
True	Payments are due at the beginning of each period, with the first payment due immediately.
False (default)	Payments are due at the end of each period.

Cumipmt(rate, nPer, pv, startPeriod, endPeriod, type)

Returns the cumulative interest paid on an annuity between, and including, startPeriod (shown as sp in equation below) and endPeriod (shown as ,code>ep in equation below). The annuity is assumed to have a constant interest rate and periodic payments. This is equal to:

[math]\displaystyle{ \sum_{n=sp}^{ep} Ipmnt(rate, n, nPer, Pv, 0, Type) }[/math]

Example: Interest payments during the first year on a $100,000 loan at 8% is:

CumIPmt(8%/12, 360, 100K, 1, 12) → -7,969.81

The result is negative since these are payments.

Cumprinc(rate, nPer, pv, startPeriod, endPeriod, type)

Returns the cumulative principal paid on an annuity between, and including, startPeriod (shown as sp in equation below) and endPeriod (shown as ep in equation below). The annuity is assumed to have a constant interest rate and periodic payments. The result is equal to:

[math]\displaystyle{ \sum_{n=sp}^{ep} Ppmnt(Rate, n, NPer, Pv, 0, Type) }[/math]

Example: The total principal paid during the first year on a $100,000 loan at 8% is:

CumPrinc(8%/12, 360, 100K, 1, 12) → -835.36

The result is negative since these are payments.

Fv(rate, nPer, pmt, pv, type)

Returns the future value of an annuity investment with constant periodic payments and fixed interest rate. The result is positive if you receive money at the end of the annuity’s lifetime, and negative if you must make a payment at the end of the annuity’s lifetime.

Examples: You invest $1000 in an annuity that pays 6% annual interest, compounded monthly (0.5% per month), that pays out $50 at the end of each month for 12 months, and then refunds whatever is left after 12 months. The amount refunded is:

Fv(0.5%, 12, 50, -1000) → $444.90

You borrow $50,000 at a fixed annual rate of 12% (1% per month). You make monthly payments of $550 for 15 years, and then pay off the remaining balance in a single balloon payment. That final balloon payment is (the negative is because it is a payment for you):

-Fv(1%, 15*12, -550, 50000) → $25,020.99

You open a fixed-rate bank account that pays 0.5% per month in interest. At the beginning of each month (including when you open the account) you deposit $100. The amount in the account at the end of the each of the first three years is:

Fv(0.5%, [12, 24, 36], -100, 0, True) → [$1239.72, $2555.91, $3953.28]

Ipmt(rate, per, nPer, pv, fv, type)

Returns the interest portion of a payment on an annuity, assuming constant period payments and fixed interest rate.

Example: The interest you pay in the 24th month on a 30-year fixed $100K loan at an 8%/12 monthly interest rate is (the result of IPmt is negative since this is a payment for you):

-IPmt(8%/12, 24, 12*30, 100K) → $655.59

Irr(values, i, guess)

Returns the internal rate of return (IRR) of a series of periodic payments (negative values) and inflows (positive values). The IRR is the discount rate at which the net present value (NPV) of the flows is zero. The array values must be indexed by i.

If the cash flow never changes sign, Irr() has no solution and returns NaN (not a number). If a cash flow changes sign more than once, Irr() might have multiple solutions, and returns the first solution found. The implementation uses an iterative gradient-descent search to locate a solution. The optional argument, guess, can be provided as a starting value for the search (default is 10%). When there are multiple solutions, the one closest to guess is usually returned. If no solution is found within 30 iterations, Irr() returns ,code>NaN.

To compute the IRR for a non-periodic cash flow, use XIRR().

Example:

Variable Earnings :=

Time ▶
2015	2016	2017	2018	2019	2020
-1M	-500K	-100K	100K	1M	2M

Irr(Earnings, Time) → 17.15%

MIrr(values, i, financeRate, reinvestRate)

Computes the modified internal rate of return for a series of periodic cash flows, given in values over the index i. The MIrr is the rate of return of an investment when capital invested must be borrowed at financeRate, and intermediate returns are re-invested at reinvestRate. Because the result of MIrr is expressed as a rate-of-return, it shares the intuitive appeal of Irr as a measure of the quality of a cash flow, while avoiding the many pitfalls and distortions associated with Irr. The MIrr is defined by the following formula

[math]\displaystyle{ MIrr(x, i, f, r) ={ (\frac{Npv(r, x * (x \gt 0), i) * (1 + r)^{n + 1}} {Npv(f, -x * (x \lt 0), i) *(1 + f)^{n + 1} })}^{1/n} - 1 }[/math]

Example:

Variable Earnings :=

Time ▶
2015	2016	2017	2018	2019	2020
-1M	-500K	-100K	100K	1M	2M

MIrr(Earnings, Time, 8%, 4%) → 15.24%

Tip

To compute MIrr for a non-periodic cash flow, use XMIrr().

Nper(rate, pmt, pv, fv, type)

Returns the number of periods of an annuity with constant periodic payments and fixed interest rate.

Example: You invest $10,000 in an annuity that pays 8% annually. Each year you withdraw $1,000. Your annuity lasts for:

NPer(8%, 1000, -10K) → 20.91 (years)

Npv(discountRate, values, i, offset)

Returns the net-present value of a cash flow with equally spaced periods. The values parameter contains a series of periodic payments (negative values) and inflows (positive values), indexed by i. Future values are discounted by discountRate per period. The optional offset parameter specifies the offset of the first value relative to the current time period. By default, offset is 1, indicating that the first value is discounted as if it is one step in the future. Npv(.., offset: 0) applies no discount to the first value, which should be used when the cash flow starts in the current time period. The NPV is given by:

[math]\displaystyle{ \sum_{j = offset}^{n + 1 - offset} \frac{Values[I = j]}{(1 + DiscountRate)} }[/math]

The first value is discounted as if it is one step in the future. To treat the first value as occurring in the first time period, set the optional offset parameter to zero.