The following financial functions are provided. Note that there is an example of each financial function in the PipeDream Examples archive.
CTERM(interest,fv,pv)
Calculates the number of compounding periods required for an investment at a present value (pv) to reach a future value (fv) earning a fixed interest rate (interest).
For 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(cost,salvage,life,period)
Calculates the depreciation allowance on an asset at a specific period of time using the double declining balance method.
For 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.
FV(payment,interest,term)
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.
For 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(guess,range)
Calculates the internal rate of return of an investment, and series of calculations based on an initial guess.
For example, slot A1 contains the initial investment -5000 (entered as a
negative number because you are spending it). Slots A2 - A14 each contain the
number 500, representing payments returned on the investment. Slot A16 contains
the number .01, a first guess for the internal rate of return. The formula
IRR(A16,A1A14)
displays the result 0.04, the internal percentage
rate of return for the cash flows in column A.
MIRR(values_array,finance_rate,reinvest_rate)
Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of the cash. values_array is an array or a reference to slots which contain numbers. The numbers represent a series of payments (negative values) and income (positive values) occurring at regular periods. finance_rate is the interest rate you pay on the money used in the cash flows. reinvest_rate is the interest rate you receive on the cash flows as you reinvest them.
For 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 slot B1 and
the annual incomes in slots 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(interest,range)
Calculates the net present value of a series of future cash flows at a fixed period interest rate.
For example, slots B10 - 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(principal,interest,term)
Calculates the payment on a loan given the principal, periodic interest rate and number of terms.
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.
For 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(payment,interest,term)
Computes the present value of a series of equal payments discounted at a periodic interest rate for a term.
For 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(fv,pv,term)
Calculates the periodic interest required for an investment at a present value (pv) to reach a future value (fv) within a certain period of time (term).
For 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(cost,salvage,life)
Computes the straight line depreciation allowance of an asset for any given period, given the cost, the predicted salvage value and life of the asset.
For 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(cost,salvage,life,period)
Determines the depreciation allowance of a given year using the sum-of-years' digits method.
For 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(payment,interest,fv)
Computes the number of periods required for regular payments at a fixed periodic interest rate to reach a future value (fv).
For 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.