Easy Explanation Notes with Examples

Financial Functions in Excel

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.

RATE function calculates the interest rate per period for an annuity.

DISC function calculates the discount rate for a security.

2- Price

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.

3- Yield

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.

5- Payment

FV function calculates the future value of an investment.

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.

PV function is used to get the present value/ current worth of the future cash flows that will receive at a future date.

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.

Depreciation

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.






Copyright © 2016 - 2018 Explainry.com | All Rights Reserved