Excel > Function reference > Math and trigonometry

A bibliography of financial, statistical, and engineering methods

The following books provide detailed information on financial, statistical, and engineering methods.

Abramowitz, Milton, and Irene A. Stegun. Handbook of Mathematical Functions with Formulas, Graphs, and Mathematical Tables. 10th ed. New York, NY: Dover Publications, 1974.

Antkies, Candace Mary. Standard Securities Calculation Methods: Fixed Income Securities Formulas for Price, Yield and Accrued Interest: Volume 1. New York, NY: Securities Industry Association.

Antkies, Candace Mary. Standard Securities Calculation Methods, Fixed Income Securities Formulas for Analytic Measures: Volume 2. New York, NY: Securities Industry Association.

Devore, Jay L. Probability and Statistics for Engineering and the Sciences. 5th ed. Pacific Grove, CA: Duxbury Press, 1998.

Fabozzi, Frank J. The Handbook of Fixed-Income Securities. 6th ed. New York, NY: Mcgraw-Hill Trade, 2000.

Hewlett-Packard, HP-12C Solutions Handbook. Palo Alto, CA: Hewlett-Packard, 2001.

McCall, Robert B. Fundamental Statistics for the Behavioral Sciences. 7th ed. Pacific Grove, CA: Brooks Cole, 1998.

Monks, Joseph G. Operations Management: Theory and Problems. 3rd ed. New York, NY: Macmillan/McGraw-Hill, 1988.

Press, W. H., B. P. Flannery, S. A. Teukolsky, and W. T. Vetterling. Numerical Recipes in C: The Art of Scientific Computing. 2nd ed. New York, NY: Cambridge University Press, 1993.

Sokal, Robert R., and F. James Rohlf. Biometry: The Principles and Practice of Statistics in Biological Research. 3rd ed. New York, NY: W. H. Freeman, 1994.

Stigum, Marcia, and John Mann. Money Market Calculations: Yields, Break-Evens, & Arbitrage. 2nd ed. Homewood, IL: Irwin Professional, 1991.

See Also

• List of worksheet functions (by category)

Excel > Function reference > Financial

Financial functions

Click one of the links in the following list to see detailed help about the function.

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

Excel > Function reference > Financial

ACCRINT function

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

Syntax

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

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Issue   is the security's issue date.

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.

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.

Calc_method  Calc_method is a logical value that specifies the way to calculate the total accrued interest when the date of settlement is later than the date of first_interest. A value of TRUE (1) returns the total accrued interest from issue to settlement. A value of FALSE (0) returns the accrued interest from first_interest to settlement. If you do not enter the argument, it defaults to TRUE.

Remarks

• Microsoft Office Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Office Excel for the Macintosh uses a different date system as its default.
• 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 #VALUE! error value.
• If rate ≤ 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:

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

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• ACCRINTM function
• DATE function
• Financial functions

Excel > Function reference > Financial

ACCRINTM function

Excel 2007

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

Syntax

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

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Issue   is the security's issue date.

Settlement   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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• Issue, settlement, and basis are truncated to integers.
• If issue or settlement is not a valid date, ACCRINTM returns the #VALUE! 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:

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

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• ACCRINT function
• DATE function
• Financial functions

Excel > Function reference > Financial

AMORDEGRC function

Excel 2007

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.

Syntax

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

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Cost   is the cost of the asset.

Date_purchased   is the date of the purchase of the asset.

First_period   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 year basis to be used.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• This function will return the depreciation until the last period of the life of the assets or until the cumulated value of depreciation is greater than the cost of the assets minus the salvage value.
• The depreciation coefficients are:

• The depreciation rate will grow to 50 percent for the period preceding the last period and will grow to 100 percent for the last period.
• If the life of assets is between 0 (zero) and 1, 1 and 2, 2 and 3, or 4 and 5, the #NUM! error value is returned.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AMORLINC function
• DAYS360 function
• Financial functions

Excel > Function reference > Financial

AMORLINC function

Excel 2007

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.

Syntax

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

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Cost   is the cost of the asset.

Date_purchased   is the date of the purchase of the asset.

First_period   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 year basis to be used.

