ModeLoop: Financial Modelling in Excel

Excel data validation is the cheapest insurance in any financial model: it controls what goes into your input cells before a bad entry breaks something. This episode shows how to build drop-down menus, scenario switches and input rules that keep a model clean when someone else opens it.

In this episode:

• Drop-down menus for a Base / Upside / Downside scenario switch
• Keeping lists on a dedicated Lists sheet with named ranges
• Input messages, error alerts and custom validation rules
• Dependent drop-downs with INDIRECT and rejecting duplicates with COUNTIF
• Circle Invalid Data: auditing an inherited model in seconds

📖 Full blog post: https://modeloop.net/Blog/P2E11-Data-Validation-in-Excel-Drop-Down-Menus-That-Keep-Your-Model-Clean
📺 Watch the video lecture: https://youtu.be/atQfF-APl0g
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

NPV, IRR and XIRR are the Excel functions behind every investment return calculation, from DCF valuation and LBO equity returns to solar and wind project finance. This lecture shows how each works, when to use which, and the mistakes that silently break financial models.

In this episode:

• The number one NPV mistake: the Period 0 cash flow
• IRR: how it works and when it breaks down
• Why XIRR, not IRR, is right for real transactions with irregular dates
• Use cases: LBO equity IRR, project finance, DCF, capital budgeting
• #NUM! errors, multiple IRRs and the reinvestment assumption

📖 Full blog post: https://modeloop.net/Blog/P2E10-IRR-XIRR-NPV-in-Excel-Mastering-Return-Calculations-for-Investment-Analysis
📺 Watch the video lecture: https://youtu.be/7PjAB8Um-84
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

The four Excel date functions every financial modeller needs: DATE, EOMONTH, EDATE and YEARFRAC. Learn how Excel stores dates, how to build dynamic period headers and timelines, and how day-count conventions affect interest accruals in project finance and debt models.

In this episode:

• How Excel stores dates as serial numbers
• EOMONTH for month-end periods and loan schedules
• EDATE for consistent project finance timelines
• YEARFRAC and day-count conventions (Act/Act, Act/365, 30/360)
• DATE for dynamic period headers

📖 Full blog post: https://modeloop.net/Blog/P2E8-Date-Functions-in-Excel-EOMONTH-EDATE-DATE-YEARFRAC-for-Financial-Models
📺 Watch the video lecture: https://youtu.be/WrzqeYvk7lw
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

XLOOKUP is the modern Excel function that replaces VLOOKUP, HLOOKUP and most INDEX MATCH patterns with one clean formula. This lecture covers the full syntax, all six arguments, and how to use XLOOKUP for scenario selection, multi-column returns and robust financial models.

In this episode:

• Full XLOOKUP syntax and all six arguments
• Exact, approximate and wildcard matches
• Returning multiple columns at once
• Handling missing values with if_not_found
• When INDEX MATCH is still useful

📖 Full blog post: https://modeloop.net/Blog/P2E9-XLOOKUP-for-Financial-Models-The-Modern-Replacement-for-VLOOKUP-and-INDEX-MATCH
📺 Watch the video lecture: https://youtu.be/NUk-9wG3oo0
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

Stop totalling rows by hand. This lecture covers SUMIF, SUMIFS, COUNTIF, COUNTIFS and AVERAGEIFS, the conditional aggregation functions every financial modeller uses to build budget summaries, ageing reports and model integrity checks.

In this episode:

• SUMIF for single-condition totals
• COUNTIF for counts and integrity checks
• SUMIFS and COUNTIFS for multiple criteria
• Date criteria and wildcard matching
• Budget summaries and ageing analysis

📖 Full blog post: https://modeloop.net/Blog/P2E7-Summarizing-Data-with-SUMIF-COUNTIF-and-SUMIFS
📺 Watch the video lecture: https://youtu.be/3mOjlh5YPfs
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

INDEX MATCH is the most flexible lookup pattern in Excel for financial modelling. This lecture explains INDEX and MATCH on their own, how they combine, and how INDEX-MATCH-MATCH handles two-dimensional lookups in time-series assumption tables.

In this episode:

• INDEX: returning values by position
• MATCH: finding the position of a value
• Why INDEX MATCH beats VLOOKUP
• INDEX-MATCH-MATCH for two-way lookups
• Scenario selectors and reverse lookups

📖 Full blog post: https://modeloop.net/Blog/P2E6-Why-INDEX-and-MATCH-Are-a-Dynamic-Duo-You-Need-to-Know
📺 Watch the video lecture: https://youtu.be/6AEYeUCP3MA
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

VLOOKUP vs XLOOKUP: which lookup function should you use in a financial model? This lecture covers VLOOKUP syntax and examples, the five limitations that can break your models, and why XLOOKUP is the modern standard.

In this episode:

• VLOOKUP syntax with tax-rate and scenario examples
• 5 VLOOKUP limitations that break models
• XLOOKUP syntax and advantages
• Scenario switches, reverse lookups and approximate matching

📖 Full blog post: https://modeloop.net/Blog/P2E5-Lookup-Functions-Demystified-From-VLOOKUP-to-the-Superior-XLOOKUP
📺 Watch the video lecture: https://youtu.be/QkJqPw0PQJ4
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

Master Excel's IF, AND and OR functions and combine them to build models that react to real financial conditions: covenant tests, distribution locks, flags and waterfall logic.

In this episode:

• IF syntax, examples and nested IFs
• AND for all-conditions-true tests (covenants, distributions)
• OR for at-least-one-true flags
• Combining IF, AND and OR in waterfall and covenant logic

📖 Full blog post: https://modeloop.net/Blog/P2E4-Logical-Functions-Explained-IF-AND-OR
📺 Watch the video lecture: https://youtu.be/OXD5i_whMzI
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

Absolute vs relative cell references ($A$1 vs A1) is the single most important Excel concept for financial modelling. Get it wrong and formulas break silently when you copy them across. This lecture covers relative, absolute and mixed references, the F4 shortcut and the golden rule of locking assumptions.

In this episode:

• Relative references (A1) and when to use them
• Absolute references ($A$1) for fixed assumptions
• Mixed references ($A1 and A$1) for time-series models
• The F4 shortcut to cycle reference types
• Using mixed references in sensitivity tables

📖 Full blog post: https://modeloop.net/Blog/P2E3-Absolute-vs-Relative-References-A1-vs-A1-The-Single-Most-Important-Excel-Concept
📺 Watch the video lecture: https://youtu.be/ZF2LJXRTrqQ
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.

Good formatting makes a financial model clear, trustworthy and easy to audit. These conventions are the financial modelling best practices behind every professional model. This lecture covers the professional formatting framework used on real deals: colour coding (blue for inputs, black for formulas), number formats, units and layout conventions.

In this episode:

• Colour coding: blue inputs, black formulas, green links
• Number formats for thousands, millions and percentages
• Consistent layout and column structure
• Formatting that makes models faster to review

📖 Full blog post: https://modeloop.net/Blog/P2E2-The-Power-of-Cell-Formatting-How-to-Make-Your-Models-Clean-and-Readable
📺 Watch the video lecture: https://youtu.be/BIY1BP3natI
🎓 Courses: Excel for Financial Modelling | AI in Project Finance Modelling
🌐 modeloop.net

ModeLoop is hosted by Samir Asadov, CFA, ranked top 3 financial modeller worldwide (FMWC), with 15 years in M&A and project finance for renewables.