XIRR Not Agreeing With IRR

[Cheeseburger]

I have to calculate the nominal yield of a series of lease payments. Now when the lease payments are at regular monthly intervals, this is very easy using the IRR function. For a nominal yield I would just take the IRR result and multiply by 12 to annualize it. For an effective yield I would take ((1+IRR)^12)-1. But for my purposes, IRR x12 perfectly matches a third party software we have called Tvalue - which is what I want.

The problem is, when I have irregular payment streams. The lease contracts compound monthly, but sometimes 3, 4 or even 5 payments can be made in a single month. My third party lease software has no problem doing this but it is very tedious when making changes. I would like to at least preliminarily use excel. Now to do this with excel I have to use the XIRR function because the payments aren't at perfect monthly intervals. My problem is, I can't get XIRR to match my Tvalue software. I thought using the nominal function =Nominal(XIRR,12) might work, but it is still different. Then I thought, wait if XIRR assumes daily compounding maybe I should try =Nominal(XIRR,365) and while in some scenarios it was closer to my expected results, in other cases it was much further off than Nominal(XIRR,12).

Now to simplify the problem since you guys don't have the Tvalue software to compare... For simple monthly payment streams IRR x12 perfectly matches my expected result. But I can't even get XIRR to match that! For example, notice a very simple and regular payment stream below:

2017-01-01	-29000
2017-02-01	5000
2017-03-01	5000
2017-04-01	5000
2017-05-01	5000
2017-06-01	5000
2017-07-01	5000

<tbody>
</tbody>

In this scenario:
IRR = .977
IRR x12 = 11.728 which matches my third party Tvalue software perfectly
((1+IRR)^12)-1 = 12.379
XIRR = 12.519
Nominal(XIRR,12) = 11.854

Why can't I find any formula to get XIRR and IRR to agree? That is a fairly large difference and in certain scenarios such as larger yields, the difference is even greater.

Now just in case someone has a simple answer... My final problem will be getting an irregular payment stream like the one below to match my third party Tvalue software:

2015-12-01	-320000
2016-01-01	5000
2016-01-18	8500
2016-01-24	17000
2016-02-01	5000
2016-02-18	8500
2016-02-24	17000
2016-03-01	5000
2016-03-18	8500
2016-03-24	17000
2016-04-01	5000
2016-04-18	8500
2016-04-24	17000
2016-05-01	5000
2016-05-18	8500
2016-05-24	17000
2016-06-01	55000
2016-06-18	75000
2016-06-24	111000

<colgroup><col><col></colgroup><tbody>
</tbody>

In this case:
XIRR = 59.175
Nominal(XIRR,12) = 47.395
TValue = 47.137

 

[DRSteele]

The two functions IRR and XIRR will almost never give identical results, most significantly because XIRR uses Excel Date Serial Numbers as inputs and IRR uses simple serial periods. The interval is therefore incongruent between the two Excel functions.

Read this post and watch the associated video for a précis. Excel: =IRR() vs. =XIRR() (continued...) | ASimpleModel | ASimpleModel.com

Also, this and other videos in the ExcelIsFun channel may illuminate the topic. https://www.youtube.com/watch?v=dqQPmsLHZyE<strike></strike>

 

[Cheeseburger]

So is there any way to get a nominal interest rate for an irregular payment stream?

XIRR gives an effective interest rate for irregular payments assuming continuous compounding... but I want to calculate the nominal yield of a series of irregular payments.

The two functions IRR and XIRR will almost never give identical results, most significantly because XIRR uses Excel Date Serial Numbers as inputs and IRR uses simple serial periods. The interval is therefore incongruent between the two Excel functions.

Read this post and watch the associated video for a précis. Excel: =IRR() vs. =XIRR() (continued...) | ASimpleModel | ASimpleModel.com

Also, this and other videos in the ExcelIsFun channel may illuminate the topic. https://www.youtube.com/watch?v=dqQPmsLHZyE<strike></strike>

 

[DRSteele]

As the gentleman on the other forum responded to you (XIRR Compounding Issues), XIRR compounds daily.

 

[Cheeseburger]

Unfortunately I don't think knowing that XIRR compounds daily (though I actually believe it compounds continuously) is enough to solve my problem given the irregular payment stream.

I guess I am at a bit of an impasse.

As the gentleman on the other forum responded to you (XIRR Compounding Issues), XIRR compounds daily.

 

[DRSteele]

Well, of all the Excel functions, XIRR is the only one designed to deal with irregular payments schedules. I think you can just put in the dates next to the payments and it will report an annualised IRR (based on daily compounding).

I think we know that it's daily because Excel truncates any Date Serial Numbers that include portions of days when it assesses the Dates argument in XIRR.