Chapter 15. Financial Functions

The basic financial calculations in Microsoft Excel can be divided into the following areas:

  • Simple interest calculation. Simple interest calculation is characterized by the fact that the interest is not added to the capital at the due date. This kind of calculation is mostly used for periods shorter than a year (sometimes only a few days).

    Financial mathematics recognizes many different interest terms. In every case, the interest is the price for borrowed (borrowing rate) or lent (loan interest) capital. The price is based on the interest rate for 100 monetary units or one monetary unit. The interest rate always relates to a certain period (usually an entire year). To denote a period of a year, p.a. (per annum) is added.

    You will encounter two different basic interest calculations over and over in this chapter: anticipative interest yield and interest yield in arrears.

    Anticipative interest yield is paid for the capital due at the end, calculated based on the interest rate and paid at the beginning of the period. For interest yield in arrears, the interest is calculated and paid at the end of the interest period for the capital it started with.

  • Compound interest calculation. In this case, interest is added to the capital at maturity. This results in compound interest. Because Excel formulas are used frequently, it is assumed that the interest rate doesn’t change in the entire given period.

  • Annuity calculation. An annuity is a periodic payment of the same amount. All functions available in Excel assume that the payment date for the annuity is the same as the interest date. The only difference is between anticipative annuities and annuities in arrears, which are paid at the end of the period.

  • Repayment calculation. Financial mathematics uses at least three basic forms for the repayment of a loan: repayment via a single payment at the end, repayment via installments in the same amounts, and repayment via annuity payments for which the repayment amount plus interest stays the same. The last type is basically a type of annuity calculation.

    The Excel functions for calculating repayments refer only to the first and the third type. This does not mean that you cannot reproduce other situations on worksheets by using many different means without using functions.

  • Price calculation. Calculations of the price and yield and the overall return on an investment are an especially complex area of financial mathematics. Many of the integrated functions focus on these areas. The exchange rate is always defined as the relative cash value of future payments after the accrued interest is subtracted, if necessary. The rate of return is the figure (as an interest rate) that implements an actual market interest rate.

  • Investment calculation. Investments are often calculated with a static or dynamic investment analysis. This might include cost/revenue comparisons as well as the amortization calculation. These are based on cost and payment calculations. Dynamic methods consider the compound interest and evaluate deposits and disbursements. Excel provides several functions for dynamic investment calculations (capital value method and internal interest rate method).

  • Amortization calculation. This is important for understanding financial mathematics processes: Payments are not only defined by the amount but also by the payment date. It makes a difference whether a debtor pays his debt today or a year from today. The longer it takes to pay the debt back, the higher the amount due; interest is added to the debt. Sometimes this principle can be defined the other way around: Late money is worth little. This has nothing to do with inflation. From a financial point of view, it is irrelevant whether $110 is repaid in a year or $100 is repaid today (based on an annual interest rate of 10 percent).

    Therefore, amortization calculations are not really part of financial mathematics. Traditionally, amortization calculations are discussed in textbooks, and Excel provides the corresponding functions in a separate group. The meaning of the depreciation methods provided by Excel varies according to country/region because depreciation is regulated by the country’s specific tax laws.

Important

In the standard Microsoft Office Excel 2003 installation, most financial mathematics functions are not easily accessible. Before you can use the Analysis Functions group, you need to enable it by selecting Tools/Add-Ins in the Add-Ins dialog box (see Figure 15-1). This does not apply to Excel 2007 and Excel 2010.

When it comes to financial functions (see Table 15-1), the descriptions in Excel Help are not always the descriptions of the financial functions themselves. Therefore, you should compare it to the background information in this chapter and, if necessary, use the examples and consult additional literature for clarification.

In the Add-Ins dialog box, you need to enable the Analysis ToolPak.

Figure 15-1. In the Add-Ins dialog box, you need to enable the Analysis ToolPak.

Table 15-1. Overview of the Financial Functions

Function

Description

ACCRINT()

Calculates the accrued interest of a debt due in full intra-annually or the accrued interest of a fixed-interest security with periodic interest payments

ACCRINTM()

Calculates the accrued interest of a debt due in full intra-annually or the accrued interest of a fixed-interest security with one interest payment per year

AMORDEGRC()

Calculates the depreciation amount based on the French accounting system

AMORLINC()

Calculates the linear depreciation amount for assets

COUPDAYBS()

Determines the number of days that have passed between the last interest payment and the change of ownership of a fixed-interest security with regular coupon dates

COUPDAYS()

Determines the number of days of the interest period into which the change of ownership of a fixed-interest security with regular coupon dates falls

COUPDAYSNC()

Determines the number of days between the day on which the change of ownership of a fixed-interest security with regular coupon dates took place and the next coupon date

COUPNCD()

