-DURATION_ADD function - Microsoft Excel functions +

+DURATION function durations;fixed interest securities mw added two entries -DURATION_ADD +

DURATION

Calculates the duration of a fixed interest security in years.

- DURATION_ADD("Settlement"; "Maturity"; Coupon; Yield; Frequency; Basis) + DURATION("Settlement"; "Maturity"; Coupon; Yield; Frequency; Basis) Settlement is the date of purchase of the security. @@ -581,7 +580,7 @@

A security is purchased on 2001-01-01; the maturity date is 2006-01-01. The Coupon rate of interest is 8%. The yield is 9.0%. Interest is paid half-yearly (frequency is 2). Using daily balance interest calculation (basis 3) how long is the duration? - =DURATION_ADD("2001-01-01";"2006-01-01";0.08;0.09;2;3) + =DURATION("2001-01-01";"2006-01-01";0.08;0.09;2;3)

@@ -591,7 +590,7 @@ EFFECT function mw added one entry -EFFECT +

EFFECT

Returns the net annual interest rate for a nominal interest rate. Nominal interest refers to the amount of interest due at the end of a calculation period. Effective interest increases with the number of payments made. In other words, interest is often paid in installments (for example, monthly or quarterly) before the end of the calculation period.

@@ -610,7 +609,7 @@ EFFECT_ADD function mw changed "effective rates" -EFFECT_ADD +

EFFECT_ADD

Calculates the effective annual rate of interest on the basis of the nominal interest rate and the number of interest payments per annum.

@@ -631,7 +630,7 @@ DDB function mw corrected two typos and added "depreciations;..." -DDB +

DDB

Returns the depreciation of an asset for a specified period using the arithmetic-declining method. Use this form of depreciation if you require a higher initial depreciation value as opposed to linear depreciation. The depreciation value gets less with each period and is usually used for assets whose value loss is higher shortly after purchase (for example, vehicles, computers). Please note that the book value will never reach zero under this calculation type.

@@ -658,7 +657,7 @@ DB function mw added "depreciations;.." -DB +

DB

Returns the depreciation of an asset for a specified period using the fixed-declining balance method. This form of depreciation is used if you want to get a higher depreciation value at the beginning of the depreciation (as opposed to linear depreciation). The depreciation value is reduced with every depreciation period by the depreciation already deducted from the initial cost.

@@ -685,7 +684,7 @@ internal rates of return;regular payments mw changed "calculating;..." and "internal rates" -IRR +

IRR

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). If the payments take place at irregular intervals, use the XIRR function.

@@ -696,7 +695,7 @@ Guess (optional) is the estimated value. An iterative method is used to calculate the internal rate of return. If you can provide only few values, you should provide an initial guess to enable the iteration.

Under the assumption that cell contents are A1=-10000, A2=3500, A3=7600 and A4=1000, the formula =IRR(A1:A4) gives a result of 11,33%. - Because of the iterative method used, it is possible for IRR to fail and return Error 523, with "Error: Calculation does not converge" in the status bar. In that case, try another value for Guess. + Because of the iterative method used, it is possible for IRR to fail and return Error 523, with "Error: Calculation does not converge" in the status bar. In that case, try another value for Guess.

calculating; interests for unchanged amortization installments @@ -704,7 +703,7 @@ ISPMT function -ISPMT +

ISPMT

Calculates the level of interest for unchanged amortization installments.

ISPMT(Rate; Period; TotalPeriods; Invest) diff --git a/source/text/scalc/01/04060119.xhp b/source/text/scalc/01/04060119.xhp index fb8ad96dd8..ea6686d20a 100644 --- a/source/text/scalc/01/04060119.xhp +++ b/source/text/scalc/01/04060119.xhp @@ -27,7 +27,7 @@ -Financial Functions Part Two +

Financial Functions Part Two

@@ -38,7 +38,7 @@ PPMT function -PPMT +

PPMT

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.

PPMT(Rate; Period; NPer; PV; FV; Type) @@ -70,7 +70,7 @@ CUMPRINC function mw added two entries -CUMPRINC +

CUMPRINC

Returns the cumulative interest paid for an investment period with a constant interest rate.

CUMPRINC(Rate; NPer; PV; S; E; Type) @@ -95,7 +95,7 @@ CUMPRINC_ADD function -CUMPRINC_ADD +

CUMPRINC_ADD

Calculates the cumulative redemption of a loan in a period.

@@ -128,7 +128,7 @@ CUMIPMT function -CUMIPMT +

CUMIPMT

Calculates the cumulative interest payments, that is, the total interest, for an investment based on a constant interest rate.

CUMIPMT(Rate; NPer; PV; S; E; Type) @@ -153,7 +153,7 @@ CUMIPMT_ADD function -CUMIPMT_ADD +

CUMIPMT_ADD

Calculates the accumulated interest for a period.

@@ -186,7 +186,7 @@ sales values;fixed interest securities mw added two entries -PRICE +

PRICE

Calculates the market value of a fixed interest security with a par value of 100 currency units as a function of the forecast yield.

PRICE(Settlement; Maturity; Rate; Yield; Redemption; Frequency; Basis) @@ -205,7 +205,7 @@

A security is purchased on 1999-02-15; the maturity date is 2007-11-15. The nominal rate of interest is 5.75%. The yield is 6.5%. The redemption value is 100 currency units. Interest is paid half-yearly (frequency is 2). With calculation on basis 0, the price is as follows: -=PRICE("1999-02-15"; "2007-11-15"; 0.0575; 0.065; 100; 2; 0) returns 95.04287. +=PRICE("1999-02-15"; "2007-11-15"; 0.0575; 0.065; 100; 2; 0) returns 95.04287.

PRICEDISC function @@ -213,7 +213,7 @@ sales values;non-interest-bearing securities mw added two entries -PRICEDISC +

PRICEDISC

Calculates the price per 100 currency units of par value of a non-interest- bearing security.

PRICEDISC(Settlement; Maturity; Discount; Redemption; Basis) @@ -228,14 +228,14 @@

A security is purchased on 1999-02-15; the maturity date is 1999-03-01. Discount in per cent is 5.25%. The redemption value is 100. When calculating on basis 2 the price discount is as follows: -=PRICEDISC("1999-02-15"; "1999-03-01"; 0.0525; 100; 2) returns 99.79583. +=PRICEDISC("1999-02-15"; "1999-03-01"; 0.0525; 100; 2) returns 99.79583.

PRICEMAT function prices;interest-bearing securities mw added one entry -PRICEMAT +

PRICEMAT

Calculates the price per 100 currency units of par value of a security, that pays interest on the maturity date.

PRICEMAT(Settlement; Maturity; Issue; Rate; Yield; Basis) @@ -253,18 +253,18 @@

Settlement date: February 15 1999, maturity date: April 13 1999, issue date: November 11 1998. Interest rate: 6.1 per cent, yield: 6.1 per cent, basis: 30/360 = 0. The price is calculated as follows: -=PRICEMAT("1999-02-15";"1999-04-13";"1998-11-11"; 0.061; 0.061;0) returns 99.98449888. +=PRICEMAT("1999-02-15";"1999-04-13";"1998-11-11"; 0.061; 0.061;0) returns 99.98449888.

calculating; durations durations;calculating -DURATION function +PDURATION function -DURATION +

PDURATION

Calculates the number of periods required by an investment to attain the desired value.

-DURATION(Rate; PV; FV) +PDURATION(Rate; PV; FV) Rate is a constant. The interest rate is to be calculated for the entire duration (duration period). The interest rate per period is calculated by dividing the interest rate by the calculated duration. The internal rate for an annuity is to be entered as Rate/12. @@ -282,7 +282,7 @@ SLN function mw added one entry -SLN +

SLN

Returns the straight-line depreciation of an asset for one period. The amount of the depreciation is constant during the depreciation period.

SLN(Cost; Salvage; Life) @@ -302,7 +302,7 @@ Macauley duration mw added one entry -MDURATION +

MDURATION

Calculates the modified Macauley duration of a fixed interest security in years.

MDURATION(Settlement; Maturity; Coupon; Yield; Frequency; Basis) @@ -319,7 +319,7 @@

A security is purchased on 2001-01-01; the maturity date is 2006-01-01. The nominal rate of interest is 8%. The yield is 9.0%. Interest is paid half-yearly (frequency is 2). Using daily balance interest calculation (basis 3) how long is the modified duration? -=MDURATION("2001-01-01"; "2006-01-01"; 0.08; 0.09; 2; 3) returns 4.02 years. +=MDURATION("2001-01-01"; "2006-01-01"; 0.08; 0.09; 2; 3) returns 4.02 years.

calculating;net present values @@ -327,7 +327,7 @@ NPV function -NPV +

NPV

Returns the present value of an investment based on a series of periodic cash flows and a discount rate. To get the net present value, subtract the cost of the project (the initial cash flow at time zero) from the returned value. If the payments take place at irregular intervals, use the XNPV function. @@ -348,7 +348,7 @@ NOMINAL function mw made "nominal interest rates;..." a one level entry -NOMINAL +

NOMINAL

Calculates the yearly nominal interest rate, given the effective rate and the number of compounding periods per year.

NOMINAL(EffectiveRate; NPerY) @@ -365,7 +365,7 @@ NOMINAL_ADD function -NOMINAL_ADD +

NOMINAL_ADD

Calculates the annual nominal rate of interest on the basis of the effective rate and the number of interest payments per annum.

@@ -384,7 +384,7 @@ converting;decimal fractions, into mixed decimal fractions mw added one entry -DOLLARFR +

DOLLARFR

Converts a quotation that has been given as a decimal number into a mixed decimal fraction.

DOLLARFR(DecimalDollar; Fraction) @@ -404,7 +404,7 @@ DOLLARDE function mw added one entry -DOLLARDE +

DOLLARDE

Converts a quotation that has been given as a decimal fraction into a decimal number.

DOLLARDE(FractionalDollar; Fraction) @@ -425,7 +425,7 @@ internal rates of return;modified mw added "internal rates of return;..." -MIRR +

MIRR

Calculates the modified internal rate of return of a series of investments.

MIRR(Values; Investment; ReinvestRate) @@ -444,7 +444,7 @@ yields, see also rates of return mw added two entries -YIELD +

YIELD

Calculates the yield of a security.

YIELD(Settlement; Maturity; Rate; Price; Redemption; Frequency; Basis) @@ -463,14 +463,14 @@

A security is purchased on 1999-02-15. It matures on 2007-11-15. The rate of interest is 5.75%. The price is 95.04287 currency units per 100 units of par value, the redemption value is 100 units. Interest is paid half-yearly (frequency = 2) and the basis is 0. How high is the yield? -=YIELD("1999-02-15"; "2007-11-15"; 0.0575 ;95.04287; 100; 2; 0) returns 0.065 or 6.50 per cent. +=YIELD("1999-02-15"; "2007-11-15"; 0.0575 ;95.04287; 100; 2; 0) returns 0.065 or 6.50 per cent.

YIELDDISC function rates of return;non-interest-bearing securities mw added one entry -YIELDDISC +

YIELDDISC

Calculates the annual yield of a non-interest-bearing security.

YIELDDISC(Settlement; Maturity; Price; Redemption; Basis) @@ -485,14 +485,14 @@

A non-interest-bearing security is purchased on 1999-02-15. It matures on 1999-03-01. The price is 99.795 currency units per 100 units of par value, the redemption value is 100 units. The basis is 2. How high is the yield? -=YIELDDISC("1999-02-15"; "1999-03-01"; 99.795; 100; 2) returns 0.052823 or 5.2823 per cent. +=YIELDDISC("1999-02-15"; "1999-03-01"; 99.795; 100; 2) returns 0.052823 or 5.2823 per cent.

YIELDMAT function rates of return;securities with interest paid on maturity mw added one entry -YIELDMAT +

YIELDMAT

Calculates the annual yield of a security, the interest of which is paid on the date of maturity.

YIELDMAT(Settlement; Maturity; Issue; Rate; Price; Basis) @@ -509,7 +509,7 @@

A security is purchased on 1999-03-15. It matures on 1999-11-03. The issue date was 1998-11-08. The rate of interest is 6.25%, the price is 100.0123 units. The basis is 0. How high is the yield? -=YIELDMAT("1999-03-15"; "1999-11-03"; "1998-11-08"; 0.0625; 100.0123; 0) returns 0.060954 or 6.0954 per cent. +=YIELDMAT("1999-03-15"; "1999-11-03"; "1998-11-08"; 0.0625; 100.0123; 0) returns 0.060954 or 6.0954 per cent.

calculating;annuities @@ -517,7 +517,7 @@ PMT function -PMT +

PMT

Returns the periodic payment for an annuity with constant interest rates.

PMT(Rate; NPer; PV; FV; Type) @@ -545,7 +545,7 @@ annual return on treasury bills mw changed "treasury bills;..." and added one entry -TBILLEQ +

TBILLEQ

Calculates the annual return on a treasury bill. A treasury bill is purchased on the settlement date and sold at the full par value on the maturity date, that must fall within the same year. A discount is deducted from the purchase price.

TBILLEQ(Settlement; Maturity; Discount) @@ -558,7 +558,7 @@

Settlement date: March 31 1999, maturity date: June 1 1999, discount: 9.14 per cent. The return on the treasury bill corresponding to a security is worked out as follows: -=TBILLEQ("1999-03-31";"1999-06-01"; 0.0914) returns 0.094151 or 9.4151 per cent. +=TBILLEQ("1999-03-31";"1999-06-01"; 0.0914) returns 0.094151 or 9.4151 per cent.

TBILLPRICE function @@ -566,7 +566,7 @@ prices;treasury bills mw added two entries -TBILLPRICE +

TBILLPRICE

Calculates the price of a treasury bill per 100 currency units.

TBILLPRICE(Settlement; Maturity; Discount) @@ -579,7 +579,7 @@

Settlement date: March 31 1999, maturity date: June 1 1999, discount: 9 per cent. The price of the treasury bill is worked out as follows: -=TBILLPRICE("1999-03-31";"1999-06-01"; 0.09) returns 98.45. +=TBILLPRICE("1999-03-31";"1999-06-01"; 0.09) returns 98.45.

TBILLYIELD function @@ -587,7 +587,7 @@ rates of return of treasury bills mw added two entries -TBILLYIELD +

TBILLYIELD

Calculates the yield of a treasury bill.

TBILLYIELD(Settlement; Maturity; Price) @@ -600,7 +600,7 @@

Settlement date: March 31 1999, maturity date: June 1 1999, price: 98.45 currency units. The yield of the treasury bill is worked out as follows: -=TBILLYIELD("1999-03-31";"1999-06-01"; 98.45) returns 0.091417 or 9.1417 per cent. +=TBILLYIELD("1999-03-31";"1999-06-01"; 98.45) returns 0.091417 or 9.1417 per cent.

Back to Financial Functions Part One -- cgit

Financial Functions Part One

AMORDEGRC

AMORLINC

ACCRINT

ACCRINTM

RECEIVED

PV

SYD

DISC