# Calculate Series SUM

1. ## Calculate Series SUM

Hi everyone,

I try to get a series sum calculated in excel which is as follows:

IMG_1457.jpg

Any idea this can be solved thru a formula or function?

Much appreciated  Register To Reply

2. ## Re: Calculate Series SUM

Can you supply (on an excel sheet) the individual values and the value of the constant which make the final answer?

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.  Register To Reply

3. ## Re: Calculate Series SUM

I'll go for the low hanging fruit. Here's one way I might do this:

0) I was unsure from you picture whether the second term was a stand alone term or whether it was included in the summation. For the example purposes, I have assumed it is a standalone term [equivalent to 1000/(1+k)^30+SUM(for t=1 to 30)(47.5/(1+k)^t)]
1) Enter the individual terms in the summation in a column. Something like =47.5/\$C\$2^ROW(A1) copied down 30 rows. C2 contains the 1+k value (it looks like something close to 1.055 is the correct value for 1+k to get 891).
2) Enter a SUM() function to get the summation. =SUM(A1:A30,1000/\$C\$2^30) [assuming I entered the formula from step 1 in A1 and copied to A30].

I cannot be sure, but the picture suggests that the next step is to find the value for 1+k that yields the desired target value (891). There is no way to solve this function for k, so one needs to use numeric methods. This is most easily done using Excel's built Goal Seek or Solver utilities.

3) (Optional) Enter 891 into a convenient cell. Enter =(cell with 891)-(cell with summation from step 2). I like to enter this step, because it becomes easier for future problems simply enter a new target for 891 and execute the Solver algorithm again.
4) Call Solver (or Goal Seek, but I prefer Solver) and tell it to
4a) Set target cell: Helper cell from step 3.
4b) To value of: 0
4c) By changing: C2 (cell with 1+k value).

That would be one way to set this up. Did I interpret the problem correctly?  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 