Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IRR trouble: Want to figure average annual rate of return

    Good grief ~ I am a smart gal ~ what is my hang up with IRR? Here is my deal:

    I have monthly % return data for 9.5 years for an investment. I want to determine the AVERAGE ANNUAL RATE OF RETURN. but I can't figure it out!!!!!!

    Investment begins with $50,000; Value after 9.5 years is $135,869.62

    please help!!!

  2. #2
    5 Star Lounger
    Join Date
    Mar 2010
    Location
    east coast
    Posts
    701
    Thanks
    89
    Thanked 8 Times in 8 Posts
    first normalise
    the gain is 2.7173924
    (50,000 x 2.7173924 = $135,869.62)

    now you need to compute [what] ^ 9.5 = 2.717....
    where << ^ >> mean exponentiation

    take logarithms of both sides
    use any base you like

    9.5 * log[what] = log 2.717...
    where << * >> means multiply

    log[what] = 0.43415... / 9.5 = 0.045700248....
    where << / >> means divide

    now unlog
    ten ^ 0.045700248 = 1.11096467
    (using base ten logs)

    subtract 1 because [what] = 1.interestrate

    your annual equivalent interest rate is 11.096467%

    check it
    1.11096467 ^ 9.5 = ????

    if ??? = 2.717.... then you know you did the math rigth
    Last edited by speedball; 2013-06-27 at 13:36. Reason: typo

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I believe that Speedball's logic is a little off. Speedball is calculating the Annual interest rate with compounding annually. For a Annual interest rate, compounded monthly, the number of periods is not 9.5 but 9.5*12 = 114

    I calculate an average Annual Interest rate of about 10.57% if compounded monthly.

    The key is solving the following equation for IR:
    Initial * (1+IR/12)^Months = Final
    [Speedball solved the equation: Initial * (1+IR)^Years = Final]

    To solve:
    Initial * (1+IR/12)^Months = Final
    goes as follows:
    (1+IR/12)^Months = Final / Initial
    log((1+IR/12)^Months) = log(Final / Initial)
    Months * log((1+IR/12)) = log(Final / Initial)
    log((1+IR/12)) = log(Final / Initial) / Months
    10^log(1+IR/12) = 10^ (log(Final / Initial) / Months)
    (1+IR/12) = 10^ (log(Final / Initial) / Months)
    IR/12 = 10^ (log(Final / Initial) / Months) - 1
    IR = 12 * (10^ (log(Final / Initial) / Months) - 1)
    IR = 12* (10^ (log(135869.62 /50000) / (9.5*12)) - 1)
    IR = 10.56914%

    Steve
    Last edited by sdckapr; 2013-06-28 at 08:12.

  4. #4
    New Lounger
    Join Date
    May 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, well Steve's end result is the closest.... but is there an easy way to create an Excel formula to calculate this???

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    The Excel function =IRR(...) returns the period result and you must annualize the values yourself.
    The Excel function =XIRR(...) returns an annualized result

    zeddy

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The average Interest rate is the excel formula:
    =12*(10^ (log(Final / Initial) / Months) - 1)

    If A1 has Initial, B1 the years, and C1 the Final value, it becomes:
    =12 * (10^ (log(C1 / A1) / (B1*12)) - 1)

    IRR or XIRR are inappropriate if you are compounding the interest on a principal. IRR (and XIRR) presume you are investing the initial quantity (which is no longer available as cash and works with the cash flow generated from that investment. The formula I give is putting money into a bank or fund of some kind and getting interest. IRR and XIRR would be for something like buying a restaurant and then making so much money each period. The IRR and XIRR require the money obtained for each period to do the calcs.

    Steve
    PS it should be pointed out that The compound interest value requires only the Initial, number of periods, and final to reach a value. The IRR requires the initial investment and the returns for each period (the XIRR requires the dates for each period and thus can work with irregular returns). Even with the same initial and final values, the IRR will give different results depending on the returns at each period. Investments that return more initially I think will give higher IRRs than better returns at the end.
    Last edited by sdckapr; 2013-06-28 at 06:12.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    So all your months are the same then?
    Would that be the 30-day month standard?

    zeddy

  8. #8
    New Lounger
    Join Date
    Jul 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by NOTthepro View Post
    Okay, well Steve's end result is the closest.... but is there an easy way to create an Excel formula to calculate this???
    Periodic monthly interest rate
    =RATE( 9.5*12, 0, -50000, 135869.62 )
    0.8808%

    Nominal annual rate
    =RATE( 9.5*12, 0, -50000, 135869.62 ) * 12
    10.5691%

    AEY Annual effective yield
    =EFFECT( RATE( 9.5*12, 0, -50000, 135869.62 ) * 12, 12 )
    11.0965%

    Usage
    =RATE ( NPER, PMT, PV, [FV], [type] )

  9. #9
    5 Star Lounger
    Join Date
    Mar 2010
    Location
    east coast
    Posts
    701
    Thanks
    89
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by sdckapr View Post
    I believe that Speedball's logic is a little off. Speedball is calculating the Annual interest rate with compounding annually. For a Annual interest rate, compounded monthly, the number of periods is not 9.5 but 9.5*12 = 114

    I calculate an average Annual Interest rate of about 10.57% if compounded monthly.

    The key is solving the following equation for IR:
    Initial * (1+IR/12)^Months = Final
    [Speedball solved the equation: Initial * (1+IR)^Years = Final]

    To solve:
    Initial * (1+IR/12)^Months = Final
    goes as follows:
    (1+IR/12)^Months = Final / Initial
    log((1+IR/12)^Months) = log(Final / Initial)
    Months * log((1+IR/12)) = log(Final / Initial)
    log((1+IR/12)) = log(Final / Initial) / Months
    10^log(1+IR/12) = 10^ (log(Final / Initial) / Months)
    (1+IR/12) = 10^ (log(Final / Initial) / Months)
    IR/12 = 10^ (log(Final / Initial) / Months) - 1
    IR = 12 * (10^ (log(Final / Initial) / Months) - 1)
    IR = 12* (10^ (log(135869.62 /50000) / (9.5*12)) - 1)
    IR = 10.56914%

    Steve
    my bank either compounds daily or quarterly depending on the account
    never had a bank that compounded monthly except for your payments on an auto loan and then they used a sum of digits method to compute interest vs. principal repayment. which is a whole nuther formula altogether.

    and note that the original problem asked for ANNUAL rate of return not a monthly rate

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    note that the original problem asked for ANNUAL rate of return not a monthly rate
    The answer was the annual rate of return. If compounded daily, does it use actual days, the typical 365 days/year, a nominal 360 days / year? All will lead to different results and the actual days/year requires knowing the exact start and end dates, not a nominal number of 9.5 years. One would need to know how many days are in 9.5 years if compounding daily...

    If actual days/year are used, the annual interest rate will vary depending on whether or not it is a leap year...

    Steve
    PS if compounding quarterly, then replace the 12's with 4's in equation and the term "months" with "quarters".
    Last edited by sdckapr; 2013-07-04 at 11:26.

  11. #11
    New Lounger
    Join Date
    Apr 2013
    Location
    California
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by NOTthepro View Post
    Good grief ~ I am a smart gal ~ what is my hang up with IRR? Here is my deal:

    I have monthly % return data for 9.5 years for an investment. I want to determine the AVERAGE ANNUAL RATE OF RETURN. but I can't figure it out!!!!!!

    Investment begins with $50,000; Value after 9.5 years is $135,869.62

    please help!!!
    Hi,

    I think what you are looking for is the CAGR (Compound Annual Growth Rate) which can be calculated with just the values given. See www.investopedia.com/terms/c/cagr.asp and http://www.investexcel.net/3300/how-...rate-in-excel/ for more information.

    On a five button financial calculator like the HP 10Bii:
    N=9.5, I/Yr=(to be calculated), PV=-50,000, PMT=0, FV=135,869.62, P/YR set to 1
    Press I/Yr to get 11.096% compounded annual growth rate.

    The CAGR formula ((end_value/start_value)^(1/# of years))-1 or
    ((135869.62/50000)^(1/9.5))-1=11.096%

    The Invest-Excel page has examples for both CAGR and XIRR calculations.

    Hope this helps.
    marlin368

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As mentioned previously the 11.096% is an annual growth rate compounded annually. It actually is a little misleading in practice if you do this. If you have an investment that only pays out annually, the amount for 9.5 years will be the same as 9 years. The fact that the question asked about 9.5 years suggests that compounding is done more frequently than annually or is a trick questions.

    With annual compounding the amount earned at 9 years is the same throughout the entire year, there is increase until the 10 year mark. So in practice whatever money you have at 9.5 years (or 9.01 - 9.99) is the same as what you had at 9 years, so the formula would be only for integer values. Anywhere from 9 years until 10 years would be:
    ((135869.62/50000)^(1/9))-1=11.748%

    Of course, if you compounded monthly you would get by adjusting your formula to get the monthly amount and multiplying by 12 [Monthly would allow a 9.5 year value]:
    12*(((135869.62/50000)^(1/(9.5*12)))-1)=10.569%

    As pointed out in previous posts, if compounded at different rates other annual interest rates would be obtained. An annual rate from quarterly compounding would be between the 2 values above. An Annual rate from daily compounding would be lower than either value.

    Steve

  13. #13
    New Lounger
    Join Date
    Jul 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    As mentioned previously the 11.096% is an annual growth rate compounded annually. It actually is a little misleading in practice if you do this. If you have an investment that only pays out annually, the amount for 9.5 years will be the same as 9 years. The fact that the question asked about 9.5 years suggests that compounding is done more frequently than annually or is a trick questions.
    10.569% is the nominal interest rate whereas the interest rate that speedball calculated as 11.096467%
    is the annual effective yield

    Both rates are correct depending on which rate you were looking for

    Quote Originally Posted by sdckapr View Post
    With annual compounding the amount earned at 9 years is the same throughout the entire year, there is increase until the 10 year mark. So in practice whatever money you have at 9.5 years (or 9.01 - 9.99) is the same as what you had at 9 years, so the formula would be only for integer values. Anywhere from 9 years until 10 years would be:
    ((135869.62/50000)^(1/9))-1=11.748%
    Steve
    This is obviously a misstatement

    Interest is earned on the partial time period as well

    Just try to visualize with the help of a formula for future value of $1

    FVIF(i%, n)

    FVIF(10%, 9)
    =(1+10%)^9
    =(1.10)^9
    = $2.357947691

    Now see this

    FVIF(10%, 9.5)
    =(1+10%)^9.5
    =(1.10)^9.5
    = $2.4730364018431784153485758093154

    FVIF(10%, 9.5) - FVIF(10%, 9)
    = $2.4730364018431784153485758093154 - $2.357947691
    = $0.11508871084317841534857580931545

    So for first 6 months in year 9, the $1 earned an interest of $0.1151

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Interest may be earned, but it would not show up on monthly statements, if you have an account that earns interest that is compounded annually. The amount in your account will stay the same throughout the entire year and only be incremented at the start of the next year. If the account statement for the 9th year lists $13,5869.62 it will be the same until the 10th year statement gives a new increase. That is the model that speedball is using for calculations.

    If you have an account that compounds annually, even if you get a monthly statement, all through the year the amount will stay the same, you will not get the interest until the start of the new year. If you closed the account, you could claim that partial, but if looking at statements it would not be there and it seems to me that part of NOTthepro's question is about matching data on monthly statements.

    The value will depend on the model being used for the interest calculation and what period you are basing the compounding on what the value will be. Yes both answers are "correct" for what they are calculating, they just use different models to estimate the exponential increase. The question remains, what is being asked, because the 2 methods give 2 different numbers.

    It would help if NOTthepro provided the monthly data to see as well as what NOTthepro believes the "correct answer" to be since NOTthepro indicated the annual rate based on monthly compounding is closer than the annual rate based on annual compounding. With the "correct answer" we could better see the model NOTthepro wants to base the result on.

    [A side comment You need to watch your signficant figures. If you subtract a number with 9 decimals from a number with more than 9 (in your example 31) the result can have at MOST 9 decimals since we don't know what the 10th value is for it. Your result not only ignores this fact, it actually seems to gain an additional figure which comes from neither value being manipulated! You may want to review Allen Wyatt's article "Thoughts and Ideas on Significant Digits in Excel" at http://excelribbon.tips.net/T012083_..._in_Excel.html]

    Steve

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a chart with the different compoundings modeled and Average annual percentage rate for each model.
    Compounding.png
    The daily used 365.16 days/year since I used as the 9.5 year period Jan 1, 2004 to July 1, 2013.

    Steve

Tags for this Thread

Posting Permissions

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