EnterpriseSheet formulas

Document -> EnterpriseSheet formula -> Financial Functions

Financial Functions

Formulas | Description | Examples |
---|---|---|

COUPDAYS | Syntax: COUPDAYS(settlement, maturity, frequency, [basis])Returns the number of days in the coupon period that contains the settlement date. |
=COUPDAYS(A2,A3,A4,2,1) |

COUPNCD | Syntax: COUPNCD(settlement, maturity, frequency, [basis])Returns a number that represents the next coupon date before the settlement date. |
=COUPNCD(A2,A3,A4,2,1) |

COUPNUM | Syntax: COUPNUM(settlement, maturity, frequency, [basis])Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon. |
= COUPNUM(A2,A3,A4,2,1) |

COUPPCD | Syntax: COUPPCD(settlement, maturity, frequency, [basis])Returns a number that represents the previous coupon date before the settlement date. |
=COUPPCD(A2,A3,A4,2,1) |

DB | Syntax: DB(cost, salvage, life, period, [number_months])Returns the depreciation of an asset for a given time period based on the fixed-declining balance method. cost is the original cost of the asset. salvage is the salvage value after the asset has been fully depreciated. life is the useful life of the asset or the number of periods that you will be depreciating the asset. period is the period that you wish to calculate the depreciation for. Use the same units as for the life. number_months is optional. It is the number of months in the first year of depreciation. If this parameter is omitted, the DB function will assume that there are 12 months in the first year. |
=DB(A2,A3,A4,5, 7 ) =DB(10000,10000,6,5, 7 ) |

DDB | Syntax: DDB(cost, salvage, life, period, [factor])Returns the depreciation of an asset for a given time period based on the fixed-declining balance method. cost is the original cost of the asset. salvage is the salvage value after the asset has been fully depreciated. life is the useful life of the asset or the number of periods that you will be depreciating the asset. period is the period that you wish to calculate the depreciation for. Use the same units as for the life. factor is optional. It is the rate at which the balance declines. If this parameter is omitted, the DDB function will assume the factor to be 2. |
=DDB(3000,300,10,1,2) |

DOLLARDE | Syntax: DOLLARDE(fractional_dollar, fraction)Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number. |
=DOLLARDE(1.125, 8) |

DOLLARFR | Syntax: DOLLARFR(decimal_dollar, fraction)Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction. |
=DOLLARFR(1.125, 8) |

EFFECT | Syntax: EFFECT(nominal_rate, npery)This function returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. |
=EFFECT(A2,A3) |

FV | Syntax: FV(interest_rate, number_payments, payment, [PV], [Type])This function returns the future value of an investment based on an interest rate and a constant payment schedule. interest_rate is the interest rate for the investment. number_payments is the number of payments for the annuity. payment is the amount of the payment for each period. This should be entered as a negative value. PV is optional. It is the present value of the payments. Type is optional. It indicates when the payments are due. Type can be one of the following values: 0 - Payments are due at the end of the period. (default), 1 - Payments are due at the beginning of the period. |
=FV(6%/12, 10, -800, -2000) |

IPMT | Syntax: IPMT(interest_rate, period, number_payments, PV, [FV], [type])Returns the interest payment for an investment based on an interest rate and a constant payment schedule. period is the period to calculate the interest rate. It must be a value between 1 and number_payments. interest_rate is the interest rate for the loan. number_payments is the number of payments for the loan. PV is the present value or principal of the loan. FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PMT function assumes a FV value of 0. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
=IPMT(10%, 3, 5000, 20) |

IRR | Syntax: IRR(range, [estimated_irr])This function returns the internal rate of return for a series of cash flows represented by the numbers in values. The cash flows must occur at regular intervals, but do not have to be the same amounts for each interval. range is a range of cells that represent the series of cash flows. estimated_irr is optional. It is the your guess at the internal rate of return. |
=IRR(A1:A6) |

MIRR | Syntax: MIRR(range, finance_rate, reinvestment_rate)This function returns the modified internal rate of return for a series of cash flows. The internal rate of return is calculated by using both the cost of the investment and the interest received by reinvesting the cash. range is a range of cells that represent the series of cash flows. finance_rate is the interest rate that you pay on the cash flow amounts. reinvestment_rate is the interest rate that you receive on the cash flow amounts as they are reinvested. |
=MIRR(A1:A9,A10,12%) |

NPER | Syntax: NPER(interest_rate, payment, PV, [FV], [Type])This function returns the number of periods for an investment based on an interest rate and a constant payment schedule. interest_rate is the interest rate for the investment. payment is the amount of the payment for each period. This should be entered as a negative value. PV is the present value of the payments. FV is optional. It is the future value that you\'d like the investment to be after all payments have been made. If this parameter is omitted, the NPER function will assume a FV of 0. Type is optional. It indicates when the payments are due. Type can be one of the following values: 0 - Payments are due at the end of the period. (default), 1 - Payments are due at the beginning of the period. |
=NPER(12%/12, -100, 500, 10000) =NPER(A1/12, A2, A3, A4) |

NPV | Syntax: NPV(discount_rate, value1, value2, ... value_n)This function returns the net present value of an investment. discount_rate is the discount rate for the period. value1, value2, ... value_n are the future payments and income for the investment (ie: cash flows). |
=NPV(A1, A2:A8) |

PMT | Syntax: PMT(interest_rate, number_payments, PV, [FV], [type])Returns the payment amount for a loan based on an interest rate and a constant payment schedule. interest_rate is the interest rate for the loan. number_payments is the number of payments for the loan. PV is the present value or principal of the loan. FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PMT function assumes a FV value of 0. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
=PMT(6%, -100, 500) =PMT(a1, a2,a3) |

PPMT | Syntax: PPMT(interest_rate, period, number_payments, PV, [FV], [type])Returns the payment on the principal for a particular payment based on an interest rate and a constant payment schedule. interest_rate is the interest rate for the loan. period is the period used to determine how much principal has been repaid. Period must be a value between 1 and number_payments. number_payments is the number of payments for the loan. PV is the present value or principal of the loan. FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PPMT function assumes a FV value of 0. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
=PPMT(A1, A2, 10, A3) =PPMT(5%, 20, 8, 8000) |

PV | Syntax: PV(interest_rate, number_payments, payment, [FV], [Type])This function returns the present value of an investment based on an interest rate and a constant payment schedule. interest_rate is the interest rate for the investment. number_payments is the number of payments for the annuity. payment is the amount of the payment made each period. FV (optional) is the desired value (future value) to be reached at the end of the periodic payments. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. |
=PV(0.5, 50, 600, 1, 0) |

RATE | Syntax: RATE(NPER, PMT, PV, [FV], [type], [guess])Returns the constant interest rate per period of an annuity. NPER is the total number of periods, during which payments are made (payment period). PMT is the constant payment (annuity) paid during each period. PV is the cash value in the sequence of payments. FV (optional) is the future value, which is reached at the end of the periodic payments. Type (optional) defines whether the payment is due at the beginning (1) or the end (0) of a period. Guess (optional) determines the estimated value of the interest with iterative calculation. |
=RATE(A2*12, A3, A4) |

SLN | Syntax: SLN(cost, salvage, life)This function returns the depreciation of an asset for a period based on the straight-line depreciation method. cost is the original cost of the asset. salvage is the salvage value after the asset has been fully depreciated. life is the useful life of the asset or the number of periods that you will be depreciating the asset. |
=SLN(A1, A2, A3) =SLN(8000, 1000, 10) |

SYD | Syntax: SYD(cost, salvage, life, period)This function returns the depreciation of an asset for a given time period based on the sum-of-years digits depreciation method. cost is the original cost of the asset. salvage is the salvage value after the asset has been fully depreciated. life is the useful life of the asset or the number of periods that you will be depreciating the asset. period is the period that you wish to calculate the depreciation for. Use the same units as for the life. |
=SYD(A1,A2,A3,1) =SYD(8000, 1000, 10,10) |

VDB | Syntax: VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance. Cost: The initial cost of the asset. Salvage: The value at the end of the depreciation. This value can be 0. Life: the number of periods over which the asset is depreciated. Start_period: The starting period for which you want to calculate the depreciation. Start_period must use the same units as life. End_period: The ending period for which you want to calculate the depreciation. End_period must use the same units as life. Factor: The rate at which the balance declines. If factor is omitted, it is assumed to be 2. No_switch: A logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation. |
=VDB(A2, A3, A4, 0, 0.875, 1.5) |