• Annuity — The investment spreadsheet functions involve a series of periodic payments over a term measured in the number of payment periods. This set of spreadsheet functions allows you to compute one value, knowing three of the other values.
 • Bill — These spreadsheet functions compute values for Treasury billls.
 • Bond — These spreadsheet functions compute values for bonds.
 • Cash Flow — These spreadsheet functions operate on tables of data that record income and expenditures.
 • CD — These spreadsheet functions compute values for certificates of deposit.
 • Depreciation — These spreadsheet functions compute depreciation over time.
 • Stock — These spreadsheet functions compute values for common stock.

##### Understanding calendar conventions

Financial @functions support four different calendar conventions to count the difference in days between two dates. The optional Calendar argument lets you specify which calendar convention to use.

 Calendar Description 30/360 The 30/360 calendar convention assumes all months have 30 days and every year has 360 days. Using the 30/360 calendar, the number of years, months and days between two dates are counted separately. Then, the number of days between two dates is the sum of three quantities: the number of years times 360, the number of months time 30, and the number of days. Actual/Actual The Actual/Actual calendar convention considers the actual number of days between two dates and the actual number of days in the year. For example, February 28, 1994 and August 31, 1994 are 184 days apart. February 28, 1994 and March 1, 1994 are 1 day apart. Actual/360 The Actual/360 calendar convention considers the actual number of days in each month, but assumes 360 days in the year. Actual/365 The Actual/365 calendar convention considers the actual number of days in each month, but assumes 365 days in the year, thus making no provision for leap year.

 • @AMAINT Calculates the accumulated interest paid on an amortized loan after n payments.
 • @AMINT Calculates the periodic interest rate for an amortized loan.
 • @AMPMT Calculates the periodic payment for an amortized loan.
 • @AMPMTI Calculates the interest portion of the nth periodic payment of an amortized loan.
 • @AMPRN Calculates the initial principal of an amortized loan.
 • @AMRES Calculates the end value of an amortized loan or the future value of an annuity.
 • @AMRPRN Calculates the remaining balance of an amortized loan after n payments.
 • @AMTERM Calculates the length of an amortized loan, expressed as number of payments.
 • @CTERM Returns the number of compounding time periods required to achieve a specified future value, specified the present value and interest rate.
 • @CUMIPMT Returns the cumulative interest paid on a loan between specified periods or in a single period.
 • @CUMPRINC Returns the cumulative principal paid on a loan between specified periods or in a single period.
 • @EFFECT Calculates the effective annual interest rate for a specified nominal rate and number of compounding periods a year.
 • @FV Returns the future value of an investment at the end of the term, specified the payment, interest rate, and number of payments.
 • @FVAL Returns the future value of an investment, specified the interest rate, number of payments, periodic payment rate, and optional present value and type.
 • @IPAYMT Returns the interest portion of a single payment specified the interest rate, period, number of periods, present value, and optional future value and type.
 • @IRATE Returns the interest rate specified the number of payments, payment amount, present value, and optional future value and type.
 • @MTGACC Returns the new loan term, the payoff-date, or interest saved by paying extra monthly principal for a home loan.
 • @NOMINAL Calculates the nominal annual interest rate for a specified effective rate and number of compounding periods a year.
 • @NPER Returns the number of periods specified the interest rate, payment amount, present value, and optional future value and type.
 • @PAYMT Returns periodic payments specified the rate, number of payments, present value, and optional future value and type.
 • @PMT Returns the periodic payment required to fully amortize the principal during the term, specified present value, interest rate, and number of payments.
 • @PMTC Calculates monthly payments based on semi-annual interest compounding commonly used in Canada.
 • @PPAYMT Returns the principal portion of a single payment.
 • @PV Returns the present value of an investment, specified periodic payment, interest rate, and number of periods.
 • @PVAL Returns the present value of an investment, specified the interest rate, number of payments, periodic payment rate, and optional future value and type.
 • @RATE Returns the interest rate required to achieve a specified future value, specified the future value, present value, and number of payments.
 • @TERM Returns the number of periodic payments required to achieve a specified future value, specified the periodic payment amount and interest rate.
 • @YLD2YLD Converts a yield expressed in one compounding frequency and time length to that in another frequency and/or time length.

