XIRR not amortising

[StaalB]

Hello all. I've been wrestling with this spreadsheet for a while now. I am trying to take a series of interest payments and loan receipts/repayments on varying dates and a rate based on Libor and calculate the XIRR. The loan is received net of financing fees. I need to amortise the net loan at the internal interest rate. Because the interest rate and payment dates are variable I use XIRR. At each payment date I the interest rate changes so I true it up to the new XIRR rate. The regular cash amortisation I get right but not the XIRR amortisation. I end up with unpaid £814,649 which seems to be over accrued interest.

Loan £361,585,500
Fees £4,563,426

You can check my historical XIRR by changing the Last IPD in cell F11 to the any of the previous IPDs.

I don't know if I am allowed to do this but I have uploaded a googledoc with what I have done. I don't think it is possible for someone to help without looking at the whole schedule.

Amort - Google Sheets

Any help would be much appreciated.

 

[joeu2004]

I am not taking the time to try to understand what you are doing. But there is one glaring mistake that accounts for the 814,649 error.

Excel XIRR returns an annual rate compounded daily. But you are using it as simple annual rate.

For example, for the XIRR in J16, you calculate the daily interest amount to be Q15*J16/A16 in K16, where Q15 is the previous balance and A16 is the days per year.

Instead, you should convert the XIRR to a simple annual rate -- NOMINAL(J16,365) -- or otherwise calculate a daily rate. For example, Q15*(1+J16)^(1/365)-Q15 in K16.

Note that I use 365, not A16. That might be another issue: Excel XIRR always assumes a 365-day year. Hence, (1+xirr)^(1/365)-1 is the daily rate that XIRR uses.

When we copy the corrected formula into K17:K1112, Q12 becomes -1820, a reduction of 99.78% in magnitude.

In fact, the calculation "error" goes from a large positive amount to a small negative amount. It is unclear whether the small "error" is significant. And if it is, perhaps there are other issues in your design that accounts for it, like the difference between 365 and 366-day years. (TBD)

 

[StaalB]

Thought I had clicked reply yesterday. Thanks for your reply anyway. -1,820 is certainly an acceptable difference as it's 0.005% of the total £35m cash interest to be paid over the life of the loan. Is it not maybe down to the XIRR being an approximation?

 

[joeu2004]

-1,820 is certainly an acceptable difference as it's 0.005% of the total £35m cash interest to be paid over the life of the loan.

Good! That was my thought, too.

Is it not maybe down to the XIRR being an approximation?

I thought that, too.

But the XIRR still should cause the NPV of the cash flows to be relatively close to zero. And 0.005% (5E-5) might qualify.

However, I suspect it is some flaw in how you apply the XIRR results in your model. But my suspicion is out-of-school, so it might be wrong. As I said: I am not taking the time to understand your model and your use of XIRR in it.

I wish I had the time. Off-hand, I don't understand why you need to use XIRR, in the first place, since you have the "actual" forecasted LIBOR rates. Oh well, it's not worth your time to try to educate me.

 

[StaalB]

I am doing it for accounting purposes. I need to expense the £4m financing fees over the life of the loan using the effective interest rate. I could do it a much simpler way assuming cashflows are annual and using IRR which would be "close enough". Although you still have to end up re-calculating the IRR every payment date when the rate changes. I just wanted to see how it would work if I did it more accurately.