kulerucket
Member of DD Central
Posts: 336 
Likes: 93
|
Post by kulerucket on Aug 24, 2017 18:29:18 GMT
Lets say I want to know the interest rate earned on a loan so far with the date and value when bought against the current value, I have been using:
=XIRR({-buy_value, today_value}, {buy_date, TODAY()})
However I have recently found the INTRATE function where you can do:
=INTRATE( buy_date, TODAY(), buy_value, today_value)
Comparing the results, XIRR always returns a higher rate. Can anyone explain the difference?
|
|
yangmills
Member of DD Central
Posts: 83
Likes: 494
|
Post by yangmills on Aug 24, 2017 22:00:35 GMT
For your simple example of two cashflows at two dates then the difference is due XIRR being based on compounded interest using a Act/365 day count fraction while INTRATE uses simple interest with a NASD 30/360 day count fraction. You can change the day count convention of INTRATE using an additional final parameter in the array. Even assuming the same day count convention, then for periods < 1 year, the compounded rate will be less than the simple rate, while for periods > 1 year, the simple rate will be greater than the compounded rate. I can write down the different calculation formulas if you require.
Really, however, the two functions are quite different. XIRR is used to calculate the flat compounded rate needed such that the NPV of a series of arbitrary cashflows is zero. INTRATE is calculating a simple discount rate between just two cashflows (i.e the simple rate that discounts the final value back to the initial value).
|
|
kulerucket
Member of DD Central
Posts: 336
Likes: 93
|
Post by kulerucket on Aug 25, 2017 9:56:30 GMT
Thanks that helps a lot. I found the formulas but I found it hard to compare them and I wasn't sure what basis XIRR used so I just left the default. If I use a period of exactly a year and INTRATE with basis=3 (Act/365) or default basis=0 I get equal rates. I see that the XIRR rate is higher for <y and lower for >y so it seems to be the other way around (see below). But anyway I get the idea. I think that for single non-compounding (interest only) loans, INTRATE is more what I'm after. Whereas for tracking overall return based on multiple transfers in and out of account XIRR is the only real option. | from | to | buy | sell | INTRATE(b=0) | INTRATE(b=3) | XIRR
| | 01/01/2017 | 01/07/2017 | 100 | 110 | 20.00% | 20.17% | 21.19% | | 01/01/2017 | 01/01/2018 | 100 | 110 | 10.00% | 10.00% | 10.00% | | 01/01/2017 | 01/07/2018 | 100 | 110 | 6.67% | 6.68% | 6.58% |
|
|
