Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Category: Financial Functions

Excel Financial Functions perform many of the common financial calculations, such as the calculation of yield, interest rates, duration, valuation and depreciation.

YIELDMAT function: Description, Usage, Syntax, Examples and Explanation

What is YIELDMAT function in Excel? YIELDMAT function is one of the Financial functions in Microsoft Excel that returns the annual yield of a security that pays interest at maturity. Syntax of YIELDMAT function YIELDMAT(settlement, maturity, issue, rate, pr, [basis]) The YIELDMAT function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the date after the…

ACCRINT function: Description, Usage, Syntax, Examples and Explanation

What is ACCRINT function in Excel? ACCRINT function is one of the Financial functions in Microsoft Excel that returns the accrued interest for a security that pays periodic interest. Syntax of ACCRINT function ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method]) The ACCRINT function syntax has the following arguments: Issue: The security’s issue date. First_interest: The security’s first interest date. Settlement:…

ACCRINTM function: Description, Usage, Syntax, Examples and Explanation

What is ACCRINTM function in Excel? ACCRINTM function is one of the Financial functions in Microsoft Excel that returns the accrued interest for a security that pays interest at maturity. Syntax of ACCRINTM function ACCRINTM(issue, settlement, rate, par, [basis]) The ACCRINTM function syntax has the following arguments: Issue: The security’s issue date. Settlement: The security’s maturity date. Rate: The security’s annual…

AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation

What is AMORDEGRC function in Excel? AMORDEGRC function is one of the Financial functions in Microsoft Excel that 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…

AMORLINC function: Description, Usage, Syntax, Examples and Explanation

