Cheeseburger
New Member
- Joined
- Feb 13, 2016
- Messages
- 4
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:
<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:
<colgroup><col><col></colgroup><tbody>
</tbody>
In this case:
XIRR = 59.175
Nominal(XIRR,12) = 47.395
TValue = 47.137
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