Easy Explanation Notes with Examples

Microsoft Excel Function Library

Excel Function Library, lists and explains all Microsoft Excel functions categorically with easy examples for free.

Financial

ACCRINT function calculates the accrued interest for a security that pays interest on a periodic basis.

ACCRINTM function returns the accrued interest of a security (usually a bond) that pays interest at maturity.

AMORDEGRC Function returns the depreciation of an asset for each accounting period.

AMORLINC function calculates depreciation of an asset, on a prorated basis, for a specified accounting period.

COUPDAYBS function calculates the number of days from the start of a coupon period to the settlement date of the security

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

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

COUPNCD function is used to find out the first coupon payment date after the settlement date of a security/bond.

COUPNUM function calculates the total number of coupon payments to be made from the settlement date to the maturity date of the security.

COUPPCD function provides the most recent coupon payment date before the settlement date of the security.

CUMIPMT function calculates the cumulative interest paid between the start date and the end date.

CUMPRINC function calculates the cumulative principal paid on a loan between the specified period.

DB function calculates depreciation of an asset by using fixed declining balance method over the useful life of the asset.

DDB function calculates depreciation of an asset by using the Double Declining (DDB) Method or another method specified by the user.

DISC function calculates the discount rate for a security.

DOLLARDE function converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number.

DURATION function calculates the Macauley duration of a security that has a par value of $100 and pays interest on a periodic basis.

EFFECT function calculates an effective annual interest rate for a loan or an investment.

FV function calculates the future value of an investment.

FVSCHEDULE function returns the future value of an investment (an initial principal) after applying series of compound interest rates.

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

ISPMT function calculates the amount of interest paid on a loan/ investment for a specific period.

IPMT function calculates the interest payment portion from a given periodic payment against an investment, based on periodic constant payments.

IRR (Internal Rate of Return) function calculates the internal rate of return for an investment having subsequent cash inflows occurring at regular intervals, such as monthly or annually.

NOMINAL function is one of the Financial functions, it calculates the nominal interest rate of a bond having an effective interest rate and compounding periods per year.

NPER function returns the number of periods required to pay off a loan, given the constant payments and constant interest rate.

NPV function calculates the net present value of an investment by using a discount rate and a series of future payments.

N function converts a value to a number.

ODDFPRICE function calculates the price of a bond having an irregular first period.

ODDFYIELD function calculates the yield of a security that has an irregular (short or long) first period.

ODDLPRICE function calculates the price per $100 face of a security having an irregular/odd (short or long) last coupon period.

ODDLYIELD function calculates the yield of a security that has an odd (irregular) last period.

PMT function is used to calculate the periodic payment for a loan.

PPMT function calculates the principal value of an investment for a given period.

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

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

PRICEMAT function calculates the price per $100 face value of a security (e.g. a bond) that pays interest at maturity.

PV function is used to get the present value/ current worth of the future cash flows that will receive at a future date.

RATE function calculates the interest rate per period for an annuity.

RECEIVED function calculates the amount of return at maturity of fully invested security.

SLN function uses the straight-line depreciation method to find out the depreciation of an asset for one period.

SYD function returns the sum-of-years’ digits depreciation of an asset for a specific period.

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

TBILLPRICE function calculates the price of a Treasury bill.

TBILLYIELD function calculates the yield for a Treasury bill.

VDB function calculates the depreciation of an asset, for a specified given period, by using any selected depreciation method, such as the double declining balance method etc.

XIRR function calculates the internal rate of return for an investment having cash flows occurring at irregular intervals.

XNPV function calculates the NPV for irregular cash flows.

YIELD function calculates a security’s yield that pays interest periodically, such as a bond.

YIELDDISC Function returns the annual yield for a discounted security.

YIELDMAT function calculates the annual yield of the bond.

Logical

AND function tests a number of logical conditions set by a user.

FALSE function returns the Boolean value False.

NOT function is a logical function and returns the opposite of a given logical value i.e. if supplied with the value TRUE, the function returns the FALSE and vice-versa.

