+ Reply to Thread
Results 1 to 9 of 9

Apparent contradiction in calculation of average percentages

  1. #1
    Registered User
    Join Date
    01-20-2011
    Location
    dallas, tx
    MS-Off Ver
    Excel 2007
    Posts
    5

    Apparent contradiction in calculation of average percentages

    Please take a look at the attached sheet.


    Every year after that first year returns ~250%.

    But if I were to total the dollar amounts up, I'd get ~189% returns over that 10 year span.

    Here's the issue: If I divide that 189% return over 10 years, it's only 18.9% returns per year.



    How is that possible when the year by year calculations clearly state I am returning ~250% per year after that first year?
    Attached Files Attached Files
    Last edited by Richard Buttrey; 01-20-2011 at 08:01 PM. Reason: Thread title

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,636

    Re: I know I am doing something wrong here... but what?

    Hi,

    But your totals are including that first year which shows a return of just 58%. Hence the simple average of the 10 years is 189%.

    Rgds
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-20-2011
    Location
    dallas, tx
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: I know I am doing something wrong here... but what?

    Hi and thanks for the response, but this isn't about the low returns on that first year.

    Even if I were to disregard the first year coompletely, that Total column on the right would should a return of ~250%.

    ~250% divided by 9 years would still be only about 28% per year return.

    28% vs. the ~250% shown on every yearly column: Where is the discrepancy coming from?

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,636

    Re: I know I am doing something wrong here... but what?

    Hi,

    I don't understand the point you're making. OK disregard the first year. The total EOY Profits for 9 years is 149,760 and the total EOY Costs is 58,590. The first divided by the second is 256% which is consistent with the range of individual %s for the 9 years which show a minimum of 238% and maximum of 289%.

    I think what you're trying to do is take the average result for the 9 years, i.e. 256% and divide this again by 9 to give 28%. However other than that being a mathematical calculation I don't believe it actually means much. You have made a 58% profit in year 1 and an average of 256% in EACH of the next 9 years, hence over the 10 years it's an inescapable fact that the overall average is 189%

    Regards

  5. #5
    Registered User
    Join Date
    01-20-2011
    Location
    dallas, tx
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: I know I am doing something wrong here... but what?

    Quote Originally Posted by Richard Buttrey View Post

    I think what you're trying to do is take the average result for the 9 years, i.e. 256% and divide this again by 9 to give 28%. However other than that being a mathematical calculation I don't believe it actually means much.

    Regards
    Hello

    I actually think doing this division by 9 is vitally important.

    1. At the end of the 9 year period, I've only, in reality, made a profit of ~$91,000 after spending 58,590. A roughly 250% return. I must divide this by 9 (years) because that shows my average annual return. That comes out to a return of around 28% per year.

    2. Again, doing it that way shows I've realistically only returned 28% per year. Yet as we can see from the year by year calc, we should be returning ~250% per year.

    What is causing this discrepancy?

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,636

    Re: I know I am doing something wrong here... but what?

    Hi,

    No,
    Your profit for the 9 years from Yr 2 to Yr 10 is 149,760 not 91,000. The return is 256%

    I think you're mixing up your reasoning and terminology. In 9 years you have returned an overall profit of 256% The 28% you're fixated on is simply the division of that 256% by 9. It doesn't mean that in any year you've returned a margin of ~28% as can be seen from your data.

    You can't average averages like you're attempting and draw the sort of wrong conclusions you are coming to.

    There is no discrepancy, just a misunderstanding and misinterpretation as to what your numbers are telling you.

    Regards

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,636

    Re: I know I am doing something wrong here... but what?

    Hi,

    Just noticed the thread title. I've modified it for you but please note the forum rules about choosing meaningful titles for the benefit of others.

    Regards

  8. #8
    Registered User
    Join Date
    01-20-2011
    Location
    dallas, tx
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Apparent contradiction in calculation of average percentages

    Hi

    So let's do a 5% bank return for the sake of comparison - which is really what I am trying to get at with this exercise in the first place.

    I've attached a new sheet that shows compound interest rate of 5%, with the exact same amounts deposited each year as 'Investment BB'.

    At the end of the run, total profit (before taxes) were as follows;

    Investment BB: $78,510
    Bank 5% compound interest rate: $35,712

    Investment BB did better than Bank 5% interest by 2.2x.

    2.2x. Effectively earning 11% returns per year.



    A ridiculously far cry from the ~250% Investment BB was showing year over year.... no?

    Is my reasoning off again, Richard?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-20-2011
    Location
    dallas, tx
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Apparent contradiction in calculation of average percentages

    5% returns vs. "250%"

+ 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