Excel - Microsoft Excel Couppcd Function

Description

The Excel COUPPCD function returns the previous coupon date, before the settlement date for a security. 

The syntax of the function is : 

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

Where the arguments are as follows: 

Note that, when supplying dates to Excel functions, these should be input as either: 

• References to cells containing dates

or 

• Dates returned from formulas

Warning: 

Excel Couppcd Function Example

In the spreadsheet below the Excel Couppcd function is used to calculate the coupon date before the settlement date, for a security with settlement date 01-Jan-2011, maturity date 25-Oct-2012, and four coupon payments per year. The security uses the US (NASD) 30/360 day count basis. 

The spreadsheet on the left shows the syntax of the function and the spreadsheet on the right shows the result. 

Couppcd Function Errors

If you get an error from the Excel Couppcd Function, this is most likely to be one of the following: 

Common Errors 

Excel - Microsoft Excel DDB Function

Description

The Excel DB function calculates the depreciation of an asset, using the Double Declining Balance Method, or another specified depreciation rate. 

The format of the function is : 

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

where the arguments are as shown in the table below: 

DDB Function Example

In the example below, the DDB function uses the double declining depreciation method to calculate the yearly depreciation of an asset that cost $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years. 

DDB Function Common Errors

If you get an error from the Excel DDB Function, this is likely to be one of the following : 

Common Errors 

Excel - Microsoft Excel Dollarde Function

Description

The Excel Dollarde function converts a dollar value, expressed as a fraction, as a dollar value, expressed as a decimal. 

The syntax of the function is : 

DOLLARDE( fractional_dollar, fraction ) 

Where the arguments are as follows: 

The fraction argument should be supplied as an integer, but if this is provided as a decimal value, Excel will truncate it to an integer. 

Excel Dollarde Function Examples

The following spreadsheet shows the Excel Dollarde function, used to covert prices in fractional notation to decimal notation. 

The formulas used are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right. 

Dollarde Function Errors

The most common errors from the Dollarde function are listed in the following table: 

Common Errors 

Excel - Microsoft Error Type

Description

The Excel Error.Type function receives an error value and returns an integer, that tells you the type of the supplied error.

The syntax of the function is:

ERROR.TYPE( error_val )

Where the supplied error_val is a value (or a reference to a cell containing a value), that you want to return the error type of.

The integers returned by the Excel Error.Type function, and their corresponding error types are listed in the table below:

Error.Type Function Examples

Column B of the following spreadsheet shows four examples of the Error.Type function.

Excel - Microsoft Excel Effect Function

Description

The Excel EFFECT function returns the effective annual interest rate for a given nominal interest rate and number of compounding periods per year. 

The format of the function is : 

EFFECT( nominal_rate, npery )

where the nominal_rate argument is a numerical value, between 0 and 1, which represents the nominal interest rate, and the npery argument is a positive integer that gives the number of compounding periods per year. 

Excel Effect Function Example

Column A of the spreadsheet below, shows examples of the Excel Effect Function, used to calculate the effective annual interest rate for 3 different nominal interest rates and numbers of compounding periods. 

The formulas are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right. 

If you get an error from the Excel Effect function this is likely to be one of the following :

Common Errors 

Excel - The Excel INFO Function

Description

The Excel Info function returns a text string containing information about the current operating environment.

The syntax of the function is:

INFO( type_text )

Where the type_text argument is a text string that specifies the type of information to be returned.

Possible values for the type_text argument are listed in the table below:

Info Function Examples

The following spreadsheet contains five examples of the Excel Info function.

Note that the examples were run on a Windows operating system. The Info function may return different results on other operating systems.

Info Function Errors

If you get an error from the Excel Info function this is likely to be one of the following:

Common Errors

Excel - Microsoft Excel LOG Function

Description

The Excel LOG function calculates the logarithm of a given number, to a supplied base. 

The format of the function is : 

LOG( number, [base] ) 

where the arguments are listed in the table below : 

Excel Log Function Examples

In the example below, the Excel Log function is used to calculate the logarithms of different numbers, using different bases. 

The format of the functions is shown in the spreadsheet on the left and the result is shown in the spreadsheet on the right. 

Note that, in cells A2 and A4 of the above spreadsheet, the [Base] argument has not been supplied to the function and so, in these examples, base 10 is used. 

Log Function Errors

If you get an error from your Excel Log function, use the following table to diagnose the likely cause : 

Common Errors 

Excel - Microsoft Excel LOG10 Function

Description

The Excel LOG10 function calculates the base 10 logarithm of a given number. 

The format of the function is : 

LOG10( number ) 

where the number argument is a positive real number that you want to calculate the base 10 logarithm of. 

Log10 Function Examples

In the examples below, the Excel Log10 function is used to calculate the base 10 logarithms of different numbers. 