##### Using Annuity function arguments

 Argument Description Adv Number of cash flows (payments, deposits) made before the annuity begins. Fv Future value. Int Interest charged on the loan per period (not per year; many loans quote annual interest rates that must be divided by the number of payments per year). Nper Number of periods of the loan or investment (should be an integer greater than 0). Odd Number >= 0 that specifies the number of periods between the start of a loan and the first payment (for example, if the loan is made two and a half months before the first monthly payment is due, use 2.5) Payment Cash flow made each period. Per A specified loan or investment period, 1 through Nper. Pmt Payment. Principal Amount of money loaned or the initial deposit on an annuity that increases principal (like depositing \$2,500 to open a savings account) Pv Present value. Rate Interest rate (should be greater than -1). Residual Remaining principal and interest at the end of a loan that the annuity did not take care of ResOff Number of periods after the annuity ends before the residual must be paid; express it as a fraction of a period (for example, in a monthly loan, 1.5 means 1.5 months before the residual is due) Simp Specifies how the interest is calculated: 0 for compounded interest, 1 for simple interest Term The total number of cash flows (payments or deposits) to make Type 0 if payments are at the end of each period, 1 if at the beginning. This optional argument lets you use financial @functions to compute either an ordinary annuity, where periodic payments are made at the end of each period, or an annuity due, where payments are made at the beginning of each period. Quattro Pro assumes that Type = 0 unless you indicate otherwise.

Non-integer values are allowed for Nper, and the @functions give results that are consistent with other spreadsheet programs, but which are actually not very meaningful. If you borrow money from a bank for, say, 15.2 months with interest paid monthly, giving Nper a value of 15.2 in the financial @functions will only be a rough indicator of what the bank will tell you to pay. In order to compute the figures the way the bank would, you have to consider two transactions, one for 15 months and one for 0.2 months.

The functions assume that there is no residual unless a nonzero value is specified for the optional argument Residual. When a residual is specified, the functions assume that it is paid along with the last payment. When it is not, a positive value should be specified for the optional argument ResOff. For example, if the residual is paid three months after the last monthly payment, ResOff = 3. Compound interest is used during any fractional component of ResOff unless Simp = 1.

Advance payments, specified by Adv, are made on or before the first day of the loan period. They are included in the total payment count. The functions assume zero advance payments unless a nonzero value is specified for the optional argument Odd.

Odd specifies the time period between the beginning of a loan (or issue of an annuity) and the date of the first periodic payment, and does not necessarily constitute exactly one normal payment period. For example, if a loan begins on March 19, 1993 and monthly payments are due the first of every month beginning April 5, 1993, the first payment period is 17 days long. Since the implied normal first payment period, March 5 to April 5, is 31 days long, Odd = 17/31.

 • In the following @functions, as well as @NPV and @IRR, amounts with positive signs represent money received, and amounts with negative signs represent money paid: @FVAL, @IRATE, @IPAYMT, @NPER, @PAYMT, @PPAYMT, and @PVAL. This convention applies to arguments and to the results of the @functions. In 1-2-3-compatible @functions (such as @PV, @PMT, @FV, @RATE, @TERM, and @CTERM) the amounts are usually all positive regardless of which way the money changes hands.

 • @DISC Returns the discount rate for a security.
 • @INTRATE Returns the simple annualized yield for a fully invested security.
 • @PRICEDISC Returns the price per 100 face value of a security that pays periodic interest.
 • @RECEIVED Returns the amount received at maturity for a fully invested security.
 • @TBILLEQ Returns the bond equivalent yield for a Treasury bill.
 • @TBILLPRICE Returns the price per 100 face value for a Treasury bill.
 • @TBILLYIELD Returns the yield for a Treasury bill.
 • @YIELDDISC Returns the annual yield for a discounted security.

 • @ACCRINT Returns the accrued interest for a bond.
 • @ACCRINTXL Returns the accrued interest for a bond. (Excel version)
 • @ACCRUED Returns the accrued interest for a bond. (Lotus-123 version)
 • @COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date.
 • @COUPDAYS Returns the number of days in the coupon period that contains the settlement date.
 • @COUPDAYSNC Returns the number of days from the settlement date to the next coupon date.
 • @COUPNCD Returns the next coupon date after the settlement date.
 • @COUPNUM Returns the number of coupons payable between the settlement date and maturity date.
 • @COUPPCD Returns the previous coupon date before the settlement date.
 • @DURATION Returns the Macaulay duration of a security with par value of 100.
 • @MDURATION Returns the modified Macauley duration for a security with an assumed par value of 100.
 • @ODDFPRICE Returns the price per 100 face value of a security with an odd first period.
 • @ODDFYIELD Returns the yield of a security with an odd first period.
 • @ODDLPRICE Returns the price per 100 face value of a security with an odd last period.
 • @ODDLYIELD Returns the yield of a security with an odd last period.
 • @PRICE Returns the price per 100 face value of a security that pays periodic interest.
 • @YIELD Returns the yield on a security that pays periodic interest.
 • @YIELDPER Returns the yield for securities paying periodic interest.

