Excel question for Management Fee calculation

you'll need an amortization table likely no matter what...so it's good you've built that out. I've attached an excel file here with 4 options: (Post): interprets your request verbatim (A): uses your request, but changes the dates so the end period are annual 12-mo periods (B): Uses a monthly ending balance * 1.25%/12 (C): Uses a monthly beginning balance * 1.25%/12

Without knowing your task at hand, I think you need to make a few small tweaks. Again I am making assumptions here so if I'm wrong please ignore and just go with option (Post).
(1) the dates you provided are mis-matched. It looks like you're looking for end of year balance...meaning it should be as of 12,24,36,48, etc... not 12+1, which assumes next yrs beginning balance (2) You are using an end of year annual place in time balance of the loan and multiplying it by an annual rate. Assuming the loan is like most, you will make monthly payments on it. Thus your ending balance will change each month. Therefore, taking the ending balance in month 12 and * by 1.25% would imply that balance has been the same for the entire year. Instead you need to take to ending balance each MONTH, and multiply it by 1.25% / 12. As a general rule of thumb, whenever dealing with loans/amortization schedules, always defer to using a monthly formula. (3) You are multiplying it by the ending balance. This could go either way, but typically the way loans are structured is the interest is calculated based on the BEGINNING balance of the period in question...so your fee should follow as well.

If nothing else, applying these changes (switch from 12,25,37 to 12,24,36; use monthly bal * rate/12; use beg bal instead of end balance) earns you a bigger fee by 6.4% - nothing to sneeze at. And this structure I described is perfectly acceptable and really more common practice, so you shouldn't have any issues passing it through whoever your deal is with.

hope this helped.