Financial functions

Use the COUPDAYBS (coupon days before settlement) function to calculate the number of days from the beginning of the coupon period until its settlement date. The number returned includes both the first day of the period and the settlement date.

Use the COUPDAYS function to return the number of coupon days in the coupon period that contains the settlement date.

Use the COUPDAYSNC function to calculate the number of coupon days from the settlement date until the next coupon date. The number returned excludes the settlement date and includes the last day of the next coupon period.

The COUPNCD function calculates the next coupon date after a settlement date.

The COUPNUM function returns the number of coupons payable between a settlement and maturity date.

The COUPPCD function calculates the previous coupon date before a settlement date.

The CUMIPMT function calculates the cumulative interest paid on a loan over a period given equal payments made to the balance.

The CUMPRINC function calculates the amount of principal paid on a loan over a period, given consistent, equal payments.

You can use the DURATION function to calculate the Macauley duration for an assumed parity value of 100 monetary units.

The Macauley duration is the weighted average maturity of cash flows. That is, the weighted average distance to payment. It's used to measure a bond price's response to changes in yield. A higher Macauley duration value indicates a riskier investment.

The FV function calculates the future value of an investment. The future value is the lump sum or closing balance received at the end of an investment.

The IPMT function calculates the amount of interest to be paid on a loan in a given payment period. The function assumes a consistent interest rate and payment timings in each period.

The IRR function calculates the internal rate of return for a series of positive and negative transactions. It can be used either with all transactions over a timescale, or with specified transactions on certain dates.

You can use the MDURATION function to calculate the modified Macauley duration for an assumed parity value of 100 monetary units.

The modified Macauley duration expresses the measurable change in the value of a bond in response to a change in interest rates. The result represents the effect that a 1% change in interest rates will have on the price of a bond.

The NPER function calculates the required number of periods to achieve a certain value for a loan or investment. This is based on a given interest rate, consistent payments, and opening and closing balance.

The NPV function calculates the net present value for a series of positive and negative transactions with a constant interest rate.

The PMT function calculates the payments due for a loan or annuity over a specified number of periods, given a consistent interest rate and payment amount.

The PPMT function calculates how much of a payment is allocated to its principal part rather than interest. The function assumes a consistent interest rate and payment timings in each period.

The PRICE function calculates the price per 100 monetary units invested for a bond that pays periodic interest.

The PV function calculates the present value of an investment or the principal value of a loan.

The RATE function calculates the interest rate for a loan or investment based on length, payments, and present and future value.

Use the YEARFRAC function to calculate the fraction of a year between two dates (inclusive of the start date, exclusive of the end date).

The function uses a basis (day-count convention) to count the number of days between these dates, and then divide that number by the basis.

Use this function to calculate the yield to maturity (YTM) of a bond.