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
Creating an overdraft facility in Excel financial model
Answers
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?
1
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.
Here's a treasure trove of free excel spreadsheets, free
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
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.
1
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.
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
1
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.
Robert,
We've reached the point of diminishing returns from online discussion, so please send me the spreadsheet and I'll have a look.
Filed Under:
Budgeting & Forecasting