We all use excel when it comes to some calculation, analyzing company data such as sales figures, sales taxes or commissions. I will list down the financial formulas used in excel.
XNPV
Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
Syntax
XNPV(rate, values, dates)
- Rate = Discount rate for a period
- Values = Positive or negative cash flows (an array of values)
- Dates = Specific dates (an array of dates)
NPV
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Syntax
NPV(rate,value1,[value2],...)
- Rate = Discount rate for a period
- Value 1, [Value 2], [Value 3]… = Positive or negative cash flows
- Here, negative values would be considered as payments and positive values would be treated as inflows.
IRR
Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.
Syntax
IRR(values, [guess])
- Values = Positive or negative cash flows (an array of values)
- [Guess] = An assumption of what you think IRR should be
XIRR
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.
Syntax
XIRR(values, dates, [guess])
- Values = Positive or negative cash flows (an array of values)
- Dates = Specific dates (an array of dates)
- [Guess] = An assumption of what you think IRR should be
MIRR
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 cash.
Syntax
MIRR(values, finance_rate, reinvest_rate)
- Values = Positive or negative cash flows (an array of values)
- Finance rate = Interest rate paid for the money used in cash flows
- Reinvestment rate = Interest rate paid for reinvestment of cash flows
NPER
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
Syntax
NPER(rate,pmt,pv,[fv],[type])
- Rate = It is the interest rate/period
- PMT = Amount paid per period
- PV = Present Value
- [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
- [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
PMT
Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax
PMT(rate, nper, pv, [fv], [type])
- Rate = It is the interest rate/period
- Nper = Number of periods
- PV = Present Value
- [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
- [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
PPMT
Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
Syntax
PPMT(rate, per, nper, pv, [fv], [type])
- Rate = It is the interest rate/period
- Per = The period for which the principal is to be calculated
- Nper = Number of periods
- PV = Present Value
- [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
- [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
IPMT
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
Syntax
IPMT(rate, per, nper, pv, [fv], [type])
- Rate = The interest rate per period.
- Per = The period for which you want to find the interest and must be in the range 1 to nper.
- Nper = The total number of payment periods in an annuity.
- Pv = The present value, or the lump-sum amount that a series of future payments is worth right now.
- Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
- Type Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
EFFECT
Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
Syntax
EFFECT(nominal_rate, npery)
- Nominal_Rate = Nominal Interest Rate
- NPERY = Number of compounding per year
FV
Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
Syntax
FV(rate,nper,pmt,[pv],[type])
- Rate = It is the interest rate/period
- Nper = Number of periods
- [Pmt] = Payment/period
- PV = Present Value
- [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)`
FVSCHEDULE
Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate.
Syntax
FVSCHEDULE(principal, schedule)
- Principal = is the present value of a particular investment
- Schedule = A series of interest rate put together (in case of excel, we will use different boxes and select the range)
PV
Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
Syntax
PV(rate, nper, pmt, [fv], [type])
- Rate = It is the interest rate/period
- Nper = Number of periods
- [Pmt] = Payment/period
- FV = Future Value
- [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
DB
Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
Syntax
DB(cost, salvage, life, period, [month])
- Cost = The initial cost of the asset.
- Salvage = The value at the end of the depreciation (sometimes called the salvage value of the asset).
- Life = The number of periods over which the asset is being depreciated (sometimes called the useful life of the asset).
- Period = The period for which you want to calculate the depreciation. Period must use the same units as life.
- Month Optional. The number of months in the first year. If month is omitted, it is assumed to be 12.
I have listed which are used on frequent basis. I will keep on adding to the list as I learn more functions.
