07-07-2020 12:41 PM
07-07-2020 12:41 PM
I need some help. I'm trying to come up with a formula that applies a payment first to one category until it has been paid down to zero, and then moves on to the next category. I also need for it to do two other things: 1- carry over any remaining monies to the next category and 2- calculate monthly interest.
My order of operations are
payment must be applied to
costs ( until paid down to zero) * any remaining $$ after cost balance is zeroed out goes to
fees (until paid down to zero)*any remaining $$ after fees balance is zeroed out goes to
interest (so long as there is a balance in principal, this category changes daily until the next payment is made. then payment is subtracted from balance) if interest does go down to zero, the remaining $$ is applied to principal, But so long as there is a balance in principal, interest will still accrue until principal is completely paid off.
I hope I'm not confusing anyone. Somebody here has to speak/read/write "crazyladyworkneeds"
07-07-2020 02:28 PM
Is it safe to assume you have a start at a worksheet in which you're trying to do this? I ask because it would be a lot easier to help you if we could see what you're actually working with (not just a verbal image; not even a visual image; the actual Excel spreadsheet)...
I will say it probably won't be "a formula" -- as in a single formula--to accomplish all that. It might be possible, but it's often difficult to decipher complicated formulas, especially for those who are relatively new to Excel and it's wonders. So if you've set your mind on somehow coming up with a single formula, I highly recommend first taking each operation and doing it more simply, even if it means adding columns (or cells) that are simply temporary staging points for the next operation. Once you've done all that to the point where it all works, you can start working to combine steps via nested formulas and the like.
07-08-2020 09:34 AM
Give this a try. It seems to me it's working accurately. I did NOT, however, check all your formulas. The only ones I modified are those in columns K, L, M, N.
Let me know either way...if it doesn't work, let me know what's happening that should not be.