Internal Rate of Return with Variable Payment Date Streams

[Cheeseburger]

Let's say we have 100 lease contracts, each one has a number of months remaining on the contract, certain payments, and certain payment dates.

So for example say we have the 2 contracts below:

Contract----------Payments Remaining------------Principal-------------Payment Amount-------------Payment Date (Day of Month)
1---------------------------27------------------------$78,500------------------$3,159.74-------------------------------1st
2---------------------------40-----------------------$289,450------------------$7,953.39-------------------------------1st



I need to calculate what our combined internal rate of return is on these 2 contracts. In this case, I believe the solution is simple enough... In excel I can just make a table with the two payment streams totaled and use the formula IRR on the combined monthly payment streams. The answer is 5.887%


My problem comes with the following scenario:

Contract----------Payments Remaining------------Principal-------------Payment Amount-------------Payment Date (Day of Month)
1---------------------------27------------------------$78,500------------------$3,159.74-------------------------------1st
2---------------------------40-----------------------$289,450------------------$7,953.39-------------------------------15th


Because the payment streams are coming in at different days of the month it appears to throw off the IRR calculation from the true rate (which we calculated with software). How could I adjust for the per diem to achieve the true rate of return?

[MrShorty]

I am not an expert on these financial calculations, but isn't that part of the difference between the IRR() and the XIRR() functions? https://support.office.com/en-us/art...b-a303ad9adc9d

[Cheeseburger]

I am not an expert on these financial calculations, but isn't that part of the difference between the IRR() and the XIRR() functions? https://support.office.com/en-us/art...b-a303ad9adc9d

Well that sounded like it was going to be the solution... But I tried comparing the calculation to our TVal software and get the results shown in the attached image. In this I basically combined 2 contracts - each with a 29,000 initial value, and 6 monthly repayments, with one contract repaying on the 1st of every month and 1 repaying on the 15th.

Amort.png

I am not sure what explains the discrepancy between the 12.688% that TVal calculates, and the 13.53% that excel calculates. FYI changing the compounding period in Tval is not the problem.

[MrShorty]

How much of this is an Excel question and how much is a business math question? Can you describe the formulas/algorithms that TVal software uses? At this point, it seems like it is a question of understanding how the other software performs the calculation, then figuring out how to perform the same calculation in Excel.

[Cheeseburger]

How much of this is an Excel question and how much is a business math question? Can you describe the formulas/algorithms that TVal software uses? At this point, it seems like it is a question of understanding how the other software performs the calculation, then figuring out how to perform the same calculation in Excel.

Yea, I am not sure if this is more of a finance question than an excel one. That said, I think I know why there is a discrepancy, I just don't know how to solve it. The XIRR formula is assuming that you are reinvesting the cash generated... For my purposes, I don't want to make this assumption.

[Cheeseburger]

Okay... I think I solved my own problem, or at least close. But I would appreciate input on if this is the correct solution. Below is my work:

amort.png

So basically... In excel when I calculate IRR using monthly payments the result is a monthly IRR not annual. To make it annual you don't simply multiply by 12... You put the monthly IRR into the following formula (1+IRR)^12-1. This basically assumes that the funds are reinvested and compounded.

With XIRR however, the result is already an annual return. The problem is this calculation has assumed reinvestment of the funds. TVal on the other hand doesn't imply reinvestment (I am looking to match Tval). So basically I have to reverse the compounding interest in XIRR by using the formula ((1+XIRR)^(1/12)-1) and then multiplying by 12. This brings me VERY close to Tval... The small difference I believe is from XIRR taking into account leap years, but maybe someone could confirm this is the reason for the difference.

Just to be clear IRR x12 equals the same as Tval when the payments are at regular intervals. So in the case above (payments all on the 1st) the 11.73% result from IRR x12 matches Tval. So basically my only question is... why is IRR properly annualized ((1+IRR)^12-1), still slightly off from XIRR.