Hi all,
Our company received some Royalty funding during 2022 which is repayable once we start production / generate revenue. The amount payable each month will be a percentage of sales meaning there is no set repayment amount each month and it will be over a year before any repayments are made.
Our auditors have advised that the funding is to be treated as a financial liability at amortised cost.
I'm trying to create the funding valuation / amortisation schedule but I'm running into difficulties with the amortisation as it's not coming back to nil after the last payment.
I've calculated the effective interest rate (EIR) based on the projected cashflows and have deemed the fair value of the funding to be the amount received. The unwind of the discount should be the opening balance each period multiplied by the EIR.
I can't seem to upload an excel file but does anyone have any thoughts on what I could be doing wrong?
Thanks
Financial Liability at Amortised Cost - unwinding of discount
Re: Financial Liability at Amortised Cost - unwinding of discount
Try using the goal seek function in Excel. Create the amortisation schedule and then use goal seek to determine the EIR that will bring the balance to nil after the last payment.
Good luck!
Good luck!
Re: Financial Liability at Amortised Cost - unwinding of discount
have you seen Marek's example in the knowledge base?
Re: Financial Liability at Amortised Cost - unwinding of discount
Thanks DJP - I've already completed this as an alternative / sense check and came back with an EIR that's a couple of percent lower than I achieved using Excel's XIRR function which I believe is the correct methodology. A couple of percent doesn't sound like much but over the repayment period, it equates to ~$1.5m of a closing balance. The goal seek result may well be the correct answer but for my own sanity I would like to understand where I've gone wrong in my original calculation.
Thanks JRSB. I have seen Marek's example in the knowledge base which was helpful but it didn't help me pinpoint what's wrong in my model. Perhaps the variable payments and period of no payments is driving the difference.
Re: Financial Liability at Amortised Cost - unwinding of discount
The XIRR function assumes that all periods are equal, which is not always the case (as your example shows). If you use XIRR you will almost always get differences. The goal seek function is way more reliable.