What is AMORLINC function in Excel? AMORLINC function is one of the Financial functions in Microsoft Excel that 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 of AMORLINC function AMORLINC(cost, date_purchased, first_period, salvage,…

COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation

What is COUPDAYBS function in Excel? COUPDAYBS function is one of the Financial functions in Microsoft Excel that returns the number of days from the beginning of a coupon period until its settlement date. Syntax of COUPDAYBS function COUPDAYBS(settlement, maturity, frequency, [basis]) The COUPDAYBS function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the…

COUPDAYS function: Description, Usage, Syntax, Examples and Explanation

What is COUPDAYS function in Excel? COUPDAYS function is one of the Financial functions in Microsoft Excel that returns the number of days in the coupon period that contains the settlement date. Syntax of COUPDAYS function COUPDAYS(settlement, maturity, frequency, [basis]) The COUPDAYS function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the date after…

COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation

What is COUPDAYSNC function in Excel? COUPDAYSNC function is one of the Financial functions in Microsoft Excel that returns the number of days from the settlement date to the next coupon date. Syntax of COUPDAYSNC function COUPDAYSNC(settlement, maturity, frequency, [basis]) The COUPDAYSNC function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the date after…

COUPNCD function: Description, Usage, Syntax, Examples and Explanation

What is COUPNCD function in Excel? COUPNCD function is one of the Financial functions in Microsoft Excel that returns a number that represents the next coupon date after the settlement date. Syntax of COUPNCD function COUPNCD(settlement, maturity, frequency, [basis]) The COUPNCD function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the date after the…

COUPNUM function: Description, Usage, Syntax, Examples and Explanation

What is COUPNUM function in Excel? COUPNUM function is one of the Financial functions in Microsoft Excel that returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon. Syntax of COUPNUM function COUPNUM(settlement, maturity, frequency, [basis]) The COUPNUM function syntax has the following arguments: Settlement: The security’s settlement date. The security…

CUMIPMT function: Description, Usage, Syntax, Examples and Explanation

What is CUMIPMT function in Excel? CUMIPMT function is one of the Financial functions in Microsoft Excel that returns the cumulative interest paid on a loan between start_period and end_period. Syntax of CUMIPMT function CUMIPMT(rate, nper, pv, start_period, end_period, type) The CUMIPMT function syntax has the following arguments: Rate: The interest rate. Nper: The total number of payment periods. Pv: The…

CUMPRINC function: Description, Usage, Syntax, Examples and Explanation

What is CUMPRINC function in Excel? CUMPRINC function is one of the Financial functions in Microsoft Excel that returns the cumulative principal paid on a loan between start_period and end_period. Syntax of CUMPRINC function CUMPRINC(rate, nper, pv, start_period, end_period, type) The CUMPRINC function syntax has the following arguments: Rate: The interest rate. Nper:  The total number of payment periods. Pv: The…

DB function: Description, Usage, Syntax, Examples and Explanation

What is DB function in Excel? DB function is one of the Financial functions in Microsoft Excel that returns the depreciation of an asset for a specified period using the fixed-declining balance method. Syntax of DB function DB(cost, salvage, life, period, [month]) The DB function syntax has the following arguments: Cost: The initial cost of the asset. Salvage    Required. The value at…

DDB function: Description, Usage, Syntax, Examples and Explanation

What is DDB function in Excel? DDB function is one of the Financial functions in Microsoft Excel that returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. Syntax of DDB function DDB(cost, salvage, life, period, [factor]) The DDB function syntax has the following arguments: Cost: The initial cost of…

COUPPCD function: Description, Usage, Syntax, Examples and Explanation

What is COUPPCD function in Excel? COUPPCD function is one of the Financial functions in Microsoft Excel that returns a number that represents the previous coupon date before the settlement date. Syntax of COUPPCD function COUPPCD(settlement, maturity, frequency, [basis]) The COUPPCD function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the date after the…

DOLLARDE function: Description, Usage, Syntax, Examples and Explanation

What is DOLLARDE function in Excel? DOLLARDE function is one of the Financial functions in Microsoft Excel that converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number. Fractional dollar numbers are sometimes used for security prices. The fraction part of the value is divided…

DOLLARFR function: Description, Usage, Syntax, Examples and Explanation

What is DOLLARFR function in Excel? DOLLARFR function is one of the Financial functions in Microsoft Excel that uses the DOLLARFR to convert decimal numbers to fractional dollar numbers, such as securities prices. Syntax of DOLLARFR function DOLLARFR(decimal_dollar, fraction) The DOLLARFR function syntax has the following arguments: Decimal_dollar:  A decimal number. Fraction: The integer to use in the denominator of a…

EFFECT function: Description, Usage, Syntax, Examples and Explanation

What is EFFECT function in Excel? EFFECT function is one of the Financial functions in Microsoft Excel that returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. Syntax of EFFECT function EFFECT(nominal_rate, npery) The EFFECT function syntax has the following arguments: Nominal_rate: The nominal interest rate. Npery: The number of…

FV function: Description, Usage, Syntax, Examples and Explanation

What is FV function in Excel? FV function is one of the Financial functions in Microsoft Excel that calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment. Syntax of FV function FV(rate,nper,pmt,[pv],[type]) For a more complete description of the arguments in FV…

IPMT function: Description, Usage, Syntax, Examples and Explanation

What is IPMT function in Excel? IPMT function is one of the Financial functions in Microsoft Excel that returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. Syntax of IPMT function IPMT(rate, per, nper, pv, [fv], [type]) The IPMT function syntax has the following arguments: Rate: The interest rate…

FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation

What is FVSCHEDULE function in Excel? FVSCHEDULE function is one of the Financial functions in Microsoft Excel that 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 of FVSCHEDULE function FVSCHEDULE(principal, schedule) The FVSCHEDULE function syntax has…

INTRATE function: Description, Usage, Syntax, Examples and Explanation

What is INTRATE function in Excel? INTRATE function is one of the Financial functions in Microsoft Excel that returns the interest rate for a fully invested security. Syntax of INTRATE function INTRATE(settlement, maturity, investment, redemption, [basis]) The INTRATE function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the date after the issue date when…

ISPMT function: Description, Usage, Syntax, Examples and Explanation

What is ISPMT function in Excel? ISPMT function is one of the Financial functions in Microsoft Excel that calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments. Syntax of ISPMT function ISPMT(rate, per, nper, pv) The ISPMT function syntax has the following arguments: Argument Description Rate Required. The interest rate for…

MDURATION function: Description, Usage, Syntax, Examples and Explanation

What is MDURATION function in Excel? MDURATION function is one of the Financial functions in Microsoft Excel that returns the modified Macauley duration for a security with an assumed par value of $100. Syntax of MDURATION function MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) The MDURATION function syntax has the following arguments: Settlement:  The security’s settlement date. The security settlement date is…

MIRR function: Description, Usage, Syntax, Examples and Explanation

What is MIRR function in Excel? MIRR function is one of the Financial functions in Microsoft Excel that 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 of MIRR function MIRR(values, finance_rate, reinvest_rate) The MIRR function syntax has the…

