I have a problem that I think can be solved by a maths formula but I am really not good enough to solve. So please feel free to impress me :)
My Problem :
Say I have a loan with a customer, that has a schedule like this :
01 Jan - Pay $100 to Customer
01 Feb - Customer Repays $20 (Loan Balance Now $80)
01 Mar - Customer Repays $20 (Loan Balance Now $60)
01 Apr - Customer Repays $20 (Loan Balance Now $40)
01 May - Customer Repays $20 (Loan Balance Now $20)
01 Jun - Customer Repays $20 (Loan Balance Now $0)
Total Loan Repaid : $100
All ok here, very simple example, but what if we want to accrue interest given the current balance over the amount of days, then add that value to what the customer should pay back on each repayment date?
Then it would look something like this :
01 Jan - Pay $100 to Customer
01 Feb - Customer Repays $20 + $5 Interest (Loan Balance Now $80)
01 Mar - Customer Repays $20 + $4 Interest (Loan Balance Now $60)
01 Apr - Customer Repays $20 + $3 Interest (Loan Balance Now $40)
01 May - Customer Repays $20 + $2 Interest (Loan Balance Now $20)
01 Jun - Customer Repays $20 + $1 Interest (Loan Balance Now $0)
Total Loan Repaid : $100
Total Interest Repaid : $11
Here you can see that the customer is still paying back the whole loan, and also the interest generated on the balance which as it gets smaller (due to the customer paying back the loan) the interest amount accrued gets smaller too. This is a bit more complicated but still ok.
The problem I am having is that I want each repayment to be the same value, so the customer always has to pay the same amount. The main issue with this is that as I raise the loan repayment amount then the loan balance drops more and the interest is less.
How can I maths out what the number will be for each repayment day that will mean paying off the entire loan but also include interest accrued and having the same repayment value each day.
Not sure if this is even possible, and the idea of using an annuity loan doesnt really work because of the mix of interest accrual on a running balance and a fixed repayment value.
For the example above I was able to trial and error the actual result but its a nightmare to do, and it still ends up with the last entry having a different repayment value:
01 Jan - Pay $100 to Customer
01 Feb - Customer Repays $19.54 + $1.02 Interest (Loan Balance Now $80.46)
01 Mar - Customer Repays $19.82 + $0.74 Interest (Loan Balance Now $60.64)
01 Apr - Customer Repays $19.94 + $0.62 Interest (Loan Balance Now $40.70)
01 May - Customer Repays $20.16 + $0.40 Interest (Loan Balance Now $20.54)
01 Jun - Customer Repays $20.54 + $0.21 Interest (Loan Balance Now $0)
Total Loan Repaid : $100
Total Interest Repaid : $2.99
If anyone can solve this then you are a god.
Knowing this does not make anyone a god. It is not something that should impress anyone either. It just means that I've had a chance to learn it. I suspect very few people have actually figured this out on their own. Some might have had tutors who guided them rather than telling them, and in some sense they have come up with this on their own, but that doesn't really count either.
This is a standard application of geometric series, although, to be fair, it is the most complicated application that you should expect to see and be ready for on a final year high school math exam here in Norway. I expect the bar is set similarily in other countries where this is on the curriculum.
First, the theory and the fomulas: A geometric sequence is a sequence of numbers where each term differs from the one before it by multiplication of some fixed factor. For instance, $3, 6, 12, 24, 48,\ldots$ is a geometric sequence, because each term is twice the number before it. Another one is $$ 1, 1.01, 1.0201, 1.030301,1.04060401,\ldots $$ where each number is the one before multiplied by $1.01$.
A geometric series is a sum of consecutive terms in a geometric sequence. For instance, $3+6+12+24+48$. There is a nice formula for finding this sum without having to add all the numbers one by one. That formula is $$ S = a_0\frac{r^{n}-1}{r-1} $$ where $S$ is the sum, $a_0$ is the first term, $r$ is the fixed factor ("ratio") that decides one term from the previous, and $n$ is the total number of terms.
For instance, we get $$ 3+6+12+24+48 = 3\frac{3^5 - 1}{3-1} = 3\frac{242}{2} = 363 $$
Now that we have the formula, let's look at your loans. I want you to change how you look at the loans. I don't want you to see it as one big loan that you pay back in five installments. I want you to look at it as five separate loans. In February, you pay back all of the first loan, and leave the remaining loans. In March you repay the second loan entirely, and don't touch the remaining loans, and so on.
We want each loan to reach, by the time you pay them, some fixed amount $x$. That means that the first loan should have size $\frac{x}{p}$ (where $p$ is the rate of interest: $5\%$ per month would make $p = 1.05$). The second loan should have size $\frac x{p^2}$, the thhird loan $\frac x{p^3}$, the fourth loan $\frac x{p^4}$ and the final loan should be $\frac x{p^5}$.
Together they should make $100$ dollars, so we get $$ 100 = \frac{x}{p} + \frac{x}{p^2} + \frac{x}{p^3} + \frac{x}{p^4} + \frac{x}{p^5} $$ and this is a geometric series (note that each term comes from the one before by multiplying with $\frac1p$). That means we can use our formula to express the sum: $$ 100 = S = \frac xp\cdot \frac{\left(\frac1p\right)^5-1}{\frac 1p - 1}\\ x = \frac{100p\left(\frac 1p - 1\right)}{\frac1{p^5}-1} $$ So if you, for instance, want to follow your first interest example of $5\%$ interest per month, then we get $$ x = \frac{100\cdot 1.05\cdot \left(\frac1{1.05} - 1\right)}{\frac{1}{1.05^5}- 1}\approx 23.1 $$