Financial functions ODDFYIELD - Portal

Financial functions

ACCRINT Returns the accrued interest for a security that pays periodic interest ACCRINTM Returns the accrued interest for a security that pays interest at maturity AMORDEGRC Returns the depreciation for each accounting period AMORLINC Returns the depreciation for each accounting period 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 CUMIPMT Returns the cumulative interest paid between two periods CUMPRINC Returns the cumulative principal paid on a loan between two periods DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify DISC Returns the discount rate for a security DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction DURATION Returns the annual duration of a security with periodic interest payments EFFECT Returns the effective annual interest rate FV Returns the future value of an investment FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates INTRATE Returns the interest rate for a fully invested security IPMT Returns the interest payment for an investment for a given period IRR Returns the internal rate of return for a series of cash flows ISPMT Calculates the interest paid during a specific period of an investment. MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100 MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates NOMINAL Returns the annual nominal interest rate NPER Returns the number of periods for an investment NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate ODDFPRICE Returns the price per $100 face value of a security with an odd first period

Financial Functions in Excel

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 PMT Returns the periodic payment for an annuity PPMT Returns the payment on the principal for an investment for a given period PRICE Returns the price per $100 face value of a security that pays periodic interest PRICEDISC Returns the price per $100 face value of a discounted security PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity PV Returns the present value of an investment RATE Returns the interest rate per period of an annuity RECEIVED Returns the amount received at maturity for a fully invested security SLN Returns the straight-line depreciation of an asset for one period SYD Returns the sum-of-years' digits depreciation of an asset for a specified period 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 VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic YIELD Returns the yield on a security that pays periodic interest YIELDDISC Returns the annual yield for a discounted security. For example, a Treasury bill YIELDMAT Returns the annual yield of a security that pays interest at maturity

ACCRINT

Returns the accrued interest for a security that pays periodic interest.

Syntax ACCRINT(issue, first_interest, settlement, rate, par, frequency, basis) ? ? Issue is the security's issue date. Dates may be entered as

text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")). ? ? First_interest is the security's first interest date. ? ? Settlement 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.

- 1 -

? ? Par is the security's par value. If you omit par, 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.

Basis

Day count basis

0 or omitted US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Remarks

? ? Microsoft Excel stores dates as sequential serial numbers

so that it can perform calculations on them. Excel stores

January 1, 1900, as serial number 1 if your workbook uses

the 1900 date system. If your workbook uses the 1904

date system, Excel stores January 1, 1904, as serial

number 0 (January 2, 1904, is serial number 1). For

example, in the 1900 date system, Excel stores January 1,

1998, as serial number 35796 because it is 35,795 days

after January 1, 1900. Issue, first_interest, settlement,

frequency, and basis are truncated to integers.

? ? If issue, first_interest, or settlement is not a valid date,

ACCRINT returns the #NUM! error value.

? ? If coupon = 0 or if par = 0, ACCRINT returns the #NUM!

error value.

? ? If frequency is any number other than 1, 2, or 4,

ACCRINT returns the #NUM! error value.

? ? If basis < 0 or if basis > 4, ACCRINT returns the #NUM!

error value.

? ? If issue = settlement, ACCRINT returns the #NUM! error

value.

? ? ACCRINT is calculated as follows:

ACCRINT ?

Par ?

Rate ? Frequency

?NC Ai

i? 1 NLi

where:

Ai = number of accrued days for the ith quasi-coupon period within odd period.

NC = number of quasi-coupon periods that fit in odd

period. If this number contains a fraction, raise it to

the next whole number.

NLi = normal length in days of the ith quasi-coupon period within odd period.

Example

A treasury bond has the following terms:

February 28, 1998, issue date

August 31, 1998, first interest date

May 1, 1998, settlement date

10.0 percent coupon

$1,000 par value

Frequency is semiannual

30/360 basis

The accrued interest (in the 1900 date system) is:

ACCRINT("2/28/1998", "8/31/1998", "5/1/1998", 0.1,

1000, 2, 0) equals 16.94444

ACCRINTM

Financial Functions in Excel

Returns the accrued interest for a security that pays interest at maturity.

Syntax

ACCRINTM(issue, maturity, rate, par, basis)

? ? Issue is the security's issue date. Dates may be entered as

