more-arw search

Q&A Forum

Creating an overdraft facility in Excel financial model

creating overdraft facility excel financial modelI need to create an overdraft facility or variable loan so that the amount of the loan varies monthly according to the requirements of the business. When I create a formula in Excel to calculate the interest on the loan needed for the current month it creates an error message and is circular. This is because the cash balance at the end of the month includes the loan, which depends on the cash balance. How do I fix this?

Answers

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

Robert,

Let's take an example...

Suppose at the end of January you owe $1000. If interest were 1% per month, at the end of February you'd owe $1000 + $10 on the beginning balance.

If you borrowed an additional $100 on February 15, you'd owe approximately $100 more plus about .5% of $100, or 50 cents for the interest.

This would give you a total of about $1110.50 at the end of February...if my mental arithmetic is correct.

Then, in March, you'd repeat that logic, with no circular reference.

What am I missing?

Robert Menzies
Title: Director
Company: Menzies Aviation Consulting
(Director, Menzies Aviation Consulting) |

Hi Charley,
Sorry my query was not clearer.
The missing bit is that the business needs large amounts of funds spent on fixed assets, so I need to have a formula that says in February there is say $2,000 outgoings on capex so the additional funds are $2,000. Over the 10 year plan I need a formula that checks in what months does the business need to borrow more. Once you have a logic formula to check IF the cash is < zero, then borrow the missing amount, then it becomes circular.
So the calculations are yours PLUS a logic test to see WHEN the business needs to increase or decrease the loan.

Sarah Jackson
Title: Associate Editor
Company: Proformative
(Associate Editor, Proformative) |

Here's a treasure trove of free excel spreadsheets, free accounting spreadsheets, plus all kinds of other excel resources, such as tests, tutorials and more:

https://www.proformative.com/resources/free-accounting-spreadsheet

Plus, this free "Excel Shortcuts Cheat Sheet"

https://www.proformative.com/whitepapers/excel-shortcuts-cheat-sheet

Enjoy!

Best... Sarah

Don Wall
Title: CFO
Company: Double B Foods, Inc
(CFO, Double B Foods, Inc) |

Robert,

Sounds like the cash calculation is in a balance sheet format, cash is dependent on the loan balance and other balance sheet items. If I am correct, take the calculations of cash and loan to a separate section, then if cash <0 you can add the loan proceeds to bring to 0, and adjust both cash and loan balances on the balance sheet from the separate calculation areas. This should eliminate the circular reference.

Robert Menzies
Title: Director
Company: Menzies Aviation Consulting
(Director, Menzies Aviation Consulting) |

Don,
Thanks but when I enter an “IF’ logic formula to borrow the amount I am short Excel gives me a circular reference and instead of correctly calculating the amount of the loan as negative cash shortfall it just gives the result “o” and the circular reference.
I tried to move the calculations to another sheet or below the balance sheet but still have the same problem.

Jake Feldman
Title: Managing Director
Company: Global TaxFin Advisory Group LLC
(Managing Director, Global TaxFin Advisory Group LLC) |

Hi Robert,

This is a classic funds flow spreadsheet analysis issue, especially if you are preparing both income statement and balance sheet projections. Besides the cash/debt circularity, the balance sheet also depends on current period income in the equity section but income depends on interest income/expense which depend on cash/loan balances. You can solve this with simultaneous equations or proper sequencing of equations in Excel. One approach is to setup a separate plug line at the end of the balance sheet that is the net cash/debt difference between all the other balance sheet items, excluding cash/debt. If the plug is > 0, you set cash = plug. If the plug < 0, you set debt = -plug (minus plug). Interest income/expense should be equal to rate*average plug balance [(prior+current)/2]

It's OK to have circularities in Excel as long as they are properly sequenced, so that Excel can follow its iterative sequential logic to solving such circularities.

Hope this is sufficiently clear.

Jake

Robert Menzies
Title: Director
Company: Menzies Aviation Consulting
(Director, Menzies Aviation Consulting) |

Hi Jake,

Thanks but when I enter an “IF’ logic formula to borrow the amount I am short Excel gives me a circular reference and instead of correctly calculating the amount of the loan as negative cash shortfall it just gives the result “o” and the circular reference.
I tried to move the calculations to another sheet or below the balance sheet but still have the same problem.

Jake Feldman
Title: Managing Director
Company: Global TaxFin Advisory Group LLC
(Managing Director, Global TaxFin Advisory Group LLC) |

Robert,

We've reached the point of diminishing returns from online discussion, so please send me the spreadsheet and I'll have a look.

9933 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.