+ Reply to Thread
Results 1 to 3 of 3

Formula Manipulation While Calculating Returns

  1. #1
    Registered User
    Join Date
    05-16-2021
    Location
    Saint-Petersburg
    MS-Off Ver
    MS Office 365
    Posts
    13

    Formula Manipulation While Calculating Returns

    Hello, everyone!

    I have a range of data with daily stock returns presented in percentage for each company starting from the day when event has happened. For each stock, which is presented on the first row I need to add additional column and calculate cumulative returns, which is the value of 1$ investment.

    a) I'm wondering what could be the best way to calculate cumulative returns data without adding a new column for each company
    b) What could be the formula for this kind of operation, where we just put '1' (invest 1$) in the first cell in front of the first return and then we keep on with formulas?

    I also add a sample with highlighted cells to be more clear.

    Thank you beforehand!
    Attached Files Attached Files
    Last edited by Orif; 05-27-2021 at 02:56 AM.

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,100

    Re: Formula Manipulation While Calculating Returns

    Maybe creating a list for your companies and dates, then adding a dropdown+A vlookup for date.
    and based on that date a single column formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See attached
    Attached Files Attached Files
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    05-16-2021
    Location
    Saint-Petersburg
    MS-Off Ver
    MS Office 365
    Posts
    13

    Re: Formula Manipulation While Calculating Returns

    Hi, Paul!

    Thanks for the solution, it works!

    I've decided to duplicate the first two rows on the right side of my dataset without generating new columns. There I tried to use your formula and I got correct results!
    However, I am just being curious how to deal with #VALUE! message which I get after 30 calculated cumulative returns. I understand that it happens due to logical test I put for my dates, but maybe there is a way to omit #VALUE! message within the formula.

    Many thanks once more!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating cumulative returns
    By TrueTears in forum Excel General
    Replies: 0
    Last Post: 03-05-2013, 09:47 AM
  2. calculating annualised returns + Standard deviation of returns
    By Jaspal in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-01-2013, 09:22 AM
  3. [SOLVED] Calculating Monthly Returns
    By duration in forum Excel General
    Replies: 17
    Last Post: 09-18-2012, 07:33 AM
  4. Calculating Average Annual Returns from a Series of Annual Returns
    By Bruinsfan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-03-2012, 09:50 PM
  5. Replies: 12
    Last Post: 03-03-2012, 10:31 AM
  6. String Manipulation in MS Query works, returns no results in Worksheet
    By PePPaZ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2010, 02:33 PM
  7. Calculating Year to Date returns
    By Pasha81 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2010, 08:00 AM
  8. [SOLVED] For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation
    By vmegha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2005, 08:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1