I’ll start by saying – if you want to become a financial modeler, finance analyst, or business analyst, then you must develop a solid understanding of Excel formulas and know them like the back of your hand.
Even if you don’t know the name of your husband/wife/partner – you MUST know these formulas. It’s THAT important. It’s also a good idea to know these if you use Excel on a day-to-day basis.
It’ll save you time and allow you to quiet quit (or knock off at 5 pm)!
I started my career at a Big 4 accountancy firm. Over the last few years, I’ve been involved in training some of the new members of our team and running our in-house financial modeling training program. Recently, a fresh graduate asked me, “What are the top 5 formulas?”
Whilst everyone has their own favorite functions and specific ways of calculating data, I’ll list what I think are the 5 critical formulas/functions that any financial analyst will need to know to function efficiently and effectively.
Top 5 Formulas For Finance Analysts
IF
This formula allows Excel to automate the decision-making in our spreadsheets. The formula lets you develop a logic test (i.e. cell A1 is greater than B1) and then returns an answer if it’s true or false. The formula:
- =IF(logic you need Excel to check, if logic is TRUE, if logic is FALSE)
Such a simple and elegant formula.
You might think you have several logic tests you want to include within one formula, but let me stop you there.
A nested multi-condition IF can be a financial modeler’s worst nightmare! Break IF formulas down over several steps. It will be easier to trace and audit your own work!
If you really want to shine (or make your life easier), check out the other IFs like SUMIFS and COUNTIFS.
Index Match
Next up is the Index Match – these are technically two formulas, but they usually go hand in hand.
I think this combination is one of the most versatile, simple, and powerful formulas. Over the course of my career, this would be in the top 2 formulas I’ve used in frequency (outside of sum!).
It can have a huge impact on the way you analyze and perform calculations over datasets. Whilst VLOOKUP performs a similar function, it is restricted by the following limitations:
- The resulting cell cannot be traced using the F5 > Go To Special function (the biggest one for me)
- If set up incorrectly, it will require a hard-coded lookup row/column, which is problematic when inserting and deleting columns and rows.
- The return range must be to the right of the lookup range
Check this out for a more in-depth Index Match guide.
XNPV / XIRR
Sorry. There are two formulas here (again), but it’s hard not to cover both at once! In Corporate Finance 101, you would have likely covered valuation methodologies and how to calculate returns.
XNPV calculates the net present value for a set of cash flows based on a discount rate input. XIRR, on the other hand, calculates the discount rate based on the investment entry/project costs and associated cash inflows.
Here’s how I think about the Excel syntax for each:
- =XNPV(discount rate, cash flows, model dates) – Remember the first date selected in the model dates range is your valuation date.
- =XIRR(cash flows, model dates) – similar to XNPV, the first date is your valuation date
PMT
This formula calculates the total payments of a loan. This includes both principal and interest. Very handy for calculating mortgages and lease payments.
The PMT formula:
- =PMT(interest rate, number of payments, loan balance)Note: ensure you are consistent with the periods (i.e., monthly interest rate and monthly payments remaining).
- If you want to isolate the principal payment, use PPMT.
- If you want to just the interest, use IPMT.
- Adding both of these together will give you the same answer as PMT.
Here is a tutorial on how to build a Mortgage Calculator In Excel.
Best Practice Modeling
Sorry for going off-piste a bit here (and I’ve also listed out more than 5 formulas above!). I will honestly say best practice modeling ranks above all of these.
Keep your models simple and transparent. Other users of your spreadsheets will perceive the quality of your file within the first two minutes of flicking through the tabs!
A spreadsheet or model should be easily picked up by another user and understood. There’s no point using fancy formulas in a spreadsheet if the only person who can follow it is you! I write about why best practice modeling is important here.
Final Thoughts
There you have it, my top 5(ish) formulas for helping you become a top-tier financial analyst.
Once you have the basics down pat, it’s important that you learn how to interpret and distill the outputs of your financial analysis. Take it to the next level by not just dumping a bunch of charts and tables – what does it all mean?
Thanks for reading, and I hope you found this helpful.
You may also be interested in:
