I am taking a managerial accounting class, and our project has a few specific rules to follow. The class is not excel based, all the calcs we do are pencil and paper, this project was extra credit.
The rule is, we have an open line of credit that we may borrow from, to maintain a minimum cash balance of at least $5000.
Any time our cash balance is > $5000, we are to borrow as much as necessary to maintain our minimum of $5000.
Our credit line is in $1000 increments, and interest is calculated at 18%, annual, but applied only at repayment, for the time the funds were borrowed.
I wrote a formula to use to calculate the amount borrowed...
=IF(B14<0,ROUNDUP(SUM((5000)+ABS(B14)),-3),0)
B14 - our cash balance (shortage),
ROUNDUP, so that if we are $1 short of our minimum, we borrow in increments of $1000
5000 - our cash minimum
ABS(B14) - cash value can be negative
The opposite rule applies; if we have a balance on our credit line, we are to pay back as much as possible, above our minimum $5000 cash surplus, plus interest.
I cannot figure out how to "backwards" the formula, so that we pay a portion on the outstanding principle if we have the funds, pay nothing if we do not have at least $5000 plus $1000 + interest in surplus, or pay all. The pay all part is not hard to figure out.
This is just extra-credit, and I'm doing it for fun, but I'm sure there are real world applications.
Any help would be appreciated.
Bookmarks