 # Excel Finance Formulas

Page Visited: 1226

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. #### Girish

Hello Guys I am a website developer by profession but is always keen on learning new things. I have been investing in Mutual funds, stock market for the past few years because of which I have gained good knowledge. I started my entrepreneur journey in 2019 which lead me to learn more things as I am moving forward. I always love to share whatever I learn. Always had a craze for cars from my childhood, which inspired me to start this website. #### Girish

Hello Guys I am a website developer by profession but is always keen on learning new things. I have been investing in Mutual funds, stock market for the past few years because of which I have gained good knowledge. I started my entrepreneur journey in 2019 which lead me to learn more things as I am moving forward. I always love to share whatever I learn. Always had a craze for cars from my childhood, which inspired me to start this website.

### Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

This site uses Akismet to reduce spam. Learn how your comment data is processed.