Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default Converting annual rate of return to quarterly equivalent?

I am having trouble coming up with a method to convert the annual total
return of an investment to the equivalent quarterly return that would
generate the same final value when compounded 4 times during the year--March
31, June 30, Sept 30, and Dec 31.

Suppose I start the year with 10,000 dollars and end the year with 11,000
dollars. The annual rate of return, compounded annual growth rate, or
whatever you want to call it is 10%, calculated by (11000/10000)-1=.1, or
10%.

What fixed quarterly rate would I have to earn for each of the 4 quarters to
generate the same 11,000 value on Dec 31, assuming quarterly compounding??
The quarterly ending dollar values (other than for Dec 31) are unknown and
irrelevant for this exercise.

Trial and error on a handheld tells me it's between 2.41% and 2.42% per
quarter, but how do I get there in Excel 2007?

I have fiddled with the Nominal and Effect functions to no avail and can't
see how IRR or XIRR would help. Maybe a mortgage or annuity function, about
which I am clueless?

Thanks for any help.

Carl


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Converting annual rate of return to quarterly equivalent?

Hi Carl,

I can definitely help you with this. To convert an annual rate of return to a quarterly equivalent, you can use the following formula:

(1 + annual rate)^(1/4) - 1

Let's use your example of a 10% annual rate of return. Using the formula above, we can calculate the quarterly equivalent rate as follows:

(1 + 0.10)^(1/4) - 1 = 0.0244 or 2.44%

So the fixed quarterly rate you would need to earn for each of the four quarters to generate the same $11,000 value on Dec 31, assuming quarterly compounding, is 2.44%.

To apply this formula in Excel 2007, simply enter the annual rate of return in a cell (let's say A1), and then use the following formula in another cell:
  1. =[/b](1+A1)^(1/4)-1[/b]

This will give you the quarterly equivalent rate.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Converting annual rate of return to quarterly equivalent?

On Jun 1, 11:58 am, "Carl LaFong" wrote:
I am having trouble coming up with a method to convert the annual total
return of an investment to the equivalent quarterly return that would
generate the same final value when compounded 4 times during the year--March
31, June 30, Sept 30, and Dec 31.

Suppose I start the year with 10,000 dollars and end the year with 11,000
dollars. The annual rate of return, compounded annual growth rate, or
whatever you want to call it is 10%, calculated by (11000/10000)-1=.1, or
10%.

What fixed quarterly rate would I have to earn for each of the 4 quarters to
generate the same 11,000 value on Dec 31, assuming quarterly compounding??
The quarterly ending dollar values (other than for Dec 31) are unknown and
irrelevant for this exercise.

Trial and error on a handheld tells me it's between 2.41% and 2.42% per
quarter, but how do I get there in Excel 2007?

I have fiddled with the Nominal and Effect functions to no avail and can't
see how IRR or XIRR would help. Maybe a mortgage or annuity function, about
which I am clueless?

Thanks for any help.

Carl


(1+x)^4 = (1+0.1) =
(1+x) = 1.1^(1/4) =
x = 1.1^(1/4)-1

HTH
Kostis Vezerides

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Converting annual rate of return to quarterly equivalent?

=1.1^(1/4)-1
or
=POWER(1.1,1/4)-1
--
David Biddulph

"Carl LaFong" wrote in message
...
I am having trouble coming up with a method to convert the annual total
return of an investment to the equivalent quarterly return that would
generate the same final value when compounded 4 times during the
year--March 31, June 30, Sept 30, and Dec 31.

Suppose I start the year with 10,000 dollars and end the year with 11,000
dollars. The annual rate of return, compounded annual growth rate, or
whatever you want to call it is 10%, calculated by (11000/10000)-1=.1, or
10%.

What fixed quarterly rate would I have to earn for each of the 4 quarters
to generate the same 11,000 value on Dec 31, assuming quarterly
compounding?? The quarterly ending dollar values (other than for Dec 31)
are unknown and irrelevant for this exercise.

Trial and error on a handheld tells me it's between 2.41% and 2.42% per
quarter, but how do I get there in Excel 2007?

I have fiddled with the Nominal and Effect functions to no avail and can't
see how IRR or XIRR would help. Maybe a mortgage or annuity function,
about which I am clueless?

Thanks for any help.

Carl



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default Converting annual rate of return to quarterly equivalent?

Thanks to both of you for the quick response. I have rarely used the Power
function and likely would never have figured it out.


Carl


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SLN & Annual rate of Depreciation CAnn Excel Worksheet Functions 1 February 11th 07 11:19 PM
Annual interest rate jw Excel Worksheet Functions 2 September 7th 05 05:13 PM
APR - Annual Percentage Rate to Actual Interest Rate Safu Excel Worksheet Functions 9 May 18th 05 05:03 AM
Annual Percentage Rate sts111 Excel Discussion (Misc queries) 1 February 1st 05 02:26 PM
APR - Annual Percentage Rate marlia Excel Worksheet Functions 2 December 9th 04 10:05 PM


All times are GMT +1. The time now is 09:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"