Remark

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AMORDEGRC function
• DAYS360 function
• Financial functions

Excel > Function reference > Financial

COUPDAYBS function

Excel 2007

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

Syntax

COUPDAYBS(settlement,maturity,frequency,basis)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, 30 years after the January 1, 2008, issue date.
• All arguments are truncated to integers.
• If settlement or maturity is not a valid date, COUPDAYBS returns the #VALUE! 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

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COUPDAYS function
• COUPDAYSNC function
• COUPNCD function
• COUPNUM function
• COUPPCD function
• DATE function
• Financial functions

Excel > Function reference > Financial

COUPDAYS function

Excel 2007

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

Syntax

COUPDAYS(settlement,maturity,frequency,basis)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date is January 1, 2038, 30 years after the January 1, 2008 issue date.
• All arguments are truncated to integers.
• If settlement or maturity is not a valid date, COUPDAYS returns the #VALUE! 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

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COUPDAYBS function
• COUPDAYSNC function
• COUPNCD function
• COUPNUM function
• COUPPCD function
• DATE function
• Financial functions

Excel > Function reference > Financial

COUPDAYSNC function

Excel 2007

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

Syntax

COUPDAYSNC(settlement,maturity,frequency,basis)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• All arguments are truncated to integers.
• If settlement or maturity is not a valid date, COUPDAYSNC returns the #VALUE! 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

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COUPDAYBS function
• COUPDAYS function
• COUPNCD function
• COUPNUM function
• COUPPCD function
• DATE function
• Financial functions

Excel > Function reference > Financial

COUPNCD function

Excel 2007

Returns a number that represents the next coupon date after the settlement date.

Syntax

COUPNCD(settlement,maturity,frequency,basis)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• All arguments are truncated to integers.
• If settlement or maturity is not a valid date, COUPNCD returns the #VALUE! 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

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a date, select the cell, and then on the Sheet tab, in the Number group, click the arrow next to Number Format, and click Short Date or Long Date.

See Also

• COUPDAYBS function
• COUPDAYS function
• COUPDAYSNC function
• COUPNUM function
• COUPPCD function
• DATE function
• Financial functions

Excel > Function reference > Financial

COUPNUM function

Excel 2007

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)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• All arguments are truncated to integers.
• If settlement or maturity is not a valid date, COUPNUM returns the #VALUE! 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

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COUPDAYBS function
• COUPDAYS function
• COUPDAYSNC function
• COUPNCD function
• COUPPCD function
• DATE function
• Financial functions

Excel > Function reference > Financial

COUPPCD function

Excel 2007

Returns a number that represents the previous coupon date before the settlement date.

Syntax

COUPPCD(settlement,maturity,frequency,basis)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• All arguments are truncated to integers.
• If settlement or maturity is not a valid date, COUPPCD returns the #VALUE! error value.
• If frequency is any number other than 1, 2, or 4, COUPPCD returns the #NUM! error value.
• If basis < 0 or if basis > 4, COUPPCD returns the #NUM! error value.
• If settlement ≥ maturity, COUPPCD returns the #NUM! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a date, select the cell, and then on the Sheet tab, in the Number group, click the arrow next to Number Format, and click Short Date or Long Date.

See Also

• COUPDAYBS function
• COUPDAYS function
• COUPDAYSNC function
• COUPNCD function
• COUPNUM function
• DATE function
• Financial functions

Excel > Function reference > Financial

CUMIPMT function

Excel 2007

Returns the cumulative interest paid on a loan between start_period and end_period.

Syntax

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

Rate   is the interest rate.

Nper   is the total number of payment periods.

Pv   is the present value.

Start_period   is the first period in the calculation. Payment periods are numbered beginning with 1.

End_period   is the last period in the calculation.

Type   is the timing of the payment.

Remarks

• Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 10 percent, use 10%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 10% for rate and 4 for nper.
• Nper, start_period, end_period, and type are truncated to integers.
• If rate ≤ 0, nper ≤ 0, or pv ≤ 0, CUMIPMT returns the #NUM! error value.
• If start_period < 1, end_period < 1, or start_period > end_period, CUMIPMT returns the #NUM! error value.
• If type is any number other than 0 or 1, CUMIPMT returns the #NUM! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.