NPER function: Description, Usage, Syntax, Examples and Explanation

What is NPER function in Excel? NPER function is one of the Financial functions in Microsoft Excel that returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Syntax of NPER function NPER(rate,pmt,pv,[fv],[type]) The NPER function syntax has the following arguments: Rate: The interest rate per period. Pmt: The payment made each period;…

NPV function: Description, Usage, Syntax, Examples and Explanation

What is NPV function in Excel? NPV function is one of the Financial functions in Microsoft Excel that 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 of NPV function NPV(rate,value1,[value2],…) The NPV function syntax has the following arguments: Rate: The rate of discount…

ODDFPRICE function: Description, Usage, Syntax, Examples and Explanation

What is ODDFPRICE function in Excel? ODDFPRICE function is one of the Financial functions in Microsoft Excel that returns the price per $100 face value of a security having an odd (short or long) first period. Syntax of ODDFPRICE function ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis]) The ODDFPRICE function syntax has the following arguments: Settlement: The security’s settlement…

NOMINAL function: Description, Usage, Syntax, Examples and Explanation

What is NOMINAL function in Excel? NOMINAL function is one of the Financial functions in Microsoft Excel that returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year. Syntax of NOMINAL function NOMINAL(effect_rate, npery) The NOMINAL function syntax has the following arguments: Effect_rate: The effective interest rate. Npery: The number of compounding periods…

ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation

What is ODDFYIELD function in Excel? ODDFYIELD function is one of the Financial functions in Microsoft Excel that returns the yield of a security that has an odd (short or long) first period. Syntax of ODDFYIELD function ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis]) The ODDFYIELD function syntax has the following arguments: Settlement: The security’s settlement date. The security…

ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation

What is ODDLPRICE function in Excel? ODDLPRICE function is one of the Financial functions in Microsoft Excel that returns the price per $100 face value of a security having an odd (short or long) last coupon period. Syntax of ODDLPRICE function ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis]) The ODDLPRICE function syntax has the following arguments: Settlement: The security’s settlement…

ODDLYIELD function: Description, Usage, Syntax, Examples and Explanation

What is ODDLYIELD function in Excel? ODDLYIELD function is one of the Financial functions in Microsoft Excel that returns the yield of a security that has an odd (short or long) last period. Syntax of ODDLYIELD function ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis]) The ODDLYIELD function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement…

DISC function: Description, Usage, Syntax, Examples and Explanation

What is DISC function in Excel? DISC function is one of the Financial functions in Microsoft Excel that returns the discount rate for a security. Syntax of DISC function DISC(settlement, maturity, pr, redemption, [basis]) The DISC function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is the date after the issue date when the security…

PPMT function: Description, Usage, Syntax, Examples and Explanation

What is PPMT function in Excel? PPMT function is one of the Financial functions in Microsoft Excel that returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate. Syntax of PPMT function PPMT(rate, per, nper, pv, [fv], [type]) The PPMT function syntax has the following arguments: Rate: The…

PRICE function: Description, Usage, Syntax, Examples and Explanation

What is PRICE function in Excel? PRICE function is one of the Financial functions in Microsoft Excel that returns the price per $100 face value of a security that pays periodic interest. Syntax of PRICE function PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]) The PRICE function syntax has the following arguments: Settlement: The security’s settlement date. The security settlement date is…

PMT function: Description, Usage, Syntax, Examples and Explanation

What is PMT function in Excel? PMT function is one of the Financial functions in Microsoft Excel that calculates the payment for a loan based on constant payments and a constant interest rate.Use the Excel Formula Coach to figure out a monthly loan payment. At the same time, you’ll learn how to use the PMT function in a formula. Syntax of PMT function PMT(rate,…

PRICEDISC function: Description, Usage, Syntax, Examples and Explanation

What is PRICEDISC function in Excel? PRICEDISC function is one of Financial functions in Microsoft Excel that returns the price per $100 face value of a discounted security. Syntax of PRICEDISC function PRICEDISC(settlement, maturity, discount, 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…

PRICEMAT function: Description, Usage, Syntax, Examples and Explanation

What is PRICEMAT function in Excel? PRICEMAT function is one of Financial functions in Microsoft Excel that returns the price per $100 face value of a security that pays interest at maturity. Syntax of PRICEMAT function PRICEMAT(settlement, maturity, issue, rate, yld, [basis]) Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example,…