Analysis ToolPak Add-In Financial Functions

By Greg Harvey

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.