Determines the date of the first coupon interest payment after a fixed-interest security with regular coupon dates has changed ownership

COUPNUM()

Determines the number of interest payments (coupon dates) that the new owner is facing after buying a fixed-interest security with regular coupon dates

COUPPCD()

Determines the date of the latest interest payment before the change of ownership of a fixed-interest security with regular coupon dates

CUMIPMT()

Calculates the accrued interest that is paid between two points in time when a loan is repaid as an annuity loan

CUMPRINC()

Calculates the part of the repayment that is paid between two points in time when a loan is repaid as an annuity loan

DB()

Calculates the depreciation amounts using a geometrically degressive depreciation method

DDB()

Calculates the depreciation amounts by using a multiple-rate depreciation method

DISC()

Calculates the anticipative interest rate of a given cash value or final value and given duration

DOLLARDE()

Converts the decimal places of a number that is interpreted as the numerator of a fraction into a decimal number, if the denominator is given

DOLLARFR()

Converts the decimal places of a decimal number into the numerator of a fraction with a given denominator and displays it in the decimal places

DURATION()

Calculates the (average) capital commitment of a fixed-interest security

EFFECT()

Calculates the equivalent effective annual interest rate from a nominal interest rate that is equally divided into several periods of the same length within one year

FV()

Calculates the final value of a regular payment flow, taking into consideration possible single payments at the beginning of the period in question, according to the finance mathematical benefit principle

FVSCHEDULE()

Calculates the final value for capital with variable period interest rates

INTRATE()

Determines the repayment amount of a deposit resulting from a simple anticipative deduction of accrued interest

IPMT()

Determines the part of an annuity that is used for the interest payment of a loan that is repaid according to the principle of annuity repayment

IRR()

Calculates a par value of the dynamic investment calculation: the internal interest rate

ISPMT()

Calculates the sum of interest that accrues for a certain part of the year with a given annual interest rate and simple yield in arrears

MDURATION()

Calculates the figure known as modified duration for fixed-interest securities

MIRR()

Calculates the internal yield of an investment while evaluating the negative period surpluses with a different interest rate as positive

NOMINAL()

Calculates the nominal interest rate that in a finance mathematically correct way leads to equivalence from a given effective interest rate

NPER()

Determines the duration of a process of the compound interest rate, annuity calculation, or repayment calculation

NPV()

Calculates the net cash value of future surplus periods (cash flow) of an investment based on a given interest rate used for the calculation

ODDFPRICE()

Calculates the price of a fixed-interest security while considering a first interest period

ODDFYIELD()

Calculates the yield of a fixed-interest security for the duration from the settlement day to the due date

ODDLPRICE()

Calculates the price of a fixed-interest security in a final interest period

ODDLYIELD()

Calculates the return of a fixed-interest security in a final interest period

PMT()

Returns the annuity amount of a process mapped to the annuity calculation or, in a repayment calculation, the annuity for a loan with annuity repayment

PPMT()

Determines the part of an annuity that is used for the repayment of a loan

PRICE()

Calculates the price of a fixed-interest security

PRICEDISC()

Calculates the disbursement amount of a simply discounted security with anticipative interest yield

PRICEMAT()

Returns the price (as a percent based on a nominal value) of a security that has a simple interest yield in arrears

PV()

Calculates the cash value of a regular payment flow, taking into consideration possible single payments at the end of the period in question, according to the finance mathematical benefit principle

RATE()

Determines the applicable interest rate for compound interest and annuity calculation

RECEIVED()

Calculates the equivalent interest rate in arrears for a security that has received a markdown (discount, disagio) for an intra-annual duration

SLN()

Calculates the depreciation amounts of capital assets by using the linear depreciation method

SYD()

Determines the depreciation amounts of an asset according to the arithmetic-degressive method

TBILLEQ()

Calculates the equivalent annual interest rate in arrears based on 365 days for a given anticipative annual interest rate based on 360 days

TBILLPRICE()

Returns the price of a discounted security as a percent, as if the value was 100 monetary units

TBILLYIELD()

Returns the yield of a discounted security as an annual interest rate in arrears

VDB()

Calculates the depreciation amounts for assets using the geometrically degressive depreciation method

XIRR()

Returns the internal interest rate for a series of not-necessarily periodic payments in the intra-annual scope

XNPV()

Returns the capital value for a series of not-necessarily periodic surpluses (as the difference between deposits and disbursements) in the intra-annual scope

YIELD()

Determines the yield of a fixed-interest security (loan/bond)

YIELDDISC()

Calculates the annual yield (as an interest rate in arrears) for a security that has received a markdown (discount, disagio) for an intra-annual duration

YIELDMAT()

Calculates the annual yield of a fixed-interest security in the intra-annual scope (without compound interest)

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
13.59.173.242