The format of the functions is shown in the spreadsheet on the left and the result is shown in the spreadsheet on the right. 

Excel - Microsoft Excel MDETERM Function

Description

The Excel MDETERM function calculates the determinant of a square matrix. 

The format of the function is : 

MDETERM( array ) 

where the array argument is an array of numerical values, that represent a square matrix (i.e. has the same number of rows as columns). 

Mdeterm Function Examples

In the examples below, the Excel Mdeterm function is used to calculate the determinant of a 2x2 and a 3x3 matrix. 

Mdeterm Function Error

If you get an error from the Excel Mdeterm Function, this is likely to be the #VALUE! error: 

Common Error 

Excel - Microsoft Excel Minverse Function

Description

The Excel MINVERSE function calculates the inverse of a square matrix. 

The format of the function is : 

MINVERSE( array ) 

where the array argument is an array of values representing a square matrix (i.e. has the same number of rows as columns). 

The resulting inverse matrix has the same number of rows and columns as the original supplied array. 

As the Excel Minverse function returns an array of values, the function needs to be entered as an Array Formula. 

Array Formulas: 

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel Array Formulas page for more details. 

Note that the result from the Minverse function result is calculated to approximately 16 decimal places, so you may get some rounding errors. 

Minverse Function Example

In the example below, the Excel Minverse function is used to find the inverse of the 4x4 matrix in cells A1-D4 of the example spreadsheet. The resulting 4x4 matrix is shown in cells F1-I4. 

The formula for the function can be seen in the formula bar. The curly braces { } show that the function has been input as an Array Formula. 

We can test the Minverse function by multiplying the two matrices in the above spreadsheet, using the Mmult Function. This gives the 4x4 Identity Matrix : 

Minverse Function Errors

If you get an error from your Excel Minverse function this is likely to be one of the following :

Common Errors 

Be aware also, that if you don't highlight the full range of cells for the resulting matrix, Excel will just show the part of the result that fits into the highlighted range. There will be no error message to let you know that your highlighted range is not big enough. 

This will also be the case if you fail to enter the formula as an array formula - the cell which you enter the formula into will simply show you the first entry of the resulting matrix. 

Excel - Microsoft Excel Mmult Function

Description

The Excel MMULT function calculates the matrix product of two arrays (representing matrices). 

The format of the function is : 

MMULT( array1, array2 ) 

where array1 and array2 are arrays of numerical values, representing matrices, where the number of columns in array1 is equal to the number of rows in array2. 

The resulting matrix has the same number of rows as array1 and the same number of columns as array2. 

As the Excel Mmult function returns an array of values, the function needs to be entered as an Array Formula. 

Array Formulas: 

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel Array Formulas page for more details. 

Mmult Function Examples

In the example below, the 3x4 matrix in cells A1-D3 is multiplied by the 4x2 matrix in cells F1-G4, to give the resulting 3x2 matrix in cells I1-J3. 

The formula for the function can be seen in the formula bar. The curly braces { } show that the function has been input as an Array Formula. 

Mmult Function Error

If you get an error from the Excel Mmult function, this is likely to be one of the following : 

Common Error 

Be aware also, that if you don't highlight the full range of cells for the resulting matrix, Excel will just show the part of the result that fits into the highlighted range. There will be no error message to let you know that your highlighted range is not big enough. 

This will also be the case if you fail to enter the formula as an array formula - the cell which you enter the formula into will simply show you the first entry of the resulting matrix. 

Excel - Microsoft Excel MOD Function

Description

The Excel MOD function returns the remainder of a division between two supplied numbers. 

The format of the function is : 

MOD( number, divisor ) 

where the arguments are as follows: 

These arguments can be supplied to the function either as simple numbers, as references to cells containing numbers, or as values returned from other functions. This is shown in the examples below. 

Excel Mod Function Examples

The following spreadsheets show simple examples of the Excel Mod function. The format of the function is shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right. 

Excel - Microsoft Excel Mround Function

Description

The Excel Mround function rounds a supplied number up or down to the nearest multiple of a given number. 

The syntax of the function is: 

MROUND( number, multiple ) 

Where the arguments are as follows: 

Mround Function Examples

The following spreadsheet shows several examples of the Mround function: 

The above examples show how the Mround function rounds the supplied number up or down, to the nearest multiple of the supplied multiple argument. 

Mround Function Error

If you get an error from the Excel Mround function, this is likely to be the #NUM! error : 

Common Error 

Excel - Microsoft Excel Multinomial Function

Description

The Excel MULTINOMIAL function returns the ratio of the factorial of a sum of supplied values to the product of factorials of those values. 

where the supplied number arguments are the integer values (or ranges of cells containing integer values) for which you want to calculate the multinomial. If any of the supplied values are decimals, these are truncated to integers. 

