Publication number: ELQ-56467-1
View all versions & Certificate
Excel Revolving Credit Calculator for annual models
An automatic excel algebraic approach for revolving loan facility in annual financial models (no macro & circular links)
This model is for:
- financial modelers
- corporate finance
- investment professionals
Is it impossible to make a fully automated revolving credit calculator for annual models without using macros or circular references? Many would say yes, but actually this is doable.
The attached excel is a very simple annual model that includes a fully automated algorithm that allows to calculate an optimal schedule of debt payments.
When you have a monthly financial model, the algorithm is relatively simple as the issue is solved by shifting the income tax and interest payments to the next month. Closely matching what happens in a real case actually.
However, when wanting to use an annual time frame in a financial model, it becomes a real pain to find an optimal debt when you have a revolving loan facility implemented.
The solution provided here is efficient thanks to a smart combination of different flags.
Some checks also included show:
1/ When cashflows are positive (by looking at the interest payments and income tax savings), all excess cash is swept to reduce the amount of debt.
2/ When the investing and operating cashflows are negative, there is a calculation of additional debt required to cover the cash gap. It is calculated so that the ending cash balance corresponds to the minimum required cash balance.
Models using circular references and macros are harder to use, audit and update and very often subject to errors. In addition it is impossible to include a common sensitivity analysis in models built this way.
Consequently, it is fairly common to come across financial modelers who make manual adjustments to debt calculations in the case of operating and investing cashflow change. Not a best practice and definitely not the most optimal.
This is my relatively simple solutions to the issue described.
Feel free to share it out.
- Ivan Klykov
This business tool includes
1 Excel Model
Ivan Klykov offers you this business tool for free!
download for free
Add to bookmarks
- Nathan LE(last updated: 28/10/2018 18:34)
- Mostafa Ramdan(last updated: 18/10/2018 09:32)
- Julius V Sihombing(last updated: 16/10/2018 03:14)
- GIDEON A. QUIST(last updated: 17/07/2018 17:57)
- Mekhradzh Aliev(last updated: 13/07/2018 16:44)
- Ayan Banerjee(last updated: 28/06/2018 10:50)
- Brice Meilo(last updated: 05/06/2018 08:58)
- Ng Aaron(last updated: 04/06/2018 06:37)
- Abhishek Banerjee1982(last updated: 29/05/2018 13:03)
- Elvis Boamah(last updated: 30/04/2018 16:54)
- Jason Burdett MBA MAICD(last updated: 08/12/2017 08:13)
- Mamadou Dr. Bass(last updated: 29/09/2017 09:43)
- Alexandr Marchenko(last updated: 24/04/2017 11:04)
- Dicky Suriakusumah(last updated: 19/03/2017 22:29)
People using this tool also downloaded
LBO Model - Company Valuation ToolAn LBO Model to learn how to Value all types of Businesses Like a Private Equity Professionalfinancevaluationleveraged buyoutprivate equityfinancial modelinglbolbo analysislbo model2,821 remove_red_eyefreeby Jon Taylor
E-Commerce Financial Model for StartupsAn extremely detailed, methodical package for entrepreneurs on how to build a financial model for an e-commerce startup.financestartupskpisfinancial modelingproduct managementecommercee-commerce projectionsfail harder14,318 remove_red_eyefreeby Matt Carroll
Financial Model for a StartupA simple & powerful model to learn financial modeling, for a startup or small businesslearningfinancestartupentrepreneurshipsmall businessfinancial modelingfinancial statementsraising funds1,205 remove_red_eyefreeby Sumit Kapur
Financial Modeling In a NutshellFinancial Modeling Guide with Free Excel Model Samplefinancevaluationfinancial modelfinancial modelingexcel1,410 remove_red_eye
Microsoft Excel ShortcutsWork on Excel Faster with this checklist of excel keyboard shortcutsfinancetipsfinancial modelingexceltricksmethod205 remove_red_eyefreeby Firmex
The Leading Marketplace for Financial Modelers - Eloquens.comEloquens.com is the world's leading marketplace for finding your idea financial models and sharing your best content.financefinancial modelingmarketplacefinancial modelsspreadsheetsknow-howfinancial modelersexcel modelsdon't reinvent the wheel46 remove_red_eyefreeby Eloquens Team
How to Build a Financial Model | The Complete GuideThis is a fantastic guide offering tips and tricks to help you improve your financial modeling ability.financemodelingfinancial modelingexcelcfi25 remove_red_eye
Three Statement Financial ModelingA guide to help investment bankers and associates know how to create a three-statement financial model.financeinvestment bankingfinancial modeling126 remove_red_eye
Revenue Models for Consumer Retail CompaniesTeaching you how to build a revenue model for a retail consumer business.revenuefinancefinancial modelinglborevenue modelexamplehow toretailretail consumer businesschuck e cheese342 remove_red_eye
Cash Flow Statement Template in ExcelCash Flow Statement Template in Excel containing a suggested list of profit and expenses items your business may have.businessfinancecash flowaccountingcashcash flow statementtemplateexcelcalculations541 remove_red_eye
Any questions on Excel Revolving Credit Calculator For Annual Models?
The user community and author are here to help. Go ahead!