Financial Functions

The following financial functions are provided by PipeDream. There is an example of each financial function in the PipeDream Examples.

CTERM

Returns the number of compounding periods (usually years) required for an investment at a present value to reach a future value to reach a future value earning a fixed interest rate.

Syntax:

CTERM(interest, future_value, present_value)

interest is the interest rate per period, expressed as a decimal fraction
future_value is the desired future value of the investment
present_value  is the present value of the investment

The formula underlying CTERM is:

periods = ln(future_value ÷ present_value) ÷ ln(1 + interest)

Example:

How long does it take to turn £1000 into £2000 at a fixed rate of 10% per year? The answer is calculated with the formula CTERM(0.10,2000,1000). The answer in this case is 7.27 years.

DDB

Returns the calculated depreciation allowance on an asset at a specific period of time using the double declining balance method.

Syntax:

DDB(cost, salvage , life, period, {, factor:number})

cost is the original value
salvage  is the value at the end of the life
life is the number of periods for which you want to calculate the depreciation allowance
period is the period for which the depreciation is being measured
factor is the rate at which the balance declines. Default is 2.0
Example:

You pay £5000 for a machine that will have a salvage value of £1000 at the end of 5 years. What is the depreciation in the first year? The formula to find out is DDB(5000,1000,5,1) which returns £2000, the depreciation allowance for the first year.

Revision:

Starting with 4.56, this function accepts an optional extra parameter, factor.

FV

Computes the future value of an investment based on a number of equal payments, each earning interest at a periodic rate, over a specified term.

Syntax:

FV(payment, interest, term)

payment is the amount of each individual payment
interest  is the interest rate, expressed as a decimal fraction
term is the number of periods during which payments will be made

FV uses the following formula to calculate future value:

future value = payment × ((1 + interest)term − 1) ÷ interest

Example:

You put £400 annually into an account which earns 5% per annum. What is the value of the account after 25 years? The formula FV(400,0.05,25) calculates the result, £19,090.84.

IRR

Returns the calculated internal rate of return of an initial series of expected returns, based on an initial guess.

Syntax:

IRR(guess, data)

guess  The guessed rate of return, entered as a decimal fraction
data The initial investment should be entered as a negative number in the first cell in the range or element of the array. Further cells in the range or elements of the array should contain positive numbers, the expected returns on the investment.
Example:

Cell A1 contains the initial investment -5000 (note that this is entered as a negative number because you are spending it). Cells A2 to A14 each contain the number 500, representing payments returned on the investment. Cell A16 contains the number 0.01, a first guess for the internal rate of return (1%). The formula IRR(A16,A1A14) displays the result 0.04, the internal percentage rate of return (4%) for the cash flows in column A.

MIRR

Returns the modified internal rate of return for a series of periodic cash flows.

Syntax:

MIRR(values:array_or_range, finance_rate, reinvest_rate)

values an array or a range of cells which contain numbers representing payments (negative numbers) and receipts (positive numbers) occurring at regular periods
finance_rate the interest rate you pay
reinvest_rate  the interest rate you receive on reinvested cash

MIRR considers both the cost of the investment and the interest received on reinvestment of the cash.

Example:

You are a potter just completing your third year in business. Three years ago, you borrowed £120,000 at 10% annual interest to buy a workshop and equipment. Your annual income for the past three years has been £39,000, £30,000, and £21,000. During these years, you reinvested your profits, earning 12% annually. If you enter the initial cost as a negative number in cell B1 and the annual incomes in cells B2, B3 and B4, to calculate the investment’s modified rate of return after 3 years, you would type MIRR(B1B4,.10,.12) which would return -0.04804 as the result.

NPV

Returns the net present value of a series of future receipts at a fixed periodic interest rate.

Syntax:

NPV(interest, range)

interest  is the interest rate
range is the range of cells containing the amounts of the receipts

It is assumed that the receipts are at equal intervals and take place at the end of the period.

The following formula is used to calculate net present value:

net present value = Σi=n ( vi ÷ (1 + interest)i )

Example:

Cells B10 to B14 each contain the number 120 each representing a cash flow. What is the net present value of the cash flows given a discount rate of 10%? The formula NPV(0.10,B10B14) displays the result £454.89, the present value of the future cash flows.

PMT

Returns the payment on a loan.

Syntax:

PMT(principal, interest, term)

Calculates the payment on a loan given:

principal  is the amount of the loan
interest is the rate of the interest
term is the term of the loan (number of periods)

To calculate monthly payments given an annual percentage rate, and a term in years, divide the interest rate by 12 and multiply the term by 12.

The following formula is used to calculate the payment:

payment = principal × ( interest ÷ (1 − (1 + interest)-term) )

Example:

What is the monthly payment on a loan of £150,000, given a 10% annual interest rate and a term of 20 years? The formula PMT(150000,0.10/12,20*12) returns the correct monthly payment, £1447.53.

PV

Computes the present value of a series of equal payments discounted at a periodic interest rate for a term.

Syntax:

PV(payment, interest , term)

payment is the regular payment made
interest is the rate of interest per period the payments will receive
term is the length of the investment in periods

The following formula is used to calculate the present value:

present value = payment × ( (1 − (1 + interest)-term) ÷ interest )

Example:

You are considering starting a trust fund for your grandson, where you pay £2000 a year for 30 years. Given that you could earn 12% by investing your money elsewhere, what is the present value of the trust fund, including the interest that you won’t earn? The formula PV(2000,0.12,30) returns £16110.37, the cash value of the trust fund in today’s pounds.

RATE

Returns the periodic interest rate required for an investment at a present value to reach a future value within a given period of time.

Syntax:

RATE(future_value, present_value, term)

future_value Future value
present_value  Present value.
Future and present values must be expressed in equivalent units (e.g. in the same currency)
term Period of the investment

The following formula is used to calculate the interest rate:

interest = ( (future_value ÷ present_value) 1/term ) − 1

Example:

25 years from now, you want to retire with £1,000,000 in the bank. You have £50,000 to put into a pension fund. How much interest (compounded monthly) will you need to earn to accumulate the million pounds? The answer is given by the formula RATE(1000000,50000,25*12) . The answer is 0.01, which you multiply by 12 to get the annual interest rate of 12% (0.12).

SLN

Computes the straight line depreciation allowance of an asset for any given period.

Syntax:

SLN(cost, salvage, life)

cost is the original value of the asset
salvage  is the predicted future value at the end of the depreciation period. Both cost and salvage must be presented in the same units (e.g. both in the same currency)
life is the period for which depreciation is being calculated

The result returned is the straight line depreciation of the asset over the specified period.

The following formula is used to calculate the depreciation:

depreciation = (costsalvage) ÷ life

Example:

You buy a computer for £500 which has a salvage value of £100 in five years. In any given year, the depreciation allowance is given by the formula SLN(500,100,5) which returns £80.00.

SYD

Determines the depreciation allowance of a given year using the sum-of-years’ digits method.

Syntax:

SYD(cost, salvage, life, period)

cost is the original value of the asset
salvage  is the predicted future value at the end of the depreciation period. Both cost and salvage must be presented in the same units (e.g. both in the same currency)
life is the number of periods in the depreciation period
period is the year of the life for which you wish to calculate the depreciation allowance
Example:

You purchase a £500 computer that has a salvage value of £100 after five years. The depreciation allowance for the first year is given by the formula SYD(500,100,5,1) which returns £133.33.

TERM

Computes how many periods it will take regular equal payments at a fixed periodic interest rate to reach a future value.

Syntax:

TERM(payment, interest, future_value )

payment The regular payment made each period
interest The interest rate received
future_value  The future value you want to achieve

The following formula is used to calculate the term:

term = ln(1 + (future_value × interest ÷ payment)) ÷ ln(1 + interest)

Example:

Given that you can invest £5000 a year into a pension fund, at 10% annual interest, how long will it take to accumulate £1,000,000? The formula TERM(5000,0.10,1000000) returns the answer, 31.94 years.