IF function checks whether a specified condition is met or not.

IFERROR function returns a specified value if an error found; otherwise, returns the result of the formula.

OR function tests multiple logical conditions at the same time.

TRUE function returns the logical value TRUE and often used when you want the value TRUE based on a condition.

Lookup & Reference

ADDRESS function returns a cell reference, if specified cell and row numbers are provided. For example, ADDRESS(2,2) returns $B$2.

AREAS function returns the number of areas in a reference. For example, =AREAS((A2:C3,B5:C7)) returns 2 areas.

CHOOSE function returns a value from a list corresponding to the index number provided.

COLUMN function returns the column number against the given cell reference.

COLUMNS function uses to find out the number of columns in a selected array or reference.

OFFSET function returns a cell or range of cells from specified rows and columns of a selected cell or range of cells.

Text

For a brief description of the most common text functions, read the TEXT functions section.

BAHTTEXT function converts a number to Thai text.

CLEAN function is used to remove non-printable characters.

CONCATENATE function is used to join different text strings to a single string.

DOLLAR function converts a number value to text.

EXACT function is used to compare different text strings.

LARGE function returns with the largest numerical value in a list.

LOWER function returns with the lowerest numerical value from a list.

SEARCH function returns the location of a character within a string.

SMALL function (To find out k-th smallest values e.g.2nd smallest value).

SUBSTITUTE function is used to replace specific text in a text string.

T function checks whether the supplied value is a text or not.

TEXT function converts a numeric value to a specified text format

TRIM function is used to remove irregular spaces between words which may appear while importing data from another application.

UNICHAR function returns a Unicode character against a specified number.

UNICODE function returns the Unicode number for the first character of a supplied text string.

PROPER function is used to capitalize each starting letter of a text string while ignoring numbers and punctuation.

REPT function repeats a text string, a given number of times.

UPPER case function converts text to uppercase.

Date & Time

HOUR function is used to extract an hour component between  0 (12:00 A.M.) to 23 (11:00 P.M.)

DAY function is used to extract a day number (1 to 31) from a date given.

DATE function is used to combine three different values (of date, month and year) to form a date.

MONTH function returns with a month number from 1 to 12.

TODAY function is useful when you need a current date in a worksheet and it remains updated whenever you open the worksheet.

Year function is used to extract 4-digit year number from a given Excel’s format date.

YEARFRAC function to get a fractional value representing fractional years between two dates.

TIMEVALUE function returns with an accurate text value in the decimal number of the time.

NOW function returns with the current date and time which is keep updated whenever you open the worksheet.

NETWORKDAYS function returns with a number of working days between two dates after excluding weekends and holidays.

Math & Trig

ABS function is used to turn negative values into positive one.

ROUND function used to round a number to the decimal places.

SUMIFS function performs the SUM function with multiple criteria.

SUMIF function performs the SUM function with single criteria.

SUMPRODUCT function first multiplies corresponding arrays, then returns with the sum.

Statistical

Statistical functions

COUNT function calculates how many cells/numbers are present in a selected range of cells.

COUNTA function counts cells that are not empty.

COUNTBLANK function used to count the number of blank cells in a range of cells.

COUNTIF function is used to count cells that meet a criterion.

COUNTIFS function counts the number of cells based on single or multiple criteria.

MAX function returns with the larger of two values.

MIN function returns with the minimum value from a set of values.

Information

ISNUMBER function checks whether a cell has a number or text value.

ISODD function checks whether a cell’s data has odd or even numbers.

ISERROR function checks whether a value is an error or not

ISLOGICAL function tests whether a value is a logical or not.

ISTEXT function tests whether a value is a text or not.

ISBLANK function tests whether a value is blank or not.

INFO function returns with information about the current operating environment.

CELL function returns with cell’s information regarding formatting, location, and contents etc.

Web

ENCODEURL function converts URL to coded form.




Copyright © 2016 - 2019 Explainry.com | All Rights Reserved