text strings within quotation marks (for example,

"1/30/1998" or "1998/01/30"), as serial numbers (for

example, 35825, which represents January 30, 1998, if

you're using the 1900 date system), or as results of other

formulas or functions (for example,

DATEVALUE("1/30/1998")).

? ? Maturity is the security's maturity date.

? ? Rate is the security's annual coupon rate.

? ? Par is the security's par value. If you omit par,

ACCRINTM uses $1,000.

? ? Basis is the type of day count basis to use.

Basis

Day count basis

0 or omitted US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Remarks ? ? Microsoft Excel stores dates as sequential serial numbers

so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. ? ? Issue, settlement, and basis are truncated to integers. ? ? If issue or settlement is not a valid date, ACCRINTM returns the #NUM! error value. ? ? If rate = 0 or if par = 0, ACCRINTM returns the #NUM! error value. ? ? If basis < 0 or if basis > 4, ACCRINTM returns the #NUM! error value. ? ? If issue = settlement, ACCRINTM returns the #NUM! error value. ? ? ACCRINTM is calculated as follows: ACCRINTM ? Par ? Rate ? A

D where: A = Number of accrued days counted according to a monthly basis. For interest at maturity items, the number of days from the issue date to the maturity date is used. D = Annual Year Basis. Example A note has the following terms: April 1, 1998, issue date June 15, 1998, maturity date 10.0 percent coupon $1,000 par value Actual/365 basis The accrued interest (in the 1900 date system) is:

- 2 -

ACCRINTM("4/1/1998", "6/15/1998", 0.1, 1000, 3) equals 20.54795

AMORDEGRC

Returns the depreciation for each accounting period. This function is provide 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.

AMORLINC

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.

COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date.

Syntax

COUPDAYBS(settlement, maturity, frequency, basis)

? ? Settlement 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. Dates may be entered as

text strings within quotation marks (for example,

"1/30/1998" or "1998/01/30"), as serial numbers (for

example, 35825, which represents January 30, 1998, if

you're using the 1900 date system), or as results of other

formulas or functions (for example,

DATEVALUE("1/30/1998")).

? ? Maturity 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.

Basis

Day count basis

0 or omitted US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Remarks ? ? Microsoft Excel stores dates as sequential serial numbers

so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. ? ? The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 1996, and is purchased by a buyer six months later. The issue date would be January

Financial Functions in Excel

1, 1996, the settlement date would be July 1, 1996, and the maturity date would be January 1, 2026, 30 years after the January 1, 1996, issue date. ? ? All arguments are truncated to integers. ? ? If settlement or maturity is not a valid date, COUPDAYBS returns the #NUM! error value. ? ? If frequency is any number other than 1, 2, or 4, COUPDAYBS returns the #NUM! error value. ? ? If basis < 0 or if basis > 4, COUPDAYBS returns the #NUM! error value. ? ? If settlement = maturity, COUPDAYBS returns the #NUM! error value. Example A bond has the following terms: January 25, 1998, settlement date November 15, 1999, maturity date Semiannual coupon Actual/actual basis The number of days from the beginning of the coupon period to the settlement date (in the 1900 date system) is: COUPDAYBS("1/25/1998","11/15/1999",2,1) equals 71

COUPDAYS

Returns the number of days in the coupon period that contains the settlement date.

Syntax

COUPDAYS(settlement, maturity, frequency, basis)

? ? Settlement 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. Dates may be entered as

text strings within quotation marks (for example,

"1/30/1998" or "1998/01/30"), as serial numbers (for

example, 35825, which represents January 30, 1998, if

you're using the 1900 date system), or as results of other

formulas or functions (for example,

DATEVALUE("1/30/1998")).

? ? Maturity 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.

Basis

Day count basis

0 or omitted US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Remarks ? ? Microsoft Excel stores dates as sequential serial numbers

so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900.

- 3 -

? ? The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 1996, and is purchased by a buyer six months later. The issue date would be January 1, 1996, the settlement date would be July 1, 1996, and the maturity date is January 1, 2026, 30 years after the January 1, 1996 issue date.

? ? All arguments are truncated to integers. ? ? If settlement or maturity is not a valid date, COUPDAYS

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

COUPDAYS returns the #NUM! error value. ? ? If basis < 0 or if basis > 4, COUPDAYS returns the

#NUM! error value. ? ? If settlement = maturity, COUPDAYS returns the #NUM!

error value.

Example A bond has the following terms: January 25, 1998, settlement date November 15, 1999, maturity date Semiannual coupon Actual/actual basis The number of days in the coupon period that contains the settlement date (in the 1900 date system) is: COUPDAYS("1/25/1998","11/15/1999",2,1) equals 181

COUPDAYSNC Returns the number of days from the settlement date to the next coupon date.

Syntax COUPDAYSNC(settlement, maturity, frequency, basis)

? ? Settlement 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. Dates may be entered as

text strings within quotation marks (for example,

"1/30/1998" or "1998/01/30"), as serial numbers (for

example, 35825, which represents January 30, 1998, if

you're using the 1900 date system), or as results of other

formulas or functions (for example,

DATEVALUE("1/30/1998")).

? ? Maturity 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.

Basis

Day count basis

0 or omitted US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Remarks ? ? Microsoft Excel stores dates as sequential serial numbers

so that it can perform calculations on them. Excel stores

Financial Functions in Excel

January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. ? ? The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 1996, and is purchased by a buyer six months later. The issue date would be January 1, 1996, the settlement date would be July 1, 1996, and the maturity date would be January 1, 2026, which is 30 years after the January 1, 1996, issue date. ? ? All arguments are truncated to integers. ? ? If settlement or maturity is not a valid date, COUPDAYSNC returns the #NUM! error value. ? ? If frequency is any number other than 1, 2, or 4, COUPDAYSNC returns the #NUM! error value. ? ? If basis < 0 or if basis > 4, COUPDAYSNC returns the #NUM! error value. ? ? If settlement = maturity, COUPDAYSNC returns the #NUM! error value.

Example A bond has the following terms: January 25, 1998, settlement date November 15, 1999, maturity date Semiannual coupon Actual/actual basis The number of days from the settlement date to the next coupon date (in the 1900 date system) is: COUPDAYSNC("1/25/1998","11/15/1999",2,1) equals 110

COUPNCD Returns a number that represents the next coupon date after the settlement date. To view the number as a date, click Cells on the Format menu, click Date in the Category box, and then click a date format in the Type box.

Syntax COUPNCD(settlement, maturity, frequency, basis) ? ? Settlement 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. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")). ? ? Maturity 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.

- 4 -

Basis

Day count basis

0 or omitted US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Remarks ? ? Microsoft Excel stores dates as sequential serial numbers

so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. ? ? The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 1996, and is purchased by a buyer six months later. The issue date would be January 1, 1996, the settlement date would be July 1, 1996, and the maturity date would be January 1, 2026, which is 30 years after the January 1, 1996, issue date. ? ? All arguments are truncated to integers. ? ? If settlement or maturity is not a valid date, COUPNCD returns the #NUM! error value. ? ? If frequency is any number other than 1, 2, or 4, COUPNCD returns the #NUM! error value. ? ? If basis < 0 or if basis > 4, COUPNCD returns the #NUM! error value. ? ? If settlement = maturity, COUPNCD returns the #NUM! error value.

Example A bond has the following terms: January 25, 1998, settlement date November 15, 1999, maturity date Semiannual coupon Actual/actual basis The next coupon date after the settlement date (in the 1900 date system) is:

COUPNCD("1/25/1998","11/15/1999",2,1) equals 35930 or May 15, 1998

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) ? ? Settlement 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. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other

Financial Functions in Excel

formulas or functions (for example,

DATEVALUE("1/30/1998")).

? ? Maturity 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.

Basis

Day count basis

0 or omitted US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Remarks ? ? Microsoft Excel stores dates as sequential serial numbers

so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. ? ? The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 1996, and is purchased by a buyer six months later. The issue date would be January 1, 1996, the settlement date would be July 1, 1996, and the maturity date would be January 1, 2026, which is 30 years after the January 1, 1996, issue date. ? ? All arguments are truncated to integers. ? ? If settlement or maturity is not a valid date, COUPNUM returns the #NUM! error value. ? ? If frequency is any number other than 1, 2, or 4, COUPNUM returns the #NUM! error value. ? ? If basis < 0 or if basis > 4, COUPNUM returns the #NUM! error value. ? ? If settlement = maturity, COUPNUM returns the #NUM! error value.

Example A bond has the following terms: January 25, 1998, settlement date November 15, 1999, maturity date Semiannual coupon Actual/actual basis The number of coupon payments (in the 1900 date system) is: COUPNUM("1/25/1998","11/15/1999",2,1) equals 4

COUPPCD Returns a number that represents the previous coupon date before the settlement date. To view the number as a date, click Cells on the Format menu, click Date in the Category box, and then click a date format in the Type box. For more information about serial numbers that represent dates, see the Remarks section.

- 5 -

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download