Reducing variable over time

[MartinL]

I have a small table but I would like to know if there is a better way with a formula of doing this.

I have a start value and 60 periods (5 years in months)
at the end of the 60 months the initial value has reduced by 53%
so how do I calculate the end value

currently I am taking my start figure and multi by 53% to get the end figure
and then dividing that figure by 59 to get a monthly subtraction figure
by taking my initial number and subtracting this calculated number I get to the number I was looking for
but there must be a better way!?

example

M1 start figure = .37
after month 60 = 53%
value at month 60 = 0.37*53 = 0.1961
difference = (0.37 - 0.1961) / 59 = 0.002947
M2 = .37 - 0.002947 = 0.3670
M3 = 0.3670 - 0.002947 = 0.364106
:
M60 = 0.199047 - 0.002947 = 0.1961

 

[shg]

Re: HELP: Reducing variable over time

The change is 0.37 * (1 - 53%) / 60 (not 59) ~ -0.002898 per month.

 

[MartinL]

Re: HELP: Reducing variable over time

The change is 0.37 * (1 - 53%) / 60 (not 59) ~ -0.002898 per month.

Hi shg

I'm not sure I agree with that...

I have added a file to show whats going on and that will hopefully highlight my reasoning - I am open to being shown I'm wrong though!

https://www.dropbox.com/s/jp7ufqpj3luuc5t/5280806.xlsx?dl=0

 

[shg]

Reckon it's a matter of definition. If I take a 60-month loan, I start with an open balance at month 0 and make 60 payments, not 59.

 

[MartinL]

Reckon it's a matter of definition. If I take a 60-month loan, I start with an open balance at month 0 and make 60 payments, not 59.

That is a fair point, however in this instance that isn't what I'm doing. Point acknowledged though

BTW Any ideas on a solution...?

 

[shg]

I don't understand the result you're trying to achieve. You know the month-over month change (which you assume is constant), and the question is .... what?

 

[MartinL]

I don't understand the result you're trying to achieve. You know the month-over month change (which you assume is constant), and the question is .... what?

Apologies, in my mind it is clear

If you look at the sheet again, in cell BK7 (highlighted in green) I have the sum of the reducing figures SUM(C7:BJ7)

To get this number I am currently creating the table with the reducing numbers over time
My question is can is do this programmatically using a formula - rather than building the table, or is this the only way I can achieve this answer?

 

[shg]

The sum of an arithmetic series a1 + a2 + ... + an is n/2 * (a1 + an)

So 60/2 * (100% + 53%) = 49.5

The starting value is 0.37, so

0.37 * 49.5 = 16.983