Loan amortization schedule in MS Excel | Effective Interest rate | Transaction cost | IFRS | PMT

Want to calculate house finance installment amount based on annuity? Got confused about the effective interest rate concept? Learn the easiest way to make the loan repayment schedules.

Also if you work at company that follows IFRS, I have included journal entries.

Download the Excel file here:

For small leasing business with multiple customers:

#IFRS #Amortization #Transactioncost # Loan #ICAP #ICAI #ICAEW #FINANCE

[anthonyvazquez]

fantastic video. Great example comparing the version of the loan w/debt issuance cost and w/o debt issuance cost. Also, loved the use of the IRR function in excel to come up w/the effective interest rate. 5-star video

[demetriosnikolaou]

Hi there, loved your video! can you please clarify this: if there are no transaction costs initially then theres is no need to change the carrying value according to IFRS 9? i.e. the amortization cost as provided by the bank amort. schedule is the same as the one that IFRS 9 states? Do you always have to use the effective rate and account for different principal balance each year than the banks' table? Is it obligatory for a company presenting statements in IFRS?

[alrushdictg]

In case of Default/ Overdue loan ( in the mid of Term Loan tenure) --please make a tutorial

[jishin]

I have a doubt that - is this amortization cost method will be possible if the interest rate is fixed based on LIBOR (floating rate). Coz in that case we dont have the total cash outflows on Day 1. Can u pls let me know in such case whether we can go for FVTPL recognition rather than Amortised Cost method

[sayednab]

can you do one one with Variable interest rate and LIBOR

[jupatj]

Hi, thank you very much for your kind explanation, only thing I'm not completely sure is; shouldn't the ammortised costs bring down the EIR? Given the fact that the higher the transaction costs are, the lower the effective yield on the loan will be. So in your example, shouldn't it be the opposite way around? Net inflow = 4.8M + 0.5M, bringing the EIR to 0.287%, which makes sense if the company is earning 515K interests from the loan, but has to pay 500K for the related transaction costs.

[stoyanstoitzev]

OB of liability 4, 300, 000 - 181, 496 repaid does not equal EB as per the table 4, 154, 665 in period 1?

[christoslefkimiotis]

So you calculated the effective interest rate as if there were no transaction costs??? what if you also had to make a balloon payment at the end (i..e. the capital...) nice video