Starting from September 2020 release (20.09), you can now call Excel financial functions in Groovy. This is great news, this means Groovy scripts will be more powerful. Excel functions are very rich and can help covering most complex calculation business may require. I have been looking for an excuse to try out this new library since I had seen release notes. I will talk about one of many functions enabled. It is NPV function which is fairly popular function in excel.
Please see following example in Excel where Net Present Value of 3 months discounted the beginning of the year. Excel formula will simply be like =NPV(C7,C5:F5) returning value of 300. Well 10% discount rate is a monthly interest rate in this hypothetical example.
In Groovy, you can access NPV function by writing Excel.NPV(discountrate,arrFutureCF). I have created a quick form to test this function, I can see groovy calculating exact same NPV figure with the same future cash flow and same discount rate.
discountrate is rate of discount over the length of one period.
arrFutureCF is values representing the payments and income.
Please have a look at the code, that is simply running on form load to calculate Net Present Value.
def discountrate = 0.1;
def arrFutureCF = [];
operation.grid.dataCellIterator('Jan','Feb','Mar','Future Cash Surplus').each{
arrFutureCF.add(it.getData())
}
operation.grid.dataCellIterator('BegBalance','Net Present Value').each { npv ->
npv.setData(Excel.NPV(discountrate,arrFutureCF));
npv.setForceReadOnly(true);
}
I can see many Excel functions coming as part of this library, some of other popular ones are IRR, XIRR, MIRR, YEAR, AMORT. I am looking forward to trying all of them in near future.