See Also

• CUMPRINC function
• Financial functions

Excel > Function reference > Financial

CUMPRINC function

Excel 2007

Returns the cumulative principal paid on a loan between start_period and end_period.

Syntax

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

Rate   is the interest rate.

Nper   is the total number of payment periods.

Pv   is the present value.

Start_period   is the first period in the calculation. Payment periods are numbered beginning with 1.

End_period   is the last period in the calculation.

Type   is the timing of the payment.

Remarks

• Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
• Nper, start_period, end_period, and type are truncated to integers.
• If rate ≤ 0, nper ≤ 0, or pv ≤ 0, CUMPRINC returns the #NUM! error value.
• If start_period < 1, end_period < 1, or start_period > end_period, CUMPRINC returns the #NUM! error value.
• If type is any number other than 0 or 1, CUMPRINC returns the #NUM! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.

See Also

• CUMIPMT function
• Financial functions

Excel > Function reference > Financial

DB function

Excel 2007

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

Syntax

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

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.

Remarks

• The fixed-declining balance method computes depreciation at a fixed rate. DB uses the following formulas to calculate depreciation for a period:

(cost - total depreciation from prior periods) * rate

where:

rate = 1 - ((salvage / cost) ^ (1 / life)), rounded to three decimal places

• Depreciation for the first and last periods is a special case. For the first period, DB uses this formula:

cost * rate * month / 12

• For the last period, DB uses this formula:

((cost - total depreciation from prior periods) * rate * (12 - month)) / 12

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DDB function
• Financial functions
• SLN function
• SYD function
• VDB function

Excel > Function reference > Financial

DDB function

Excel 2007

Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify.

Syntax

DDB(cost,salvage,life,period,factor)

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). This value can be 0.

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).

Important  All five arguments must be positive numbers.

Remarks

• The double-declining balance method computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods. DDB uses the following formula to calculate depreciation for a period:
• Min( (cost - total depreciation from prior periods) * (factor/life), (cost - salvage -  total depreciation from prior periods) )
• Change factor if you do not want to use the double-declining balance method.
• Use the VDB function if you want to switch to the straight-line depreciation method when depreciation is greater than the declining balance calculation.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    The results are rounded to two decimal places.

See Also

• DB function
• Financial functions
• SLN function
• SYD function
• VDB function

Excel > Function reference > Financial

DISC function

Excel 2007

Returns the discount rate for a security.

Syntax

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

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

Maturity   is the security's maturity date. The maturity date is the date when the security expires.

Pr   is the security's price per $100 face value.

Redemption   is the security's redemption value per $100 face value.

Basis   is the type of day count basis to use.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, 30 years after the January 1, 2008, issue date.
• Settlement, maturity, and basis are truncated to integers.
• If settlement or maturity is not a valid serial date number, DISC returns the #VALUE! error value.
• If pr ≤ 0 or if redemption ≤ 0, DISC returns the #NUM! error value.
• If basis < 0 or if basis > 4, DISC returns the #NUM! error value.
• If settlement ≥ maturity, DISC returns the #NUM! error value.
• DISC is calculated as follows:

where:

B = number of days in a year, depending on the year basis.

DSM = number of days between settlement and maturity.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a percentage, select the cell, and then on the Sheet tab, in the Number group, click Percent Style .

See Also

• DATE function
• Financial functions
• PRICEDISC function
• YIELDDISC function

Excel > Function reference > Financial

DOLLARDE function

Excel 2007

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.

Syntax

DOLLARDE(fractional_dollar,fraction)

Fractional_dollar   is a number expressed as a fraction.

Fraction   is 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 0, DOLLARDE returns the #DIV/0! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DOLLAR function
• DOLLARFR function
• Financial functions

Excel > Function reference > Financial

DOLLARFR function

Excel 2007

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.

Syntax

DOLLARFR(decimal_dollar,fraction)

Decimal_dollar   is a decimal number.

Fraction   is 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.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DOLLAR function
• DOLLARDE function
• Financial functions

Excel > Function reference > Financial

