Mastering Business Analysis with Crystal Reports 9 (Wordware Applications Library)
|
Financial functions perform common business calculations, such as determining the payment for a loan or the future value or net present value of an investment.
| Function | Description | Arguments |
|---|---|---|
| ACCRINT (issueDate, firstInterestDate, settlementDate, rate, parValue, frequency) ACCRINT (issueDate, firstInterestDate, settlementDate, rate, parValue, frequency, basis) | Returns the accrued interest for a security that pays periodic interest. | issueDate is the security's issue date. firstInterestDate is the security's first interest date. settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. rate is the security's annual coupon rate. parValue is the security's par value. If you omit parValue, ACCRINT uses $1,000. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use, for example: 0 American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| ACCRINTM (issueDate, maturityDate, rate, parValue) ACCRINTM (issueDate, maturityDate, rate, parValue, basis) | Returns the accrued interest for a security that pays interest at maturity. | issueDate is the security's issue date. maturityDate is the security's maturity date. The maturity date is the date when the security expires. rate is the security's annual coupon rate. parValue is the security's par value. If you omit parValue, ACCRINTM uses $1,000. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| AmorDEGRC (cost, purchaseDate, firstPeriodEndDate, salvage, period, rate) AmorDEGRC (cost, purchaseDate, firstPeriodEndDate, salvage, period, rate, basis) | Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account. The function is similar to AmorLINC except that a depreciation coefficient is applied in the calculation depending on the life of the assets. | cost is the cost of the asset. purchaseDate is the date of the purchase of the asset. firstPeriodEndDate is the date of the end of the first period. salvage is the salvage value at the end of the life of the asset. period is the period. rate is the rate of depreciation. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| AmorLINC (cost, purchaseDate, firstPeriodEndDate, salvage, period, rate) AmorLINC (cost, purchaseDate, firstPeriodEndDate, salvage, period, rate, basis) | Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account. | cost is the cost of the asset. purchaseDate is the date of the purchase of the asset. firstPeriodEndDate is the date of the end of the first period. salvage is the salvage value at the end of the life of the asset. period is the period. rate is the rate of depreciation. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| CoupDayBS (settlementDate, maturityDate, frequency) CoupDayBS (settlementDate, maturityDate, frequency, basis) | Returns the number of days from the beginning of the coupon period to the settlement date. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| CoupDays (settlementDate, maturityDate, frequency) CoupDays (settlementDate, maturityDate, frequency, basis) | Returns the number of days in the coupon period that contains the settlement date. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| CoupDaysNC (settlementDate, maturityDate, frequency) CoupDaysNC (settlementDate, maturityDate, frequency, basis) | Returns the number of days from the settlement date to the next coupon date. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| CoupNCD (settlementDate, maturityDate, frequency) | Returns a number that represents the next coupon date after the settlement date. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. |
| CoupNum (settlementDate, maturityDate, frequency) | Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. |
| CoupPCD (settlementDate, maturityDate, frequency) | Returns a number that represents the previous coupon date before the settlement date. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. |
| CumIPMT (rate, nPeriods, presentValue, startPeriod, endPeriod, type) | Returns the cumulative interest paid on a loan between startPeriod and endPeriod. | rate is the interest rate. nPeriods is the total number of payment periods. presentValue is the present value. startPeriod is the first period in the calculation. Payment periods are numbered beginning with 1. endPeriod is the last period in the calculation. type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period |
| CumPrinc (rate, nPeriods, presentValue, startPeriod, endPeriod, type) | Returns the cumulative principal paid on a loan between startPeriod and endPeriod. | rate is the interest rate. nPeriods is the total number of payment periods. presentValue is the present value. startPeriod is the first period in the calculation. Payment periods are numbered beginning with 1. endPeriod is the last period in the calculation. type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period |
| DB (cost, salvage, life, period) DB (cost, salvage, life, period, month) | Returns the depreciation of an asset for a specified period using the fixed-declining balance method. | cost is the initial cost of the asset. salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset). life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset). period is the period for which you want to calculate the depreciation. period must use the same units as life. month is the number of months in the first year. If month is omitted, it is assumed to be 12. |
| DDB (cost, salvage, life, period) DDB (cost, salvage, life, period, factor) | Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. | cost is the initial cost of the asset. salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset). life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset). period is the period for which you want to calculate the depreciation. period must use the same units as life. factor is the rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method). |
| Days360 (startDate, endDate) Days360 (startDate, endDate, method) | Returns the number of days between two dates using a calendar that has 30 days in a month and 360 days in a year, which is commonly used for financial accounting. If the end date is earlier than the start date, a negative answer is returned. | startDate is a Date or DateTime for the start of an interval of time. endDate is a Date or DateTime for the end of an interval of time. method is an optional Boolean value specifying the type of basis to use. FALSE is the default and implies the American 30/360-day basis, while TRUE implies the European 30/360-day basis. |
| DISC (settlementDate, maturityDate, price, redemptionPrice) DISC (settlementDate, maturityDate, price, redemptionPrice, basis) | Returns the discount rate for a security. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. price is the security's price per $100 face value. redemptionPrice is the security's redemption value per $100 face value. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| DollarDE (fractionNumber, fractionBase) | Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number. Use DollarDE to convert fractional dollar numbers, such as securities prices, to decimal numbers. | fractionNumber is a number expressed as a fraction. fractionBase is the integer to use in the denominator of the fraction. |
| DollarFR (decimalNumber, fractionBase) | Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction. Use DollarFR to convert decimal numbers to fractional dollar numbers, such as securities prices. | decimalNumber is a decimal number. fractionBase is the integer to use in the denominator of a fraction. |
| Duration (settlementDate, maturityDate, couponRate, yield, frequency) Duration (settlementDate, maturityDate, couponRate, yield, frequency, basis) | Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of the cash flows and is used as a measure of a bond price's response to changes in yield. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. couponRate is the security's annual coupon rate. yield is the security's annual yield. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| Effect (rate, nCompoundingPeriods) | Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. | rate is the nominal interest rate. nCompoundingPeriods is the number of compounding periods per year. |
| FV (rate, nPeriods, payment) FV (rate, nPeriods, payment, presentValue) FV (rate, nPeriods, payment, presentValue, type) | Returns the future value of an investment based on periodic, constant payments and a constant interest rate. | rate is the interest rate per period. nPeriods is the total number of payment periods in an annuity. payment is the payment made each period; it cannot change over the life of the annuity. Typically, payment contains principal and interest but no other fees or taxes. If payment is omitted, you must include the paymentValue argument. presentValue is the present value or the lump-sum amount that a series of future payments is worth right now. If paymentValue is omitted, it is assumed to be 0, and you must include the payment argument. type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period |
| FVSchedule (value, rates) | Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSchedule to calculate future value of an investment with a variable or adjustable rate. | value is the present value. rates is an array of interest rates to apply. |
| IntRate (settlementDate, maturityDate, price, redemptionValue) IntRate (settlementDate, maturityDate, price, redemptionValue, basis) | Returns the interest rate for a fully invested security. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. price is the amount invested in the security. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| IPmt (rate, period, nPeriods, presentValue) IPmt (rate, period, nPeriods, presentValue, futureValue) IPmt (rate, period, nPeriods, presentValue, futureValue, type) | Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. | rate is the interest rate per period. period is the period for which you want to find the interest and must be in the range 1 to nPeropds. nPeriods is the total number of payment periods in an annuity. presentValue is the present value, or the lump-sum amount that a series of future payments is worth right now. futureValue is the future value, or a cash balance you want to attain after the last payment is made. If futureValue is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period |
| IRR (values) IRR (values, guess) | Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods. | values is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return. guess is a number that you guess is close to the result of IRR. |
| ISPMT (rate, period, nPeriods, presentValue) | Calculates the interest paid during a specific period of an investment. | rate is the interest rate for the investment. period is the period for which you want to find the interest and must be between 1 and nPeriods. nPeriods is the total number of payment periods for the investment. presentValue is the present value of the investment. For a loan, presentValue is the loan amount. |
| MDuration (settlementDate, maturityDate, couponRate, yield, frequency) MDuration (settlementDate, maturityDate, couponRate, yield, frequency, basis) | Returns the modified duration for a security with an assumed par value of $100. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. couponRate is the security's annual coupon rate. yield is the security's annual yield. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| MIRR (values, financeRate, reinvestRate) | Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash. | values is an array or a reference to cells that contain numbers. These numbers represent a series of payments (negative values) and income (positive values) occurring at regular periods. financeRate is the interest rate you pay on the money used in the cash flows. reinvestRate is the interest rate you receive on the cash flows as you reinvest them. |
| Nominal (rate, nCompoundingPeriods) | Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year. | rate is the effective interest rate. nCompoundingPeriods is the number of compounding periods per year. |
| NPer (rate, payment, presentValue) NPer (rate, payment, presentValue, futureValue) NPer (rate, payment, presentValue, futureValue, type) | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. | rate is the interest rate per period. payment is the payment made each period; it cannot change over the life of the annuity. Typically, payment contains principal and interest but no other fees or taxes. presentValue is the present value or the lump-sum amount that a series of future payments is worth right now. futureValue is the future value or a cash balance you want to attain after the last payment is made. If futureValue is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period |
| NPV (rate, values) | Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). | rate is the rate of discount over the length of one period. values are 1 to 29 arguments representing the payments and income. |
| OddFPrice (settlementDate, maturityDate, issueDate, firstCouponDate, rate, yield, redemptionValue, frequency) OddFPrice (settlementDate, maturityDate, issueDate, firstCouponDate, rate, yield, redemptionValue, frequency, basis) | Returns the price per $100 face value of a security having an odd (short or long) first period. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. issueDate is the security's issue date. firstCouponDate is the security's first coupon date. rate is the security's interest rate. yield is a non-negative number specifying the security's yield. redemptionValue is the security's redemption value per $100 face value. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| OddFYield (settlementDate, maturityDate, issueDate, firstCouponDate, rate, price, redemptionValue, frequency) OddFYield (settlementDate, maturityDate, issueDate, firstCouponDate, rate, price, redemptionValue, frequency, basis) | Returns the yield of a security that has an odd (short or long) first period. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. issueDate is the security's issue date. firstCouponDate is the security's first coupon date. rate is the security's interest rate. price is a non-negative number or currency specifying the security's purchase price per $100 of face value. redemptionValue is the security's redemption value per $100 face value. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| OddLPrice (settlementDate, maturityDate, lastInterestDate, rate, yield, redemptionValue, frequency) OddLPrice (settlementDate, maturityDate, lastInterestDate, rate, yield, redemptionValue, frequency, basis) | Returns the price per $100 face value of a security having an odd (short or long) last coupon period. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. lastInterestDate is the security's last coupon date. rate is the security's interest rate. yield is the security's annual yield. redemptionValue is the security's redemption value per $100 face value. frequency is a number specifying the number of coupons per year. The supported values are 1 (annual payments), 2 (semiannual payments), and 4 (quarterly). basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| OddLYield (settlementDate, maturityDate, lastInterestDate, rate, price, redemptionValue, frequency) OddLYield (settlementDate, maturityDate, lastInterestDate, rate, price, redemptionValue, frequency, basis) | Returns the yield of a security that has an odd (short or long) last period. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. lastInterestDate is the security's last coupon date. rate is the security's interest rate. price is the security's price. redemptionValue is the security's redemption value per $100 face value. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| Pmt (rate, nPeriods, presentValue) Pmt (rate, nPeriods, presentValue, futureValue) Pmt (rate, nPeriods, presentValue, futureValue, type) | Calculates the payment for a loan based on constant payments and a constant interest rate. | rate is the interest rate for the loan. nPeriods is the total number of payments for the loan. presentValue is the present value or the total amount that a series of future payments is worth now; also known as the principal. futureValue is the future value or a cash balance you want to attain after the last payment is made. If futureValue is omitted, it is assumed to be 0; that is, the future value of a loan is 0. type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period |
| PPmt (rate, period, nPeriods, presentValue) PPmt (rate, period, nPeriods, presentValue, futureValue) PPmt (rate, period, nPeriods, presentValue, futureValue, type) | Calculates the payment for a loan based on constant payments and a constant interest rate. | rate is the interest rate for the loan. period is a number that specifies the payment period in the range 1 through nPeriods. nPeriods is the total number of payments for the loan. presentValue is the present value, or the total amount that a series of future payments is worth now; also known as the principal. futureValue is the future value, or a cash balance you want to attain after the last payment is made. If futureValue is omitted, it is assumed to be 0; that is, the future value of a loan is 0. type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period |
| Price (settlementDate, maturityDate, couponRate, yield, redemptionValue, frequency) Price (settlementDate, maturityDate, couponRate, yield, redemptionValue, frequency, basis) | Returns the price per $100 face value of a security that pays periodic interest. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. couponRate is the security's annual coupon rate. yield is the security's annual yield. redemptionValue is the security's redemption value per $100 face value. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| PriceDisc (settlementDate, maturityDate, discountRate, redemptionValue) PriceDisc (settlementDate, maturityDate, discountRate, redemptionValue, basis) | Returns the price per $100 face value of a discounted security. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. discountRate is the security's discount rate. redemptionValue is the security's redemption value per $100 face value. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| PriceMat (settlementDate, maturityDate, issueDate, interestRate, yield) PriceMat (settlementDate, maturityDate, issueDate, interestRate, yield, basis) | Returns the price per $100 face value of a security that pays interest at maturity. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. issueDate is the security's issue date, expressed as a serial date number. interestRate is the security's interest rate at date of issue. yield is the security's annual yield. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| PV (rate, nPeriods, payment) PV (rate, nPeriods, payment, futureValue) PV (rate, nPeriods, payment, futureValue, type) | Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender. | rate is the interest rate per period. nPeriods is the total number of payment periods in an annuity. payment is the payment made each period and cannot change over the life of the annuity. futureValue is the future value, or a cash balance you want to attain after the last payment is made. type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period |
| Rate (nPeriods, payment, presentValue) Rate (nPeriods, payment, presentValue, futureValue) Rate (nPeriods, payment, presentValue, futureValue, type) Rate (nPeriods, payment, presentValue, futureValue, type, guess) | Returns the interest rate per period of an annuity. Rate is calculated by iteration and can have zero or more solutions. | nPeriods is the total number of payment periods in an annuity. payment is the payment made each period and cannot change over the life of the annuity. presentValue is the present value or the total amount that a series of future payments is worth now. futureValue is the future value or a cash balance you want to attain after the last payment is made. type is the timing of the payment. For example: 0 - Payment at the end of the period 1 - Payment at the beginning of the period guess is your guess for what the rate will be. |
| Received (settlementDate, maturityDate, investment, discountRate) Received (settlementDate, maturityDate, investment, discountRate, basis) | Returns the amount received at maturity for a fully invested security. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. investment is the amount invested in the security. discountRate is the security's discount rate. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| SLN (cost, salvage, life) | Returns the straight-line depreciation of an asset for one period. | cost is the initial cost of the asset. salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset). life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset). |
| SYD (cost, salvage, life, period) | Returns the sum-of-years' digits depreciation of an asset for a specified period. | cost is the initial cost of the asset. salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset). life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset). period is the period and must use the same units as life. |
| TBillEq (settlementDate, maturityDate, discountRate) | Returns the bond-equivalent yield for a Treasury bill. | settlementDate is the Treasury bill's settlement date. The security settlement date is the date after the issue date when the Treasury bill is traded to the buyer. maturityDate is the Treasury bill's maturity date. The maturity date is the date when the Treasury bill expires. discountRate is the Treasury bill's discount rate. |
| TBillPrice (settlementDate, maturityDate, discountRate) | Returns the price per $100 face value for a Treasury bill. | settlementDate is the Treasury bill's settlement date. The security settlement date is the date after the issue date when the Treasury bill is traded to the buyer. maturityDate is the Treasury bill's maturity date. The maturity date is the date when the Treasury bill expires. discountRate is the Treasury bill's discount rate. |
| TBillYield (settlementDate, maturityDate, price) | Returns the yield for a Treasury bill. | settlementDate is the Treasury bill's settlement date. The security settlement date is the date after the issue date when the Treasury bill is traded to the buyer. maturityDate is the Treasury bill's maturity date. The maturity date is the date when the Treasury bill expires. price is the Treasury bill's price per $100 face value. |
| VDB (cost, salvage, lifetime, startPeriod, endPeriod) VDB (cost, salvage, lifetime, startPeriod, endPeriod, depreciationFactor) VDB (cost, salvage, lifetime, startPeriod, endPeriod, noSwitch) VDB (cost, salvage, lifetime, startPeriod, endPeriod, depreciationFactor, noSwitch) | Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance. | cost is the initial cost of the asset. salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset). lifetime is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset). startPeriod is the starting period for which you want to calculate the depreciation. startPeriod must use the same units as life. endPeriod is the ending period for which you want to calculate the depreciation. endPeriod must use the same units as life. depreciationFactor is the rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method). noSwitch is a logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation. |
| XIRR (values, dates) XIRR (values, dates, rateGuess) | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function. | values is a series of cash flows that corresponds to a schedule of payments in dates. dates is a schedule of payment dates that corresponds to the cash flow payments. rateGuess is a number that you guess is close to the result of XIRR. |
| XNPV (rate, values, dates) | Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function. | rate is the discount rate to apply to the cash flows. values is a series of cash flows that corresponds to a schedule of payments in dates. dates is a schedule of payment dates that corresponds to the cash flow payments. |
| YearFrac (startDate, endDate) YearFrac (startDate, endDate, basis) | Returns the fraction of a year spanned by the interval of time between the two dates. | startDate is the starting period for which you want to calculate the fraction. endDate is the ending period for which you want to calculate the fraction. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| Yield (settlementDate, maturityDate, couponRate, price, redemptionValue, frequency) Yield (settlementDate, maturityDate, couponRate, price, redemptionValue, frequency, basis) | Returns the yield on a security that pays periodic interest. Use Yield to calculate bond yield. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. couponRate is the security's annual coupon rate. price is the security's price per $100 face value. redemptionValue is the security's redemption value per $100 face value. frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency =4. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| YieldDisc (settlementDate, maturityDate, price, redemptionValue) YieldDisc (settlementDate, maturityDate, price, redemptionValue, basis) | Returns the annual yield for a discounted security. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. price is the security's price per $100 face value. redemptionValue is the security's redemption value per $100 face value. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
| YieldMat (settlementDate, maturityDate, issueDate, interestRate, price) YieldMat (settlementDate, maturityDate, issueDate, interestRate, price, basis) | Returns the annual yield of a security that pays interest at maturity. | settlementDate is the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. maturityDate is the security's maturity date. The maturity date is the date when the security expires. issueDate is the security's issue date, expressed as a serial date number. interestRate is the security's interest rate at date of issue. price is a non-negative number or currency specifying the security's purchase price per $100 of face value. basis is the type of day count basis to use. For example: 0 - American 30/360 (default) 1 - actual/actual 2 - actual/360 3 - actual/365 4 - European 30/360 |
|