For calculation purposes, Quattro Pro stores all dates as serial integers beginning with 0 for December 30, 1899. The minimum, -109,571, equals January 1, 1600; the maximum, 474,816, equals December 31, 3199.
Quattro Pro stores times as decimal fractions; 0.000 represents 00:000:00, and 0.99999 represents 23:59:59. To format time expressions in your notebook, right-click the cell you want to format, click Cell Properties, then click Numeric Format.
For example, @TODAY returns the current date as an integer, and @NOW returns the current date with the current time added as a fraction.
Setting Holidays for Date & Time @Functions
Business date functions have three arguments to specify which dates are holidays: Saturday, Sunday, and Holidays. By default, Saturday and Sunday are holidays. Setting the argument Saturday to 1 specifies that Saturday is a business day; setting Sunday to 1 specifies that Sunday is a business day. Use the argument Holidays to specify holidays that do not fall on weekends (unless weekends are used as business days). You can set Holidays to cells containing holiday dates, the date of a single holiday, or 0 to specify no special holidays.
Entering Number Conversion @Functions
Input numbers that include non-numeric characters (such as hexadecimal or ASCII values) must be enclosed in quotation marks (for example, "1AF3C"). Numbers that exceed 14 digits, except decimal numbers, must also be enclosed in quotation marks.
For the 64-bit number conversion @functions, numbers must be in the following ranges for each base:
Base Range
Signed decimal -9223372036854775808 to +9223372036854775807
Unsigned decimal 0 to 18446744073709551615
Hexadecimal 0000000000000000 to FFFFFFFFFFFFFFFF
Octal 0000000000000000000000 to 1777777777777777777777
If a 64-bit number conversion @function results in a string value greater than 2 - 1, the @function returns ERR.
Entering Boolean @Functions
The shift @functions, @SHLB, @SHRB, @SHLH, and @SHRH, shift the bits in a number to the left or right. You can use the shift @functions to perform quick multiplication of integers. Each binary shift to the left is equivalent to multiplying the number by 2. Each binary shift to the right is equivalent to dividing the number by 2.
Binary Decimal
00011 3
00110 6
01100 12
11000 24
As you shift bits off one end of a number, bits are added to the other end. Shift, however, is not equal to rotate. For example, the binary number 1001 shifted left is 0010. The same number rotated left is 0011. To perform this rotation, you can use a nested bit test @function to set the BitIn argument.
The addition @functions, @ADDB and @ADDH, return the sum of two numbers. The subtraction @functions, @SUBB and @SUBH, return the difference of two numbers.
The overflow @functions, @SHLBO, @SHRBO, @SHLHO, @SHRHO, @ADDBO, @ADDHO, @SUBBO, @SUBHO, return the overflow bit (either 0 or 1) of a shift, addition, or subtraction operation. For example, if you shift the binary number 1001 to the left and maintain four places, the result is 0010. The overflow bit, that is, the bit that has shifted into the fifth place not shown, is 1.
The AND @functions, @ANDB and @ANDH, combine the zeros of the input words. Any bit that is 0 in either number sets the corresponding output bit to 0.
The OR @functions, @ORB and @ORH, combine the ones of the input words. Any bit that is 1 in either number sets the corresponding output bit to 1.
The exclusive OR @functions, @XORB and @XORH, set an output bit to 1 when two corresponding input bits are not equal.
The following table shows the results of AND, OR, and exclusive OR operations of Bit A and Bit B.
Bit A Bit B A AND B A OR B A XOR B
0 0 0 0 0
0 1 0 1 1
1 0 0 1 1
1 1 1 1 0
The invert @functions, @INVB and @INVH, invert individual bits of a number; that is, all bits with a value of 1 change to 0, and all bits with a value of 0 change to 1.
The bit manipulation @functions, @BITSB, @BITRB, @BITTB, @BITSH, @BITRH, and @BITTH, let you set or reset bits, or test the value of a bit in a number.
The concatenation @functions, @CATB, @CATH, @CATNB, and @CATNH, link numbers together in a chain. For example, the concatenation of the two binary numbers 11111 and 10101 is 1111110101.
The argument Bits defines the word size (in number of binary bits) for both input and output. The default for Bits is the number of bits in the largest input number. If you perform an operation with two numbers of different word size, the smaller number is left-padded with zeros. If Bits is less than the length of an input value, then the excess most significant digits are truncated. For Boolean @functions using hexadecimal numbers, note that each hexadecimal digit equals 4 bits.
The argument BitIn represents either the binary bit inserted during a shift, the carry bit for addition, or the borrow bit for subtraction; it can be either 0 (the default) or 1.
Tip
To add or subtract negative numbers with the Boolean @functions, use two's complement notation. Two's complement notation is a code used to give meaning to a binary string. The sign bit of two's complement notation is the leftmost bit of the word. A number is positive if the sign bit is 0 and negative if it is 1. Negative integers are converted to two's complement by inverting each bit (that is, changing each 1 to a 0 and each 0 to a 1), and then adding 1.
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.
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.
Tip
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.
Entering Cash Flow @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.
For details on using cash flow @function arguments, choose one of the following topics:
Using the Discrate Argument
Using the Filter, Start, and End Arguments
Using the Flows Argument
Using the Odd and Periods Arguments
Using the Simp and PathDep Arguments
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.
Tip
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.
Tip
In @FUTV, Odd specifies the length of the last period.
Bond @Function Arguments
Argument Description
Settle Settlement date for the trade
Maturity Redemption date for the bond
Coupon Annual coupon rate expressed as a decimal
Issue Issue date, that is, the date at which the bond is first offered for sale and begins accruing interest
FirstCpn Date on which the first coupon period ends; if the first coupon period is longer or shorter than the other periods, the first coupon payment date is explicitly specified at issue; the size of the first coupon payment is linearly prorated in accordance with the length of the first coupon period
Redemption Redemption value per par of 100
Freq Frequency of coupon payments in the number of payments per year; the default is 2 (semiannual)
Calendar Calendar to observe; the default is 0 (30/360)
Yield Internal rate of return expressed as a decimal
Price Quoted price of the bond assuming a par value of 100 and not including accrued interest
