The ever-expanding scope of treasury operations and risk management activities means that treasurers need to make multiple complex calculations, quickly and accurately, on a daily basis. In this blog post, we’ll talk you through the principles and methodology behind fee amortization. We’ll also give you some useful tools and resources to simplify the task and ease the burden in your company.
Back to Basics: What is Fee Amortization?
We’re sure you’re already up to speed, but before we get started, let’s just clarify what we’re talking about here.
Fee Amortization is the process of matching the costs of a loan to the accounting periods during which the loan is outstanding.
In other words, making sure that when a debt is carried on your books, your records not only accurately reflect the interest cost that month or quarter, but include fees associated with the debt. This means that rather than putting through all of your debt fees in one big lump (usually upfront), accounting standard requires you to apportion it appropriately over the life of the loan.
What are the Rules for Treasurers?
When it comes the kind of loans handled by treasury, this can become a complex issue. There are significant fees and costs wrapped up with debt raising.
To start with, you need to amortize fees using the effective interest rate (EIR). This also needs to be determined in line with the relevant accounting standard, for example IAS39, IFRS 9 or FRS102 S11.
Let’s look at IFRS 9. Under this standard, the definition of EIR is as follows:
The rate that exactly discounts estimated future cash payments or receipts through the expected life of the financial asset or financial liability to the gross carrying amount of a financial asset or to the amortised cost of a financial liability. When calculating the effective interest rate, an entity shall estimate the expected cash flows by considering all the contractual terms of the financial instrument.
IFRS 9—Appendix A
The rules are pretty similar for IAS 39 and FRS 102, but not identical, so make sure you take a good look at the details of all accounting standards that apply to you and get clearance from your auditor before you start.
So… What’s the Best Way to Work it Out?
Okay, let’s get down to the details. Using the (free) worksheet below, we’re now going to show you how to compute the fee amortization profile associated with loans and debt issuances.
Fee Amortization Worksheet
How Do I Use the Worksheet?
This fee amortization worksheet is made up of two resources, used for different types of loan profile. The first is for calculating amortization on loans with bullet principal repayment on maturity. The second should be used if principal is repaid over the life of the loan.
We’ve filled these documents out with example figures for guidance. To use them yourself, simply replace the loan structure and fee amounts with your own numbers as appropriate and re-compute the amortization amounts.
Talk Me Through the Process
Okay, let’s take a detailed look at the fee amortization model and how to amortize fee on a loan with bullet repayment.
For the purposes of this demo, we’ll use the following loan:
|Interest/Coupon||6.15% pa (Act/365)|
Here’s how that looks in the worksheet:
Now let’s assume a fee of 45,000 is paid on draw down. The net cash flow is shown below:
Based on the above, the net loan is 955,000.
Next, you need to enter the relevant EIR, using the Excel Goal Seek Feature.
As you can see from the table below, in our example, we begin with an EIR of 7%.
N.B. These are the equations that are being used to work out the period interest, period repayment and closing/outstanding figures:
Okay, when we run these calculations using 7%, you can see that there is a negative net residual balance on the maturity.
Using the goal seek tool in Excel, we can now determine the required EIR that will completely repay the net outstanding balance:
Let’s take a look at the final results:
As you can see, the required EIR is 7.8616299% (rounded).
Below you can see the amortized fee balance. This profile displays the amortized fee balance at the semi-annual dates when you need to make your interest payments.
But for accounting, you also need to know the unamortized fee outstanding at each end of each month.
You can do this very easily in the worksheet using the free linear interpolation calculator. Simply enter the semi-annual dates into the table below and it will interpolate the unamortized balances on those month-end dates from the semi-annually.
As fee amortization is not a straight line – it’s actually curved the linear interpolation for the month-end dates are an approximation only. A better estimate is to use cubic spline interpolation. This will generate a curve that runs through all the semi-annual points.
Accounting standards are changing and evolving all the time – and they’re getting more complicated and onerous, too.
The most recent standard requires a fair bit of calculation to reflect the carrying value of loans derivatives in financial statements. These calculations take a ton of time and it’s all too easy to make mistakes.
Worksheets like these take out some of the strain, but to really tackle the workload, you’ll need to invest in top technology. For operational purposes, the best practice really is to seek out a powerful treasury management system (TMS) from a trustworthy vendor.
That’s because a well-chosen treasury management system will automate tasks like these. They’ll do all of the calculations for you in the background and generate the appropriate journal entries for your accounting system.
What better way to reduce errors and lighten the workload in your organisation?
We hope you found this worksheet helpful – but we’re always looking for ways to improve. If you have any questions or suggestions for making this worksheet better for other users, or if there are other models we could create that would help with your day-to-day treasury needs, let us know! Drop our team an email at firstname.lastname@example.org. We’d love to hear from you.