| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annualizing Monthly Performance - pivot tables? other formulas?
Hi All,
This is a great way to learn hints, and in reading about Pivot Tables below, I thought someone may know the solution to my problem: - I have 2 columns of data, A is the month/year and B is the PERCENT RETURN for that month - I have 17 months of data, and was asked to ANNUALIZE the ENTIRE PERIOD? - Is this possible ? I tried using the (1 + (RETURN ^(17/12) * (1 + (RETURN ^(17/12), which is time consuming and somehow got a ridiculous number???? - HOW DO I ANNUALIZE PERCENT RETURNS more efficiently? Arrays? Pivot tables? I am so overwhelmed and forget all that I learned LONG ago...ANY HELP WILL COME BACK TO YOU IN GOOD VIBES! Keep up the great posts! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Annualizing Monthly Performance - pivot tables? other formulas?
To annualize monthly returns, you need to do it in two steps. First calculate
the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Hi All, This is a great way to learn hints, and in reading about Pivot Tables below, I thought someone may know the solution to my problem: - I have 2 columns of data, A is the month/year and B is the PERCENT RETURN for that month - I have 17 months of data, and was asked to ANNUALIZE the ENTIRE PERIOD? - Is this possible ? I tried using the (1 + (RETURN ^(17/12) * (1 + (RETURN ^(17/12), which is time consuming and somehow got a ridiculous number???? - HOW DO I ANNUALIZE PERCENT RETURNS more efficiently? Arrays? Pivot tables? I am so overwhelmed and forget all that I learned LONG ago...ANY HELP WILL COME BACK TO YOU IN GOOD VIBES! Keep up the great posts! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Annualized Returns
Hi,
I know this feed was created 2 years ago, but I am wondering if you might be able to help. I am trying to annualize my returns but if the returns go over 1.00 the return are over 4000, which would make it 400,000%. This is the formula that i am currently using: (1+monthly return)^(12)-1. Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. 11-Aug-07 To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Previous Posts In This Thread: On Friday, August 10, 2007 9:46 AM ExcelEmaile wrote: Annualizing Monthly Performance - pivot tables? other formulas? Hi All, This is a great way to learn hints, and in reading about Pivot Tables below, I thought someone may know the solution to my problem: - I have 2 columns of data, A is the month/year and B is the PERCENT RETURN for that month - I have 17 months of data, and was asked to ANNUALIZE the ENTIRE PERIOD? - Is this possible ? I tried using the (1 + (RETURN ^(17/12) * (1 + (RETURN ^(17/12), which is time consuming and somehow got a ridiculous number???? - HOW DO I ANNUALIZE PERCENT RETURNS more efficiently? Arrays? Pivot tables? I am so overwhelmed and forget all that I learned LONG ago...ANY HELP WILL COME BACK TO YOU IN GOOD VIBES! Keep up the great posts! On Saturday, August 11, 2007 10:15 PM Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice BizTalk Repeating Structures Table Looping and Table Extract http://www.eggheadcafe.com/tutorials...g-structu.aspx |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Annualized Returns
Hi,
I know this feed was created 2 years ago, but I am wondering if you might be able to help. I am trying to annualize my returns but if the returns go over 1.00 the return are over 4000, which would make it 400,000%. This is the formula that i am currently using: (1+monthly return)^(12)-1. Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. 11-Aug-07 To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Previous Posts In This Thread: On Friday, August 10, 2007 9:46 AM ExcelEmaile wrote: Annualizing Monthly Performance - pivot tables? other formulas? Hi All, This is a great way to learn hints, and in reading about Pivot Tables below, I thought someone may know the solution to my problem: - I have 2 columns of data, A is the month/year and B is the PERCENT RETURN for that month - I have 17 months of data, and was asked to ANNUALIZE the ENTIRE PERIOD? - Is this possible ? I tried using the (1 + (RETURN ^(17/12) * (1 + (RETURN ^(17/12), which is time consuming and somehow got a ridiculous number???? - HOW DO I ANNUALIZE PERCENT RETURNS more efficiently? Arrays? Pivot tables? I am so overwhelmed and forget all that I learned LONG ago...ANY HELP WILL COME BACK TO YOU IN GOOD VIBES! Keep up the great posts! On Saturday, August 11, 2007 10:15 PM Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... On Saturday, April 10, 2010 4:20 PM E Khan wrote: Annualized Returns Hi, I know this feed was created 2 years ago, but I am wondering if you might be able to help. I am trying to annualize my returns but if the returns go over 1.00 the return are over 4000, which would make it 400,000%. This is the formula that i am currently using: (1+monthly return)^(12)-1. Submitted via EggHeadCafe - Software Developer Portal of Choice WPF Report Engine, Part 2 http://www.eggheadcafe.com/tutorials...ne-part-2.aspx |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Annualized Returns
Hi,
I know this feed was created 2 years ago, but I am wondering if you might be able to help. I am trying to annualize my returns but if the returns go over 1.00 the return are over 4000, which would make it 400,000%. This is the formula that i am currently using: (1+monthly return)^(12)-1. Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. 11-Aug-07 To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Previous Posts In This Thread: On Friday, August 10, 2007 9:46 AM ExcelEmaile wrote: Annualizing Monthly Performance - pivot tables? other formulas? Hi All, This is a great way to learn hints, and in reading about Pivot Tables below, I thought someone may know the solution to my problem: - I have 2 columns of data, A is the month/year and B is the PERCENT RETURN for that month - I have 17 months of data, and was asked to ANNUALIZE the ENTIRE PERIOD? - Is this possible ? I tried using the (1 + (RETURN ^(17/12) * (1 + (RETURN ^(17/12), which is time consuming and somehow got a ridiculous number???? - HOW DO I ANNUALIZE PERCENT RETURNS more efficiently? Arrays? Pivot tables? I am so overwhelmed and forget all that I learned LONG ago...ANY HELP WILL COME BACK TO YOU IN GOOD VIBES! Keep up the great posts! On Saturday, August 11, 2007 10:15 PM Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... On Saturday, April 10, 2010 4:20 PM E Khan wrote: Annualized Returns Hi, I know this feed was created 2 years ago, but I am wondering if you might be able to help. I am trying to annualize my returns but if the returns go over 1.00 the return are over 4000, which would make it 400,000%. This is the formula that i am currently using: (1+monthly return)^(12)-1. On Saturday, April 10, 2010 4:21 PM E Khan wrote: Annualized Returns Hi, I know this feed was created 2 years ago, but I am wondering if you might be able to help. I am trying to annualize my returns but if the returns go over 1.00 the return are over 4000, which would make it 400,000%. This is the formula that i am currently using: (1+monthly return)^(12)-1. Submitted via EggHeadCafe - Software Developer Portal of Choice C# And The Little Iterator That Could http://www.eggheadcafe.com/tutorials...e-iterato.aspx |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Annualized Returns
Your method of annualizing the monthly returns is correct. Are you sure you
are using a percentage as your monthly return? If that doesn't help, post your data. Regards, Fred "E Khan" wrote in message ... Hi, I know this feed was created 2 years ago, but I am wondering if you might be able to help. I am trying to annualize my returns but if the returns go over 1.00 the return are over 4000, which would make it 400,000%. This is the formula that i am currently using: (1+monthly return)^(12)-1. Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. 11-Aug-07 To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Previous Posts In This Thread: On Friday, August 10, 2007 9:46 AM ExcelEmaile wrote: Annualizing Monthly Performance - pivot tables? other formulas? Hi All, This is a great way to learn hints, and in reading about Pivot Tables below, I thought someone may know the solution to my problem: - I have 2 columns of data, A is the month/year and B is the PERCENT RETURN for that month - I have 17 months of data, and was asked to ANNUALIZE the ENTIRE PERIOD? - Is this possible ? I tried using the (1 + (RETURN ^(17/12) * (1 + (RETURN ^(17/12), which is time consuming and somehow got a ridiculous number???? - HOW DO I ANNUALIZE PERCENT RETURNS more efficiently? Arrays? Pivot tables? I am so overwhelmed and forget all that I learned LONG ago...ANY HELP WILL COME BACK TO YOU IN GOOD VIBES! Keep up the great posts! On Saturday, August 11, 2007 10:15 PM Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice BizTalk Repeating Structures Table Looping and Table Extract http://www.eggheadcafe.com/tutorials...g-structu.aspx |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Annualized Returns
Yes, that's correct. If you have a return of 1.00 in one month you've
doubled your money to 200% in 1 month, 400% in 2 months, 800% in 3 months, hence 409600% in 12 months, a return of 409500%. Please let us into the secret as to where you get such a good return. :-) -- David Biddulph "E Khan" wrote in message ... Hi, I know this feed was created 2 years ago, but I am wondering if you might be able to help. I am trying to annualize my returns but if the returns go over 1.00 the return are over 4000, which would make it 400,000%. This is the formula that i am currently using: (1+monthly return)^(12)-1. Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. 11-Aug-07 To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Previous Posts In This Thread: On Friday, August 10, 2007 9:46 AM ExcelEmaile wrote: Annualizing Monthly Performance - pivot tables? other formulas? Hi All, This is a great way to learn hints, and in reading about Pivot Tables below, I thought someone may know the solution to my problem: - I have 2 columns of data, A is the month/year and B is the PERCENT RETURN for that month - I have 17 months of data, and was asked to ANNUALIZE the ENTIRE PERIOD? - Is this possible ? I tried using the (1 + (RETURN ^(17/12) * (1 + (RETURN ^(17/12), which is time consuming and somehow got a ridiculous number???? - HOW DO I ANNUALIZE PERCENT RETURNS more efficiently? Arrays? Pivot tables? I am so overwhelmed and forget all that I learned LONG ago...ANY HELP WILL COME BACK TO YOU IN GOOD VIBES! Keep up the great posts! On Saturday, August 11, 2007 10:15 PM Fred Smith wrote: To annualize monthly returns, you need to do it in two steps. To annualize monthly returns, you need to do it in two steps. First calculate the total return, then annualize it. In each row, calculate the running total return as: =(1+MonthlyReturn%)*(1+PreviousTotalReturn%)-1 In your summary row, calculate your annualized return as: =rate(TotalPeriods/12,0,-1,1+TotalReturn%) -- Regards, Fred "ExcelEmailer" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice BizTalk Repeating Structures Table Looping and Table Extract http://www.eggheadcafe.com/tutorials...g-structu.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formulas and Pivot tables | Excel Discussion (Misc queries) | |||
| pivot tables custom formulas | Excel Discussion (Misc queries) | |||
| Formulas and Pivot Tables | Excel Discussion (Misc queries) | |||
| Annualizing data | Excel Discussion (Misc queries) | |||
| can conditional formulas be used in or with pivot tables | Excel Worksheet Functions | |||
Linear Mode