DURATION function

Excel 2007

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.

Syntax

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

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

Maturity   is the security's maturity date. The maturity date is the date when the security expires.

Coupon   is the security's annual coupon rate.

Yld   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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• Settlement, maturity, frequency, and basis are truncated to integers.
• If settlement or maturity is not a valid date, DURATION returns the #VALUE! error value.
• If coupon < 0 or if yld < 0, DURATION returns the #NUM! error value.
• If frequency is any number other than 1, 2, or 4, DURATION returns the #NUM! error value.
• If basis < 0 or if basis > 4, DURATION returns the #NUM! error value.
• If settlement ≥ maturity, DURATION returns the #NUM! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DATE function
• Financial functions
• MDURATION function

Excel > Function reference > Financial

EFFECT function

Excel 2007

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

Syntax

EFFECT(nominal_rate,npery)

Nominal_rate   is the nominal interest rate.

Npery   is the number of compounding periods per year.

Remarks

• Npery is truncated to an integer.
• If either argument is nonnumeric, EFFECT returns the #VALUE! error value.
• If nominal_rate ≤ 0 or if npery < 1, EFFECT returns the #NUM! error value.
• EFFECT is calculated as follows:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Financial functions
• NOMINAL function

Excel > Function reference > Financial

FV function

Excel 2007

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax

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

For a more complete description of the arguments in FV and for more information on annuity functions, see PV.

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; 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.

Pv   is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.

Type   is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

Remarks

• Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
• For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note     The annual interest rate is divided by 12 because it is compounded monthly.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note     The annual interest rate is divided by 12 because it is compounded monthly.

Example 3

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note     The annual interest rate is divided by 12 because it is compounded monthly.

Example 4

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note     The annual interest rate is divided by 12 because it is compounded monthly.

See Also

• Financial functions
• FVSCHEDULE function
• IPMT function
• NPER function
• PMT function
• PPMT function
• PV function
• RATE function

Excel > Function reference > Financial

FVSCHEDULE function

Excel 2007

Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate.

Syntax

FVSCHEDULE(principal,schedule)

Principal   is the present value.

Schedule   is an array of interest rates to apply.

Remark

The values in schedule can be numbers or blank cells; any other value produces the #VALUE! error value for FVSCHEDULE. Blank cells are taken as zeros (no interest).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Financial functions
• FV function

Excel > Function reference > Financial

INTRATE function

Excel 2007

Returns the interest rate for a fully invested security.

Syntax

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

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

Maturity   is the security's maturity date. The maturity date is the date when the security expires.

Investment   is the amount invested in the security.

Redemption   is the amount to be received at maturity.

Basis   is the type of day count basis to use.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• Settlement, maturity, and basis are truncated to integers.
• If settlement or maturity is not a valid date, INTRATE returns the #VALUE! error value.
• If investment ≤ 0 or if redemption ≤ 0, INTRATE returns the #NUM! error value.
• If basis < 0 or if basis > 4, INTRATE returns the #NUM! error value.
• If settlement ≥ maturity, INTRATE returns the #NUM! error value.
• INTRATE is calculated as follows:

where:

B = number of days in a year, depending on the year basis.

DIM = number of days from settlement to maturity.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a percentage, select the cell, and then on the Sheet tab, in the Number group, click Percent Style .

See Also

• Financial functions
• RECEIVED function

Excel > Function reference > Financial

IPMT function

Excel 2007

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

Syntax

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

Rate   is the interest rate per period.

Per   is the period for which you want to find the interest and must be in the range 1 to nper.

Nper   is the total number of payment periods in an annuity.

Pv   is the present value, or the lump-sum amount that a series of future payments is worth right 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 0 (the future value of a loan, for example, is 0).

Type   is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

Remarks

• Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
• For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.

See Also

• ACCRINT function
• ACCRINTM function
• CUMIPMT function
• Financial functions
• INTRATE function
• PMT function
• PPMT function
• PV function
• RATE function

Excel > Function reference > Financial

IRR function

Excel 2007

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.

Syntax

IRR(values,guess)

Values   is 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   is 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.

Remarks

IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. The following formula demonstrates how NPV and IRR are related:

