Creating an overdraft facility in Excel financial model

I 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?

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.

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.

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.

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.

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.

Business Exchange

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.