How to Create a Constant Formula in Excel 2013
How to Describe a Custom Excel 2013 Function
How to Enter a Formula Using Cell Names in Excel 2013

Analysis ToolPak Add-In Financial Functions

By activating the Analysis ToolPak add-in with Excel 2013, you add a whole bunch of powerful financial functions to the Financial button’s drop-down menu on the Formulas tab of the Ribbon. The table shows all the financial functions that are added to the Insert Function dialog box when the Analysis ToolPak is activated. As you can see from this table, the Analysis ToolPak financial functions are varied and quite sophisticated.

Financial Functions in the Analysis ToolPak
Function What It Calculates
ACCRINT(issue,first_interest,settlement,rate,[ par],frequency,[basis],[calc_methd]) Calculates the accrued interest for a security that pays periodic interest.
ACCRINTM(issue,maturity,rate,[par],[basis]) Calculates the accrued interest for a security that pays interest at maturity.
AMORDEGRC(cost,date_purchased,first_period,salvage, period,rate,[basis]) and AMORLINC(cost,date_purchased,first_period, salvage,period,rate,[basis]) Used in French accounting systems for calculating depreciation. AMORDEGRC and AMORLINC return the depreciation for each accounting period. AMORDEGRC works like AMORLINC except that it applies a depreciation coefficient in the calculation that depends upon the life of the assets.
COUPDAYBS(settlement,maturity,frequency,[basis]) Calculates the number of days from the beginning of a coupon period to the settlement date.
COUPDAYS(settlement,maturity,frequency,[basis]) Calculates the number of days in the coupon period.
COUPDAYSNC(settlement,maturity,frequency,[basis]) Calculates the number of days from the settlement date to the next coupon date.
COUPNCD(settlement,maturity,frequency,[basis]) Calculates a number that represents the next coupon date after a settlement date.
COUPNUM(settlement,maturity,frequency,[basis]) Calculates the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.
COUPPCD(settlement,maturity,frequency,[basis]) Calculates a number that represents the previous coupon date before the settlement date.
CUMIPMT(rate,nper,pv,start_period,end_period, type) Calculates the cumulative interest paid on a loan between the start_period and end_period. The type argument is 0 when the payment is made at the end of the period and 1 when it’s made at the beginning of the period.
CUMPRINC(rate,nper,pv,start_period,end_period, type) Calculates the cumulative principal paid on a loan between the start_period and end_period. The type argument is 0 when the payment is made at the end of the period and 1 when it’s made at the beginning of the period.
DISC(settlement,maturity,pr,redemption,[ basis]) Calculates the discount rate for a security.
DOLLARDE(fractional_dollar,fraction) Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number.
DOLLARFR(decimal_dollar,fraction) Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction.
DURATION(settlement,maturity,coupon,yld, frequency,[basis]) Calculates the Macauley duration for an assumed par value of $100. (Duration is defined as the weighted average of the present value of the cash flows and is used as a measure of the response of a bond price to changes in yield.)
EFFECT(nominal_rate,npery) Calculates the effective annual interest rate given the nominal interest rate and the number of compounding periods per year.
INTRATE(settlement,maturity,investment,redemption,[ basis]) Calculates the interest rate for a fully invested security.
MDURATION(settlement,maturity,coupon,yld, frequency,[basis]) Calculates the modified Macauley duration for a security with an assumed part value of $100.
NOMINAL(effect_rate,npery) Calculates the nominal annual interest rate given the effect rate and the number of compounding periods per year.
ODDFPRICE(settlement,maturity,issue,first_coupon, rate,yld,redemption,frequency,[basis]) Calculates the price per $100 face value of a security having an odd (short or long) first period.
ODDFYIELD(settlement,maturity,issue,first_coupon, rate,pr,redemption,frequency,[basis]) Calculates the yield of a security that has an odd (short or long) first period.
ODDLPRICE(settlement,maturity, last_interest,rate,yld,redemption,frequency,[ basis]) Calculates the price per $100 face value of a security having an odd (short or long) last coupon period.
ODDLYIELD(settlement,maturity,last_interest, rate,pr,redemption,frequency,[basis]) Calculates the yield of a security that has an odd (short or long) last period.
PRICE(settlement,maturity,rate,yld,redemption,frequency,[ basis]) Calculates the price per $100 face value of a security that pays periodic interest.
PRICEDISC(settlement,maturity,discount,redemption,[ basis]) Calculates the price per $100 face value of a discounted security.
PRICEMAT(settlement,maturity,issue,rate,yld,[ basis]) Calculates the price per $100 face value of a security that pays interest at maturity.
RECEIVED(settlement,maturity,investment,discount,[ basis]) Calculates the amount received at maturity for a fully invested security.
TBILLEQ(settlement,maturity,discount) Calculates the bond-equivalent yield for a Treasury bill.
TBILLPRICE(settlement,maturity,discount) Calculates the price per $100 face value for a Treasury bill.
TBILLYIELD(settlement,maturity,pr) Calculates the yield for a Treasury bill.
XIRR(values,dates,[guess]) Calculates the internal rate of return for a schedule of cash flows that are not periodic.
XNPV(rate,values,dates) Calculates the net present value for a schedule of cash flows that are not periodic.
YIELD(settlement,maturity,rate,pr,redemption, frequency,[basis]) Calculates the yield on a security that pays periodic interest (used to calculate bond yield).
YIELDDISC(settlement,maturity,pr,redemption,[ basis]) Calculates the annual yield for a discounted security.
YIELDMAT(settlement,maturity,issue,rate,pr,[ basis]) Calculates the annual yield of a security that pays interest at maturity.

You may note that many of the Analysis ToolPak financial functions make use of an optional basis argument. This optional basis argument is a number between 0 and 4 that determines the day count basis to use in determining the fractional part of the year:

  • 0 (or omitted) to base it on the U.S. (NASD) method of 30/360

  • 1 to base the fraction on actual days/actual days

  • 2 to base the fraction on actual days/360

  • 3 to base the fraction on actual days/365

  • 4 to base the fraction on the European method of 30/360

For detailed information on the other required arguments in the Analysis ToolPak financial functions shown in this table, select the function from the Financial button’s drop-down list and then click the Help on This Function link in the lower-left corner of its Function Arguments dialog box.

blog comments powered by Disqus
Time Functions in Excel 2013
The PMT Function in Excel 2013
Excel Text Functions You'll Find Helpful
How to Calculate the Net Present Value in Excel 2013
How to Use the Database Functions in Excel 2013
Advertisement

Inside Dummies.com