5 Excel Formulas Every Finance Professional Should Know

Finance professionals spend a lot of time in Excel compiling reports and calculating figures. What lots of finance professionals don’t know is that Excel contains lots of financial formulas which would speed their work up substantially.

In this article, Acuity Training leads us through the 5 Excel formulas every finance professional needs to know to make the most of Excel.

XNPV is used to evaluate the net present value of a series of cash flows. 

XNPV is the improved version of the NPV function.

NPV assumes that the period between the cashflows is equal which is highly unlikely in real-world scenarios. XNPV allows you to simply define when each cash flow is paid/received.

XNPV contains three arguments

=XNPV(rate, values, dates)

Below is a simple example of using XNPV for calculating the net present value against cash flow activities. 

Notice that the period between each cash flow is different. By assigning cell D6 as Rate, cells D2:H2 as Values, and cell D4:H4 as Dates, the net present value is calculated.

XIRR is used to calculate the internal rate of return for a series of cash flows.

XIRR is best suited to calculate the internal rate of return for a series of cash flows with irregular intervals. Just as XNPV is more flexible than NPV, XIRR is more flexible than IRR.

XIRR requires two arguments:

=XIRR(values, dates, [guess])

The following example calculates the IRR using XIRR for a series of irregular cash flows.

Cells B4:B8 contain the values of the cashflows while cells C4:C8 contain the date of the cashflows.

MIRR is used by finance professionals to calculate the modified internal rate of return.

The MIRR calculates the IRR for a series of cash flows considering both discount rate and reinvestment rate.

IRR and XIRR ignore the fact that funds received will be reinvested elsewhere. 

The reinvestment rate refers to the rate that cashflows received will be reinvested elsewhere.

By including the reinvestment rate in the calculation of IRR, the results become far more helpful.

The MIRR formula requires three arguments

=MIRR(values, finance_rate, reinvest_rate)

In the below example, MIRR is calculated for a series of cash flows. 

Cells B4:B8 contain the cashflow values, cell E4 contains the Finance_rate and cell E6 contains the Reinvest_rate.

The PMT function calculates the periodic payments for a loan or mortgage. 

The payments can be calculated using the loan amount, no of installments, and interest rate.

The calculation done by the function includes both the principal and interest. The function considers the no of periods and interest rate constant throughout the loan cycle.

The PMT function has three arguments

=PMT(Rate, Nper, Pv, [Fv], [Ttype])

Type: [Optional] Refers to the timing of the loan payments. 0 indicates the end of each period whereas a 1 indicates the beginning of the period. The default value is 0.

See the below example for the explanation of the PMT function. 

A person is seeking a loan for $20,000 with an annual interest rate of 6% for 10 years. With the PMT function, the monthly payment is calculated. 

Cell C5 shows the monthly interest rate, cell C7 contains the total number of monthly installments to be made and cell C3 contains the loan amount.

NB: It is critical that the interest rate is in the same units as the period of loan. So if the interest rate is annual, the payments must be made annually. In this case, as the loan payments are monthly the interest rate and the number of periods must be in months as well.

IPMT calculates the total interest paid on a loan.

Most financial professionals use this function in conjunction with PMT to calculate the monthly payments and interest amount.

By calculating the monthly amount and interest amount, the principal value can be calculated by simple subtraction. 

IPMT requires four arguments

=IPMT(Rate, Per, Nper, Pv, [Fv], [\type])

Type: [Optional] Refers to the timing the loan payments. 0 indicates the end of each period whereas a 1 indicates the beginning of the period. The default value is 0.

Let’s see what the interest amount for the 20th payment would be for a loan. 

Cell C5 indicates the monthly interest rate, cell C7 contains the total number of monthly instalments to be made and cell C3 contains the loan amount.

As we are working on monthly payments we have converted all of our inputs into monthly values.

Excel is an incredibly helpful tool for finance professionals. It contains a huge number of finance formulas and functions. Hopefully this article has explained a few new ones and will inspire you to look at some of the other more advanced financial modeling formulas in Excel.