Excel - SUM Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc