kdb - solver function to solve for IRR

Question

Having trouble implementing a rate of return calculation to compare to Excel's XIRR calc which is essentially an solver algorithm to find the discount rate at which the NPV of an investment is zero using provided cash flow and dates in a table.

Per MSFT documentation, the function loops through 100 times (not limited to this..I'm using 10,000 in my code below to test a wide range of rates)

https://support.microsoft.com/en-ie/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d?ui=en-us&rs=en-ie&ad=ie

Here is the example with my dummy data to match the above in a single table

t: ([] id:`PROJ_1`PROJ_1`PROJ_1`PROJ_1`PROJ_1`PROJ_1`PROJ_2`PROJ_2`PROJ_2`PROJ_2`PROJ_2`PROJ_2;kdbdate:2021.04.01 2021.12.31 2022.12.31 2023.12.31 2024.12.31 2025.12.31 2021.04.01 2021.12.31 2022.12.31 2023.12.31 2024.12.31 2025.12.31; cf: -800 200 250 300 350 400 -500 150 170 178 250 300);
t: update cum_cf: sums cf by id from t;
t: update irr: count [t]# enlist `float$() from t;  // assign a float return value

calcIRR:{[t] update irr: first[irr_func] t by id from t};
updateTable: calcIRR ::; 
t: updateTable over t; 

irr_func:{[d];  //need to test various discount rates to compare to 0 ;
    Pi: exec sums cum_cf from d;
    D1: exec first kdbdate from d;
    Di: exec last kdbdate from d;
    r: .001* til 10000;  //create vector of discount rates 10,000 seems excessive but covers a wide range
    val:Pi % xexp[(1.0 + r);(Di - D1)%365];  // calculate the value at the different rates
    
    // look for value closest to zero and return the indexed r associated with it

  result: val
 };

I am looking at kx's documentation on precision https://code.kx.com/q/basics/precision/ to make the comparison.

Thanks in advance!

Jorge Sawyer · Accepted Answer · 2021-02-22T23:52:07.370

I suspect that excel is using Newton's Method here, rather than running through a list of 100 equally spaced guesses. To calculate using this method we can start with your table, plus an initial guess of 0.5:

q)show t:([] id:`PROJ_1`PROJ_1`PROJ_1`PROJ_1`PROJ_1`PROJ_1`PROJ_2`PROJ_2`PROJ_2`PROJ_2`PROJ_2`PROJ_2;kdbdate:2021.04.01 2021.12.31 2022.12.31 2023.12.31 2024.12.31 2025.12.31 2021.04.01 2021.12.31 2022.12.31 2023.12.31 2024.12.31 2025.12.31; cf: -800 200 250 300 350 400 -500 150 170 178 250 300; irr:0.5)
id     kdbdate    cf   irr
--------------------------
PROJ_1 2021.04.01 -800 0.5
PROJ_1 2021.12.31 200  0.5
PROJ_1 2022.12.31 250  0.5
PROJ_1 2023.12.31 300  0.5
PROJ_1 2024.12.31 350  0.5
PROJ_1 2025.12.31 400  0.5
PROJ_2 2021.04.01 -500 0.5
PROJ_2 2021.12.31 150  0.5
PROJ_2 2022.12.31 170  0.5
PROJ_2 2023.12.31 178  0.5
PROJ_2 2024.12.31 250  0.5
PROJ_2 2025.12.31 300  0.5

and define the following function which applies one step of Newton's Method:

q)foo:{[t]update irr:irr-sum[cf*(1+irr) xexp neg[(kdbdate-first kdbdate)%365]]%sum[neg[(kdbdate-first kdbdate)%365]*cf*(1+irr) xexp neg[(kdbdate-first kdbdate)%365]-1] by id from t}

This takes the current irr guess and updates it to irr-f(irr)%f'(irr), where f is the function net present value function and f' is that function's derivative (for each id, of course).

use foo t to apply once or foo over t to apply iteratively use foo over t:

q)foo over t
id     kdbdate    cf   irr
--------------------------------
PROJ_1 2021.04.01 -800 0.2444791
PROJ_1 2021.12.31 200  0.2444791
PROJ_1 2022.12.31 250  0.2444791
PROJ_1 2023.12.31 300  0.2444791
PROJ_1 2024.12.31 350  0.2444791
PROJ_1 2025.12.31 400  0.2444791
PROJ_2 2021.04.01 -500 0.2966161
PROJ_2 2021.12.31 150  0.2966161
PROJ_2 2022.12.31 170  0.2966161
PROJ_2 2023.12.31 178  0.2966161
PROJ_2 2024.12.31 250  0.2966161
PROJ_2 2025.12.31 300  0.2966161

Thank you once again Jorge! I just had to tweak the foo function above and update he (1+r) to (1+irr). — Jason_L, Feb 22 '21 at 23:29
Ahh, good catch. I copied off the wrong line from my terminal. By the way, if you want a sense of how quickly the results converge you can use `foo scan t` to see the intermediate steps. With it you can see that we only need 6 iterations for convergence for the example table above. — Jorge Sawyer, Feb 22 '21 at 23:55
Appreciate the help again. I import a primary discount rate as part of my ETL process so I'm using that as a guess (0.1 in my case) so running that and using 'scan' it still knocks it out in 6 iterations. Changing negative values so I have a neg IRR now took ~19 iterations. — Jason_L, Feb 23 '21 at 16:09
Yeah, typically Newton's method is very efficient. Which is why Excel gives up after 100 iterations - if the value hasn't converged by that point there there is likely some issue causing the value to diverge entirely. That should be rare for polynomials (as in the case of irr calculation), but it makes sense to have protection in place. — Jorge Sawyer, Feb 24 '21 at 02:37
Yes, I also need to wrap the function to handle my non-investment projects as well (i.e. default IRR to NA or hard code 100% for example) Now that you've shared this and I've been reading up, this will be vital for my breakeven calculations at various discount rates as well and then to figure out how to implement in my existing code. Superb share, Jorge! — Jason_L, Feb 24 '21 at 19:13

kdb - solver function to solve for IRR

1 Answers1