+ Reply to Thread
Results 1 to 3 of 3

Formula needed for irregular rate of return through out any given time frame/year

  1. #1
    Registered User
    Join Date
    01-30-2013
    Location
    edmonton
    MS-Off Ver
    excel 2010
    Posts
    4

    Formula needed for irregular rate of return through out any given time frame/year

    Im wondering if anyone could provide me with a bit of help for a formula to calculate this here.

    lets say i invest 10000 jan 1st
    jan 3rd it is worth 10120
    jan 8th 10140
    jan 10 10200
    jan 15 10240

    I currently have columns stating the different change in value from jan 1st to jan 3rd, jan 3rd to 8th.
    a column showing what the % change is day to day

    How do i get a percent earned on each row
    so i believe i start with difference / original investment

    10120 - 10000 = 120
    120/10120 = 0.01186 1.18%

    10140-10000=140
    140/10140 = 0.01381 1.38%

    10200-10000=200
    200/10200=0.01961 1.96%

    I'm not sure if I'm off track.. but I'm looking for a formula that will calculate my rate of return on each new day i input values. I feel like 1.96% year to date return is not correct? do i need to do something with days in a year and create more columns to figure days remaining or days into the 365.. Very confused please help!!
    thanks
    Last edited by jmord888; 01-30-2013 at 12:52 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,890

    Re: Formula needed for irregular rate of return through out any given time frame/year

    You need to divide the percentage you calculated by the number of days elapsed to get the daily rate. You will then need to multiply that by 365 to get the annual rate. your 1.96% is the raw percentage change.

    In your example for Jan 15
    .01961/15 = .001307
    .001307*365=.477177 or 47.72%
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    01-30-2013
    Location
    edmonton
    MS-Off Ver
    excel 2010
    Posts
    4

    Re: Formula needed for irregular rate of return through out any given time frame/year

    Would there be an easy way to calculate that out for a full column, i have monitored my stocks 2 days a month for the past 2 years and would like to change the dates to make it flow consistently going forward.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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