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 Best Practice includes
1 Excel Model
Ivan Klykov offers you this Best Practice for free!
download for free
Add to bookmarks
4.7 / 5 (34 votes)
- Rate this Downloadable Best Practice
Write a review
- Vasile Popovici the Excel guru(last updated: 10/07/2021 18:34)
- Orianna Rangel 914(last updated: 17/05/2021 18:43)
- Stephen Wachira(last updated: 22/03/2021 06:45)
- mohamed Kamal(last updated: 07/10/2020 02:24)
- Amir Ganic(last updated: 16/07/2020 07:07)
- Ityo Elvis Taveretem(last updated: 07/05/2020 10:49)
- Andries Engelbrecht(last updated: 14/04/2020 12:55)
- Andre Poyser(last updated: 31/01/2020 19:32)
- Helimah Kemboi(last updated: 14/10/2019 09:57)
- Sonam Berde, CFA(last updated: 09/10/2019 18:40)
- Keisha Gill(last updated: 08/10/2019 17:34)
- Mirza Waleed(last updated: 03/10/2019 18:51)
- Vladimir Petropoljac(last updated: 23/08/2019 08:49)
- Frank Isla(last updated: 08/08/2019 16:32)
- Choi Tony(last updated: 01/07/2019 01:18)
- Sampath Dinusha(last updated: 05/03/2019 12:22)
- LUAY BREAM(last updated: 20/02/2019 11:21)
- Ahmed El-Saba, Ph.D(last updated: 15/01/2019 06:50)
- Anna Fedorchenko(last updated: 01/01/2019 22:55)
- Sihle Sibeko(last updated: 15/12/2018 10:12)
- 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)
People using this Best Practice also downloaded
Commercial Real Estate Deal SizerEasy-to-use Commercial Real Estate Deal Sizer to quickly analyze acquisition opportunitiesfinancereal estatefinancial modelingexcelcommercial real estate324add_shopping_cart$19.00by Julian Scheeff
Complete Private Equity ModelComplete financial model for valuing, forecasting, and acquiring companies in the form of private equity investments.investmentfinancevaluationleveraged buyoutdcf3,342add_shopping_cart$89.00by DW Financial Services
Real Estate - Hotel/Resort Development Excel Model TemplateComplete financial model for financing, building, operating, refinancing, and selling hotel/resort property types.investmentfinancereal estatepropertyprivate equity4,092add_shopping_cart$40.00by DW Financial Services
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 management24,931add_shopping_cartfreeby Matt Carroll
LBO (Leveraged Buyout) Excel ModelComplete LBO (Leveraged Buyout) model for financial analysis of potential investments and acquisitions.investmentfinanceleveraged buyoutprivate equityinvesting2,284add_shopping_cart
LBO Model - Company Valuation ToolAn LBO Model to learn how to Value all types of Businesses Like a Private Equity Professionalfinancevaluationleveraged buyoutprivate equityfinancial modeling4,600add_shopping_cartfreeby Jon Taylor
Wind Farm Development Excel Model TemplateComplete financial model for financing, building, operating, and selling wind farms.developmentinvestmentfinanceprivate equityinvesting1,783add_shopping_cart
M&A Case Study PowerPointM&A Case Study PowerPoint Submission for CFI's 2019 Financial Modeling Competitionacquisitionpresentationfinancecase studyfinancial modeling1,348add_shopping_cartfreeby Justin Ho
Financial Modeling In a NutshellFinancial Modeling Guide with Free Excel Model Samplefinancevaluationfinancial modelfinancial modelingexcel1,772add_shopping_cartfreeby P MB
Real Estate - Property Acquisition Excel Model TemplateComplete financial model for purchasing, holding, and selling all investment property types.investmentfinancereal estatepropertyprivate equity1,962add_shopping_cart$25.00by DW Financial Services
Any questions on Excel Revolving Credit Calculator For Annual Models?
The user community and author are here to help. Go ahead!