VBA for Project Finance Models | Circular Debt Calculations

Name: VBA for Project Finance Models | Circular Debt Calculations
Rating: 4.6 (50 reviews)

Eliminate circular references and enhance your model's calculation performance by 10x or more!

Highest Rated

Created byDealMaker Financial Training

Last updated 6/2020

English

What you'll learn

Excel VBA
Advanced project finance modeling
VBA for circular problems in project finance

Course content

5 sections • 40 lectures • 4h 32m total length

Why Learn VBA???3:48
My VBA journey, and why made this course!3:30
Learn VBA through a project-like, hands-on approach to build financial models for project finance and circular debt calculations, drawing on eight years of Excel experience and real-world deals.
Course prerequisites: knowledge, experience and software5:21
How to learn to code FOR REAL!2:41

Setting up the editor, text types and text events12:03
Enable the developer tab, open the VBA editor, and master editor text types, syntax checks, and debugging features to write and debug Excel VBA code.
The main code window10:47
VBA editor modes6:02
The Immediate window4:49
The Locals and Call Stack windows9:02
Use the locals window in break mode to monitor current procedure variables and values as you step, and pair it with the call stack window to map active calls.
The Watch Window10:22
The Project Explorer and Property windows6:59
TEN Top Tips for using the VBA editor8:24
Discover ten essential tips for mastering the VBA editor: line continuation with underscore, colon-line joining, procedure navigation, variable inspection, find and replace, precise undo/redo habits, and explicit variable declarations.

Review of debt calculations with circular logic6:50
Resolving breakpoints using Goalseek - introduction7:55
Resolving breakpoints using Goalseek - solution10:30
Resolving breakpoints using CopyPaste - introduction4:33
Learn to resolve circular debt calculations by using copy-paste as an iterative break, replacing goal seek with a while loop and range value property access for faster VBA.
Resolving breakpoints using CopyPaste - solution1:36
Comparing the Goalseek and CopyPaste methods5:49
Handling multiple circular calculations - introduction7:56
Handling multiple circular calculations - solution6:08

The hidden cost of the breakpoint method4:04
A solution hidden in a 3,000 year old maths equation!7:33
Explore a 3,000-year root-finding algorithm for linking price and equity return in project finance models, and implement its iterative mechanics in VBA to build a fast, transparent price-discovery engine.
Creating a custom solver to replace Goalseek - introduction5:25
Creating a custom solver to replace Goalseek - solution8:52
Making our custom solver more flexible than Goalseek5:14
How the new solver algorithm works11:10

Another way to approach our problem2:10
Introduction to shadow modeling in VBA9:44
Master a shadow model in VBA for circular debt calculations, using off-sheet variables and arrays to replicate time-series, avoid circular references, and compute cumulative revenues with for loops.
Getting cell data in and out of VBA - the FAST way!8:48
How to use User Defined Functions (UDFs) for shadow modeling5:57
A UDF for the debt-CFADS circular - introduction9:51
A UDF for the debt-CFADS circular - solution10:56
A UDF for the debt-IDC circular - introduction9:03
A UDF for the debt-IDC circular - solution4:52
A UDF to handle circular interactions of circularities! - introduction9:58
A UDF to handle circular interactions of circularities! - solution3:13
Implement a VBA UDF to handle circular interactions in project finance models, compute depreciation and IDC-driven debt outputs, and return debt, financing costs, and maximum debt as a three-value array.
Professionalism - the risks of shadow modeling with UDFs7:16
Explain the professional risks of shadow modeling with udfs in project finance models, comparing utf and breakpoint approaches in vba, and highlighting audit impact and setup challenges under deal pressure.
Professionalism - the rewards of shadow modeling with UDFs5:46
Professionalism - ways to increase transparency and auditability with UDFs6:52
BONUS LECTURE0:56

Requirements

Knolwedge of project finance modeling
Strong Excel skills

Description

WORK IN PROJECT FINANCE???
I BET YOU HAVE NO IDEA WHAT TIMESAVING IMPROVEMENTS YOU'RE MISSING...!!!

Any of the following sound familiar???

You drive yourself crazy trying to model without tripping another circular reference
You have to wait for AGES for your model to solve ONE scenario
You have to wait longer than the age of the Universe to solve MULTIPLE scenarios
You groan whenever assumptions are updated (*ahem* corrected!) because you have to wait and solve everything again!
You have NO idea how to use your model to solve for input values which achieve target outcomes, because Goalseek doesn't work in your CopyPaste macro model!

The reason you have all these time costs is because circular logic BREAKS EXCEL

The solutions most people know about are slow, and often used poorly, which slow down calculations even MORE!

But there's no avoiding it. You HAVE to use circular logic in non-recourse debt and project finance modeling .

WHY? Because it affects one of the single most important questions you and your work are being used to answer: HOW MUCH DEBT CAN A PROJECT RAISE???

So let me ask you....

What would it be worth to you, to be able to do those tasks 5x faster...???
Let's be clear... that means a 30 minute model run finishes in 6 minutes!

What if I told you, you could calculate 10x faster...???
Your 30 minute calculations complete in just 3 minutes!!!

For those of you using long-life models time and time again, these timesavings will be HUGE.
They will radically redefine the amount of time consumed by your work!

This course will cover industry-standard best-practice approaches
They are the same ones taught by top course providers like Mazars/Corality and EY...
But *this* course also adds the extra details that will help you use these techniques in the most efficient way possible!

This course will teach you mathematical secrets which will allow you to Solve circular models for any input!
Yes! You WILL be able to use this solution even in a models which have copypaste macros in them!
You will learn a method which is faster and more robust than running your model through endless copypaste-goalseek loops!
Best of all, this method lets you keep a SIMPLE copy-paste macro at the heart of your model!

This course will go beyond best practice - if you can handle it!
You will learn advanced methods that go far beyond what they have time to teach you in standard courses.
This could be a real challenge but if you succeed, your models will:

Calculate 10x faster or more
Not need manually macro triggers
Your models will work like a normal Excel file without circularity breaks
You will be able to use Excel's Goalseek to backsolve again

You will also be coached to use this knowledge responsibly with professional awareness
You'll learn how to understand and assess the tradeoffs that come with choosing between standard and advanced solutions so you can apply the one that is best suited to your current project.

Obviously, this is a highly specialised course, but if you model non-recourse debt or project finance deals, circular problems are probably the bane of your life...

Yelling "I'M MAD AS HELL" won't help you... but taking this course WILL!

Who this course is for:

Analysts/modelers with 6 months or more experience or more in project finance
Students following on from my project finance course

VBA for Project Finance Models | Circular Debt Calculations

What you'll learn

Explore related topics

Course content

Introduction & VBA study preparations!4 lectures • 15min

Mastering the VBA editor8 lectures • 1hr 8min

Mastering the "breakpoint method" for handling circular calculations8 lectures • 51min

Backsolving breakpoint models - without Goalseek6 lectures • 42min

Handling circular calculations 10x FASTER, WITHOUT manual macro triggers14 lectures • 1hr 35min

Requirements

Description

Who this course is for: