Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

Any Expert advise some of the "Excel's most popular financial functions"?

user-image
Question added by Joel Thomas , Auditor , DME HoldingGroup
Date Posted: 2016/06/17
Deleted user
by Deleted user

I've listed all of the Excel Financial functions, with a * next those that are only available after installing the Analysis ToolPak (To install, go to Tools > Add-ins > and select Analysis ToolPak).

Depreciation Formulas

  • DB - Fixed-Declining Balance
  • DDB - Double-Declining Balance
  • SLN - Straight-Line Depreciation
  • SYD - Sum-of-Years' Digits
  • VDB - Variable Declining Balance
  • AMORLINC - (for the French accounting system) Depreciation for each accounting period
  • AMORDEGRC - (for the French accounting system) Uses a depreciation coefficient

Formulas for Interest, Cash Flow, Investments, Annuities

  • CUMIPMT - Cumulative Interest Payment
  • CUMPRINC - Cumulative Principal
  • EFFECT - Effective annual interest rate
  • FV - Future Value of an investment
  • FVSCHEDULE - Future Value with a variable rate
  • IPMT - Interest Payment for an investment or loan
  • IRR - Internal Rate of Return
  • ISPMT - Interest Payment during a Specific period (for compatibility with Lotus)
  • MIRR - Modified Internal Rate of Return
  • NPER - Number of Periods for an investment or loan
  • NPV - Net Present Value formula
  • PMT - Periodic Payment for an annuity
  • PPMT - Payment on the Principal for an annuity or loan
  • PV - Present Value of an investment
  • RATE - Interest rate per period
  • XIRR - Internal Rate of Return (not necessarily periodic)
  • XNPV - Net Present Value (not necessarily periodic)

Functions for Coupons

  • COUPDAYBS - Days from the Beginning of the Coupon period to the Settlement date
  • COUPDAYS - Days in the coupon period that contains the Settlement date
  • COUPDAYSNC - Days from the Settlement date to the Next Coupon date
  • COUPNCD - Next Coupon Date after the settlement date
  • COUPPCD - Previous Coupon Date before the settlement date
  • COUPNUM - Number of coupons between the settlement and maturity date

Finance Formulas for Securities

  • ACCRINT - Accrued Interest
  • ACCRINTM - Accrued Interest at Maturity
  • DISC - Discount rate
  • DURATION - Annual Duration
  • INTRATE - Interest rate for a fully invested security
  • MDURATION - Macauley modified duration (with an assumed par value of $100)
  • NOMINAL - Annual nominal interest rate
  • ODDFPRICE - Price per $100 face value with an Odd First period
  • ODDFYIELD - Yield with an Odd First period
  • ODDLPRICE - Price per $100 face value with an Odd Last period
  • ODDLYIELD - Yield with an Odd Last period
  • PRICE - Price per $100 face value
  • PRICEDISC - Price per $100 face value of a Discounted security
  • PRICEMAT - Price per $100 face value of a security that pays interest at Maturity
  • RECEIVED - Amount received at maturity for a fully invested security
  • YIELD - Yield on a security that pays periodic interest
  • YIELDDISC - Annual yield for a discounted security (Treasury bill)
  • YIELDMAT - Annual yield of a security that pays interest at maturity

Formulas for Dollar Price Conversions

  • DOLLARDE - Converts a dollar price from a Fraction to a Decimal number
  • DOLLARFR - Converts a dollar price from a Decimal number to a Fraction

Treasury Bill Functions

  • TBILLEQ - Bond-equivalent yield for a Treasury Bill
  • TBILLPRICE - Price per $100 face value for a Treasury Bill
  • TBILLYIELD - Yield for a Treasury Bill

Ghada Eweda
by Ghada Eweda , Medical sales hospital representative , Pfizer pharmaceutical Plc.

Excel text functions (string functions)   

There exist a great lot of Microsoft Excel functions to manipulate text strings espeacily in finance and accounting. Here are the most essential ones:

1-TEXT function

TEXT(value, format_text) is used to convert a number or a date into a test string in the specified format, where:

  • Value is a numeric value you want to convert to text.
  • Format_text is the desired format.

The following formulas demonstrate the Excel TEXT function in action:

=TEXT(A1,"mm/dd/yyyy") - convert a date in cell A1 into a text string in the traditional US date format, such as "01/01/2015" (month/day/year).

=TEXT(A1,"€#,##0.00") - converts a number in A1 into a currency text string such as "€3.00".Excel Text function

2-TRIM function

TRIM(text) removes leading, trailing spaces as well as excess spaces between words. Where textis either a text string or reference to the cell containing the text from which you want to remove spaces. The following screenshot demonstrates an example of usage:TRIM formula example

3-SUBSTITUTE function

SUBSTITUTE(text, old_text, new_text, [instance_num]) replaces one set of characters with another in a specified cell or a text string. The syntax of the SUBSTITUTE function is as follows:

  • Text - the original text string or reference to a cell where you want to substitute certain characters.
  • Old_text - the characters you want to replace.
  • New_text - the characters you want to replace the old text with.
  • Nth_appearance - an optional parameter that specifies which occurrence of old_text you want to replace with new_text. If omitted, then every occurrence of the old text will be replaced with the new text.

For example, the following SUBSTITUTE formula replaces all commas in cell A1 with semicolons:

=SUBSTITUTE(A2, ",", ";")SUBSTITUTE formula examples

4-VALUE function

VALUE(text) - converts a text string to a number.

This function is really helpful when it comes to converting text-formatted values representing the numbers into numbers that can be used in other Excel formulas and calculations.

5-EXACT function

EXACT(text1, text2) compares two text strings and returns TRUE if both values are exactly the same, including case, FALSE otherwise.

For example, if A2 is "apples" and B2 is "Apples", the formula =EXACT(A2, B2) will returns FALSE, because they are not exact match.

danish shafiq
by danish shafiq , Accountant , Anayat Fan

I also agree with the experts answers.

Irshad Kaptan
by Irshad Kaptan , Manager Admin & HR , National Cleaning Company (KSC) HWTC-QP Project

Round is the most common and important function in MS Excel to round figure the amount.

thanks for the invitation and keep it up.

Barkat Ali
by Barkat Ali , Accountant , Abdullah Bin Ahmed Bin Mohd Al Muzahmi Trading

I am agree with ghada ewada , and Moin Akber.......

More Questions Like This