Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

OpenOffice Calc User Guide
Previous Page Home Next Page

A note about interest rates

You can enter interest rates one of two ways.

  • As a decimal. To enter an interest rate as a decimal, divide it by 100 before entering it into a function. For example, to compute a loan with a 3.25% interest rate, enter .0325 into the function.

  • As a percentage. To enter an interest rate as a percentage, type in the interest rate followed by the % key. For example, to compute a loan with a 3.25% interest rate, enters 3.25% into the function.

Either way will work. However, if you enter it as 3.25, the function will treat it as a 325% interest rate.

Accounting systems vary in the number of days in a month or a year used in calculations. The following table gives the integers used for the basis parameter used in some of the financial analysis functions.

Table 11: Basis calculation types

Basis

Calculation

0 or missing

US method (NASD), 12 months of 30 days each.

1

Exact number of days in months, exact number of days in year.

2

Exact number of days in month, year has 360 days.

3

Exact number of days in month, year has 365 days.

4

European method, 12 months of 30 days each.

Table 12: Financial analysis functions

Syntax

Description

*ACCRINT(issue; first_interest; settlement; rate; par; frequency; basis)

Calculates the accrued interest of a security in the case of periodic payments. Issue is the issue date of the security. First_interest is the first interest date of the security. Settlement is the maturity date. Rate is the annual nominal rate of interest (coupon interest rate). Par is the par value of the security. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*ACCRINTM(issue; settlement; rate; par; basis)

Calculates the accrued interest of a security in the case of one-off payment at the settlement date. Issue is the issue date of the security. Settlement is the maturity date. Rate is the annual nominal rate of interest (coupon interest rate). Par is the par value of the security. Basis is chosen from a list of options and indicates how the year is to be calculated.

*AMORDEGRC(cost; date_purchased; first_period; salvage; period; rate; basis)

Calculates the amount of depreciation for a settlement period as degressive amortization. Unlike AMORLINC, a depreciation coefficient that is independent of the depreciable life is used here. Cost is the acquisition cost. Date_purchased is the date of acquisition. First_period is the end date of the first settlement period. Salvage is the salvage value of the capital asset at the end of the depreciable life. Period is the settlement period to be considered. Rate is the rate of depreciation. Basis is chosen from a list of options and indicates how the year is to be calculated.

*AMORLINC(cost; date_purchased; first_period; salvage; period; rate; basis)

Calculates the amount of depreciation for a settlement period as linear amortization. If the capital asset is purchased during the settlement period, the proportional amount of depreciation is considered. Cost is the acquisition cost. Date_purchased is the date of acquisition. First_period is the end date of the first settlement period. Salvage is the salvage value of the capital asset at the end of the depreciable life. Period is the settlement period to be considered. Rate is the rate of depreciation. Basis is chosen from a list of options and indicates how the year is to be calculated.

*COUPDAYBS(settlement; maturity; frequency; basis)

Returns the number of days from the first day of interest payment on a security until the settlement date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*COUPDAYS(settlement; maturity; frequency; basis)

Returns the number of days in the current interest period in which the settlement date falls. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*COUPDAYSNC(settlement; maturity; frequency; basis)

Returns the number of days from the settlement date until the next interest date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*COUPNCD(settlement; maturity; frequency; basis)

Returns the date of the first interest date after the settlement date, and formats the result as a date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*COUPNUM(settlement; maturity; frequency; basis)

Returns the number of coupons (interest payments) between the settlement date and the maturity date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*COUPPCD(settlement; maturity; frequency; basis)

Returns the date of the interest date prior to the settlement date, and formats the result as a date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

CUMIPMT(rate; NPER; PV; S; E; type)

Calculates the cumulative interest payments (the total interest) for an investment based on a constant interest rate. Rate is the periodic interest rate. NPER is the payment period with the total number of periods. NPER can also be a non-integer value. The rate and NPER must refer to the same unit, and thus both must be calculated annually or monthly. PV is the current value in the sequence of payments. S is the first period. E is the last period. Type is the due date of the payment at the beginning (1) or end (0) of each period.

*CUMIPMT_ADD(rate; NPER; PV; start_period; end_period; type)

Calculates the accumulated interest for a period. Rate is the interest rate for each period. NPER is the total number of payment periods. The rate and NPER must refer to the same unit, and thus both must be calculated annually or monthly. PV is the current value. Start_period the first payment period for the calculation. End_period the last payment period for the calculation. Type is the due date of the payment at the beginning (1) or end (0) of each period.

CUMPRINC(rate; NPER; PV; S; E; type)

