# XIRR Not Agreeing With IRR

#### Cheeseburger

##### New Member
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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### DRSteele

##### Well-known Member
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>

Last edited:

#### Cheeseburger

##### New Member
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>

#### Cheeseburger

##### New Member
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-known Member
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.

Replies
3
Views
618
Replies
1
Views
230
Replies
6
Views
92
Replies
6
Views
114
Replies
7
Views
576

1,191,388
Messages
5,986,321
Members
440,017
Latest member
vasanrajeswaran

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back