Financial 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:
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:
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%
Enable comment auto-refresher