Top 6 Excel Financial Functions

  • 5 min
  • Efficiency Tips
  • By Yolanda MacKay, 19/11/2020

It is no secret that finance analysts spend the majority of their working day on the Microsoft Office suite, and more specifically Excel. For this reason, knowing the key formulas which save you the most time is more important than ever, in order to become as efficient as possible.

If you haven’t already read our previous article, check out some new exciting Excel formulas.

But if you are looking to focus on finance functions, download our demo spreadsheet to get to know our top 6!


Excel Financial Function #1: FV – Future Value

This function is used to calculate the Future Value of an investment for fixed, repeated payments where the interest rate remains the same. 

Excel-Financial-Function-1-FV-Future-Value

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

Take a look at this excelfunctions.net article for some examples on how to use it and for more in depth information on the arguments.

Excel Financial Function #2: XNPV – Net Present Value

This formula is the opposite of the one above, working to calculate the company’s Net Present Value using irregular dates, taking into account discount rate applied, value of in / outflows at different dates for these transactions. XNPV is more specific as it uses precise dates that relate to each cash flow in the series, whilst the NPV formula assumes that time periods remain the same.

Excel-Financial-Function-2-XNPV-Net-Present-Value

Formula =XNPV(rate, values, dates)

Take a look at some examples here! https://exceljet.net/excel-functions/excel-xnpv-function

Excel Financial Function #3: XIRR – Extended Internal Rate of Return

XXIR calculates a business’ internal rate of return for different cash flows according to different dates.

Returns have always been the basic benchmarks for investors while assessing and measuring potential or existing growth. IRR indicates the expected annual growth over time in a way that’s easier to compare between investments than using just NPV. The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0. It can be used to compare potential investments or calculate returns on investments where there are multiple transactions happening at different times. 

Excel-Financial-Function-3-XIRR-Extended-Internal-Rate-of-Return

Finance analysts should use XIRR over the regular IRR formula. Most cash flow periods won’t match with IRR. Therefore, it is better to use XIRR. Cash flows will be different for different periods, and XIRR provides accurate details about each and every transaction for the specified time.

Formula =XIRR(value, dates, [guess])

If you want to learn more, take a look here! https://corporatefinanceinstitute.com/resources/excel/functions/xirr-function/

Excel Financial Function #4: MIRR – Modified Internal Rate of Return

This formula works to calculate the expected return of a series of cash flows whilst still factoring in the cost of borrowing (discount rate) and reinvestment rate.

Finance analysts use MIRR to calculate and understand the success or failure of investment compared to another investment.

Excel-Financial-Function-4-MIRR---Modified-Internal-Rate-of-Return

The standard Internal Rate of Return (IRR) assumes that all cash flows received from an investment are reinvested at the same rate. The Modified Internal Rate of Return (MIRR) allows you to set different reinvestment rates for cash flows received. MIRR arrives at a single solution for any series of cash flows, while IRR can have two solutions for a series of cash flows that alternate between negative and positive.

Formula =MIRR(values, finance_rate, reinvest_rate)

Here’s what Microsoft says about the MIRR function! https://support.microsoft.com/en-us/office/mirr-function-b020f038-7492-4fb4-93c1-35c345b53524

Excel Financial Function #5: EFFECT – Effective Interest Rate

The EFFECT function calculates the effective annual interest rate for non-annual compounding loans. Analysts often need to make decisions on which financial loan will be best for a business. The EFFECT function is helpful as it makes it easier to draw comparisons between loans with different rates or periods, ultimately helping to make a decision, and shows how compounding periods change the Effective Interest Rate.

Excel-Financial-Function-5-EFFECT-Effective-Interest-Rate

Formula =EFFECT(nominal_rate, npery)

Be sure to check out this website to gain an indepth look on how to use the function! https://www.exceltip.com/mathematical-functions/how-to-use-the-effect-function-in-excel.html

Excel Financial Function #6: PMT – Payment

This is probably the most commonly used feature in excel by Finance analysts, as it is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods and the loan amount.

This function is frequently used in Real Estate financial modeling where they use this formula to calculate the present value of investments after considering number of periods and rate of interest. It provides total payment, including the interest and principal amounts.

Excel-Financial-Function-6-PMT-Payment

The Excel PMT function calculates the constant periodic payment required to pay off (or partially pay off) a loan or investment, with a constant interest rate, over a specified period.

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

TIP: When used in conjunction with IPMT and PPMT, analysts can make a detailed report of all the investments and their expected interest and principal amounts.

For some examples on how to use the formula, look here: https://www.excel-easy.com/examples/pmt.html

BONUS: discover more Financial formulas

In Excel, under the Formulas tab, there are many pre-made Excel formulas which can be very useful too.

Excel Basic Tip 12

Other Excel articles:

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments