Results 1 to 15 of 15

20130627, 11:40 #1
 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!!!

20130627, 12:35 #2
 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 rigthLast edited by speedball; 20130627 at 12:36. Reason: typo

20130627, 14:16 #3
 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%
SteveLast edited by sdckapr; 20130628 at 07:12.

20130627, 16:46 #4
 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???

20130627, 17:05 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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

20130628, 04:48 #6
 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; 20130628 at 05:12.

20130628, 07:05 #7
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
So all your months are the same then?
Would that be the 30day month standard?
zeddy

20130701, 10:33 #8
 Join Date
 Jul 2013
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
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] )

20130704, 09:58 #9
 Join Date
 Mar 2010
 Location
 east coast
 Posts
 701
 Thanks
 89
 Thanked 8 Times in 8 Posts
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

20130704, 10:24 #10
 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
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; 20130704 at 10:26.

20130704, 18:19 #11
 Join Date
 Apr 2013
 Location
 California
 Posts
 1
 Thanks
 0
 Thanked 0 Times in 0 Posts
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...rateinexcel/ 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 InvestExcel page has examples for both CAGR and XIRR calculations.
Hope this helps.
marlin368

20130705, 03:45 #12
 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

20130705, 06:20 #13
 Join Date
 Jul 2013
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
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
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

20130705, 07:54 #14
 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

20130705, 09:52 #15
 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