Applying XIRR to payment schedule?

Jan 23, 2017 11:15:58 GMT kulerucket said:

If I take loan 476321-01 with a rate of 11% and try to reconstruct the interest rate from the payment schedule using XIRR, I always calculate a little too much interest. Can anyone see what's wrong?

28/12/2016	-€743.28
28/01/2017	€252.32
28/02/2017	€252.32
28/02/2017	€252.54
XIRR	11.74%	'=XIRR(B1:B4,A1:A4)
NOMINAL	11.10%	'=NOMINAL(B5,360)

I thought that maybe it was a rounding error but when I try different loans I get a similar result. There is something systematic I am not taking into account.

Hi

The interest which is applied when Mintos calculates payments is not compounding so means you have more or less Interest/12 per month.
The XIRR calculation assumes compounding interest which means it gives you the interest your would have to apply compounding (1+Interest)^(1/12)-1 to come to this payments.

BR
Mopcku

Jan 23, 2017 22:16:11 GMT kulerucket said:

I have replicated the interest payments using Principal*Interest/360*Days. I then use the figures to get an XIRR but then (I think) change the compounding interest rate to a non-compounding one using NOMINAL(). The documentation on NOMINAL() does seem to follow the formula you gave so I think that I should be getting the right answer.

I have tried the same method to more or less replicate the YTM on loans in the secondary market, but I am always a few tenths of a percent off.

If you use (1+r)^(NumberDays/365)-1 as rate for the period instead of r*NumberDays/365 to calculate your interest payments and then calculate XIRR on this new calculated payment schedule you will get exactly r as XIRR .

I also just tried to understand what this mentioned by you function NOMINAL is doing and found out it is calculating n*[(1+r)^(1/n)-1]. So it is kind of conversion between the rate types.

By the way exactly because of this inconsistency between how rates are applied for interest payments and in XIRR calculation you have higher XIRR than interest for each loan you sell in Bondora secondary market (In case you sell the loan at par),

I dont know i am not so long in p2p and already see so many wrong or let say not very right calculations...

Tried to replicate many calculations and there very funny things i am always finding out