Returns the cumulative interest paid for an investment period with a constant interest rate. Rate is the periodic interest rate. NPER is the payment period with the total number of periods. NPER can also be a non-integer value. The rate and NPER must refer to the same unit, and thus both must be calculated annually or monthly. PV is the current value in the sequence of payments. S is the first period. E is the last period. Type is the due date of the payment at the beginning (1) or end (0) of each period.

*CUMPRINC_ADD(rate; NPER; PV; start_period; end_period; type)

Calculates the cumulative redemption of a loan in a period. Rate is the interest rate for each period. NPER is the total number of payment periods. The rate and NPER must refer to the same unit, and thus both must be calculated annually or monthly. PV is the current value. Start period is the first payment period for the calculation. End period is the last payment period for the calculation. Type is the due date of the payment at the beginning (1) or end (0) of each period.

DB(cost; salvage; life; period; month)

Returns the depreciation of an asset for a specified period using the double-declining balance method. Cost is the initial cost of an asset. Salvage is the value of an asset at the end of the depreciation. Life defines the period over which an asset is depreciated. Period is the length of each period. The life must be entered in the same date unit as the depreciation period. Month (optional) denotes the number of months for the first year of depreciation.

DDB(cost; salvage; life; period; factor)

Returns the depreciation of an asset for a specified period using the arithmetic-declining method. Note that the book value will never reach zero under this calculation type. Cost fixes the initial cost of an asset. Salvage fixes the value of an asset at the end of its life. Life is the number of periods defining how long the asset is to be used. Period defines the length of the period. The period must be entered in the same time unit as the life. Factor (optional) is the factor by which depreciation decreases.

*DISC(settlement; maturity; price; redemption; basis)

Calculates the allowance (discount) of a security as a percentage. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Price is the price of the security per 100 currency units of par value. Redemption is the redemption value of the security per 100 currency units of par value. Basis is chosen from a list of options and indicates how the year is to be calculated.

*DOLLARDE(fractional _dollar; fraction)

Converts a quotation that has been given as a decimal fraction into a decimal number. Fractional_dollar is a number given as a decimal fraction. (In this number, the decimal value is the numerator of the fraction.) Fraction is a whole number that is used as the denominator of the decimal fraction.

*DOLLARFR(decimal _dollar; fraction)

Converts a quotation that has been given as a decimal number into a mixed decimal fraction. The decimal of the result is the numerator of the fraction that would have Fraction as the denominator. Decimal_dollar is a decimal number. Fraction is a whole number that is used as the denominator of the decimal fraction.

DURATION(rate; PV; FV)

Calculates the number of periods required by an investment to attain the desired value. Rate (a constant) is the interest rate to be calculated for the entire duration. Entering the interest rate divided by the periods per year, can calculate the interest after each period. PV is the present value. FV is the desired future value of the investment.

*DURATION_ADD (settlement; maturity; coupon; yield; frequency; basis)

Calculates the duration of a fixed interest security in years. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Coupon is the annual coupon interest rate (nominal rate of interest). Yield is the annual yield of the security. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*EFFECT_ADD(nominal _rate; Npery)

Calculates the effective annual rate of interest on the basis of the nominal interest rate and the number of interest payments per annum. Nominal interest refers to the amount of interest due at the end of a calculation period. Nominal_rate is the annual nominal rate of interest. Npery is the number of interest payments per year.

EFFECTIVE(NOM; P)

Calculates the effective annual rate of interest on the basis of the nominal interest rate and the number of interest payments per annum. Nominal interest refers to the amount of interest due at the end of a calculation period. NOM is the nominal interest. P is the number of interest payment periods per year.

FV(rate; NPER; PMT; PV; type)

Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Rate is the periodic interest rate. NPER is the total number of periods. PMT is the annuity paid regularly per period. PV (optional) is the present cash value of an investment. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.

*FVSCHEDULE(principal; schedule)

Calculates the accumulated value of the starting capital for a series of periodically varying interest rates. Principal is the starting capital. Schedule is a series of interest rates. Schedule has to be entered with cell references.

*INTRATE(settlement; maturity; investment; redemption; basis)

Calculates the annual interest rate that results when a security (or other item) is purchased at an investment value and sold at a redemption value with no interest being paid. Settlement is the date of purchase of the security. Maturity is the date on which the security is sold. Investment is the purchase price. Redemption is the selling price. Basis is chosen from a list of options and indicates how the year is to be calculated.

IPMT(rate; period; NPER; PV; FV; type)

Calculates the periodic amortization for an investment with regular payments and a constant interest rate. Rate is the periodic interest rate. Period is the period for which the compound interest is calculated. NPER is the total number of periods during which annuity is paid. Period=NPER, if compound interest for the last period is calculated. PV is the present cash value in sequence of payments. FV (optional) is the desired value (future value) at the end of the periods. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.

IRR(values; guess)

Calculates the internal rate of return for an investment. The values represent cash flow values at regular intervals; at least one value must be negative (payments), and at least one value must be positive (income). Values is an array containing the values. Guess (optional) is the estimated value. If you can provide only a few values, you should provide an initial guess to enable the iteration.

ISPMT(rate; period; total_periods; invest)

Calculates the level of interest for unchanged amortization installments. Rate sets the periodic interest rate. Period is the number of installments for calculation of interest. Total_periods is the total number of installment periods. Invest is the amount of the investment.

*MDURATION(settlement; maturity; coupon; yield; frequency; basis)

Calculates the modified Macauley duration of a fixed interest security in years. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Coupon is the annual nominal rate of interest (coupon interest rate) Yield is the annual yield of the security. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

MIRR(values; investment; reinvest_rate)

Calculates the modified internal rate of return of a series of investments. Values corresponds to the array or the cell reference for cells whose content corresponds to the payments. Investment is the rate of interest of the investments (the negative values of the array) Reinvest_rate is the rate of interest of the reinvestment (the positive values of the array).

NOMINAL(effective_rate; Npery)

Calculates the yearly nominal interest rate, given the effective rate and the number of compounding periods per year. Effective_rate is the effective interest rate Npery is the number of periodic interest payments per year.

*NOMINAL_ADD(effective_rate; Npery)

Calculates the yearly nominal rate of interest, given the effective rate and the number of compounding periods per year. Effective_rate is the effective annual rate of interest. Npery is the number of interest payments per year.

NPER(rate; PMT; PV; FV; type)

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Rate is the periodic interest rate. PMT is the constant annuity paid in each period. PV is the present value (cash value) in a sequence of payments. FV (optional) is the future value, which is reached at the end of the last period. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.

NPV(Rate; value_1; value_2; ... value_30)

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate. Rate is the discount rate for a period. Value_1; value_2;... value_30 are values representing deposits or withdrawals.

*ODDFPRICE(settlement; maturity; issue; first_coupon; rate; yield; redemption; frequency; basis)

Calculates the price per 100 currency units par value of a security, if the first interest date falls irregularly. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Issue is the date of issue of the security. First_coupon is the first interest date of the security. Rate is the annual rate of interest. Yield is the annual yield of the security. Redemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*ODDFYIELD(settlement; maturity; issue; first_coupon; rate; price; redemption; frequency; basis)

Calculates the yield of a security if the first interest date falls irregularly. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Issue is the date of issue of the security. First_coupon is the first interest period of the security. Rate is the annual rate of interest. Price is the price of the security. Redemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*ODDLPRICE(settlement; maturity; last_interest; rate; yield; redemption; frequency; basis)

Calculates the price per 100 currency units par value of a security, if the last interest date falls irregularly. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Last_interest is the last interest date of the security. Rate is the annual rate of interest. Yield is the annual yield of the security. Redemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*ODDLYIELD(settlement; maturity; last_interest; rate; price; redemption; frequency; basis)

Calculates the yield of a security if the last interest date falls irregularly. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Last_interest is the last interest date of the security. Rate is the annual rate of interest. Price is the price of the security. Redemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

PMT(rate; NPER; PV; FV; type)

Returns the periodic payment for an annuity with constant interest rates. Rate is the periodic interest rate. NPER is the number of periods in which annuity is paid. PV is the present value (cash value) in a sequence of payments. FV (optional) is the desired value (future value) to be reached at the end of the periodic payments. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.

PPMT(rate; period; NPER; PV; FV; type)

Returns for a given period the payment on the principal for an investment that is based on periodic and constant payments and a constant interest rate. Rate is the periodic interest rate. Period is the amortization period. NPER is the total number of periods during which annuity is paid. PV is the present value in the sequence of payments. FV (optional) is the desired (future) value. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.

*PRICE(settlement; maturity; rate; yield; redemption; frequency; basis)

Calculates the market value of a fixed interest security with a par value of 100 currency units as a function of the forecast yield. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Rate is the annual nominal rate of interest (coupon interest rate). Yield is the annual yield of the security. Redemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*PRICEDISC(settlement; maturity; discount; redemption; basis)

Calculates the price per 100 currency units of par value of a non-interest- bearing security. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Discount is the discount of a security as a percentage. Redemption is the redemption value per 100 currency units of par value. Basis is chosen from a list of options and indicates how the year is to be calculated.

*PRICEMAT(settlement; maturity; issue; rate; yield; basis)

Calculates the price per 100 currency units of par value of a security, that pays interest on the maturity date. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Issue is the date of issue of the security. Rate is the interest rate of the security on the issue date. Yield is the annual yield of the security. Basis is chosen from a list of options and indicates how the year is to be calculated.

PV(rate; NPER; PMT; FV; type)

Returns the present value of an investment resulting from a series of regular payments. Rate defines the interest rate per period. NPER is the total number of payment periods. PMT is the regular payment made per period. FV (optional) defines the future value remaining after the final installment has been made. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.

RATE(NPER; PMT; PV; FV; type; guess)

Returns the constant interest rate per period of an annuity. NPER is the total number of periods, during which payments are made (payment period). PMT is the constant payment (annuity) paid during each period. PV is the cash value in the sequence of payments. FV (optional) is the future value, which is reached at the end of the periodic payments. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. Guess (optional) determines the estimated value of the interest with iterative calculation.

*RECEIVED(settlement; maturity; investment; discount; basis)

Calculates the amount received that is paid for a fixed-interest security at a given point in time. Settlement is the date of purchase of the security. Maturity is the date on which the security matures. Investment is the purchase sum. Discount is the percentage discount on acquisition of the security. Basis is chosen from a list of options and indicates how the year is to be calculated.

RRI(P; PV; FV)

Calculates the interest rate resulting from the profit (return) of an investment. P is the number of periods needed for calculating the interest rate. PV is the present value (must be >0). FV is determines what is desired as the cash value of the deposit.

SLN(cost; salvage; life)

Returns the straight-line depreciation of an asset for one period. The amount of the depreciation is constant during the depreciation period. Cost is the initial cost of an asset. Salvage is the value of an asset at the end of the depreciation. Life is the depreciation period determining the number of periods in the depreciation of the asset.

SYD(cost; salvage; life; period)

Returns the arithmetic-declining depreciation rate. Use this function to calculate the depreciation amount for one period of the total depreciation span of an object. Arithmetic declining depreciation reduces the depreciation amount from period to period by a fixed sum. Cost is the initial cost of an asset. Salvage is the value of an asset after depreciation. Life is the period fixing the time span over which an asset is depreciated. Period defines the period for which the depreciation is to be calculated.

*TBILLEQ(settlement; maturity; discount)

Calculates the annual return on a treasury bill. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). (The settlement and maturity date must be in the same year.) Discount is the percentage discount on acquisition of the security.

*TBILLPRICE(settlement; maturity; discount)

Calculates the price of a treasury bill per 100 currency units. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Discount is the percentage discount upon acquisition of the security.

*TBILLYIELD(settlement; maturity; price)

Calculates the yield of a treasury bill. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Price is the price (purchase price) of the treasury bill per 100 currency units of par value.

VDB(cost; salvage; life; start; end; factor; type)

Returns the depreciation of an asset for a specified or partial period using a variable declining balance method. Cost is the initial value of an asset. Salvage is the value of an asset at the end of the depreciation. Life is the depreciation duration of the asset. Start is the start of the depreciation entered in the same date unit as the life. End is the end of the depreciation. Factor (optional) is the depreciation factor. FA=2 is double rate depreciation. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period.

*XIRR(values; dates; guess)

Calculates the internal rate of return for a list of payments which take place on different dates. The calculation is based on a 365 days per year basis, ignoring leap years. If the payments take place at regular intervals, use the IRR function. Values and dates are a series of payments and the series of associated date values entered as cell references. Guess (optional) is a guess for the internal rate of return. The default is 10%.

*XNPV(rate; values; dates)

Calculates the capital value (net present value) for a list of payments which take place on different dates. The calculation is based on a 365 days per year basis, ignoring leap years. If the payments take place at regular intervals, use the NPV function. Rate is the internal rate of return for the payments. Values and dates are a series of payments and the series of associated date values entered as cell references.

*YIELD(settlement; maturity; rate; price; redemption; frequency; basis)

Calculates the yield of a security. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Rate is the annual rate of interest. Price is the price (purchase price) of the security per 100 currency units of par value. Redemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

*YIELDDISC(settlement; maturity; price; redemption; basis)

Calculates the annual yield of a non-interest-bearing security. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Price is the price (purchase price) of the security per 100 currency units of par value. Redemption is the redemption value per 100 currency units of par value. Basis is chosen from a list of options and indicates how the year is to be calculated.

*YIELDMAT(settlement; maturity; issue; rate; price; basis)

Calculates the annual yield of a security, the interest of which is paid on the date of maturity. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Issue is the date of issue of the security. Rate is the interest rate of the security on the issue date. Price is the price (purchase price) of the security per 100 currency units of par value. Basis is chosen from a list of options and indicates how the year is to be calculated.

OpenOffice Calc User Guide
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire