Top Financial Functions in Excel (15+ Functions)

1 – FV Function (Future Value)

The FV function is an Excel financial function that is used to calculate the future value of an investment based on a series of periodic payments and a constant interest rate.

The syntax of the FV function is:

FV(rate,nper,pmt,[pv],[type])

Rate: It is the rate of interest per period
nper: The total number of payment periods in an annuity.
pmt: The amount of payment in each period. This amount is fixed. It contains the principal amount and the interest. If pmt is omitted, pv must be included.
[pv]: (Optional) It is the present value. It also indicates the lump sum amount of a future payment.
[type]: (Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning.

Let’s see an example of how you can apply the FV function to get the future value in Excel. We have the following parameters:

Number of years: 10
Period value: 12 (as months)
Interest rate: 9%
Present value: $1000
Note: This value will be inputted as positive. Important for the calculation (see later).
Payment and Type values are left blank (or as 0).

To find the future value, we apply the FV formula as follows:

=-FV(C6/C5,C4*C5,C8,C7,C9)

C6/C5 calculates the Interest per Period (Annual IR/Periods PY).
C4*C5 calculates the number of months (Year*Month=Periods).
Payments (C8) and Type (C9).
C7 is the Present Value, valued at $1000.
The negative (-) before the formula plays a crucial role in the calculation.
- The PV and FV are opposites (PV=Inflow Cash, FV=Outflow Cash)
- If PV is positive, FV will be negative by calculation.
- Thus, a negative operator (-) is used to get the correct value.

Let’s say you invest $50 per month. Your account will be valued at $12,127.07

2 – PV Function (Present Value)

The PV function is an Excel financial function that calculates the present value of an expected cash flow, which can either be a loan or an investment based on a constant interest rate.

The syntax of the PV function is:

PV(rate, nper, pmt, [fv], [type])

Rate: It is the rate of interest per period
nper: The total number of payment periods in an annuity.
pmt: The amount of payment in each period. This amount is fixed. It contains the principal amount and the interest. If pmt is omitted, fv must be included.
[fv]: (Optional) It is the future value. Also indicated as the lump sum amount of the present payment.
[type]: (Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning.

Let’s see an example of how you can apply the PV function to get the present value of a loan in Excel. We’ll use the following:

Number of years: 5
Period value: 12 (as months)
Interest rate: 8%
Future value: $20000
Payment and Type values are left blank (or as 0).

Insert the following formula to apply the PV formula to calculate the present value:

=-PV(C6/C5,C4*C5,C8,C7,C9)

C6/C5 calculates the Interest per Period (Annual IR/Periods PY).
C4*C5 calculates the number of months (Year*Month=Periods).
Payments (C8) and Type (C9).
C7 is the Future Value, valued at $20000.
The negative (-) before the formula plays a crucial role in the calculation.
- The PV and FV are opposites (PV=Inflow Cash, FV=Outflow Cash)
- If FV is positive, PV will be negative by calculation.
- Thus a negative operator (-) is used to get the correct value.

Let’s say you received a cash receipt of $100 per month. Your account will be valued at $18,356.05

3 – PMT Function (Payment)

The PMT function calculates the loan payment for a constant interest rate and constant payment.

The syntax of the PMT function is:

PMT(rate, nper, pv, [fv], [type])

Rate: It is the rate of interest per period
nper: The total number of payment periods.
pv: It is the present value. Also indicates the lump sum amount of a future payment.
[fv]: (Optional) It is the future value. Also indicated as the lump sum amount of the present payment.
[type]: (Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning.

Let’s apply the PMT function to get the constant payment for a loan at a constant interest rate in Excel. We’ll use the following:

Number of years: 5
Period value: 12 (as months)
Interest rate: 8%
Present value: $20000
Future value and Type values are left blank (or as 0).

To apply the PMT formula to calculate the payment per period, insert the following formula:

=PMT(C6/C5,C4*C5,C8,C7,C9)

C6/C5 calculates the Interest per Period (Annual IR/Periods PY).
C4*C5 calculates the number of months (Year*Month=Periods).
C8 is the Future value, and the Payment Type is C9.
C7 is the Present Value, valued at $20000.

4 – Rate Function (Interest Rate)

The RATE function calculates the interest rate per period for an investment or loan.

The syntax of the RATE function is:

RATE(nper, pmt, pv, [fv], [type])

nper: The total number of payment periods.
pmt: The amount of payment in each period. This amount is fixed. It contains the principal amount and the interest. If pmt is omitted, fv must be included.
pv: It is the present value. It also indicates the lump sum amount of a future payment.
[fv]: (Optional) It is the future value. Also indicated as the lump sum amount of the present payment.
[type]: (Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning of the period.

Let’s apply the RATE function to get the interest rate for a loan for a fixed period of time in Excel. We’ll use the following:

Number of years: 5
Period value: 12 (as months)
Present value: -$3000
Future value: $30000
Payment and Type values are left blank (or as 0).

To apply the RATE formula to calculate the interest rate, insert the following formula:

=RATE(C5*C4,C7,C6,C8,C9)

C5*C4 calculates the NPER or number of months (Year*Month=Periods).
C4*C5 calculates the number of months (Year*Month=Periods).
Payments (C7) and Type (C9).
C6 is the Present Value, valued at $3000 and entered as negative as,
- The PV and FV are opposites (PV=Inflow Cash, FV=Outflow Cash)
- If PV is positive, FV will be negative by calculation.
- Thus a negative operator (-) is used to get the correct value.
C8 is the future value of the loan entered as positive.

5 – NPER Function (Number of Periods)

The financial function NPER calculates period numbers for periodic, constant payments for constant interest rates.

The syntax of the RATE function is:

NPER(rate, pmt, pv, [fv], [type])

Rate: It is the rate of interest per period
pmt: The amount of payment in each period. This amount is fixed. It contains the principal amount and the interest. If pmt is omitted, fv must be included.
pv: It is the present value. Also indicated as the lump sum amount of a future payment.
[fv]: (Optional) It is the future value. Also indicated as the lump sum amount of the present payment.
[type]: (Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning of the period.

Let’s apply the NPER function to get the number of periods to pay a loan for a fixed interest rate in Excel. We’ll use the following:

Number of years: 12
Annual Interest Rate: 10%
Present value: $10000
Payment: -$500
Future value and Type values are left blank (or as 0).

Insert the following formula to apply the NPER function to calculate the interest rate:

=NPER(C5/C4,C7,C6,C8,C9)

6 – NPV Function (Net Present Value)

The NPV function calculates the net present value of an investment.

The syntax of the NPV function is:

NPV(rate,value1,[value2],...)

Rate: discount rate
Value 1, [value2],[value3]…: payments and income. It should be equally spaced in time and occur at the end of each period.

Let’s use the NPV function to calculate net present value. We’ll use the following:

Annual Discount Rate: 5%
Initial Investment Cost: $30000
1st Year Return: $10000
2nd Year Return: $5000
3rd Year Return: $12000
4th Year Return: $7000

Insert the following formula to apply the NPV function to calculate the net present value:

=NPV(C4,C6:C9)+C5

7 – IRR Function (Internal Rate of Return)

The IRR function calculates the internal rate of return for a series of cash.

The syntax of the IRR function is:

IRR(values, [guess])

Values: An array or a reference to cells that contain numbers for which the internal rate of return is calculated
[guess]: Guessed number close to the result of IRR.

Here we have some data relevant to IRR function:

Initial Investment Cost: $30000
1st Year Return: $10000
2nd Year Return: $5000
3rd Year Return: $12000
4th Year Return: $7000

Insert the following formula to apply the IRR formula to calculate the internal rate of return:

=IRR(C4:C8)

8 – XNPV Function (NPV for Non-Scheduled Cash Flow)

The XNPV function calculates the net present value of net present value of a cash flow series that are not necessarily periodic.

The syntax of the XNPV function is:

XNPV(rate, values, dates)

Rate: An array or a reference to cells that contain numbers for which the internal rate of return is calculated
Values: A series of cash flows corresponding to a payment schedule (in dates). The first payment is optional and corresponds to a payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All payments are discounted based on a 365-day year. The values must contain minimum one positive value and one negative value.
Dates: A schedule of payment dates that corresponds to the cash flow payments. The first payment date must be in top of the list. But the other dates can occur in any order.

Here we have some data relevant to XNPV function:

Date values arranged in a column.
Annual Discount Rate: 5%
Initial Investment Cost: $30000
1st Year Return: $10000
2nd Year Return: $5000
3rd Year Return: $12000
4th Year Return: $7000

Insert the following formula to apply the XNPV function to calculate the net present value for non-scheduled cash flow:

=XNPV(D,D6:D10,B6:B10)

9 – XIRR Function (IRR for Non-Scheduled Cash Flow)

The XIRR function calculates the internal rate of return for a series of cash flows that occur at irregular intervals.

The syntax of the XIRR function is:

XIRR(values, dates, [guess])

Values: A series of cash flows corresponding to a payment schedule (in dates). The first payment is optional and corresponds to a payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All payments are discounted based on a 365-day year. The values must contain minimum one positive value and one negative value.
Dates: Payment schedule (in dates). The dates can occur in any order in the list. But dates must be given by using the DATE function.
[guess]: A number that is close to the output of XIRR.

Here we have some data relevant to XIRR function:

Date values arranged in a column.
Initial Investment Cost: $30000
1st Year Return: $10000
2nd Year Return: $5000
3rd Year Return: $12000
4th Year Return: $7000

Insert the following formula to apply XIRR formula to calculate the internal rate of return for non-scheduled cash flow:

=XIRR(D5:D9,B5:B9)

10 – MIRR Function (Modified Internal Rate of Return)

The MIRR function calculates the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the reinvestment rate.

The syntax of the MIRR function is:

MIRR(values, finance_rate, reinvest_rate)

Values: An array or a cell reference that contains cash amount.
Finance_rate: The interest rate paid on the money used in the cash flows.
Reinvest_rate: The interest rate received on the cash flows after reinvesting them.

Here we have some data relevant to MIRR function:

Finance Rate: 5%
Reinvest Rate: 10%
Initial Investment Cost: $30000
1st Year Return: $10000
2nd Year Return: $5000
3rd Year Return: $12000
4th Year Return: $7000

Insert the following formula to apply the MIRR function to calculate the modified internal rate of return after 2 years:

=MIRR(D7:D9,D4,D5)

Insert the following formula to apply the MIRR function to calculate the modified internal rate of return after 4 years:

=MIRR(D7:D11,D4,D5)

11 – FVSCHEDULE Function (Future Value Applying Series of Compound Interest Rates)

The FVSCHEDULE function in Excel calculates the future value of an investment based on a schedule of interest rates.

The syntax of the FVSCHEDULE function:

FVSCHEDULE(principal, schedule)

principal: present value
schedule: an array of interest rates

Here we have some data relevant to FVSCHEDULE function:

Initial Investment Cost: $30000
1st Year Schedule: 5%
2nd Year Schedule: 7%
3rd Year Schedule: 8%
4th YearSchedule: 3%

Insert the following formula to apply the FVSCHEDULE function to calculate the future investment value:

=FVSCHEDULE(C4,C5:C8)

12 – PPMT Function (Principal Payment)

The PPMT function in Excel calculates the payment on principal for a given period.

The syntax of the PPMT function:

PPMT(rate, per, nper, pv, [fv], [type])

Rate: Interest rate
Per: Period
Nper: Number of periods in an annuity.
Pv: Present value
[fv]: Future value
[type]: The number 0 or 1 and indicates when payments are due.

Here we have some data relevant to PPMT function:

Year: 5
Periods Per Year: 12
Annual Interest Rate: 8%
Present Value: $20000
Future Value: $ –
Period Number: 5
Type(End=0/Beginning=1)

Insert the following formula to apply the PPMT function to calculate the payment on the principal:

=PPMT(C6/C5,C9,C4*C5,C7,C8,C10)

13 – RRI Function (Rate of Return)

The RRI function in Excel calculates an equivalent rate of interest for the growth of an investment.

The syntax of the RRI function:

RRI(nper, pv, fv)

nper: Number of Period
pv: Present Value
fv: Future Value

Here we have some data relevant to RRI function:

Period Number: 5
Present Value: $20000
Future Value: $30000
Rate of Return: 8%

Insert the following formula to apply the RRI function to calculate the rate of return:

=RRI(C4,C5,C6)

14 – EFFECT Function (Effective Annual Rate of Return)

The EFFECT function in Excel calculates the effective annual rate of interest or rate of return.

The syntax of the EFFECT function:

EFFECT(nominal_rate, npery)

Nominal_rate: Nominal interest rate
Npery: number of compounding periods per year.

Here we have some data relevant to EFFECT function:

Nominal Interest Rate: 7%
Period Per Year: 12

Insert the following formula to apply the EFFECT function to calculate the rate of return:

=EFFECT(C4,C5)

15 – IPMT Function (Interest Payment)

The IPMT function calculates the due interest payment for a periodic, constant payment and interest rate for a given time period.

The syntax of the IPMT function is:

IPMT(rate, per, nper, pv, [fv], [type])

Rate: Interest rate
Per: Period
Nper: Number of periods in an annuity.
Pv: Present value
[fv]: (Optional)Future value
[type]: (Optional) The number 0 or 1 indicates when payments are due.

Here we have some data relevant to IPMT function:

Year: 5
Periods Per Year: 12
Annual Interest Rate: 8%
Present Value: $20000
Future Value: $ –
Period Number: 5
Type(End=0/Beginning=1): 0

Insert the following formula to apply the IPMT function to calculate the interest payment:

=IPMT(C6/C5,C9,C4*C5,C7,C8,C10)

16 – DB Function (Depreciation Value)

The DB function calculates the depreciation of any asset using a fixed declining way for a definite time period.

The syntax of DB function is:

DB(cost, salvage, life, period, [month])

cost: the asset’s initial cost
salvage: the asset’s remaining value after the depreciation.
life: the number of periods at the end of the depreciating period or the lifetime of an asset when it can be used
period: the time period for which you want the depreciation value of the asset. It uses the unit of life.
[month]: (Optional) The month number of first year. If it is omitted, then Excel takes it as 12 by default.

Let us calculate the depreciation value of an asset using DB function. We’ll use the following:

Initial Cost: $90000
Salvage Value: $10000
Life in Years: 9
Number of Months in 1st Year: 5

We will calculate the depreciation value of the asset after 3, 6, and 9 years.

To apply the DB function in Excel, insert the following formula in a cell:

=DB($C$4,$C$5,$C$6,3,$C$7)

C4 is the initial cost
C5 is salvage value
C6 is life
3 is the period till which we want depreciation
C7 is the month number in 1st year

We calculated the depreciation value after 6 and 9 years, as shown in the image below.

17 – STOCKHISTORY Function (Get Stock’s Historical Data)

The STOCKHISTORY function loads the historical data from a certain period of time regarding a financial instrument (stock) as an array. When this spill formula is applied, Excel dynamically returns the data range in the required amount of cells.

Note:

This function is available only for Microsoft 365 Personal, Family, Business Standard, and Business Premium versions

The syntax of STOCKHISTORY function is:

STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

stock: it is the ticker symbol for the financial instrument of which you want the historical data.
start_date: the starting date from which you want the data
[end_date]: (Optional) the date till which you want the data
[interval]: (Optional) This number denotes the interval of each data. “0” for daily, “1” for weekly, and “2” for monthly interval.
[headers]: (Optional) This number is to decide if you want to display headings or not. “0” for no headers, “1” for showing headers, and “2” for showing instrument identifiers and headers.
[property0- property5]: (Optional) This number indicates which column from historical data you want to retrieve. “0” for Dates, “1” for Close, “2” for Open, “3” for High, “4” for Low, and “5” for Volume.

Let’s see how this STOCKHISTORY function works in Excel. We’ll use the following information:

Stock name/Ticker Symbol: Amzn
Start date: 1/1//2024
End Date: 1/20/2024

Insert the following formula in a cell that has enough blank cells below to place the data fetched using the STOCKHISTORY function:

=STOCKHISTORY(C4,C5,C6,0,2,0,1)

C4 is ticker symbol
C5 is start date
C6 is end date
0 returns daily data, and 2 retrieves the headers
0 fetches the Dates, and 1 brings the closing amount of the financial instrument