Financial functions in Excel are mostly used to calculate depreciation of assets or if you made a loan or an investment and need to calculate related values like future value, present value, interest rate, and, number of installments to pay off the loan etc.
For a complete list of Excel financial functions, visit Microsoft Excel Function Library. In this article, financial functions are provided by category wise hereunder:
Loan or Investment
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.
NPER function returns the number of periods required to pay off a loan, given the constant payments and constant interest rate.
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.
1- Interest rate
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.
CUMIPMT function calculates the cumulative interest paid between the start date and the end date.
EFFECT function calculates an effective annual interest rate for a loan or an investment.
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.
NOMINAL function calculates the nominal interest rate of a bond having an effective interest rate and compounding periods per year.
IPMT function calculates the interest payment portion from a given periodic payment against an investment, based on periodic constant payments.
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.
ODDFPRICE function calculates the price of a bond having an irregular first period.
ODDLPRICE function calculates the price per $100 face of a security having an irregular/odd (short or long) last coupon 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.
ODDFYIELD function calculates the yield of a security that has an irregular (short or long) first period.
ODDLYIELD function calculates the yield of a security that has an odd (irregular) last period.
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.
4- Treasury bill
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.
FVSCHEDULE function returns the future value of an investment (an initial principal) after applying series of compound interest rates.
NPV function calculates the net present value of an investment by using a discount rate and a series of future payments.
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.
RECEIVED function calculates the amount of return at maturity of fully invested security.
6- Date or 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.
CUMPRINC function calculates the cumulative principal paid on a loan between the specified period.
DURATION function calculates the Macauley duration of a security that has a par value of $100 and pays interest on a periodic basis.
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.
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.
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.
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.