I'm fairly new to using functions, and not very handy at all with nesting functions inside functions. I've looked through several other threads and haven't had any luck. It's very possible I'm just searching for the wrong thing.
We pay a portion of our employees' student loans as an incentive for being employed with us. Each year they have to request the payments, but some don't remember to submit their requests until a few years have passed. I've created an excel calculator that is intended to help me calculate payments for up to six years (most annual payments we'll make), which will deduct the previous year's payment from the current balance to give us the new current balance. Depending on when they joined our company, they can receive a max of $3000 or $7500 per year.
Typically the program will pay 15% of the disbursed amount of each loan, up to the annual max. If the sum of using 15% would make the payment greater than the annual max, the payment for each loan is calculated based off of each loan's percent of the total of all loans. If the current balance is less than either of those, we just pay the current balance (hence needing to deduct each year's payment from the next year so we don't overpay any particular loan).
I've been able to get excel to calculate 15%, or the percentage of the total if 15% exceeds that annual max, but I can't figure out how to get it to populate the current balance while keeping the other two conditions in mind.
I've attached the calculator that I've created thus far. Any help would be greatly appreciated!
Moderator's note: Your file has personal data, which I removed. Please do not include personal data in posts or files. I put a note here instead of a new post so that your thread would still show up in the Unanswered Threads list.
Bookmarks