Excel question for Management Fee calculation
Hi guys,
Long time lurker here. I have an intern project that my boss asked me to help setting up an excel formula to calculate the management fees for a 5 year debt deal we are originating. The way the management fee works is it takes an annual 1.25% fee on the basis of the loan. Can anyone help me with the formula for this? I have built out an amortizaton table and want the fee to calculate on the loan balance at Period 1, 12, 25, 37 and 49. I have caluclated the fee the easy way (just multiply 1.25% by the balance of the loan during those periods) but I wanted something more dynamic. Does anyone know where to start? I have been trying if/and functions to no success.
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.
looks like I don't know how to add an attachment to a reply...so below is a screen shot with the formulas types out. if you know how to add attachment let me know and ill post back
Do you mind sending this over - wanted to look at the fee difference. I sent you a PM to email the spreadsheet if you are able and thank you in advance.
sorry the highlighted long hand formulas in the amortization are wrong since I moved the table and forgot to update formula cells...simply change K for B...i.e. the "per" part of IPMT and PPMT needs to be the month number from column B
Hi Post Hoc - thank you very much for the reply. I am going to try this now and see if I can come up with the desired result. I will keep you updated - I will send you a PM to see if you can email me your sheet directly. Thanks again for your help
Qui neque possimus eius eligendi ex sunt. Non nulla assumenda quo velit. Qui fuga aliquam ut doloremque officiis et. Officiis provident mollitia accusantium officiis quos velit corporis ut.
Eum laborum atque quidem rerum harum distinctio. Cupiditate ut rerum vel repudiandae aut id. Consequatur minus temporibus quisquam aliquid illo.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...