0
$\begingroup$

I'm trying to calculate equal payments for loans charging daily rates with different periods.

If the periods are exactly the same i.e. 30 days, I can use a formula to work out the repayment amounts without a problem. However, I've not yet found the perfect solution to my problem.

In order to understand what I need to do I've created a spreasheet that shows the steps I'm currently taking.

  • Step 1 - is to compound the interest and work out a final balance as if no repayments were made. Although the rate here is equal, this allows me to vary the rate if necessary to immitate different interest periods.

  • Step 2 - calculates the running balance using the max repayment from step 1, resulting in an overpayment.

  • Step 3 - I take the overpayment, divide it by the number of perds and then adjust the previous repayment. I loop through over/underpayments until the result is 0 (or near).

enter image description here

Whilst this currently uses a spreadsheet - the final code would be used in a program. However I can't help but think there are shortcuts to be made using tried and tested formulas.

Any advice appreciated.

P.s. I'm not a maths wizard!

$\endgroup$
5
  • $\begingroup$ I think you need to be clearer about what you're trying to achieve. Do you want a program that can take a daily rate or an APR and then work out equal monthly (not 30 day) repayments? (Which would have to consider the ongoing reduction in the principal.) This is what a bank's program would do. $\endgroup$
    – user117644
    Commented Mar 3, 2015 at 14:36
  • $\begingroup$ Yes I'm going to use the daily rate, not APR, and calculate a repayment amount and total repayment. I understand thats what bank programs do, but this is about comparing different loans. $\endgroup$
    – Johnnyboy
    Commented Mar 3, 2015 at 14:39
  • $\begingroup$ This is different from any question I've seen here before - it's a job! Although, not a major job; questions here and even on stackoverflow are such that people answer them for a bit of fun. 30 sterling for a python function doing that in two days via paypal? $\endgroup$
    – user117644
    Commented Mar 3, 2015 at 14:44
  • $\begingroup$ Thanks for the offer, however I'm not a python user. I can code the above as per my example but its probably not the most efficient way of doing it. $\endgroup$
    – Johnnyboy
    Commented Mar 3, 2015 at 14:58
  • $\begingroup$ Okay, hope it goes well. Anyway, I think translating from python to another language must be quite a common skill. $\endgroup$
    – user117644
    Commented Mar 3, 2015 at 15:23

1 Answer 1

0
$\begingroup$

With a fixed period rate, and a fixed number of periods, you can obtain a closed form. Once either of those start varying, you'd be better off going, or required to go, to the world of numerical methods. This would provide you with just as good answers, but will be a little more work to write. Not much more though, and you might find resources to help you over on Personal Finance and Money.

For your specific example:

Starting with a balance $b$ and a payment $p$, you want to perform the operation $\{$ grow by 24%, then subtract $p$ $\}$ $3$ times, and end up with a result of $0$.

Let $b_n$ be the balance after $n$ repeats of the operation, so $b_0$ = $b$. Then

$$\begin{align} b_1 & = 1.24b_0 - p \\ b_2 & = 1.24b_1 - p \\ & = 1.24(1.24b_0 - p) - p \\ & = 1.5376 b_0 - 2.24 p \\ b_3 & = 1.24b_2 - p \\ & = 1.24(1.5376 b_0 - 2.24 p) - p \\ & = 1.906624 b_0 - 3.7776 p \end{align}$$

If we want $b_3$ to be $0$, we need to set $p$ to $1.906624 b_0 / 3.7776$, which here gives $50.47183396865735$, which in my reconstruction of your spreadsheet correctly gives a result of $0$ (allowing for floating-point inaccuracy).

$\endgroup$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .