Simplify: Xt = Xt-1 [1- (1/n-t) - r]

tkonst76 · Oct 9, 2018

Hi everyone,

Can someone help me to simplify the following equation if possible?

X_t = X_t-1 [1- (1/n-t) - r] (1)

where t=1...n, X_t & X_t-1 are time constants and r is also a constant number
I am looking something of the form:

X_t = X₀ * .....

where X₀ is a constant at time zero. I thought of the alternatives:

X_t = X₀ * Π [1- (1/n-t) - r] (2)

were Π is the product of t=1...n. The rest parameters are as in (1).
However I don't know how to simplify the product term. I also tried to transform the product term into a sum of logarithms but still I cant get it.

Just to mention that my background is not in mathematics.
Any help is much appreciated. Thanks in advance for all the help.

Costas

Denis · Oct 9, 2018

tkonst76 said:
X_t = X_t-1 [1- (1/n-t) - r] (1)

Is 1/n-t ok or should it be 1/(n-t)

What does the equation represent: a financial formula?
Like n = number of periods, r = interest rate, t = time ?

tkonst76 · Oct 9, 2018

Denis said:
Is 1/n-t ok or should it be 1/(n-t)

What does the equation represent: a financial formula?
Like n = number of periods, r = interest rate, t = time ?

Hi, thanks for the response. I am trying to formulate a pattern from an excel spreadsheet which is used in finance. As you mention n = number of periods, r = interest rate, t = time. However the equation is not a formal financial formula, as far as I am aware. Regarding the fraction, it should be (n-t).
The pattern goes like this:

t=0 X₀= X - X/ 10 - rX
t=1 X₁= X₀ - X₀/(10-0) - rX₀t=2 X2 = X₁ - X₁/(10-1) - rX1
t=3 X3 = X₂ - X₂/(10-2) - rX2
....

t=10 X₁₀= X₉ - X₉/(10-9) - rX₉

Denis · Oct 9, 2018

Hmmmm.....sure looks strange...

Here's a financial formula that yours looks a bit like:
f = p*(1+ r)^n
where:
f = future value ..... your Xt
p = present value ..... your Xo
r = rate per period ..... your r
n = number of periods ..... your n

Example :
p = 1000
r = .01 (from 12% annual cpd. monthly)
n = 12
f = 1000*(1.01)^12 = 1126.83

Can you provide an example of yours?

Dr.Peterson · Oct 9, 2018

tkonst76 said:
Hi, thanks for the response. I am trying to formulate a pattern from an excel spreadsheet which is used in finance. As you mention n = number of periods, r = interest rate, t = time. However the equation is not a formal financial formula, as far as I am aware. Regarding the fraction, it should be (n-t).
The pattern goes like this:

t=0 X₀= X - X/ 10 - rX
t=1 X₁= X₀ - X₀/(10-0) - rX₀t=2 X2 = X₁ - X₁/(10-1) - rX1
t=3 X3 = X₂ - X₂/(10-2) - rX2
....

t=10 X₁₀= X₉ - X₉/(10-9) - rX₉

Can you tell us what this is supposed to do? Surely, you have some idea of its purpose! If X_i represents a balance after i periods, it looks as if you are reducing it by an increasing percentage each period (the reciprocal of the number of periods remaining), in addition to charging interest on the balance.

But in the last period, the divisor is 1, so you are removing all that is left, and still charging interest, so the balance at the end will always be negative. Similarly, I would have expected X₀ to be just your starting value X, not to be already decreased. I wonder if you have misstated some details. What is the actual formula in the spreadsheet?

As for your request, I have to say that not all products can be simplified into closed formulas. A spreadsheet seems like a fine way to handle this.

Denis · Oct 9, 2018

The example I gave you will look like this:

Code:

  n  interest balance
  0           1000.00(X0)
  1    10.00  1010.00
  2    10.10  1020.10
  3    10.20  1030.30 : 1020.10(X2) * 1.01 = 1030.30(X3)
....
 11    11.05  1115.67
 12(t) 11.16  1126.83(X12)

tkonst76 · Oct 10, 2018

Hi all and thanks for your help. Below is a formula (as well as a number) example of how the pattern goes.
My intention is to create (if possible) a function that replicates the pattern with only the 4 inputs below without having to reproduce all the previous period calculations. In other words to forcast the target value given the inputs only

Inputs:	Starting Nominal	10000
	Total Number of Periods	10
	Prepayment Rate	0.02
	Calculated Period	3

Period	Remaining Periods	Starting Nominal	Linear Principal Payment	Prepayment	Ending Target Value
0	=$C$2-A7	=C1	=C7/B7	=$C$3*C7	=C7-D7-E7
=A7+1	=$C$2-A8	=C7-D7-E7	=C8/B8	=$C$3*C8	=C8-D8-E8
=A8+1	=$C$2-A9	=C8-D8-E8	=C9/B9	=$C$3*C9	=C9-D9-E9
=A9+1	=$C$2-A10	=C9-D9-E9	=C10/B10	=$C$3*C10	=C10-D10-E10
=A10+1	=$C$2-A11	=C10-D10-E10	=C11/B11	=$C$3*C11	=C11-D11-E11
=A11+1	=$C$2-A12	=C11-D11-E11	=C12/B12	=$C$3*C12	=C12-D12-E12
=A12+1	=$C$2-A13	=C12-D12-E12	=C13/B13	=$C$3*C13	=C13-D13-E13
=A13+1	=$C$2-A14	=C13-D13-E13	=C14/B14	=$C$3*C14	=C14-D14-E14
=A14+1	=$C$2-A15	=C14-D14-E14	=C15/B15	=$C$3*C15	=C15-D15-E15
=A15+1	=$C$2-A16	=C15-D15-E15	=C16/B16	=$C$3*C16	=C16-D16-E16
=A16+1	=$C$2-A17	=C16-D16-E16

AND


Period	Remaining Periods	Starting Nominal	Linear Principal Payment	Prepayment	Ending Target Value
0	10	10,000.00	1,000.00	200.00	8,800.00
1	9	8,800.00	977.78	176.00	7,646.22
2	8	7,646.22	955.78	152.92	6,537.52
3	7	6,537.52	933.93	130.75	5,472.84
4	6	5,472.84	912.14	109.46	4,451.24
5	5	4,451.24	890.25	89.02	3,471.97
6	4	3,471.97	867.99	69.44	2,534.54
7	3	2,534.54	844.85	50.69	1,639.00
8	2	1,639.00	819.50	32.78	786.72
9	1	786.72	786.72	15.73	- 15.73
10	0	- 15.73

This pattern is followed by a type of loan which has two legs. The first leg (Linear Principal Payment) is reduced linearly based on the total periods value and the 2nd leg (Prepayment) is calculated based on a rate.

Denis · Oct 10, 2018

Soooo...what we have is the equivalent of a loan
being repaid using 2 "strange payments!".
Like:

Code:

  N  PAYMENT1  PAYMENT2   BALANCE
  0                       10000.00
  1   1000.00    200.00    8800.00
  2    977.78    176.00    7646.22
  3    955.78    152.92    6537.52
....
  8    844.85     50.69    1639.00
  9    819.50     32.78     786.72
t=10   786.72     15.73     -15.73

This program (Basic) handles it:

b=10000:t=10:r=.02
LOOP n FROM 1 TO t
u = b/(t-n+1) 'PAY'T1
v = b*r 'PAY'T2
b = b-u-v
PRINT n,u,v,b
ENDLOOP

And you're looking for a formula:
given b,t,r what is b after t periods?

Is that correct?

The 2 payments can be combined as 1:
b/(t-n+1) + br = b(1+rk)/k where k = t-n+1

tkonst76 · Oct 10, 2018

Denis said:
Soooo...what we have is the equivalent of a loan
being repaid using 2 "strange payments!".
Like:

Code:

N PAYMENT1 PAYMENT2 BALANCE 0 10000.00 1 1000.00 200.00 8800.00 2 977.78 176.00 7646.22 3 955.78 152.92 6537.52 .... 8 844.85 50.69 1639.00 9 819.50 32.78 786.72 t=10 786.72 15.73 -15.73

This program (Basic) handles it:

b=10000:t=10:r=.02
LOOP n FROM 1 TO t
u = b/(t-n+1) 'PAY'T1
v = b*r 'PAY'T2
b = b-u-v
PRINT n,u,v,b
ENDLOOP

And you're looking for a formula:
given b,t,r what is b after t periods?

Is that correct?

The 2 payments can be combined as 1:
b/(t-n+1) + br = b(1+rk)/k where k = t-n+1

Yes Denis exactly. This is the idea. I am looking for the formula that handles the above, such that I can find b at any period t.

Denis · Oct 11, 2018

tkonst76 said:
Yes Denis exactly. This is the idea. I am looking for the formula that handles the above, such that I can find b at any period t.

So, in this example, the formula would give -15.73?
a = 10000
r = .02
t = 10
b = ? (-15.73)

tkonst76 · Oct 11, 2018

Yes. In previous answer you can find the analysis with the results.

Denis · Oct 11, 2018

97% sure a "formula" not possible.

If one is possible, I'm sure DrP and/or Jeff will step in and shame me

Simplify: Xt = Xt-1 [1- (1/n-t) - r]

tkonst76

New member

Denis

Senior Member

tkonst76

New member

Denis

Senior Member

Dr.Peterson

Elite Member

Denis

Senior Member

tkonst76

New member

Denis

Senior Member

tkonst76

New member

Denis

Senior Member

tkonst76

New member

Denis

Senior Member