#### Cash Flow Financial spreadsheet functions

Cash flow analysis is a process of listing a stream of cash gains and losses (positive and negative cash flows), modifying them using a percentage or percentages (discount rate(s)), and determining their future value, present value, or rate of growth (or decline; both are called the internal rate of return).

Unlike an annuity, this stream of cash flows does not always occur periodically, and does not have a fixed interest rate for each cash flow.

You can use cash flow functions to estimate the net present value of a cash flow stream, project the future value of the stream, compute the gains the stream is making as a percentage, or compute how the discount rates must change to achieve a specific future value.

 • @DURAT Calculates the Macaulay duration of a cash flow stream.
 • @FUTV Calculates the future value of a cash flow stream.
 • @IRR  Returns the internal rate of return of an investment.
 • @MIRR Calculates the modified internal rate of return on an investment consisting of payments made at regular intervals.
 • @NETPV Calculates net present value of a stream of cash flows.
 • @NPV  Returns the net present value of a future cash flow.
 • @PIRATE Calculates the internal rate of return for a stream of cash flows.
 • @SCMARG Calculates the discount scenario margin, the margin to add to each discount rate in order to arrive at a specified net present value.
 • @XIRR Returns the internal rate of return on an investment when cash flow is not necessarily periodic.
 • @XNPV Calculates the net present value of an investment when cash flow is not necessarily periodic.

For details on using cash flow @function arguments, choose one of the following topics:

 •

##### Using the Flows argument

In Quattro Pro, a stream of cash flows is specified by a column (or row) of values. The Flows argument of a cash flow function is set to this selection. Positive values add cash to the stream; negative values subtract from it. For example, if your savings account had two deposits of \$50, one withdrawal of \$25, and one deposit of \$75 (in that order), you could use A2..A5 or B2..E2 of the next figure to represent it in a cash flow function:

If the stream contains a series of equal cash flows, you can add an additional column (or row) to specify how many times a specified cash flow repeats. For example, you can replace A2..A5 of the previous figure with A2..B4 of the next figure:

The first column (or row) of the selection specifies how many times each cash flow occurs. For example, in the previous figure the value 2 (in A2) specifies that two cash flows of \$50 occur in the stream, not one.

 • Quattro Pro uses the size of the cash flow cells to determine whether you are specifying a column of cash flows or a row of cash flows. It assumes that selections with more than two rows contain cash flows in the second column; selections with more than two columns contain cash flows in the second row. In the case of a two-column, two-row selection, Quattro Pro assumes that the cash flows are in the second row.

##### Using the Filter, Start, and End arguments

You can use the argument, Filter, Start, and End to make Quattro Pro automatically exclude cash flows that do not fall in a certain range, such as all deposits, or any withdrawals less than \$20. Excluded cash flows are not included in the function calculations. Use Filter to specify the rules for exclusion, as shown in the next table.

 Filter Cash flows are excluded when 0 No filtering (the default) 1 Cash flow < Start 2 Cash flow <= Start 3 Cash flow > Start 4 Cash flow >= Start 5 Start < Cash flow < End 6 Start <= Cash flow <= End

As shown, Start and End are used differently, depending on the setting of Filter. They always bind the cash flows in some way; Start and End could be a range of cash flows values to use (Filter set to 5) or an upper limit for values (Filter set to 1, Start set to the upper limit).

##### Using the Discrate argument

The Discrate argument of a cash flow function specifies how the cash flows are discounted to achieve their future or net present value. It can be a single percentage (like 0.05 for 5%) that applies to all the cash flows, or a column (or row) of discount rates, one for each cash flow in the Flows cells (see the previous section). Positive discount rates decrease the cash flow; negative ones increase it. The next figure shows a stream of cash flows (in A2..B4) and their corresponding discount rates (in C2..C4).

The first two cash flows (specified by A2..B2) are discounted by 5% (as specified by C2). The third is discounted by -2.5% (an increase, as specified by the negative percentage in C3), and the final cash flow is discounted by 7.5% (as specified by C4).

##### Using the Simp and PathDep arguments

You can use the Simp argument to specify how Quattro Pro applies discount rates to cash flows. The next table shows the discounting methods available.

 Simp Discounting 0 Compounded 1 Mixture of compounded and simple 2 Simple

In addition to Simp, PathDep, which is used only when Discrate is a selection, specifies whether path-dependent discounting is used. When path-dependent discounting is used (PathDep is set to 1), the set of discount rates are chained together to determine future or net present value. If the order of discount rates changes, the future or net present value can be affected.

When path-dependent discounting is not used (PathDep is set to 0, the default), each cash flow is affected by its associated discount rate; other discount rates in Discrate do not affect it.

##### Using the Odd and Periods arguments

By default, cash flow functions assume that each cash flow occurs periodically (every month, every year, and so on). The arguments Odd and Periods let you specify irregular periods. You normally use one or the other, so these arguments appear in the function descriptions as Odd|Periods.

If the length of time of the first period is odd, specify a number for Odd. For example, if a series of cash flows are monthly, and the first period is half a month long, set Odd to 0.5; if the first period is one and a half months, set Odd to 1.5.

If several cash flows are unevenly spaced, specify cells for Periods. Periods is a column (or row) of numbers that specify the duration of each cash flow in the Flows cells. Like Odd, each value in the cells is expressed as a fraction of the regular period. For example, the next figure shows a cash flow cells in B2..B4, and a Period selection in A2..A4.

The value 1 in A2 specifies that the first cash flow (\$50) occurs at a regular period. You decide what this period is; it could be a week, a month, or a year. Assuming the regular period is a month, the value 3.5 (in A3) specifies that the second cash flow (\$75) occurs three and a half months after the first. The final value, 2, specifies that two months elapse between the second cash flow and the third.

Like Flows, the Periods selection can have an additional column (or row) added to specify how many times a specified period length repeats. Periods does not have to be the same size as Flows. For example, in the next figure, the cash flow stream is A2..B5.

Periods is C2..C4, and specifies that the first cash flow is 0.56745 periods away, the next 11 cash flows occur one period apart, and the last four cash flows are 1.5 periods apart.

 • In @FUTV, Odd specifies the length of the last period.

 • @ACCRINTM Returns the accrued interest for a security that pays interest at maturity.
 • @PRICEMAT Returns the price per 100 face value of a security that pays interest at maturity.
 • @YIELDMAT Returns the annual yield of a security that pays interest at maturity.

 • @DB Calculates the depreciation of an asset over a specified period using the fixed-declining balance method.
 • @DDB Returns the double-declining balance depreciation of an asset during the specified period.
 • @SLN Returns the straight-line depreciation of an asset over each period in its specified useful life.
 • @SYD Returns the sum-of-the-years’-digits depreciation of an asset during the specified period.
 • @VDB Calculates depreciation allowance using the variable-rate declining balance method.