Calculating internal rates of return using 30/360 day-count conventions
Mar
17
Written by:
Charles Flock
3/17/2015 2:41 PM
We have added 5 new functions to help you calculate internal rates of return (IRR) for a variety of day-count methods. Used in conjunction with our other day-counting functions, you can calculate IRR using actual/actual, actual/360, actual/364, business days / 252, no-leap-year / 365 and others as well as for 30/360.
The XNPV function calculates the net present value for an irregular series of cash flows using the Actual/365 day-count convention and assumes annual compounding. In other words, the discount factor for each cash flow is calculated using the actual number of days from the earliest date in the dataset and then dividing by 365. XIRR, which calculates the internal rate of return, simply calculates a value for discount rate such that the XNPV value is approximately zero, implicitly using the same day-count convention as XNPV.
What if you wanted to use a difference day count convention and/or a different compounding frequency? Specifically what if you wanted to calculate an internal rate of return using a 30/360 day-count convention (where a year consists of twelve 30-day months)?
To that end, we have created an XIRR30360 function. Like the XIRR function, there is no closed-form solution for finding the rate of return using a 30/360 day-count convention. Thus we also created an XNPV30360 function and the XIRR30360 function finds a solution for discount rate such that the XNPV30360 value is sufficiently close to zero (which we have defined as having an absolute value less than .0001). In other words:
ABS(XNPV30360(XIRR30360(cf,d,f,m,g),cf,d,f,m)) < .0001
Where:
|
cf
|
=
|
cash flows
|
|
d
|
=
|
dates
|
|
f
|
=
|
compounding frequency
|
|
m
|
=
|
30/360 day-count method
|
|
g
|
=
|
guess (defaults to 0.10)
|
The calculation of the number of days using 30/360 day count conventions can be a bit tricky and there are at least 3 different techniques that we know of. And, generally, the day-count calculation is used with bonds where the day-count is based on the maturity date of the bond. For purposes of this calculation, we simply assume that the last cash-flow date is the maturity date and then used our existing DAYS360 function to calculate the number of days for discounting purposes. That calculation looks like this: T360 = f * (-DAYS360(dmax,dmin,m) + DAYS360(dmax,di,m))/360
We also created a T360 function so that you could see the day-count calculation outside of the XNPV30360 and XIRR30360 functions.
XLeratorDB has an extensive library of financial calculations, so we realized that there might be other interesting ways to calculate the time value for rate of return calculations and we have included an XIRRT and XNPVT function that take cash flows and time periods (not dates) as input. This way if you want to calculate rates of return on an Actual/Actual basis or No-Leap-Year/365 basis, business days / 252, or even an actual 364 basis you will be able to do so.
You can read more about these functions by going to the documentation.
You can get the free 15-day trial and try these new functions out for yourself by clicking here. These functions were added as a result of a client inquiry so as always if you would like to see a function added to the library just send us an e-mail at support@westclintech.com