[emacs-berlin] Calculating recurring payments with Orgmode table spreadsheet

[jman]

hello fellow emacs-hackers!

At the previous meetup we briefly talked offline about Orgmode. I mentioned this usecase:

----------s---------s----------
| Payment Date     | Amount | is-recurrent | Total |
|------------------+--------+--------------+-------|
| <2024-10-02 Mon> |     22 | t            |       |
| <2024-12-02 Mon> |     15 | t            |       |
| <2024-10-12 Mon> |     20 |              |       |
| <2024-01-02 Tue> |     10 | t            |       |
----------e---------e----------

In this table each row is an expense item that can be a monthly recurrence or a one-time payment. I
want the "Total" column to have the amount calculated until end of the year (if it's a recurring
monthly expense) or just copy the value from the "Amount" column.

After an evening of sweat and learning a bit of orgmode, here's my solution (which I am very proud
of!). The following function will calculate how many months until end of the year starting from a
timestamp:

----------s---------s----------
#+begin_src emacs-lisp :tangle yes
 (defun my/months-paid-until-end-of-year (timestamp)
   "Return the number of months from TIMESTAMP until end of the year.
 As a number without zero-padding."
   (let* ((time (org-time-string-to-time timestamp))
          (month (format-time-string "%-m" time)))
     (1 + (- 12 (string-to-number month)))))
#+end_src
----------e---------e----------

And here is how I use that function in an orgmode table formula:

----------s---------s----------
| Date             | Amount | is-recurrent | Total |
|------------------+--------+--------------+-------|
| <2024-10-02 Mon> |     22 | t            |    88 |
| <2024-12-02 Mon> |     15 | t            |    15 |
| <2024-10-12 Mon> |     20 |              |    20 |
| <2024-01-02 Tue> |     10 | t            |   120 |
#+TBLFM: $4='(if (string-empty-p "$3") $2 (* $2 (my/months-paid-until-end-of-year "$1")));EL
----------e---------e----------

It's probably a bit convoluted but basically I am assigning in the fourth column 4 ($4) the result
of the elisp expression. ";EL" are two modifiers: "E" returns empty strings for empty cells so I
don't have to cope with nil and "L" interprets cell content literally so I don't have to cast
strings to numbers (orgmode by default treat all cells as strings).

It works (yay!) but can this code be improved? As a side note, I've tried getting some initial
guiding from an AI assistant and it was horrible. However it suggested an orgmode function to get
the "month of the year from a timestamp":

----------s---------s----------
| Date             | MOY |
|------------------+-----|
| <2024-10-12 Mon> |  10 |
#+TBLFM: $2=month($1)
----------e---------e----------

I couldn't figure out how to mix `month()` with my elisp code, something like this (if it worked):

----------s---------s----------
| Date             | MOY    |
|------------------+--------|
| <2024-08-02 Fri> | #ERROR |
#+TBLFM: $2='(+ 2 month($1))
----------e---------e----------

Is something like that even possible? Maybe using a temporary variable?

Thank you!

Best,