IRR trouble: Want to figure average annual rate of return

Topic

New Reply

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!!!

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Okay, well Steve’s end result is the closest…. but is there an easy way to create an Excel formula to calculate this???

Reply | Quote

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

Reply | Quote

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] )

Reply | Quote

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.

Reply | Quote

So all your months are the same then?
Would that be the 30-day month standard?

zeddy

Reply | Quote

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”.

Reply | Quote

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 http://www.investopedia.com/terms/c/cagr.asp and http://www.investexcel.net/3300/how-to-calculate-compound-annual-growth-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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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_Thoughts_and_Ideas_on_Significant_Digits_in_Excel.html]

Steve

Reply | Quote

Here is a chart with the different compoundings modeled and Average annual percentage rate for each model.
34313-Compounding
The daily used 365.16 days/year since I used as the 9.5 year period Jan 1, 2004 to July 1, 2013.

Steve

Reply | Quote