Financial Functions

The following financial functions are provided. Note that there is an example of each financial function in the PipeDream Examples archive.

CTERM

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

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

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

IRR(guess, range)

Calculates the internal rate of return of an investment, and series of calculations based on an initial guess.

For example, cell A1 contains the initial investment -5000 (entered as a negative number because you are spending it). Cells A2 - A14 each contain the number 500, representing payments returned on the investment. Cell 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

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 cells 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 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

NPV(interest, range)

Calculates the net present value of a series of future cash flows at a fixed period interest rate.

For example, cells 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

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

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

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

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

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

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.