Note that, in Excel 2007 and Excel 2010, you can supply up to 255 values to the Multinomial function, but in Excel 2003, you can only supply up to 29 values. 

Multinomial Function Example

Cell B1 of the spreadsheet below shows an the Excel Multinomial Function, used to calculate the multinomial of the values 3, 1, 2 and 5, in column A of the spreadsheet. 

The above example shows that the multinomial of the values 3, 1, 2 and 5 is 27,720. 

Multinomial Function Errors

If you get an error from the Excel Multinomial Function, this is likely to be one of the following: 

Common Errors 

Excel - Microsoft Excel Accrintm Function

Description

The Excel Accrintm function returns the accrued interest for a security that pays interest at maturity. 

The syntax of the function is : 

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

Where the arguments are as follows: 

Note that Microsoft advises that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the issue and settlement dates should be input as either: 

• References to cells containing dates

    or 

• Dates returned from other functions or formulas

Accrint Function Examples

The following spreadsheet shows an example of the Excel Accrintm function being used to calculate the accrued interest of a security that pays interest at maturity. The security has an issue date of 01-Jan-2012 and a settlement date of 31-Dec-2012. The annual coupon rate is 8% and the par value of the security is $10,000. The US (NASD) 30/360 day count basis is used. 

The above function gives the result $800

Note that, in the example above, the [basis] argument is omitted, and therefore takes the default value of 0 (US (NASD) 30/360 basis). 

Accrintm Function Errors

If you get an error from the Excel Accrintm function, this is likely to be one of the following: 

Common Errors 

Excel - Microsoft Excel Amordecrc Function

Description

The Excel Amordegrc function calculates the prorated linear depreciation of an asset for each accounting period, with a depreciation coefficient applied, depending on the life of the asset. The function has been introduced into Excel for users of the French accounting system. 

The syntax of the Amordegrc function is : 

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

Where the arguments are as follows: 

Note that the date arguments should be entered into the function as either: 

• References to cells containing dates

or 

• Dates returned from formulas

Warning: 

Excel Amordegrc Function Example

In the following example, the Excel Amordegrc function is used to calculate the depreciation of an asset during the first period. The asset was purchased on 01-Jan-2011, at a cost of €150 and the first period ends on 30-Sep-2011. The asset depreciates at a rate of 20% per year and has a salvage value of €20. The European day count basis is used. 

The function returns the value 42. 

- i.e. the asset depreciates by €42.00 during the first period. 

Note that, as recommended by Microsoft, the date arguments have been input to the above function as references to cells containing dates. 

Amordecrc Function Errors

If you get an error from the Amordegrc function, this is likely to be one of the following: 

Common Errors 

Excel - Microsoft Excel Amorlinc Function

Description

The Excel Amorlinc function calculates the prorated linear depreciation of an asset for each accounting period. The function has been introduced into Excel for users of the French accounting system. 

The syntax of the Amorlinc function is : 

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

Where the arguments are as follows: 

Note that the date_purchased and the first_period arguments should be entered into the function as either: 

• References to cells containing dates

or 

• Dates returned from formulas

Warning:

Excel Amorlinc Function Example

In the following example, the Excel Amorlinc function is used to calculate the depreciation of an asset during the first period. The asset was purchased on 01-Jan-2011, at a cost of €150 and the first period ends on 30-Sep-2011. The asset depreciates at a rate of 20% per year and has a salvage value of €20. The European day count basis is used. 

The function returns the value 30. 

- i.e. the asset depreciates by €30.00 during the first period. 

Note that, in the above example, as recommended, the date arguments have been input to the function as references to cells containing dates. 

Amorlinc Function Errors

If you get an error from the Amorlinc function, this is likely to be one of the following: 

Common Errors 

Excel - Microsoft Excel Coupdaybs Function

Description

The Excel COUPDAYBS function calculates the number of days from the beginning of a coupon's period to the settlement date. 

The syntax of the function is : 

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

Where the arguments are as follows: 

Note that the settlement and maturity dates should be input as either: 

• References to cells containing dates

or 

• Dates returned from formulas

- If you attempt to input these dates as text, there is a chance that Excel may misinterpret this due to different date systems, or date interpretation settings. 

Warning: Although you can input the dates as serial numbers, this is not recommended as the serial numbers may vary across different computer systems. 

Excel Coupdaybs Function Example

Cell A3 of the spreadsheet below contains an example of the Excel Coupdaybs function, used to calculate the number of days from the beginning of a coupon's period to the settlement date. The security has the settlement date 01-Jan-2011, the maturity date 25-Oct-2012, and 4 payments per year. The US (NASD) 30/360 day count basis is used. 

The format of the function is shown in the spreadsheet on the left and the result is shown in the spreadsheet on the right. 

Coupdaybs Function Errors

If you get an error from the Excel Coupdaybs Function, this is most likely to be one of the following: 

Common Errors