Financial Formulas in Windows Forms Calculation Engine (Calculate)

4 Dec 202424 minutes to read

PMT

The PMT function calculates the payment for a loan based on constant payments and constant interest rate.

Syntax:

PMT(rate, nper, pv, [fv], [type])

Where:

  • rate is the interest rate for the loan.

  • nper is the total number of payments for the loan.

  • pv is the present value, or the total amount that a series of future payments is worth now.

  • fv is the future value, or a cash balance you want to attain after the last payment is made. If Fv is omitted, it is assumed to be zero.

  • type is the number 0 or 1 and indicates when payments are due.

    Set type equal to If payments are due
    0 or omitted At the end of the period
    1 At the beginning of the period.

PV

Calculates the present value of an investment (i.e. the total amount that a series of future payments is worth now).

Syntax:

PV(rate, nper, pmt, [fv], [type])

where:

  • rate is the interest rate per period.

  • nper is the total number of payment periods in an annuity.

  • pmt is the payment made each period and cannot change over the life of the annuity.

  • fv is the future value, or a cash balance you want to attain after the last payment is made.

  • type is the number 0 or 1 and indicates when payments are due.

DB

Calculates the depreciation of an asset for a specified period, using the fixed-declining balance method.

Syntax:

DB(cost, salvage, life, period, [month])

where:

  • cost denotes the initial cost of the asset.

  • salvage denotes the value at the end of the depreciation (sometimes called the salvage value of the asset).

  • life denotes the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset).

  • period denotes the period for which you want to calculate the depreciation. Period must use the same units as life.

  • month denotes the number of months in the first year. If month is omitted, it is assumed to be 12.

DDB

Calculates the depreciation of an asset for a specified period, using the fixed-declining balance method.

Syntax:

DDB(cost, salvage, life, period, [month])

where:

  • cost denotes the initial cost of the asset.

  • salvage denotes the value at the end of the depreciation (sometimes called the salvage value of the asset).

  • life denotes the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset).

  • period denotes the period for which you want to calculate the depreciation. Period must use the same units as life.

  • month denotes the number of months in the first year. If month is omitted, it is assumed to be 12.

FV

Calculates the future value of an investment with periodic constant payments and a constant interest rate.

Syntax:

FV(rate,nper,pmt,[pv],[type])

where:

  • rate denotes the interest rate per period.

  • nper denotes the total number of payment periods in an annuity.

  • pmt denotes the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

IRR

Calculates the internal rate of return for a series of cash flows.

Syntax:

IRR(values, [guess])

