Financial spreadsheet functions
|
•
|
|
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.
Annuity Financial spreadsheet functions |  |
|
•
|
|
@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
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.
Bill Financial spreadsheet functions | |
|
•
|
|
@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.
|
Bond Financial spreadsheet functions | |
|
•
|
|
@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.
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.
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.
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.
|
CD Financial spreadsheet functions | |
|
•
|
|
@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.
|
Depreciation Financial spreadsheet functions | |
|
•
|
|
@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.
|
Stock Financial spreadsheet functions | |
|
•
|
|
@DOLLARDE Converts a fractional price into dollars.
|
|
•
|
|
@DOLLARFR Converts a dollar price into a fractional price.
|
|
•
|
|
@FEETBL Returns fee calculations for stock transactions.
|
|
•
|
|
@STKOPT Returns the time value and earnings value of a stock option.
|