What is a FINANCIAL formula in a spreadsheet?
Financial formulas and functions are used for financial calculations. They include interest calculations, loan payments, future value, present value, net present value (NPV), internal rate of return (IRR), and other financial metrics or analysis.
FINANCIAL formula usage examples.
The ACCRINT function calculates the accrued interest of a security that has periodic payments. It takes into account the issue date, first payment date, settlement date, interest rate, redemption value, payment frequency, and optional day count convention. The function returns the accrued interest as a decimal value.
The AMORLINC function is used to calculate the depreciation for an accounting period or the prorated depreciation if the asset was purchased in the middle of a period. It takes into account the cost of the asset, the purchase date, the end of the first period, the salvage value, the period, the rate, and the basis.
The COUPDAYBS function calculates the number of days from the first coupon, or interest payment, until settlement. It takes the settlement date, maturity date, frequency of coupon payments, and optional day count convention as inputs. The day count convention determines how the number of days is calculated, such as actual/actual or 30/360. The function returns the number of days as a result.
The COUPDAYS function calculates the number of days in the coupon, or interest payment, period that contains the specified settlement date. It takes four arguments: settlement, maturity, frequency, and [day_count_convention]. The settlement argument is the date on which the bond is purchased. The maturity argument is the date on which the bond matures. The frequency argument specifies the number of coupon payments per year. The [day_count_convention] argument is optional and specifies the method used to calculate the day count. This function is useful for bond investors who need to calculate the length of coupon periods or determine the remaining days until the next coupon payment.
The COUPDAYSNC function calculates the number of days from the settlement date until the next coupon or interest payment. It takes four arguments: the settlement date, the maturity date, the frequency of payments, and an optional day count convention. The settlement date is the date on which the security is purchased. The maturity date is the date on which the security matures. The frequency of payments specifies how often the payments are made, such as annually or semi-annually. The day count convention determines how the number of days is calculated, such as actual/actual or 30/360. This function returns the number of days as a decimal value.
The COUPNCD function is used to calculate the next coupon, interest, or dividend payment date after the settlement date. It takes the settlement date, maturity date, frequency of payments, and optional day count convention as inputs. The function returns the next payment date based on the specified parameters.
The COUPNUM function calculates the number of coupons, or interest payments, between the settlement date and the maturity date of an investment. It takes into account the frequency of coupon payments and the day count convention used for interest accrual. This function is useful for analyzing bond portfolios, estimating interest payments for loans, and other financial calculations.
The COUPPCD function is used to calculate the last coupon date before the settlement date for a security with periodic interest payments. It takes the following arguments: - settlement: The settlement date of the security. - maturity: The maturity date of the security. - frequency: The number of coupon payments per year. - [day_count_convention]: Optional argument specifying the day count convention to use for calculating the coupon dates. If omitted, the default value is 0 (actual/actual).
The CUMIPMT function calculates the cumulative interest over a range of payment periods for an investment based on constant-amount periodic payments and a constant interest rate. It takes the following arguments: - rate: The interest rate per period. - number_of_periods: The total number of payment periods. - present_value: The present value or initial investment. - first_period: The first period for which to calculate the cumulative interest. - last_period: The last period for which to calculate the cumulative interest. - end_or_beginning: A flag indicating whether payments are due at the end or beginning of the period.
The CUMPRINC function calculates the cumulative principal paid or earned over a range of periods for an investment or loan. It takes the following arguments: - rate: The interest rate per period. - number_of_periods: The total number of payment or investment periods. - present_value: The initial investment or loan amount. - first_period: The first period for which to calculate the cumulative principal. - last_period: The last period for which to calculate the cumulative principal. - end_or_beginning: A flag indicating whether payments or contributions are made at the end or beginning of each period. The function returns the cumulative principal paid or earned over the specified range of periods.
The DB function calculates the depreciation of an asset for a specified period using the arithmetic declining balance method. It takes the initial cost of the asset, the estimated salvage value at the end of its useful life, the total number of periods over which the asset will be depreciated, the specific period for which the depreciation is calculated, and an optional argument for the number of months in the first year. The function returns the depreciation amount for the specified period.
The DDB function calculates the depreciation of an asset for a specified period using the double-declining balance method. It takes the following arguments: - cost: The initial cost of the asset. - salvage: The value of the asset at the end of its useful life. - life: The number of periods over which the asset will be depreciated. - period: The period for which you want to calculate the depreciation. - [factor]: An optional argument that specifies the rate at which the asset is depreciated. If not provided, the default factor of 2 is used. The DDB function uses a declining balance method, where the depreciation amount decreases over time. It calculates the depreciation by multiplying the cost of the asset by a depreciation rate, which is determined by the factor and the remaining life of the asset. The formula subtracts the accumulated depreciation from the cost to determine the net book value of the asset at the end of each period.
The DISC function is used to calculate the discount rate of a security based on its price. It takes into account the settlement date, maturity date, price, redemption value, and an optional day count convention. The discount rate represents the rate at which the security's future cash flows are discounted to determine its present value.
The DOLLARDE function is used to convert a price quotation given as a decimal fraction into a decimal value. It takes two arguments: the fractional price and the unit. The fractional price represents the price quotation as a decimal fraction, and the unit represents the denominator of the fraction. The function returns the decimal value of the price quotation.
The DURATION function calculates the number of compounding periods required for an investment of a specified present value appreciating at a given rate to reach a target value. It takes into account the settlement date, maturity date, interest rate, yield, compounding frequency, and optional day count convention.
The EFFECT function is used to calculate the annual effective interest rate given the nominal rate and the number of compounding periods per year. It takes two arguments: the nominal rate and the number of compounding periods per year. The function returns the annual effective interest rate as a decimal value.
The FVSCHEDULE function calculates the future value of some principal based on a specified series of potentially varying interest rates. It takes two arguments: the principal amount and the rate schedule. The rate schedule is an array or range of interest rates that correspond to different time periods. The function applies each interest rate to the principal amount and calculates the future value at the end of each time period. Finally, it sums up all the future values to determine the overall future value of the principal amount.
The INTRATE function calculates the effective interest rate generated when an investment is purchased at one price and sold at another with no interest or dividends generated by the investment itself. It takes into account the buy date, sell date, buy price, sell price, and an optional day count convention parameter.
The IPMT function calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate. It takes the following arguments: - rate: The interest rate per period. - period: The period for which you want to calculate the interest payment. - number_of_periods: The total number of payment periods. - present_value: The present value or principal amount of the investment. - future_value (optional): The future value or desired future amount of the investment. - end_or_beginning (optional): A logical value that specifies whether the payment is made at the end or beginning of the period. If omitted, it is assumed to be 0 or the end of the period.
The IRR function calculates the internal rate of return on an investment based on a series of periodic cash flows. It is used to determine the discount rate that makes the net present value (NPV) of the cash flows equal to zero. The IRR function takes two arguments: cashflow_amounts, which represents the series of cash flows, and rate_guess (optional), which is an initial guess for the IRR. If rate_guess is not provided, Excel uses 0.1 (10%) as the default guess.
The ISPMT function calculates the interest paid or earned during a particular period of an investment or loan. It takes four arguments: rate (the interest rate per period), period (the specific period for which to calculate the interest), number_of_periods (the total number of periods), and present_value (the present value of the investment or loan). The function returns the interest amount for the specified period.
The MIRR function calculates the modified internal rate of return on an investment. It takes into account a series of periodic cash flows and the difference between the interest rate paid on financing versus the return received on reinvested income. The function returns the modified internal rate of return as a percentage.
The NPER function is used to calculate the number of payment periods required to reach a specific financial goal. It takes into account the interest rate, payment amount, present value, future value, and the timing of payments (end or beginning of the period). The function returns the number of periods as a result.
The NPV function calculates the net present value of an investment by discounting a series of cash flows using a specified discount rate. It takes the discount rate as the first argument, followed by the cash flows as subsequent arguments. The cash flows can be positive or negative and represent the inflows and outflows of cash over time. The NPV function returns the present value of the cash flows, which represents the net value of the investment.
The PDURATION function calculates the number of periods required for an investment to reach a specific value at a given interest rate. It takes three arguments: the interest rate, the present value, and the future value. The interest rate is the rate of return per period, the present value is the initial investment or current savings, and the future value is the desired value to be reached. The function returns the number of periods required to reach the future value.
The PMT function calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate. It takes the following arguments: - rate: The interest rate for each period. - number_of_periods: The total number of payment periods. - present_value: The present value or loan amount. - future_value (optional): The future value or desired savings goal. - end_or_beginning (optional): Specifies whether the payment is due at the end or beginning of the period.
The PRICE function is used to calculate the price of securities paying periodic interest, such as bonds. It considers factors such as the settlement date, maturity date, coupon rate, yield, redemption value, frequency of coupon payments, and optional day count convention. The function returns the price at which the security should be traded in the market.
The PRICEMAT function is used to calculate the price of a security that pays interest at maturity, based on the expected yield. It takes several arguments including the settlement date, maturity date, issue date, annual coupon rate, expected yield, and an optional day count convention. The function returns the price per $100 face value of the security.
The PV function calculates the present value of an annuity investment based on constant-amount periodic payments and a constant interest rate. It takes the following arguments: - rate: The interest rate per period. - number_of_periods: The total number of payment periods. - payment_amount: The amount of each payment. - future_value (optional): The future value remaining after the last payment has been made. If omitted, it is assumed to be 0. - end_or_beginning (optional): Specifies whether the payments are made at the end or beginning of each period. If omitted, it is assumed to be 0 (end of period).
The RATE function calculates the interest rate of an annuity investment based on constant-amount periodic payments and the assumption of a constant interest rate. It takes into account the number of payment periods, the payment amount per period, the present value of the investment, and optional parameters such as the future value, the timing of payments, and an initial guess for the interest rate.
The SLN function calculates the depreciation of an asset for one period using the straight-line method. It takes three arguments: the cost of the asset, the salvage value at the end of its useful life, and the total number of periods over which the asset will be depreciated. The function evenly distributes the depreciation amount over the specified number of periods.
The SYD function calculates the depreciation of an asset for a specified period using the sum of years digits method. It takes four arguments: cost (the initial cost of the asset), salvage (the value of the asset at the end of its useful life), life (the useful life of the asset in years), and period (the specific period for which depreciation is calculated). The function distributes the depreciation amount over the useful life of the asset, with higher amounts in the earlier years and lower amounts in the later years.
The TBILLEQ function is used to calculate the equivalent annualized rate of return for a US Treasury Bill. It takes three arguments: the settlement date, the maturity date, and the discount rate. The settlement date is the date on which the Treasury Bill is purchased, the maturity date is the date on which the Treasury Bill matures, and the discount rate is the annualized discount rate for the Treasury Bill. The function returns the equivalent annualized rate of return as a decimal value.
The VDB function is used to calculate the depreciation of an asset for a particular period or partial period. It takes into account the cost of the asset, its salvage value, its useful life, the start and end periods, and optional arguments such as the depreciation factor and switch. The function returns the depreciation amount for the specified period(s).
The XNPV function calculates the net present value of an investment by discounting a series of potentially irregularly spaced cash flows using a specified discount rate. It takes three arguments: the discount rate, an array of cash flow amounts, and an array of corresponding cash flow dates. The function considers the time value of money, giving more weight to cash flows that occur earlier in time. The result is the net present value of the investment.