IFRS 9-Amortised Cost and Effective Interest Rate Excel spreadsheet

[cottochaco005]

Hi Marek,

Thank you very much for all your input in this community. I have learned a lot from you.

I just want to clarify something in your IFRS 9 Example Amortized Cost Loan spreadsheet that shows sample computation of amortized cost of a loan payable. in your spreadsheet, you used the excel formula XIRR to get the IER. In my spreadsheet, I used goal seek to get the EIR.

Here is my computation next to yours:
https://docs.google.com/spreadsheets/d/ ... ue&sd=true

While our amortization schedules are identical, i noticed that the formulae we used for interest in P/L (column C) are not the same. As a result, our IER is also not the same.

My formula for effective interest in P/L is to multiple the opening amortized balance by the annual EIR and multiply it by the pro rata period covered using 365 days. On the other hand, your formula used some exponent in the computation.

I would really appreciate if you can clarify this one since that has always been the way I have computed for the effective interest expense.

Thank you very much and all the best!

[Marek Muc]

With goal-seek, you're essentially guessing over and over until it's 'good enough', so it might not be the most precise method. Perhaps try XIRR instead and get on with your life?