XIRR Compounding Issues

I need to calculate the yield on a series of lease payments. If the payments were at regular monthly intervals I could very easily use the IRR function. Unfortunately, often there is more than 1 payment in a month (not always but sometimes 5 or 6 payments in a month)... But the interest compounds monthly.

As the dates of the payments are irregular I have to use the XIRR function in excel... The problem is I am not sure what compounding assumptions XIRR makes, and how to convert it to a yield based on monthly compounding. If XIRR treats every payment as a compounding period, that would be wrong in my case, because the compounding period is monthly even if multiple payments are made in a month. If it is assuming daily compounding then I need to convert it to monthly.

I don't know if what I am looking for is possible, nor how to do it. I know there is a function in excel called Nominal that seems like it might sort of apply. I also know I can play with the yield using formulas like (1+XIRR)^(1/12)-1 to try and reverse the compounding... But I don't quite know how to convert it to monthly compounding in my scenario.

I have a software called Tvalue that does this accurately (it takes a lot of manual inputting dates and payments line by line) and basically I am trying to get the same result output using excel (much quicker process). The problem is the compounding rules seem to be throwing it off.

I made a similar topic a few days ago but I didn't really understand the issue at the time. Now that I sort of know what is going on, my question on how to fix it has changed to a more formula question - I thought it was appropriate to make a new thread.


Any help would be much appreciated.

The attached picture taken from this Microsoft Support Article rather suggests the algorithm is compounding on a daily basis - or that's the way I'm interpreting it.

I often check these sorts of financial calculations by creating a simple table of
Date, Opening balance, payment, interest calculated, Closing Balance

and plugging in the numbers I want to work with. Which may be similar to the Tvalue software approach but at least you will be in control of the calculations.
But presumably one way or another you will need to input dates and payments line by line whichever approach you use.



XIRR.JPG

Hi,

Could you upload some sample data and indicate what results the Tval software gives and what you expect as the result if that's different to Tval.

Okay I attached a sample file. I have two tests... One with more complex irregular payment dates where I compare XIRR to the TValue software. And then a simpler test with regular monthly payments where I compare IRR vs XIRR vs TVAL.

As you can see in the simple test IRR x12 equals my TVal result perfectly. So whatever IRR does, times 12, is doing exactly what Tval does for regular payments. I can only assume that Tvalue does the same thing for more complex irregular payments (I can't use IRR on irregular payment streams). I just need to basically get XIRR to match IRR x12. If I can accomplish that, I ideally should be able to do the same thing for both regular and irregular payment streams.


****EDIT**** Please note I edited the file I first uploaded as I made an error and reattached the updated version.

I probably should not involve myself in this, because it will only reveal my ignorance of business/finance math. Looking at the different formulas for the IRR() and XIRR() functions, they are very similar:

0=sum(for i=1 to n) of [x(i)/(1+r)^f(i)] (find r that makes this sum 0, in Excel, this can be done using Goal Seek or Solver)
for IRR() f(i)=i
for XIRR() f(i)=[d(i)-d(1)]/365

I could not find anything about this Tval calculation (I could not be certain what software package you are referring to), so I have nothing to add as far as how Tval is calculating rates of return.

At this point, as an engineer and mathematician (who knows next to nothing about the theories behind these financial functions except the math expressions given), I found the math equations interesting, but it seems to me that the real question is about the math behind the rate of return calculation. If we understood exactly how TVal was calculating this particular rate of return (and could convince ourselves that this algorithm was somehow more correct than Excel's XIRR() function), then we could program Excel to duplicate the TVal calculation.

Considering how different the exponent is between the IRR() and XIRR() functions, I don't see a simple way to force the XIRR() and IRR() results to be identical. I have not studied these functions in any detail, but I am not sure why the NOMINAL() function would force the two r's to be identical.

As I noted, I am not a financial expert. Taking the functions out of context, so that we can change anything about them that we want and not worry about whether the change is "real" or not, there are plenty of changeable parameters there that could allow the two functions to be coerced to be the same. For example, if I assume that the dates are not fixed, I can find a set of dates in column L that will make the XIRR() result be the same as the IRR() result. I put 30 in L2, then made L5=L4+L$2 (copied down). Added a helper cell =P12-S12, then told Solver to set this cell to a value of 0 by changing L2 and S12 subject to the constraint that S14 also equals 0. Solver came back with a solution that indicates that I can get XIRR() to equal IRR() by assuming each payment was made every 32.01 days instead of on the first of each month.