#### Cheeseburger

##### New Member

- Joined
- Feb 13, 2016

- Messages
- 4

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