I was recently doing some spreadsheets to work out the impact of taking on various loans. My speadsheets had the structure you would expect: one row per year and each row calculates its values from the previous one. But when it came to wanting to compare different loans, these big tables were cumbersome. So I went around looking for closed-form solutions for things like total iterest paid over n months. Sure, I could have searched for the solutions online, but it was a fun exercise to come up with the equations myself.

The system works like this: you agree to loan for amount l for a monthly interest rate r and a monthly amortisation rate a. In Germany, an interest rate ("Sollzins") on a mortgage might be 4.2% and the amortisation ("Tilgung") 1.5%, and you would divide by twelve to get the monthly rates they represent, giving you r = 0.35% and a = 0.125%.

Your payment in the first month—let's call it p—consists of the two rates together applied to the loan:

The first term covers the interest payment and the second term reduces the loan which you now owe. Let's call the remaining loan after the first month l₁:

In all subsequent months you still pay p to the bank. The part which is interest is calculated by applying the interest rate to the oustanding loan at the start of the month, and whatever is left over reduces the loan which you owe. In other words, the remaining loan after n months is as follows:

We can take this recursive equation along with the expression for l₁ and plug it into Wolfram Mathematica to give us a closed-form expression for the remaining loan after n months:

Since the interest paid in any month is proportional to the loan outstanding from the month before, we can also easily use Wolfram Mathematica to calculate the cumulative interest paid after n months:

We can also set l_n = 0 and solve for n to give us the month in which the last payment is made:

Here's an interactive calculator where you can try out the equations yourself with some example inputs: