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.