The following financial functions are provided by PipeDream 4. There is an example of each financial function in the PipeDream 4 Examples.
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.
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)
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.
Returns the calculated depreciation allowance on an asset at a specific period of time using the double declining balance method.
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 |
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.
Starting with 4.56, this function accepts an optional extra parameter, factor.
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.
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
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.
Returns the calculated internal rate of return of an initial series of expected returns, based on an initial guess.
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. |
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.
Returns the modified internal rate of return for a series of periodic cash flows.
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.
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.
Returns the net present value of a series of future receipts at a fixed periodic interest rate.
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∑1n ( vi ÷ (1 + interest)i )
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.
Returns the payment on a loan.
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) )
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.
Computes the present value of a series of equal payments discounted at a periodic interest rate for a term.
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 )
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.
Returns the periodic interest rate required for an investment at a present value to reach a future value within a given period of time.
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
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).
Computes the straight line depreciation allowance of an asset for any given period.
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 = (cost − salvage) ÷ life
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.
Determines the depreciation allowance of a given year using the sum-of-years’ digits method.
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 |
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.
Computes how many periods it will take regular equal payments at a fixed periodic interest rate to reach a future value.
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)
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.