NPV(IRR(B1:B6),B1:B6)

equals 3.60E-08 [Within the accuracy of the IRR calculation, the value 3.60E-08 is effectively 0 (zero).]

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Financial functions
• MIRR function
• NPV function
• RATE function
• XIRR function
• XNPV function

Excel > Function reference > Financial

ISPMT function

Excel 2007

Calculates the interest paid during a specific period of an investment. This function is provided for compatibility with Lotus 1-2-3.

Syntax

ISPMT(rate,per,nper,pv)

Rate   is the interest rate for the investment.

Per   is the period for which you want to find the interest, and must be between 1 and nper.

Nper   is the total number of payment periods for the investment.

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

Remarks

• Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
• For all the arguments, the cash you pay out, such as deposits to savings or other withdrawals, is represented by negative numbers; the cash you receive, such as dividend checks and other deposits, is represented by positive numbers.
• For additional information about financial functions, see the PV function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Note The interest rate is divided by 12 to get a monthly rate. The number of years the money is paid out is multiplied by 12 to get the number of payments.

See Also

• Financial functions

Excel > Function reference > Financial

LOGEST function

Excel 2007

In regression analysis, calculates an exponential curve that fits your data and returns an array of values that describes the curve. Because this function returns an array of values, it must be entered as an array formula.

The equation for the curve is:

y = b*m^x or

y = (b*(m1^x1)*(m2^x2)*_) (if there are multiple x-values)

where the dependent y-value is a function of the independent x-values. The m-values are bases corresponding to each exponent x-value, and b is a constant value. Note that y, x, and m can be vectors. The array that LOGEST returns is {mn,mn-1,...,m1,b}.

Syntax

LOGEST(known_y's,known_x's,const,stats)

Known_y's   is the set of y-values you already know in the relationship y = b*m^x.

• If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable.
• If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable.

Known_x's   is an optional set of x-values that you may already know in the relationship y = b*m^x.

• The array known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a range of cells with a height of one row or a width of one column (which is also known as a vector).
• If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.

Const   is a logical value specifying whether to force the constant b to equal 1.

• If const is TRUE or omitted, b is calculated normally.
• If const is FALSE, b is set equal to 1, and the m-values are fitted to y = m^x.

Stats   is a logical value specifying whether to return additional regression statistics.

• If stats is TRUE, LOGEST returns the additional regression statistics, so the returned array is {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r 2,sey; F,df;ssreg,ssresid}.
• If stats is FALSE or omitted, LOGEST returns only the m-coefficients and the constant b.

For more information about additional regression statistics, see LINEST.

Remarks

• The more a plot of your data resembles an exponential curve, the better the calculated line will fit your data. Like LINEST, LOGEST returns an array of values that describes a relationship among the values, but LINEST fits a straight line to your data; LOGEST fits an exponential curve. For more information, see LINEST.
• When you have only one independent x-variable, you can obtain y-intercept (b) values directly by using the following formula:

Y-intercept (b):
INDEX(LOGEST(known_y's,known_x's),2)

You can use the y = b*m^x equation to predict future values of y, but Microsoft Excel provides the GROWTH function to do this for you. For more information, see GROWTH.

• Formulas that return arrays must be entered as array formulas.
• When entering an array constant such as known_x's as an argument, use commas to separate values in the same row and semicolons to separate rows. Separator characters may be different depending on your locale setting in Regional and Language Options in Control Panel.
• You should note that the y-values predicted by the regression equation may not be valid if they are outside the range of y-values you used to determine the equation.

Example 1 m-coefficients and the constant b

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A9:B9 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 1.463275628.

When entered as an array, the m-coefficients and the constant b are returned.

y = b*m1^x1 or using the values from the array:

y = 495.3 * 1.4633x

You can estimate sales for future months by substituting the month number for x in this equation, or you can use the GROWTH function.

Example 2 Full statistics

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A9:B13 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 1.463275628.

When entered as an array, the following regression statistics are returned. Use this key to identify the statistic you want.

You can use the additional regression statistics (cells A10:B13 in the above output array) to determine how useful the equation is for predicting future values.

Important  The methods you use to test an equation using LOGEST are similar to the methods for LINEST. However, the additional statistics LOGEST returns are based on the following linear model:

ln y = x1 ln m1 + ... + xn ln mn + ln b

You should keep this in mind when you evaluate the additional statistics, especially the sei and seb values, which should be compared to ln mi and ln b, not to mi and b. For more information, consult an advanced statistics manual.

See Also

• GROWTH function
• LINEST function
• Statistical functions
• TREND function

Excel > Function reference > Financial

MDURATION function

Excel 2007

Returns the modified Macauley duration for a security with an assumed par value of $100.

Syntax

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

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

Maturity   is the security's maturity date. The maturity date is the date when the security expires.

Coupon   is the security's annual coupon rate.

Yld   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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date is January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• Settlement, maturity, frequency, and basis are truncated to integers.
• If settlement or maturity is not a valid date, MDURATION returns the #VALUE! error value.
• If yld < 0 or if coupon < 0, MDURATION returns the #NUM! error value.
• If frequency is any number other than 1, 2, or 4, MDURATION returns the #NUM! error value.
• If basis < 0 or if basis > 4, MDURATION returns the #NUM! error value.
• If settlement ≥ maturity, MDURATION returns the #NUM! error value.
• Modified duration is defined as follows:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DURATION function
• Financial functions

Excel > Function reference > Financial

MIRR function

Excel 2007

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.

Syntax

MIRR(values,finance_rate,reinvest_rate)

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.

• 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   is the interest rate you pay on the money used in the cash flows.

Reinvest_rate   is the interest rate you receive on the cash flows as you reinvest them.

Remarks

• MIRR 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 and with the correct signs (positive values for cash received, negative values for cash paid).
• If n is the number of cash flows in values, frate is the finance_rate, and rrate is the reinvest_rate, then the formula for MIRR is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Financial functions
• RATE function
• XIRR function
• XNPV function

Excel > Function reference > Financial

NOMINAL function

Excel 2007

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

Syntax

NOMINAL(effect_rate,npery)

Effect_rate   is the effective interest rate.

Npery   is the number of compounding periods per year.

Remarks

• Npery is truncated to an integer.
• If either argument is nonnumeric, NOMINAL returns the #VALUE! error value.
• If effect_rate ≤ 0 or if npery < 1, NOMINAL returns the #NUM! error value.
• NOMINAL is related to EFFECT as shown in the following equation:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• EFFECT function
• Financial functions

Excel > Function reference > Financial

NPER function

Excel 2007

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

Syntax

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

For a more complete description of the arguments in NPER and for more information about annuity functions, see PV.

Rate   is the interest rate per period.

Pmt   is 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   is the present value, or the lump-sum amount that a series of future payments is worth right 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 0 (the future value of a loan, for example, is 0).

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

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Financial functions
• FV function
• IPMT function
• PMT function
• PPMT function
• PV function
• RATE function

Excel > Function reference > Financial

NPV function

Excel 2007

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).

Syntax

NPV(rate,value1,value2, ...)

Rate   is the rate of discount over the length of one period.

Value1, value2, ...   are 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 numbers, empty cells, logical values, or text representations of numbers are counted; arguments that are 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, or text in the array or reference are ignored.

Remarks

• The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments. For more information, see the examples below.
• If n is the number of cash flows in the list of values, the formula for NPV is:

• NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, see PV.
• NPV is also related to the IRR function (internal rate of return). IRR is the rate for which NPV equals zero: NPV(IRR(...), ...) = 0.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

In the preceding example, you include the initial $10,000 cost as one of the values, because the payment occurs at the end of the first period.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

In the preceding example, you don't include the initial $40,000 cost as one of the values, because the payment occurs at the beginning of the first period.

See Also

• Financial functions
• FV function
• IRR function
• PV function
• XNPV function

Excel > Function reference > Financial

ODDFPRICE function

Excel 2007

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)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

Maturity   is the security's maturity date. The maturity date is the date when the security expires.

Issue   is the security's issue date.

First_coupon   is the security's first coupon date.

Rate   is the security's interest rate.

Yld   is the security's annual yield.

Redemption   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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• Settlement, maturity, issue, first_coupon, and basis are truncated to integers.
• If settlement, maturity, issue, or first_coupon is not a valid date, ODDFPRICE returns the #VALUE! error value.
• If rate < 0 or if yld < 0, ODDFPRICE returns the #NUM! error value.
• If basis < 0 or if basis > 4, ODDFPRICE returns the #NUM! error value.
• The following date condition must be satisfied; otherwise, ODDFPRICE returns the #NUM! error value:

maturity > first_coupon > settlement > issue

• ODDFPRICE is calculated as follows:

Odd short first coupon:

where:

A = number of days from the beginning of the coupon period to the settlement date (accrued days).

DSC = number of days from the settlement to the next coupon date.

DFC = number of days from the beginning of the odd first coupon to the first coupon date.

E = number of days in the coupon period.

N = number of coupons payable between the settlement date and the redemption date. (If this number contains a fraction, it is raised to the next whole number.)

Odd long first coupon:

where:

Ai = number of days from the beginning of the ith, or last, quasi-coupon period within odd period.

DCi = number of days from dated date (or issue date) to first quasi-coupon (i = 1) or number of days in quasi-coupon (i = 2,..., i = NC).

DSC = number of days from settlement to next coupon date.

E = number of days in coupon period.

N = number of coupons payable between the first real coupon date and redemption date. (If this number contains a fraction, it is raised to the next whole number.)

NC = number of quasi-coupon periods that fit in odd period. (If this number contains a fraction, it is raised to the next whole number.)

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

Nq = number of whole quasi-coupon periods between settlement date and first coupon.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DATE function
• Financial functions
• ODDFYIELD function
• ODDLPRICE function
• ODDLYIELD function

Excel > Function reference > Financial

ODDFYIELD function

Excel 2007

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)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

Maturity   is the security's maturity date. The maturity date is the date when the security expires.

Issue   is the security's issue date.

First_coupon   is the security's first coupon date.

Rate   is the security's interest rate.

Pr   is the security's price.

Redemption   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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• Settlement, maturity, issue, first_coupon, and basis are truncated to integers.
• If settlement, maturity, issue, or first_coupon is not a valid date, ODDFYIELD returns the #VALUE! error value.
• If rate < 0 or if pr ≤ 0, ODDFYIELD returns the #NUM! error value.
• If basis < 0 or if basis > 4, ODDFYIELD returns the #NUM! error value.
• The following date condition must be satisfied; otherwise, ODDFYIELD returns the #NUM! error value:

maturity > first_coupon > settlement > issue

• Excel uses an iterative technique to calculate ODDFYIELD. This function uses the Newton method based on the formula used for the function ODDFPRICE. The yield is changed through 100 iterations until the estimated price with the given yield is close to the price. See ODDFPRICE for the formula that ODDFYIELD uses.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    To view the number as a percentage, select the cell, and then on the Sheet tab, in the Number group, click Percent Style .

See Also

• DATE function
• Financial functions
• ODDFPRICE function
• ODDLPRICE function
• ODDLYIELD function

Excel > Function reference > Financial

ODDLPRICE function

Excel 2007

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)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

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.

Maturity   is the security's maturity date. The maturity date is the date when the security expires.

Last_interest   is the security's last coupon date.

Rate   is the security's interest rate.

Yld   is the security's annual yield.

Redemption   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.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• 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, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
• Settlement, maturity, last_interest, and basis are truncated to integers.
• If settlement, maturity, or last_interest is not a valid date, ODDLPRICE returns the #VALUE! error value.
• If rate < 0 or if yld < 0, ODDLPRICE returns the #NUM! error value.
• If basis < 0 or if basis > 4, ODDLPRICE returns the #NUM! error value.
• The following date condition must be satisfied; otherwise, ODDLPRICE returns the #NUM! error value:

maturity > settlement > last_interest

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DATE function
• Financial functions
• ODDFPRICE function
• ODDFYIELD function
• ODDLYIELD function

Excel > Function reference > Financial

ODDLYIELD function

Excel 2007

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)

Important  Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Settlement   is the security's settlement date. The security settlement date is the date af