Post
Topic
Board Economics
Re: Bitcoin loan payment formula (WARNING: MATHS AHEAD!) [FORMULAS FIXED]
by
kwhcoin
on 16/07/2011, 04:04:31 UTC

I get weird numbers. Can you provide an Open Document Spreadsheet with an example?


I did the calculation with some real numbers and included the output of an excel spreadsheet.

P: original loan amount (i.e. $10000)
d: deflation rate (i.e. 3%)
D = 1-d (i.e. 0.97)
i: loan interest rate (i.e. 5%)
I = 1+i (i.e. 1.05)
n: term length of loan (i.e. 10)
k: current pay period payment is being calculated for

Here is a general formula for the k'th payment amount.

payment[k] = P*I*(1-D/I)/(1-(D/I)^n)*D^(k-1)

Here is the first payment using the numbers:
Payment[1] = 10500*(1-0.97/1.05)/(1-(0.97/1.05)^10) = $1,461.76

Here is what it looks like in excel.
Note: Outstanding is 1.05 times the remaining principle. The Payment is per the formula above, and the Remain is the Outstanding minus the Payment.
   
k     Outstanding   Payment       Remain
1     $10,500.00    $1,461.76     $9,038.24
2     $9,490.15     $1,417.91     $8,072.25
3     $8,475.86     $1,375.37     $7,100.49
4     $7,455.51     $1,334.11     $6,121.40
5     $6,427.47     $1,294.09     $5,133.39
6     $5,390.06     $1,255.26     $4,134.79
7     $4,341.53     $1,217.60     $3,123.93
8     $3,280.13     $1,181.08     $2,099.05
9     $2,204.00     $1,145.64     $1,058.36
10   $1,111.28     $1,111.28     $0.00

As required, each payment is 3% less than the previous payment and the balance is paid in full.

I found the following URL helpful in understanding the formulas:
http://www.mathpages.com/home/kmath297.htm
(Note: In the mathpages' URL they talk about inflation instead of deflation so their "I" is my "D" and my "I" is their "Y". I just picked "I" and "D" to correspond to the "i" and "d" already being used in this thread.)