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
- 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
Financial Modeling In a NutshellFinancial Modeling Guide with Free Excel Model Samplefinancevaluationfinancial modelfinancial modelingexcel1,119 remove_red_eye
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 model1,732 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 harder8,392 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 funds908 remove_red_eyefreeby Sumit Kapur
Microsoft Excel ShortcutsWork on Excel Faster with this checklist of excel keyboard shortcutsfinancetipsfinancial modelingexceltricksmethod146 remove_red_eyefreeby Firmex
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 cheese112 remove_red_eye
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 wheel21 remove_red_eyefreeby Eloquens Team
Three Statement Financial ModelingA guide to help investment bankers and associates know how to create a three-statement financial model.financeinvestment bankingfinancial modeling78 remove_red_eye
Cash Sweep Concepts Excel TemplateUnderstand the four concepts of cash sweeping.financemodeltemplateexceldownloadcash sweepconcepts418 remove_red_eyefreeby Michael Shane
Sensitivity Analysis Using ExcelIn this video you will learn how to carry out sensitivity analysis using Excel.analysisfinancesensitivitydcfsensitivity analysisdiscounted cash flowexcel123 remove_red_eyefreeby Finance Walk
Any questions on Excel Revolving Credit Calculator For Annual Models?
The user community and author are here to help. Go ahead!