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.
Parameter Description rateThe 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, or1.08^(1/12)-1with monthly compounding).nPerNumber of periods in the lifetime of an annuity. perThe period (between 1 and «nPer») being computed. pvThe 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). fvThe 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. pmtThe total payment per period (interest + principal). If you receive payments, this is positive. If you make payments, this is negative. typeIndicates 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 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.
See also CumIPmt().
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.
See also CumPrinc().
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]
See also Fv().
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 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) ={ \Bigg(\frac{Npv(r, x * (x \gt 0), i) * (1 + r)^{n + 1}} {Npv(f, -x * (x \lt 0), i) *(1 + f)^{n + 1} }\Bigg) }^{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%
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)
See also NPer().
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() function 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.
Example:
Variable Earnings :=
Time ▶ 1999 2000 2001 2002 2003 2024 -1M -500K -100K 100K 1M 2M
At a discount rate of 5%, the net present value of this cash flow is:
Npv(5%, Earnings, Time) → $865,947.76
Pmt(rate, nPer, pv, fv, type)
Returns the total payment per period (interest + principal) for an annuity with constant periodic payments and fixed interest rate.
Example: You obtain a 30-year fixed mortgage at 8%/12 per month for $100K. Your monthly payment is (note that the result of Pmt() is negative since this is a payment for you):
-Pmt(8%/12, 30*12, 100K) → $733.76
PPmt(rate, per, nPer, pv, fv, type)
Returns the principal portion of a payment on an annuity with constant period payments and fixed interest rate.
Example: You have a 30-year fixed $100K loan at a rate of 8%/12 monthly. On your 24th payment, the amount of your payment that goes towards principal is (note that the result of PPmt() is negative since this is a payment for you):
-PPmt(8%/12, 24, 12*30, 100K) → $78.18
Pv(rate, nPer, pmt, fv, type)
Returns the present value of an annuity. The annuity is assumed to have constant periodic payments to you of «pmt» per period for «nPer» periods, with a return of «rate» per period.
Example: To receive $100 per month from an annuity that returns 6%/12 per month for the next 10 years, you would need to invest (note that the result from Pv() is negative since you are paying to make the investment):
-Pv(6%/12, 10*12, 100) → $9,007.35
Rate(nPer, pmt, pv, fv, type, guess)
Returns the interest rate (per period) for an annuity. The value returned is the interest rate that results in equal payments of «pmt» per period over the «nPer» periods of the annuity.
In general, Rate() can have zero or multiple solutions. The implementation uses an interactive search algorithm. The optional «guess» can be provided as a starting point for the search, which usually results in the solution closest to «guess» being returned. If no solution is found in 30 iterations, Rate() returns NaN.
Example: You obtain a 30-year mortgage at a supposed 7% annual percentage rate for $100K. To do so, you pay $2,000 up front in “points”, and another $1,500 in fees. Assuming you hold the loan for its full term, the effective interest rate of your loan (for you) is:
Rate(30, Pmt(7%, 30, 100K), 100K - 3500) → 7.36%
XIrr(values, dates, i, guess)
Returns the annual internal rate of return (IRR) for a series of payments (negative values) and inflows (positive values) that occur at non-periodic intervals. Both «values» and «dates» must be indexed by «i». The «values» array constrains the cash flow amounts, the dates array contains the date of each payment or inflow, where each date in Analytica is expressed as the number of days since Jan. 1, 1904. The rate is based on a 365 day year.
If the cash flow never changes sign, there is no solution and XIrr() returns NaN. If the cash flow changes sign more than once, XIrr() can have multiple solutions, but returns only the first solution found. The optional parameter, «guess», can be provided as a starting point for the iterative search, and XIrr() generally finds the solution closest to «guess». If not provided, «guess» defaults to 10%. If no solution is found within 30 iterations, XIrr() returns NaN.
To compute the IRR for a series of period payments, use Irr().
Example:
Variable EarningAmt :=
J ▶ 1 2 3 4 -400K -200K 100K 600K
Variable EarningDate :=
J ▶ 1 2 3 4 July 5, 2009 Dec 1, 2009 Jan 21, 2010 Aug 10, 2011
XIrr(EarningAmt, EarningDate, J) → 9.32%
«EarningDate» can be entered by selecting Number Format from the Result menu while editing the table for «EarningDate». From the Number format dialog, select a date format, then enter the dates.
XMIrr(values, dates, i, financeRate, reinvestRate)
Computes the modified internal rate of return for a series of non-periodic cash flows occurring on arbitrary dates. The parameters «values» and «dates» should share the index «i», with «values» containing the cash flow amounts and dates containing the corresponding date on which that cash flow occurs. Each date is specified as a number indicating the number of days elapsed since the date origin. 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».
Example:
Variable EarningAmt :=
J ▶ 1 2 3 4 -400K -200K 100K 600K
Variable EarningDate :=
J ▶ 1 2 3 4 July 5, 2009 Dec 1, 2009 Jan 21, 2010 Aug 10, 2011
XMIrr(EarningAmt, EarningDate, J, 8%, 4%) → 8.62%
See also XMIrr().
XNpv(rate, values, dates, i)
Returns the net present value (NPV) of a non-periodic cash flow with a constant discount rate. «rate» is the annual discount rate for a 365 day year. Both values, the cash-flow amounts, and dates, the date of each payment (negative value) or inflow (positive value), must be indexed by «i». See also Npv().
Example: Using the cash flow shown in the example for XIrr() above, the net present value at a 5% discount rate is:
XNpv(5%, EarningAmt, EarningDate, J) → $42,911.10
See also XNpv().
YearFrac(startDate, endDate, basis)
Returns the fraction of the year represented by the number of whole days between two dates. The «startDate» and «endDate» are numeric, denoting the number of days elapsed since the date origin. The result is always positive, with the result being the span between the lesser and greater of the two dates. Use this function when you wish to compute the proportion of a whole year as applicable to certain financial instruments. The optional «basis» parameter selects the accounting method to be used, as follows:
0(or omitted) = US (NASD) 30/3601= actual/actual2= Actual/3603= Actual/3654= European 30/360
See more at YearFrac().
See Also
Enable comment auto-refresher