where:

  • values denotes an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.

    • Values must contain at least one positive value and one negative value to calculate the internal rate of return.

    • IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.

    • If an array or reference argument contains text, logical values, or empty cells, those values are ignored.

  • guess denotes a number that you guess is close to the result of IRR.

    • Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can’t find a result that works after 20 tries, the #NUM! error value is returned.

    • In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).

    • If IRR gives the `#NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.

ISPMT

Returns the interest paid during a specified period of an investment.

Syntax:

ISPMT(rate, per, nper, pv)

where:

  • rate denotes the interest rate for the investment.

  • per denotes the period for which you want to find the interest, and must be between 1 and Nper.

  • nper denotes the total number of payment periods for the investment.

  • pv denotes the present value of the investment. For a loan, Pv is the loan amount.

MIRR

Calculates the internal rate of return for a series of periodic cash flows, considering the cost of the investment and the interest on the reinvestment of cash.

Syntax:

MIRR(values, finance_rate, reinvest_rate)

where:

  • values denotes 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.

    • Values must contain at least one positive value and one negative value to calculate the modified internal rate of return. Otherwise, MIRR returns the #DIV/0! error value.

    • If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

  • finance_rate denotes the interest rate you pay on the money used in the cash flows.

  • reinvest_rate denotes the interest rate you receive on the cash flows as you reinvest them.

NPER

Returns the number of periods for an investment with periodic constant payments and a constant interest rate.

Syntax:

NPER(rate,pmt,pv,[fv],[type])

where:

  • rate denotes the interest rate per period.

  • pmt denotes the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.

  • pv denotes the present value, or the lump-sum amount that a series of future payments is worth right now.

  • fv denotes the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

  • type denotes the number 0 or 1 and indicates when payments are due.

NPV

Calculates the net present value of an investment, based on a supplied discount rate, and a series of future payments and income.

Syntax:

NPV(rate,value1,[value2],…)

where:

  • rate denotes the rate of discount over the length of one period.

  • value1, value2, … Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income.

    • value1, value2, … must be equally spaced in time and occur at the end of each period.

    • NPV uses the order of value1, value2, … to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.

    • Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored.

    • If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.

PPMT

Calculates the payment on the principal for a given investment, with periodic constant payments and a constant interest rate

Syntax:

PPMT(rate, per, nper, pv, [fv], [type])

where:

  • rate denotes the interest rate per period.

  • per denotes to specifies the period and must be in the range 1 to nper.

  • nper denotes the total number of payment periods in an annuity.

  • pv denotes the present value — the total amount that a series of future payments is worth now.

  • fv denotes the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

  • type denotes the number 0 or 1 and indicates when payments are due.

RATE

Calculates the interest rate required to pay off a specified amount of a loan, or reach a target amount on an investment over a given period

Syntax:

RATE(nper, pmt, pv, [fv], [type], [guess])

where:

  • nper denotes the total number of payment periods in an annuity.

  • pmt denotes the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.

  • pv denotes the present value — the total amount that a series of future payments is worth now.

  • fv denotes the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument.

  • type denotes the number 0 or 1 and indicates when payments are due.

  • guess in an optional. If guess is omitted, it is assumed to be 0.1 (10 percent).

SLN

Returns the straight-line depreciation of an asset for one period

Syntax:

SLN(cost, salvage, life)

where:

  • cost denotes the initial cost of the asset.

  • salvage denotes the value at the end of the depreciation (sometimes called the salvage value of the asset).

  • life denotes the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).

SYD

Returns the sum-of-years digits depreciation of an asset for a specified period

Syntax:

SYD(cost, salvage, life, per)

where:

  • cost denotes the initial cost of the asset.

  • salvage denotes the value at the end of the depreciation (sometimes called the salvage value of the asset).

  • life denotes the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).

  • per denotes the period and must use the same units as life.

VDB

Returns the depreciation of an asset for a specified period, (including partial periods), using the double-declining balance method or another user-specified method.

Syntax:

VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])

where:

  • cost denotes the initial cost of the asset.

  • salvage denotes the value at the end of the depreciation (sometimes called the salvage value of the asset). This value can be 0.

  • life denotes the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).

  • start_period denotes the starting period for which you want to calculate the depreciation. Start_period must use the same units as life.

  • end_period denotes. the ending period for which you want to calculate the depreciation. End_period must use the same units as life.

  • factor denotes the rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method). Change factor if you do not want to use the double-declining balance method. For a description of the double-declining balance method, see DDB.

  • no_switch denotes a logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation.

    • If no_switch is TRUE, Microsoft Excel does not switch to straight-line depreciation even when the depreciation is greater than the declining balance calculation.

    • If no_switch is FALSE or omitted, Excel switches to straight-line depreciation when depreciation is greater than the declining balance calculation.

DOLLARDE

Converts a dollar price expressed as a fraction, into a dollar price expressed as a decimal.

Syntax:

DOLLARDE(fractional_dollar, fraction)

where:

  • fractional_dollar denotes a number expressed as an integer part and a fraction part, separated by a decimal symbol.

  • fraction denotes the integer to use in the denominator of the fraction.

Remarks:

  • If fraction is not an integer, it is truncated.

  • If fraction is less than 0, DOLLARDE returns the #NUM! error value.

  • If fraction is greater than or equal to 0 and less than 1, DOLLARDE returns the #DIV/0! error value.

DOLLARFR

Converts a dollar price expressed as a decimal, into a dollar price expressed as a fraction

Syntax:

DOLLARFR(decimal_dollar, fraction)

where:

  • decimal_dollar denotes a decimal number.

  • fraction denotes the integer to use in the denominator of a fraction.

Remarks:

  • If fraction is not an integer, it is truncated.

  • If fraction is less than 0, DOLLARFR returns the #NUM! error value.

  • If fraction is 0, DOLLARFR returns the #DIV/0! error value.

DURATION

Calculates the Macaulay duration of a security with an assumed par value of $100

Syntax:

DURATION(settlement, maturity, coupon, yld, frequency, [basis])

where:

  • settlement denotes the security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.

  • maturity denotes the security’s maturity date. The maturity date is the date when the security expires.

  • coupon denotes the security’s annual coupon rate.

  • yld denotes the security’s annual yield.

  • frequency denotes the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.

  • basis denotes the type of day count basis to use.

RRI

Calculates an equivalent interest rate for the growth of an investment

Syntax:

RRI(nper, pv, fv)

where:

  • nper is the number of periods for the investment.

  • pv is the present value of the investment.

  • fv is the future value of the investment.

FVSCHEDULE

Calculates the future value of an initial principal, after applying a series of compound interest rates.

Syntax:

FVSCHEDULE(principal, schedule)

where:

  • principal denotes the present value.

  • schedule denotes an array of interest rates to apply.

DISC

Calculates the discount rate for a security.

Syntax:

DISC(settlement, maturity, pr, redemption, [basis])

where:

  • settlement denotes the security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.

  • maturity denotes the security’s maturity date. The maturity date is the date when the security expires.

  • pr denotes the security’s price per $100 face value.

  • redemption denotes the security’s redemption value per $100 face value.

  • basis denotes the type of day count basis to use.

INTRATE

Calculates the interest rate for a fully invested security.

Syntax:

INTRATE(settlement, maturity, investment, redemption, [basis])

where:

  • settlement denotes the security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.

  • maturity denotes the security’s maturity date. The maturity date is the date when the security expires.

  • investment denotes the amount invested in the security.

  • redemption denotes the amount to be received at maturity.

  • basis denotes the type of day count basis to use.

CUMIPMT

Calculates the cumulative interest paid between two specified periods.

Syntax:

CUMIPMT(rate, nper, pv, start_period, end_period, type)

where:

  • rate denotes the interest rate.

  • nper denotes the total number of periods.

  • pv denotes the present value.

  • start_period denotes the first period in the calculation. Payment periods are numbered beginning with 1.

  • end_period denotes the last period in the calculation.

  • type denotes the timing of the payment.

CUMPRINC

Calculates the cumulative principal paid on a loan, between two specified periods.

Syntax:

CUMPRINC(rate, nper, pv, start_period, end_period, type)

where:

  • rate denotes the interest rate.

  • nper denotes the total number of payment periods.

  • pv denotes the present value.

  • start_period denotes the first period in the calculation. Payment periods are numbered beginning with 1.

  • end_period denotes the last period in the calculation.

  • type denotes the timing of the payment.

RECEIVED

Calculates the amount received at maturity for a fully invested Security

Syntax:

RECEIVED(settlement, maturity, investment, discount, [basis])

where:

  • settlement denotes the security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.

  • maturity denotes the security’s maturity date. The maturity date is the date when the security expires.

  • investment denotes the amount invested in the security.

  • discount denotes the security’s discount rate.

  • basis is the value that is type of day count basis to use.

ACCRINTM

The ACCRINTM returns the accrued interest for a security that pays interest at maturity.

Syntax:

ACCRINTM(issue, settlement, rate, par, [basis])

where:

  • issue denotes the security’s issue date.

  • settlement denotes the security’s maturity date.

  • rate denotes the security’s annual coupon rate.

  • par denotes the security’s par value. If you omit par, ACCRINTM uses $1,000.

  • basis denotes the type of day count basis to use.

IPMT

The IMPT calculates the interest payment, during a specific period of a loan or investment that is paid in constant periodic payments, with a constant interest rate.

Syntax:

IPMT(rate, per, nper, pv, [fv], [type])

where:

  • Rate: The interest rate per period..

  • Per : The period for which the interest payment is to be calculated and must be in the range 1 to nper.

  • Nper : The total number of payment periods(the loan or investment) in an annuity.

  • Pv : The present value, of the loan or investment.

  • Fv (optional):The future value of the loan or investment. If fv is omitted, it is assumed to be 0.

  • Type(optional) :The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.The number is 0 the payment is calculate end of the period.The number is 1 the payment calculate start of the period.

XIRR

The XIRR returns the internal rate of return for a series of cash flows that is not necessarily periodic.

Syntax:

XIRR(values, dates, [guess])

where:

  • Values : Array or reference to cells that represent the series of cash flows.

  • Dates : Array or reference to cells that represent the dates that correspond to the series of cash flows.

  • Guess (optional) : A number that you guess is close to the result of XIRR.Default value is .1 (10%)

Remarks:

  • Numbers in dates are truncated to integers.

  • XIRR expects at least one positive cash flow and one negative cash flow; otherwise, It returns the #NUM! error message.

  • If number in dates is not a valid date, It returns the #VALUE! error message.

  • If number in dates precedes the starting date, it returns the #NUM! error message.

  • If values and dates(arguments) contain a different number of values, it returns the #NUM! error message.

ACCRINT

The ACCRINT returns the accrued interest for a security that pays periodic interest on a periodic basis.

Syntax:

ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])

where:

  • issue : The issue date of the security.

  • First_interest :The first interest date of the security.

  • Settlement :The settlement date of the security.

  • Rate : Annual coupon rate for the security.

  • Par :The par value of the security .If you omit the par value, par is set to $1,000.

  • Frequency :The frequency of the interest payments for the security., frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.

  • Basis (optional) : The type of day count basis to use.

Remarks:

  • Issue, first_interest, settlement, frequency, and basis are truncated to integers.

  • If issue, first_interest, or settlement is not a valid date format, it returns the #VALUE! error message.

  • The argument (rate) ≤ 0 or the argument (par) ≤ 0, it returns the #NUM! error message.

  • The argument (frequency) is any number other than 1, 2, or 4, it returns the #NUM! error message.

  • The argument (basis) is not equal to 0,1,2,3 or 4 it returns the #NUM! error message.

  • The argument(issue) ≥ settlement, it returns the #NUM! error message.

EFFECT

The EFFECT calculate the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.

Syntax:

EFFECT(nominal_rate, npery)

where:

  • Nominal_rate : The nominal interest rate.it must be a numeric value(0 to 1).

  • Npery : The number of compounding periods per year.it must be a positive number.

Remarks:

  • Npery is truncated to an integer.

  • If one or both argument is non-numeric, it returns the #VALUE! error message.

  • The argument (nominal_rate) ≤ 0 or the argument (npery) < 1, it returns the #NUM! error message.

INTRATE

The INTRATE calculates the interest rate for a fully invested security.

Syntax:

INTRATE(settlement, maturity, investment, redemption, [basis])

where:

  • Settlement : The settlement date of the security.

  • Maturity : The maturity date of the security.. The maturity date is the date when the security expires.

  • Investment : The initial amount invested in the security.

  • Redemption :The amount to be received at maturity.

  • Basis : The type of specifies the day count basis to used in the calculation.

NOMINAL

The NOMINAL function returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year.

Syntax:

NOMINAL(effect_rate, npery)

Where:

  • effect_rate: The effective interest rate.

  • npery: The number of compounding periods per year.

Remarks:

  • npery is truncated to an integer.

  • If either argument is non-numeric, NOMINAL returns the #VALUE! error.

  • If effect_rate is less than or equal to 0 or if npery is less than 1, NOMINAL returns the #NUM! error value.

  • The NOMINAL function is related to the EFFECT function, which calculates the effective annual interest rate based on the nominal rate and the number of compounding periods.

MDURATION

The MDURATION function returns the modified Macaulay duration for a security with an assumed par value of $100.

Syntax:

MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

Where:

  • settlement: The security’s settlement date.

  • maturity: The security’s maturity date.

  • coupon: The security’s annual coupon rate.

  • yld: The security’s annual yield.

  • frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).

  • basis (Optional): The day-count convention to use (default is 0: US (NASD) 30/360):

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • If settlement or maturity is a not valid date, MDURATION returns the #VALUE! error.

  • If yld or coupon less than 0, or if frequency is not 1, 2, or 4, MDURATION returns the #NUM! error.

  • If basis is outside the range 0-4, it returns the #NUM! error.

  • If settlement is greater than or equal to maturity, MDURATION returns the #NUM! error.

  • MDURATION adjusts the Macaulay duration to account for changes in interest rates.

PDURATION

The PDURATION function returns the number of periods required by an investment to reach a specified value.

Syntax:

PDURATION(rate, pv, fv)

Where:

  • rate: The interest rate per period.

  • pv: The present value of the investment.

  • fv: The desired future value of the investment.

Remarks:

  • If rate is less than or equal to 0, or if pv or fv is less than or equal to 0, PDURATION returns the #NUM! error.

  • If any argument is non-numeric, PDURATION returns the #VALUE! error.

  • PDURATION calculates how many periods are needed for an investment to grow from its present value to the desired future value, based on the specified interest rate.

COUPDAYS

The COUPDAYS function returns the number of days in the coupon period that contains the settlement date.

Syntax:

COUPDAYS(settlement, maturity, frequency, [basis])

Where:

  • settlement: The security’s settlement date.

  • maturity: The security’s maturity date.

  • frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).

  • basis (Optional): The day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • If settlement or maturity is not a valid date, COUPDAYS returns #VALUE!.

  • If frequency is any number other than 1, 2, or 4, COUPDAYS returns #NUM!.

  • If basis is outside the range 0-4, COUPDAYS returns #NUM!.

  • If settlement is greater than or equal to maturity, COUPDAYS returns #NUM!.

COUPDAYBS

The COUPDAYBS function returns the number of days from the beginning of a coupon period until its settlement date.

Syntax:

COUPDAYBS(settlement, maturity, frequency, [basis])

Where:

  • settlement: The security’s settlement date.

  • maturity: The security’s maturity date.

  • frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).

  • basis (Optional): The day count basis to use (default is 0: US (NASD) 30/360):

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • If settlement or maturity is not a valid date, COUPDAYBS returns #VALUE!.

  • If frequency is any number other than 1, 2, or 4, COUPDAYBS returns #NUM!.

  • If basis is outside the range 0-4, it returns #NUM!.

  • If settlement is greater than or equal to maturity, it returns #NUM!.

COUPDAYSNC

The function COUPDAYSNC returns the number of days from the settlement date to the next coupon date.

Syntax:

COUPDAYSNC(settlement, maturity, frequency, [basis])

Where:

  • settlement: The security’s settlement date.

  • maturity: The security’s maturity date.

  • frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).

  • basis (Optional): The day count basis to use (default is 0: US (NASD) 30/360):

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • If settlement or maturity is not a valid date, COUPDAYSNC returns #VALUE!.

  • If frequency is any number other than 1, 2, or 4, COUPDAYSNC returns #NUM!.

  • If basis is outside the range 0-4, it returns #NUM!.

  • If settlement is greater than or equal to maturity, it returns #NUM!.

COUPPCD

The function COUPPCD returns a number that represents the previous coupon date before the settlement date.

Syntax:

COUPPCD(settlement, maturity, frequency, [basis])

Where:

  • settlement: The security’s settlement date.

  • maturity: The security’s maturity date.

  • frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).

  • basis (Optional): The day count basis to use (default is 0: US (NASD) 30/360):

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • If settlement or maturity is not a valid date, COUPPCD returns #VALUE!.

  • If frequency is any number other than 1, 2, or 4, COUPPCD returns #NUM!.

  • If basis is outside the range 0-4, it returns #NUM!.

  • If settlement is greater than or equal to maturity, it returns #NUM!.

COUPNCD

The function COUPNCD returns a number that represents the next coupon date after the settlement date.

Syntax:

COUPNCD(settlement, maturity, frequency, [basis])

Where:

  • settlement: The security’s settlement date.

  • maturity: The security’s maturity date.

  • frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).

  • basis (Optional): The day count basis to use (default is 0: US (NASD) 30/360):

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • If settlement or maturity is not a valid date, COUPNCD returns #VALUE!.

  • If frequency is any number other than 1, 2, or 4, COUPNCD returns #NUM!.

  • If basis is outside the range 0-4, it returns #NUM!.

  • If settlement is greater than or equal to maturity, it returns #NUM!.

COUPNUM

The function COUPNUM returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.

Syntax:

COUPNUM(settlement, maturity, frequency, [basis])

Where:

  • settlement: The security’s settlement date.

  • maturity: The security’s maturity date.

  • frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).

  • basis (Optional): The day count basis to use (default is 0: US (NASD) 30/360):

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • If settlement or maturity is not a valid date, COUPNUM returns #VALUE!.

  • If frequency is any number other than 1, 2, or 4, COUPNUM returns #NUM!.

  • If basis is outside the range 0-4, COUPNUM returns #NUM!.

  • If settlement is greater than or equal to maturity, COUPNUM returns #NUM!.

  • The argument is non-numeric, it returns the #VALUE! error message.

AMORLINC

The AMORLINC function returns the depreciation for each accounting period, specifically designed for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is considered.

Syntax

AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis])

Where:

  • cost: The cost of the asset.

  • date_purchased: The date when the asset was purchased.

  • first_period: The date marking the end of the first accounting period.

  • salvage: The salvage value of the asset at the end of its useful life.

  • period: The specific period for which the depreciation is to be calculated.

  • rate: The depreciation rate for the asset.

  • basis (Optional): The day count basis to use:

    • 0 or omitted: US (NASD) 30/360 (default)

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • If any arguments are invalid, the function returns an error:

    • #VALUE!: If the arguments are not valid numbers or dates.

    • #NUM!: If the arguments are outside valid ranges or unsupported.

TBILLEQ

The function TBILLEQ returns the bond-equivalent yield for a Treasury bill.

Syntax

TBILLEQ(settlement, maturity, discount)

Where:

  • settlement: 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.

  • maturity: The Treasury bill’s maturity date. The maturity date is the date when the Treasury bill expires.

  • discount: The Treasury bill’s discount rate.

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • Settlement and maturity are truncated to integers.

  • If settlement or maturity is not a valid date, TBILLEQ returns the #VALUE! error value.

  • If discount is less than or equal to 0, TBILLEQ returns the #NUM! error value.

  • If settlement is greater than maturity, or if maturity is more than one year after settlement, TBILLEQ returns the #NUM! error value.

PRICEDISC

The function PRICEDISC returns the price per $100 face value of a discounted security.

Syntax

PRICEDISC(settlement, maturity, discount, redemption, [basis])

Where:

  • settlement: The security’s settlement date. It is the date after the issue date when the security is traded to the buyer.

  • maturity: The security’s maturity date. It is the date when the security expires.

  • discount: The security’s discount rate.

  • redemption: The security’s redemption value per $100 face value.

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • Settlement, maturity, and basis are truncated to integers.

  • If settlement or maturity is not a valid date, PRICEDISC returns the #VALUE! error value.

  • If discount or redemption is less than or equal to 0, PRICEDISC returns the #NUM! error value.

  • If basis is less than 0 or greater than 4, PRICEDISC returns the #NUM! error value.

  • If settlement is greater than or equal to maturity, PRICEDISC returns the #NUM! error value.

YIELD

The YIELD function returns the yield on a security that pays periodic interest.

Syntax

YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

Where:

  • settlement: The security’s settlement date. It is the date after the issue date when the security is traded to the buyer.

  • maturity: The security’s maturity date. It is the date when the security expires.

  • rate: The security’s annual coupon rate.

  • pr: The security’s price per $100 face value.

  • redemption: The security’s redemption value per $100 face value.

  • frequency: The number of coupon payments per year (1 for annual, 2 for semiannual, 4 for quarterly).

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • Settlement, maturity, frequency, and basis are truncated to integers.

  • If settlement or maturity is not a valid date, YIELD returns the #VALUE! error value.

  • If rate is less than 0, YIELD returns the #NUM! error value.

  • If pr or redemption is less than or equal to 0, YIELD returns the #NUM! error value.

  • If frequency is any number other than 1, 2, or 4, YIELD returns the #NUM! error value.

  • If basis is less than 0 or greater than 4, YIELD returns the #NUM! error value.

  • If settlement is less than or equal to maturity, YIELD returns the #NUM! error value.

  • If there is more than one coupon period until redemption, YIELD is calculated using the Newton method, based on the formula used for the function PRICE. The yield is changed until the estimated price given the yield is close to price.

YIELDMAT

The function YIELDMAT returns the annual yield of a security that pays interest at maturity.

Syntax

YIELDMAT(settlement, maturity, issue, rate, pr, [basis])

Where:

  • settlement: The security’s settlement date. It is the date after the issue date when the security is traded to the buyer.

  • maturity: The security’s maturity date. It is the date when the security expires.

  • issue: The security’s issue date.

  • rate: The security’s interest rate at the date of issue.

  • Pr: The security’s price per $100 face value.

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • Settlement, maturity, issue, and basis are truncated to integers.

  • If settlement, maturity, or issue is not a valid date, YIELDMAT returns the #VALUE! error value.

  • If rate is less than 0 or if pr is less than or equal to 0, YIELDMAT returns the #NUM! error value.

  • If basis is less than 0 or greater than 4, YIELDMAT returns the #NUM! error value.

  • If settlement is greater than or equal to maturity, YIELDMAT returns the #NUM! error value.

YIELDDISC

The function YIELDDISC returns the annual yield for a discounted security.

Syntax

YIELDDISC(settlement, maturity, pr, redemption, [basis])

Where:

  • settlement: The security’s settlement date. It is the date after the issue date when the security is traded to the buyer.

  • maturity: The security’s maturity date. It is the date when the security expires.

  • pr: The security’s price per $100 face value.

  • redemption: The security’s redemption value per $100 face value.

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • Settlement, maturity, and basis are truncated to integers.

  • If settlement or maturity is not a valid date, YIELDDISC returns the #VALUE! error value.

  • If pr or redemption is less than or equal to 0, YIELDDISC returns the #NUM! error value.

  • If basis is less than 0 or greater than 4, YIELDDISC returns the #NUM! error value.

  • If settlement is greater than equal to maturity, YIELDDISC returns the #NUM! error value.

PRICEMAT

The function PRICEMAT returns the price per $100 face value of a security that pays interest at maturity.

Syntax

PRICEMAT(settlement, maturity, issue, rate, yld, [basis])

Where:

  • settlement: The security’s settlement date. It is the date after the issue date when the security is traded to the buyer.

  • maturity: The security’s maturity date. It is the date when the security expires.

  • issue: The security’s issue date.

  • rate: The security’s interest rate at the date of issue.

  • yld: The security’s annual yield.

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. (e.g., January 1, 1900, is represented as 1).

  • Settlement, maturity, issue, and basis are truncated to integers.

  • If settlement, maturity, or issue is not a valid date, PRICEMAT returns the #VALUE! error value.

  • If rate or yld is less than 0, PRICEMAT returns the #NUM! error value.

  • If basis is less than 0 or greater than 4, PRICEMAT returns the #NUM! error value.

  • If settlement is greater than equal to maturity, PRICEMAT returns the #NUM! error value.

AMORDEGRC

The AMORDEGRC function returns the depreciation for each accounting period under the French accounting system. If an asset is purchased in the middle of an accounting period, prorated depreciation is calculated. The function applies a depreciation coefficient based on the asset’s lifespan.

Syntax

AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])

Where:

  • cost: The cost of the asset.

  • date_purchased: The date the asset was purchased.

  • first_period: The date of the end of the first accounting period.

  • salvage: The salvage value of the asset at the end of its useful life.

  • period: The period for which the depreciation is to be calculated.

  • rate: The depreciation rate.

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as serial numbers. For example, January 1, 1900, is represented as 1.

  • Depreciation coefficients are applied based on the life of the asset:

    • Between 3 and 4 years: Coefficient = 1.5

    • Between 5 and 6 years: Coefficient = 2

    • More than 6 years: Coefficient = 2.5

  • The depreciation rate increases to 50% for the period preceding the last and to 100% for the final period.

  • AMORDEGRC returns #NUM! if the life of the asset is less than 1 year or falls into invalid ranges (e.g., between 0 and 1, 1 and 2, etc.).

  • Depreciation is calculated until the last period of the asset’s life or until the cumulative depreciation equals the cost minus the salvage value.

ODDFPRICE

The function ODDFPRICE returns the price per $100 face value of a security having an odd (short or long) first period.

Syntax

ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])

Where:

  • settlement: The security’s settlement date (the date the buyer purchases the security).

  • maturity: The security’s maturity date (the date the security expires).

  • issue: The security’s issue date.

  • first_coupon: The security’s first coupon date.

  • rate: The security’s annual interest rate.

  • yld: The security’s annual yield.

  • redemption: The redemption value per $100 face value.

  • frequency: The number of coupon payments per year (1 = annual, 2 = semiannual, 4 = quarterly).

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as sequential serial numbers (e.g., January 1, 1900, is serial number 1).

  • Settlement, maturity, issue, first_coupon, and basis are truncated to integers.

  • The function ODDFPRICE returns:

    • #VALUE! if any of the date arguments are invalid.

    • #NUM! if rate is less than 0, yld is less than 0, basis is less than 0, basis is greater than 4, or the condition maturity > first_coupon > settlement > issue is not satisfied.

  • The function ODDFPRICE considers odd short first coupons and odd long first coupons separately.

ODDFYIELD

The function ODDFYIELD returns the yield of a security that has an odd (short or long) first period.

Syntax

ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])

Where:

  • settlement: The security’s settlement date (the date the buyer purchases the security).

  • maturity: The security’s maturity date (the date the security expires).

  • issue: The security’s issue date.

  • first_coupon: The security’s first coupon date.

  • rate: The security’s annual interest rate.

  • pr: The security’s price.

  • redemption: The redemption value per $100 face value.

  • frequency: The number of coupon payments per year (1 = annual, 2 = semiannual, 4 = quarterly).

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as sequential serial numbers (e.g., January 1, 1900, is serial number 1).

  • Settlement, maturity, issue, first_coupon, and basis are truncated to integers.

  • The function ODDFYIELD returns:

    • #VALUE! - if any of the date arguments are invalid.

    • #NUM! - if rate is less than 0, pr is less than or equal to 0, basis is less than 0, or basis greater than 4.

    • #NUM!- if the condition maturity > first_coupon > settlement > issue is not satisfied.

  • An iterative technique named Newton method is used for ODDFPRICE. The Newton method iterates through 100 times to adjust the yield until the calculated price matches the provided price.

ODDLPRICE

The function ODDLPRICE returns the price per $100 face value of a security having an odd (short or long) last coupon period.

Syntax

ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis])

Where:

  • settlement: The security’s settlement date (the date the buyer purchases the security).

  • maturity: The security’s maturity date (the date the security expires).

  • last_interest: The security’s last coupon date.

  • rate: The security’s annual interest rate.

  • yld: The security’s annual yield.

  • redemption: The redemption value per $100 face value.

  • frequency: The number of coupon payments per year (1 = annual, 2 = semiannual, 4 = quarterly).

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks:

  • Dates are stored as sequential serial numbers (e.g., January 1, 1900, is serial number 1).

  • Settlement, maturity, last_interest, and basis are truncated to integers.

  • The function ODDLPRICE returns:

    • #VALUE! if any of the date arguments are invalid.

    • #NUM! if rate is less than 0, yld is less than 0, basis is less than 0, or basis greater than 4.

    • #NUM! if the condition maturity > settlement > last_interest is not satisfied.

  • The price of a security with an odd last coupon period depends on the calculation of accrued interest and present value of future cash flows.

ODDLYIELD

The function ODDLYIELD returns the yield of a security that has an odd (short or long) last period.

Syntax

ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis])

Where:

  • settlement: The security’s settlement date (the date the buyer purchases the security).

  • maturity: The security’s maturity date (the date the security expires).

  • last_interest: The security’s last coupon date.

  • rate: The security’s annual interest rate.

  • pr: The security’s price per $100 face value.

  • redemption: The redemption value per $100 face value.

  • frequency: The number of coupon payments per year (1 = annual, 2 = semiannual, 4 = quarterly).

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks

  • Dates are stored as sequential serial numbers (e.g., January 1, 1900, is serial number 1).

  • Settlement, maturity, last_interest, and basis are truncated to integers.

  • If settlement, maturity, or last_interest is not a valid date, ODDLYIELD returns #VALUE!.

  • If rate is less than 0 or pr is less than or equal to 0, ODDLYIELD returns #NUM!.

  • If basis is less than 0 or basis is greater than 4, ODDLYIELD returns #NUM!.

  • If maturity is less than or equal to settlement or settlement is less than or equal to last_interest, ODDLYIELD returns #NUM!.

PRICE

The PRICE function returns the price per $100 face value of a security that pays periodic interest.

Syntax

PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

Where:

  • settlement: The settlement date of the security, when it is traded to the buyer.

  • maturity: The maturity date of the security, when it expires.

  • rate: The annual coupon rate of the security.

  • yld: The annual yield of the security.

  • redemption: The redemption value per $100 face value.

  • frequency: The number of coupon payments per year (1 = annual, 2 = semiannual, 4 = quarterly).

  • basis (Optional): The type of day count basis to use:

    • 0 or omitted: US (NASD) 30/360

    • 1: Actual/actual

    • 2: Actual/360

    • 3: Actual/365

    • 4: European 30/360

Remarks

  • Dates are stored as sequential serial numbers (e.g., January 1, 1900, is serial number 1).

  • If settlement or maturity is not a valid date, PRICE returns the #VALUE! error.

  • If yld is less than 0, rate is less than 0, or redemption is less than or equal to 0, PRICE returns the #NUM! error.

  • If frequency is not 1, 2, or 4, PRICE returns the #NUM! error.

  • If basis is less than 0 or greater than 4, PRICE returns the #NUM! error.

  • If settlement is greater than or equal to maturity, PRICE returns the #NUM! error.

TBILLPRICE

The TBILLPRICE function returns the price per $100 face value for a Treasury bill.

Syntax

TBILLPRICE(settlement, maturity, discount)

Where:

  • settlement: The Treasury bill’s settlement date, when it is traded to the buyer.

  • maturity: The Treasury bill’s maturity date, when it expires.

  • discount: The Treasury bill’s discount rate.

Remarks

  • Dates are stored as sequential serial numbers (e.g., January 1, 1900, is serial number 1).

  • If settlement or maturity is not a valid date, the function returns the #VALUE! error.

  • If discount is less than or equal to 0, the function returns the #NUM! error.

  • If settlement > maturity or if maturity is more than one year after settlement, the function returns the #NUM! error.

TBILLYIELD

The TBILLYIELD function returns the yield for a Treasury bill.

Syntax

TBILLYIELD(settlement, maturity, pr)

Where:

  • settlement: The Treasury bill’s settlement date, when it is traded to the buyer.

  • maturity: The Treasury bill’s maturity date, when it expires.

  • pr: The Treasury bill’s price per $100 face value.

Remarks

  • If settlement or maturity is not a valid date, the function returns the #VALUE! error.

  • If pr is less than or equal to 0, the function returns the #NUM! error.

  • If settlement is greater than or equal to maturity or if maturity is more than one year after settlement, the function returns the #NUM! error.

XNPV

The XNPV function returns the net present value for a schedule of cash flows that is not necessarily periodic.

Syntax

XNPV(rate, values, dates)

Where:

  • rate: The discount rate to apply to the cash flows.

  • values: A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and typically represents a cost (negative value). Subsequent payments are discounted using a 365-day year.

  • dates: A schedule of payment dates corresponding to the cash flow payments. Dates must be in chronological order, with the first date representing the start of the schedule.

Remarks

  • Dates are stored as sequential serial numbers (e.g., January 1, 1900, is serial number 1).

  • If any argument is non-numeric, the function returns the #VALUE! error.

  • If values and dates have a different number of entries, the function returns the #NUM! error.

  • If any date in dates precedes the starting date, the function returns the #NUM! error.

  • The series in values must include at least one positive and one negative value.