Financial analysts use a wide range of complex financial software but they usually spend the most time analysing different financial data sets in spreadsheets. It shows that Excel has become a basic job qualification for this role. If it wasn’t for Excel, their job probably wouldn’t even exist.
A smart financial modelling consultant knows plenty of formulas to help them prepare an error-free report. So, in this article, we are sharing some of the most useful Excel formulas every analyst should know.
Finance analysts use this function to know the annual interest rate of investments for non-annual compound schemes. For example, a 12% annual interest rate that compounds monthly is a 12.68% effective annual interest rate.
= EFFECT (Nominal_Rate, NPERY or Number of compounding per year)
= EFFECT (12%, 12)
- Future Value (FV)
You can use this function to determine the future value of a particular investment which has a constant interest rate and regular payment. For example, a $25 million investment that has grown at 4.5% annually for 30 years and receives $1 million every year in addition to the total balance has a future value of $154.6 million.
= FV (rate, number of periods, payment, present value)
= FV (4.5%, 30, [1,000,000], [25,000,000])
= $154.6 million
The FVSCHEDULE function calculates the future value of an investment based on a schedule of a variable interest rate. If you have invested $1 million in 2017 and it has increased by 4%, 5%, and 6%, respectively in the next three years, the FV in 2020 would be $1.16 million
= FVSCHEDULE (principal, schedule)
= FVSCHEDULE (1,000,000, [4%, 5%, 6%])
- Internal Rate of Return (IRR)
IRR is a function that helps you understand whether or not an investment is profitable.
IRR = (Values, [Guess])
Values = an array of values that indicate positive or negative cash flows
Guess = an assumption of the IRR
The investment is profitable if the IRR is more than the hurdle rate (acceptable rate/average cost of capital) and vice-versa.
- Modified Internal Rate of Return (MIRR)
As the name suggests, this function is the modified version of the IRR. It is used mostly to calculate the cash from one investment that is invested in another investment.
= MIRR (values, finance rate, reinvestment rate)
Values – an array of values; positive or negative cash flows
Finance Rate – interest rate paid for the money used in cash flows
Reinvestment Rate – Interest rate paid for reinvestment
- Net Present Value (NPV)
Net Present Value is the total sum of positive and negative cash flows over the years.
= NPV ( rate, values)
Rate – discount rate for a specific period
Values – positive negative cash flows, where negative values are considered as payments, and positive values are regarded as inflows.
The most used function has to be the XNPV. It is similar to the NPV. But, the difference is that it takes into account specific dates for cash flows and is, therefore, more precise and useful.
= XNPV (discount rate, cash flows, dates)
Rate – discount rate for a period
Values – positive or negative cash flows
Dates – specific dates of payment and income
This function returns the nominal rate for a specific year based on the effective annual interest rate and the number of compounding periods per year. For example, a payment has an interest rate of 12% if the number of compounding per year is 12.
= NOMINAL (effective annual interest rate, number of compounding period per year)
= NOMINAL (12%, 12)
With this function, you can calculate the depreciation of an investment using a straight-line method, based on the following variables:
Cost – initial asset cost
Salvage – salvage value
Life – number of periods over which is depreciated
For example, the initial cost of your equipment is $1,000. Over the years, it has depreciated in the straight-line method. 5 years later, the salvage value of the equipment is $300. The depreciation is $140 per year.
= SLN (cost, salvage, life)
= SLN (cost-salvage)/life
= SLN ($1,000 – $300) / 5
= $140 per year
Similar to XNPV, this function calculates the IRR for a series of cash flows on specific dates.
= XIRR (values, dates, guess)
Many financial analysts prefer this function over the regular IRR formula, as it provides more accurate results, given that the periods between cash flows are rarely the same.
These formulas are only as good if you keep your spreadsheet organised and the data presented in a logical manner.
Which formula do you use most? Let us know by leaving a comment.