**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have a figure which is the sum of 9 months of payments. Each payment decreases by the same amount (X) so the first payment is Y, second payment is Y-X, third is Y-(Xx2), forth is Y-(Xx3) and so on. I am trying to calculate what each of the 9 payments should be.

Solved by A. W. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
19/01/2018 - 03:16

hi welcome here

User
19/01/2018 - 03:16

hello

Excelchat Expert
19/01/2018 - 03:16

well its a simple Arithmatic progression

User
19/01/2018 - 03:16

yes but its got me stuck

Excelchat Expert
19/01/2018 - 03:17

and you have the sum of it

Excelchat Expert
19/01/2018 - 03:17

dont worry be with me

User
19/01/2018 - 03:17

so the sum is 75

User
19/01/2018 - 03:17

and there are 9 paymnets

Excelchat Expert
19/01/2018 - 03:17

let me first stte the theory here

User
19/01/2018 - 03:17

(i,.e. 9 months)

Excelchat Expert
19/01/2018 - 03:17

ok

User
19/01/2018 - 03:18

the figures i have added in the sheet show the problem

User
19/01/2018 - 03:18

the figures in red have been entered

User
19/01/2018 - 03:18

but I need to know how they were calcuklated

User
19/01/2018 - 03:18

and the formula

Excelchat Expert
19/01/2018 - 03:19

ok

Excelchat Expert
19/01/2018 - 03:19

be with me

Excelchat Expert
19/01/2018 - 03:22

here first term is Y

User
19/01/2018 - 03:24

sorry?

User
19/01/2018 - 03:24

Term is 9

User
19/01/2018 - 03:24

so Y=9?

Excelchat Expert
19/01/2018 - 03:24

total terms are 9

Excelchat Expert
19/01/2018 - 03:26

sum of these 9 payments is 75

Excelchat Expert
19/01/2018 - 03:26

rihght

Excelchat Expert
19/01/2018 - 03:26

?

User
19/01/2018 - 03:27

yes correct

Excelchat Expert
19/01/2018 - 03:27

ok

Excelchat Expert
19/01/2018 - 03:27

let me write the data

Excelchat Expert
19/01/2018 - 03:27

be with me

User
19/01/2018 - 03:27

ok

Excelchat Expert
19/01/2018 - 03:35

so did you know the first payment ?

User
19/01/2018 - 03:36

no

Excelchat Expert
19/01/2018 - 03:36

and last payment ?

User
19/01/2018 - 03:36

the know numbers are in green

Excelchat Expert
19/01/2018 - 03:36

ok

Excelchat Expert
19/01/2018 - 03:37

ok

Excelchat Expert
19/01/2018 - 03:37

we have following formula for sum of an arthmatic formula

Excelchat Expert
19/01/2018 - 03:39

S=(n/2)[2a+(n-1)d]

Excelchat Expert
19/01/2018 - 03:39

where

Excelchat Expert
19/01/2018 - 03:39

n=number of terms=9

Excelchat Expert
19/01/2018 - 03:40

a=first term=unknown

Excelchat Expert
19/01/2018 - 03:41

so putting values in we get

User
19/01/2018 - 03:41

what is d?

Excelchat Expert
19/01/2018 - 03:41

d is the common difference

Excelchat Expert
19/01/2018 - 03:42

as Each payment decreases by the same amount this same amount is "d"

Excelchat Expert
19/01/2018 - 03:43

d=1.67

User
19/01/2018 - 03:43

ok but that number is unknown

Excelchat Expert
19/01/2018 - 03:43

so using formula we get

Excelchat Expert
19/01/2018 - 03:44

75=(9/2)[2a+(9-1)1.67]

Excelchat Expert
19/01/2018 - 03:44

solving it for a we get

User
19/01/2018 - 03:46

so if we take some fresh figures....

User
19/01/2018 - 03:46

sorry lease complete your working

User
19/01/2018 - 03:46

please*

Excelchat Expert
19/01/2018 - 03:46

we get

Excelchat Expert
19/01/2018 - 03:46

I am here

Excelchat Expert
19/01/2018 - 03:47

I will complete it no matter what :)

Excelchat Expert
19/01/2018 - 03:47

dont worry :)

Excelchat Expert
19/01/2018 - 03:48

you just need to know the difference between two payements

User
19/01/2018 - 03:48

so i need to know the forumla to plug in so i can solve for different figures

User
19/01/2018 - 03:48

i.e. different terms and end results etc

Excelchat Expert
19/01/2018 - 03:49

yes

Excelchat Expert
19/01/2018 - 03:49

yep

User
19/01/2018 - 03:49

So i might say....

Excelchat Expert
19/01/2018 - 03:49

you need to know the sum, number of payments and difference between each payment like 1.67 here

User
19/01/2018 - 03:50

but how can i know the difference?

Excelchat Expert
19/01/2018 - 03:50

hmm

User
19/01/2018 - 03:51

if all i know if the term (number of payments) and the end amount (£75)

Excelchat Expert
19/01/2018 - 03:51

wait

User
19/01/2018 - 03:53

does that help at all?

User
19/01/2018 - 03:54

15 is 2% of 750

User
19/01/2018 - 03:54

etc etc

User
19/01/2018 - 03:54

13.33 is 2% of 666.67

Excelchat Expert
19/01/2018 - 03:54

yes I was also making that point

Excelchat Expert
19/01/2018 - 03:55

hat Capital/Interest is constant 50 here

User
19/01/2018 - 03:55

is this a re calibrated NPV formula?

Excelchat Expert
19/01/2018 - 03:56

hmm

Excelchat Expert
19/01/2018 - 03:56

not in my knowledge

Excelchat Expert
19/01/2018 - 03:56

you need to know the difference

User
19/01/2018 - 03:58

i dont understand how we get to the difference

Excelchat Expert
19/01/2018 - 03:58

hmm

Excelchat Expert
19/01/2018 - 04:01

how you calculated the repayments and Capital values ?

User
19/01/2018 - 04:01

ok I think i am getting somehwre

User
19/01/2018 - 04:02

the % difference between C2 and D2 is the same as C5 to D5

Excelchat Expert
19/01/2018 - 04:02

because repayment- capital=15 in col b

Excelchat Expert
19/01/2018 - 04:02

well see this

User
19/01/2018 - 04:02

yes the row 6 can only be calculated if you have numbers in row 5

Excelchat Expert
19/01/2018 - 04:03

ok

User
19/01/2018 - 04:04

so it may be that the % difference in row 2

User
19/01/2018 - 04:04

drives the differences in row 5

Excelchat Expert
19/01/2018 - 04:06

well each adjeccent cell of row 2 has same difference 83.33

User
19/01/2018 - 04:09

ok no that doesnt work

Excelchat Expert
19/01/2018 - 04:09

hmm

Excelchat Expert
19/01/2018 - 04:09

well we have only thing here

Excelchat Expert
19/01/2018 - 04:10

if you divide each value of row 2 by 50 you get row 5

Excelchat Expert
19/01/2018 - 04:10

but I dont know why 50

Excelchat Expert
19/01/2018 - 04:11

session time is almost over

User
19/01/2018 - 04:12

i do not know

Excelchat Expert
19/01/2018 - 04:12

the only question which remains that why capital/interest=50

User
19/01/2018 - 04:12

50 is a strange figure

Excelchat Expert
19/01/2018 - 04:12

yep

Excelchat Expert
19/01/2018 - 04:12

but it is related some how

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*