💻 Logarithms Meet SQL in Finance 💡
Have you ever seen the expression EXP( SUM( LOG( ... ) ) ) ?
Another unique challenge I faced where Math was the solution was in a financial application. This involved compound debt adjustments over time. I needed a SQL SELECT to fetch factors from all records and MULTIPLY them instead of the typical aggregate query using SUM.
Each month introduced a new and varying factor that adjusted the debt, representing elements like interest, inflation, and more. To calculate the overall adjustment rate, we needed to multiply these factors together cumulatively to get the adjusted debt amount. Each month/factor was in a separate record and each debt had different starting months. We had to query those records and multiply all month's factors respective to each debt.
That dialect of SQL didn't have a PRODUCT() aggregate function, it only offered SUM(). Without a PRODUCT(), a lot more code would be needed to do the same calculation and place it in a report.
That's when I turned to logarithms and exponentiation. By taking the logarithm of each factor, summing them up via SQL SUM, and then exponentiating the result, I was able to compute the product of the monthly factors.
That method is from an era when engineers didn't have calculators nor computers, they used slide rules to perform calculations. That is why John Napier invented logarithms: to transform multiplications into sums and make calculations easier.
So instead of
SELECT PRODUCT( factor_field ) FROM SomeTable WHERE...
I used
SELECT EXP( SUM( LOG( factor_field ) ) ) FROM SomeTable WHERE...
I've found a few articles that illustrate and explain that solution better.
LINKS:
🔗 https://lnkd.in/gb3zVHYa
🔗 https://lnkd.in/g5kiB87p
🔗 https://lnkd.in/g4jPtV2T
