Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to caclulate porfolio returns over different time periods
I'm sure many of you have seen the performance numbers published by most
mutual fund companies for their different funds. They usually show MTD (month to date), YTD (year to date), 1 year, 3 year, and 5 year percentage returns. I would like to do the same and have Excel automatically update the return percentages every time I update my current portfolio balance with the current date. This is what I'm tracking in Excel over an extended period of time Column A - Date Column B - Amount So it shows up as: 10/25/2006 30,000 10/24/2006 29,800 10/23/2006 29,550 10/20/2006 28,900 10/19/2006 29,001 10/18/2006 29,450 10/17/2006 29,698 10/16/2006 27,300 etc...and this goes on down for 5 years of data. I'm having trouble putting together the formula's to calculate the returns. Some of the complexity relates to there being only 5 days in a week where the dates/amounts can change due to only 5 work days per week. Also, I presume for the MTD calculation, that a 30 day window would suffice even though it isn't exact due to a different amount of days in each month. I've searched for a template to accomplish this, but none seems available. Seems like a pretty common task. I can't believe it's not out there. I'd really appreciate it if someone could help me out. Thanks, Tim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to caclulate porfolio returns over different time periods
I don't see you being able to solve this problem with formulas. In addition to
not having weekend values, the biggest issue is capturing the cash flows during the period. I'm assuming this is not a static portfolio -- there is money moving in and out over the 5-year period. The two options that I see a 1. Use Quicken or Money instead. They do a good job of calculating returns over your desired periods. 2. Develop a macro. It wouldn't be too hard to create a macro which would calculate the returns, given an ending date. -- Regards, Fred "tfrentz" wrote in message ... I'm sure many of you have seen the performance numbers published by most mutual fund companies for their different funds. They usually show MTD (month to date), YTD (year to date), 1 year, 3 year, and 5 year percentage returns. I would like to do the same and have Excel automatically update the return percentages every time I update my current portfolio balance with the current date. This is what I'm tracking in Excel over an extended period of time Column A - Date Column B - Amount So it shows up as: 10/25/2006 30,000 10/24/2006 29,800 10/23/2006 29,550 10/20/2006 28,900 10/19/2006 29,001 10/18/2006 29,450 10/17/2006 29,698 10/16/2006 27,300 etc...and this goes on down for 5 years of data. I'm having trouble putting together the formula's to calculate the returns. Some of the complexity relates to there being only 5 days in a week where the dates/amounts can change due to only 5 work days per week. Also, I presume for the MTD calculation, that a 30 day window would suffice even though it isn't exact due to a different amount of days in each month. I've searched for a template to accomplish this, but none seems available. Seems like a pretty common task. I can't believe it's not out there. I'd really appreciate it if someone could help me out. Thanks, Tim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to caclulate porfolio returns over different time periods
There are no cash flows ...therefore this should be pretty simple.
Therefore, I don't see a need for XIRR or IRR formulas. Just some date manipulation and retrieval functionality. I just want to calculate the simple interest return over a given period of time. So, for the MTD example, I want a formula to subtract 30 days from today and retrieve the balance from 30 days ago, then subtract today's balance from that, and divide by the 30 day old balance. This will give me a simple rate of return over a 30 day window. If someone could help me with the MTD formula, I can figure out the YTD. Same for the 1, 3, and 5 year formulas. For 1 year, I want to subtract 365 days from today's date to find the balance from 365 days ago, and do the same as I mentioned above in the MTD example. The problem with Money or Quicken is that they don't show this level of detail. Yes, they show a total return on investment, but what if I want to know how my investment performed over the last 30 days? I don't care about the annualized return. Thanks if anyone can respond, Tim "Fred Smith" wrote: I don't see you being able to solve this problem with formulas. In addition to not having weekend values, the biggest issue is capturing the cash flows during the period. I'm assuming this is not a static portfolio -- there is money moving in and out over the 5-year period. The two options that I see a 1. Use Quicken or Money instead. They do a good job of calculating returns over your desired periods. 2. Develop a macro. It wouldn't be too hard to create a macro which would calculate the returns, given an ending date. -- Regards, Fred "tfrentz" wrote in message ... I'm sure many of you have seen the performance numbers published by most mutual fund companies for their different funds. They usually show MTD (month to date), YTD (year to date), 1 year, 3 year, and 5 year percentage returns. I would like to do the same and have Excel automatically update the return percentages every time I update my current portfolio balance with the current date. This is what I'm tracking in Excel over an extended period of time Column A - Date Column B - Amount So it shows up as: 10/25/2006 30,000 10/24/2006 29,800 10/23/2006 29,550 10/20/2006 28,900 10/19/2006 29,001 10/18/2006 29,450 10/17/2006 29,698 10/16/2006 27,300 etc...and this goes on down for 5 years of data. I'm having trouble putting together the formula's to calculate the returns. Some of the complexity relates to there being only 5 days in a week where the dates/amounts can change due to only 5 work days per week. Also, I presume for the MTD calculation, that a 30 day window would suffice even though it isn't exact due to a different amount of days in each month. I've searched for a template to accomplish this, but none seems available. Seems like a pretty common task. I can't believe it's not out there. I'd really appreciate it if someone could help me out. Thanks, Tim |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to caclulate porfolio returns over different time periods
If there are no cash flows, that simplifies things substantially. So let's see
if we can point you in the right direction. If your end date is always today, your start date is easy to calculate: 30 days before today is: =today()-30 Start of the month: =date(year(today()),month(today()),1) Start of the year: =date(year(today(),1,1) One year ago: =date(year(today())-1,month(today()),day(today()) Three years ago: I'm sure you get the picture If you want total return, it's simply =(EndValue-StartValue)/StartValue If you want annualized return, it's =rate((enddate-startdate)/365,0,-startvalue,endvalue) The only trick is to find the StartValue, which you can do with a Vlookup, as in: =vlookup(startdate,A:B,2,true) As long as your dates are in order, it will find the start date you have calculated or the next date in the list (if start date is a weekend, for example). Does this help? -- Regards, Fred "tfrentz" wrote in message ... There are no cash flows ...therefore this should be pretty simple. Therefore, I don't see a need for XIRR or IRR formulas. Just some date manipulation and retrieval functionality. I just want to calculate the simple interest return over a given period of time. So, for the MTD example, I want a formula to subtract 30 days from today and retrieve the balance from 30 days ago, then subtract today's balance from that, and divide by the 30 day old balance. This will give me a simple rate of return over a 30 day window. If someone could help me with the MTD formula, I can figure out the YTD. Same for the 1, 3, and 5 year formulas. For 1 year, I want to subtract 365 days from today's date to find the balance from 365 days ago, and do the same as I mentioned above in the MTD example. The problem with Money or Quicken is that they don't show this level of detail. Yes, they show a total return on investment, but what if I want to know how my investment performed over the last 30 days? I don't care about the annualized return. Thanks if anyone can respond, Tim "Fred Smith" wrote: I don't see you being able to solve this problem with formulas. In addition to not having weekend values, the biggest issue is capturing the cash flows during the period. I'm assuming this is not a static portfolio -- there is money moving in and out over the 5-year period. The two options that I see a 1. Use Quicken or Money instead. They do a good job of calculating returns over your desired periods. 2. Develop a macro. It wouldn't be too hard to create a macro which would calculate the returns, given an ending date. -- Regards, Fred "tfrentz" wrote in message ... I'm sure many of you have seen the performance numbers published by most mutual fund companies for their different funds. They usually show MTD (month to date), YTD (year to date), 1 year, 3 year, and 5 year percentage returns. I would like to do the same and have Excel automatically update the return percentages every time I update my current portfolio balance with the current date. This is what I'm tracking in Excel over an extended period of time Column A - Date Column B - Amount So it shows up as: 10/25/2006 30,000 10/24/2006 29,800 10/23/2006 29,550 10/20/2006 28,900 10/19/2006 29,001 10/18/2006 29,450 10/17/2006 29,698 10/16/2006 27,300 etc...and this goes on down for 5 years of data. I'm having trouble putting together the formula's to calculate the returns. Some of the complexity relates to there being only 5 days in a week where the dates/amounts can change due to only 5 work days per week. Also, I presume for the MTD calculation, that a 30 day window would suffice even though it isn't exact due to a different amount of days in each month. I've searched for a template to accomplish this, but none seems available. Seems like a pretty common task. I can't believe it's not out there. I'd really appreciate it if someone could help me out. Thanks, Tim |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to caclulate porfolio returns over different time periods
Hi Fred,
Yes, this was exactly what I was looking for. Thank you very much! The only trick was to change the order of my entries to ascending by date so the VLOOKUP function would work. All the total returns worked, however, I had unknown errors with the annualized return. I'll show you what I have in my spreadsheet based on what you provided for the RATE formula: (I added what I thought necessary to complete the RATE function... Note: the $A$1031 refers to the bottom row for the 10/28/2006 date and amount) =RATE(($A$1031-DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))/365,0,VLOOKUP(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),$B$1031) My data looks like this: Column A Column B Date Current Amount 01/01/2004 21,500 01/02/2004 21,600 .... (tons of dates here) 10/26/2006 29,000 10/27/2006 28,500 10/28/2006 30,000 Excel complains that I've entered too few arguments for this function. Not sure how to proceed beyond this... that RATE formula is getting pretty complex. Thanks for any help you can provide to complete this. Tim "Fred Smith" wrote: If there are no cash flows, that simplifies things substantially. So let's see if we can point you in the right direction. If your end date is always today, your start date is easy to calculate: 30 days before today is: =today()-30 Start of the month: =date(year(today()),month(today()),1) Start of the year: =date(year(today(),1,1) One year ago: =date(year(today())-1,month(today()),day(today()) Three years ago: I'm sure you get the picture If you want total return, it's simply =(EndValue-StartValue)/StartValue If you want annualized return, it's =rate((enddate-startdate)/365,0,-startvalue,endvalue) The only trick is to find the StartValue, which you can do with a Vlookup, as in: =vlookup(startdate,A:B,2,true) As long as your dates are in order, it will find the start date you have calculated or the next date in the list (if start date is a weekend, for example). Does this help? -- Regards, Fred "tfrentz" wrote in message ... There are no cash flows ...therefore this should be pretty simple. Therefore, I don't see a need for XIRR or IRR formulas. Just some date manipulation and retrieval functionality. I just want to calculate the simple interest return over a given period of time. So, for the MTD example, I want a formula to subtract 30 days from today and retrieve the balance from 30 days ago, then subtract today's balance from that, and divide by the 30 day old balance. This will give me a simple rate of return over a 30 day window. If someone could help me with the MTD formula, I can figure out the YTD. Same for the 1, 3, and 5 year formulas. For 1 year, I want to subtract 365 days from today's date to find the balance from 365 days ago, and do the same as I mentioned above in the MTD example. The problem with Money or Quicken is that they don't show this level of detail. Yes, they show a total return on investment, but what if I want to know how my investment performed over the last 30 days? I don't care about the annualized return. Thanks if anyone can respond, Tim "Fred Smith" wrote: I don't see you being able to solve this problem with formulas. In addition to not having weekend values, the biggest issue is capturing the cash flows during the period. I'm assuming this is not a static portfolio -- there is money moving in and out over the 5-year period. The two options that I see a 1. Use Quicken or Money instead. They do a good job of calculating returns over your desired periods. 2. Develop a macro. It wouldn't be too hard to create a macro which would calculate the returns, given an ending date. -- Regards, Fred "tfrentz" wrote in message ... I'm sure many of you have seen the performance numbers published by most mutual fund companies for their different funds. They usually show MTD (month to date), YTD (year to date), 1 year, 3 year, and 5 year percentage returns. I would like to do the same and have Excel automatically update the return percentages every time I update my current portfolio balance with the current date. This is what I'm tracking in Excel over an extended period of time Column A - Date Column B - Amount So it shows up as: 10/25/2006 30,000 10/24/2006 29,800 10/23/2006 29,550 10/20/2006 28,900 10/19/2006 29,001 10/18/2006 29,450 10/17/2006 29,698 10/16/2006 27,300 etc...and this goes on down for 5 years of data. I'm having trouble putting together the formula's to calculate the returns. Some of the complexity relates to there being only 5 days in a week where the dates/amounts can change due to only 5 work days per week. Also, I presume for the MTD calculation, that a 30 day window would suffice even though it isn't exact due to a different amount of days in each month. I've searched for a template to accomplish this, but none seems available. Seems like a pretty common task. I can't believe it's not out there. I'd really appreciate it if someone could help me out. Thanks, Tim |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to caclulate porfolio returns over different time periods
Your problem is the Vlookup. You have only the first parameter, not the
reamining ones. Try the following: =RATE(($A$1031-DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))/365,0,VLOOKUP(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),A:B,2,TRUE),$B$1031 ) -- Regards, Fred "tfrentz" wrote in message ... Hi Fred, Yes, this was exactly what I was looking for. Thank you very much! The only trick was to change the order of my entries to ascending by date so the VLOOKUP function would work. All the total returns worked, however, I had unknown errors with the annualized return. I'll show you what I have in my spreadsheet based on what you provided for the RATE formula: (I added what I thought necessary to complete the RATE function... Note: the $A$1031 refers to the bottom row for the 10/28/2006 date and amount) =RATE(($A$1031-DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))/365,0,VLOOKUP(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),$B$1031) My data looks like this: Column A Column B Date Current Amount 01/01/2004 21,500 01/02/2004 21,600 ... (tons of dates here) 10/26/2006 29,000 10/27/2006 28,500 10/28/2006 30,000 Excel complains that I've entered too few arguments for this function. Not sure how to proceed beyond this... that RATE formula is getting pretty complex. Thanks for any help you can provide to complete this. Tim "Fred Smith" wrote: If there are no cash flows, that simplifies things substantially. So let's see if we can point you in the right direction. If your end date is always today, your start date is easy to calculate: 30 days before today is: =today()-30 Start of the month: =date(year(today()),month(today()),1) Start of the year: =date(year(today(),1,1) One year ago: =date(year(today())-1,month(today()),day(today()) Three years ago: I'm sure you get the picture If you want total return, it's simply =(EndValue-StartValue)/StartValue If you want annualized return, it's =rate((enddate-startdate)/365,0,-startvalue,endvalue) The only trick is to find the StartValue, which you can do with a Vlookup, as in: =vlookup(startdate,A:B,2,true) As long as your dates are in order, it will find the start date you have calculated or the next date in the list (if start date is a weekend, for example). Does this help? -- Regards, Fred "tfrentz" wrote in message ... There are no cash flows ...therefore this should be pretty simple. Therefore, I don't see a need for XIRR or IRR formulas. Just some date manipulation and retrieval functionality. I just want to calculate the simple interest return over a given period of time. So, for the MTD example, I want a formula to subtract 30 days from today and retrieve the balance from 30 days ago, then subtract today's balance from that, and divide by the 30 day old balance. This will give me a simple rate of return over a 30 day window. If someone could help me with the MTD formula, I can figure out the YTD. Same for the 1, 3, and 5 year formulas. For 1 year, I want to subtract 365 days from today's date to find the balance from 365 days ago, and do the same as I mentioned above in the MTD example. The problem with Money or Quicken is that they don't show this level of detail. Yes, they show a total return on investment, but what if I want to know how my investment performed over the last 30 days? I don't care about the annualized return. Thanks if anyone can respond, Tim "Fred Smith" wrote: I don't see you being able to solve this problem with formulas. In addition to not having weekend values, the biggest issue is capturing the cash flows during the period. I'm assuming this is not a static portfolio -- there is money moving in and out over the 5-year period. The two options that I see a 1. Use Quicken or Money instead. They do a good job of calculating returns over your desired periods. 2. Develop a macro. It wouldn't be too hard to create a macro which would calculate the returns, given an ending date. -- Regards, Fred "tfrentz" wrote in message ... I'm sure many of you have seen the performance numbers published by most mutual fund companies for their different funds. They usually show MTD (month to date), YTD (year to date), 1 year, 3 year, and 5 year percentage returns. I would like to do the same and have Excel automatically update the return percentages every time I update my current portfolio balance with the current date. This is what I'm tracking in Excel over an extended period of time Column A - Date Column B - Amount So it shows up as: 10/25/2006 30,000 10/24/2006 29,800 10/23/2006 29,550 10/20/2006 28,900 10/19/2006 29,001 10/18/2006 29,450 10/17/2006 29,698 10/16/2006 27,300 etc...and this goes on down for 5 years of data. I'm having trouble putting together the formula's to calculate the returns. Some of the complexity relates to there being only 5 days in a week where the dates/amounts can change due to only 5 work days per week. Also, I presume for the MTD calculation, that a 30 day window would suffice even though it isn't exact due to a different amount of days in each month. I've searched for a template to accomplish this, but none seems available. Seems like a pretty common task. I can't believe it's not out there. I'd really appreciate it if someone could help me out. Thanks, Tim |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to caclulate porfolio returns over different time periods
Thanks Fred. All problems solved.
Tim "Fred Smith" wrote: Your problem is the Vlookup. You have only the first parameter, not the reamining ones. Try the following: =RATE(($A$1031-DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))/365,0,VLOOKUP(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),A:B,2,TRUE),$B$1031 ) -- Regards, Fred "tfrentz" wrote in message ... Hi Fred, Yes, this was exactly what I was looking for. Thank you very much! The only trick was to change the order of my entries to ascending by date so the VLOOKUP function would work. All the total returns worked, however, I had unknown errors with the annualized return. I'll show you what I have in my spreadsheet based on what you provided for the RATE formula: (I added what I thought necessary to complete the RATE function... Note: the $A$1031 refers to the bottom row for the 10/28/2006 date and amount) =RATE(($A$1031-DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))/365,0,VLOOKUP(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),$B$1031) My data looks like this: Column A Column B Date Current Amount 01/01/2004 21,500 01/02/2004 21,600 ... (tons of dates here) 10/26/2006 29,000 10/27/2006 28,500 10/28/2006 30,000 Excel complains that I've entered too few arguments for this function. Not sure how to proceed beyond this... that RATE formula is getting pretty complex. Thanks for any help you can provide to complete this. Tim "Fred Smith" wrote: If there are no cash flows, that simplifies things substantially. So let's see if we can point you in the right direction. If your end date is always today, your start date is easy to calculate: 30 days before today is: =today()-30 Start of the month: =date(year(today()),month(today()),1) Start of the year: =date(year(today(),1,1) One year ago: =date(year(today())-1,month(today()),day(today()) Three years ago: I'm sure you get the picture If you want total return, it's simply =(EndValue-StartValue)/StartValue If you want annualized return, it's =rate((enddate-startdate)/365,0,-startvalue,endvalue) The only trick is to find the StartValue, which you can do with a Vlookup, as in: =vlookup(startdate,A:B,2,true) As long as your dates are in order, it will find the start date you have calculated or the next date in the list (if start date is a weekend, for example). Does this help? -- Regards, Fred "tfrentz" wrote in message ... There are no cash flows ...therefore this should be pretty simple. Therefore, I don't see a need for XIRR or IRR formulas. Just some date manipulation and retrieval functionality. I just want to calculate the simple interest return over a given period of time. So, for the MTD example, I want a formula to subtract 30 days from today and retrieve the balance from 30 days ago, then subtract today's balance from that, and divide by the 30 day old balance. This will give me a simple rate of return over a 30 day window. If someone could help me with the MTD formula, I can figure out the YTD. Same for the 1, 3, and 5 year formulas. For 1 year, I want to subtract 365 days from today's date to find the balance from 365 days ago, and do the same as I mentioned above in the MTD example. The problem with Money or Quicken is that they don't show this level of detail. Yes, they show a total return on investment, but what if I want to know how my investment performed over the last 30 days? I don't care about the annualized return. Thanks if anyone can respond, Tim "Fred Smith" wrote: I don't see you being able to solve this problem with formulas. In addition to not having weekend values, the biggest issue is capturing the cash flows during the period. I'm assuming this is not a static portfolio -- there is money moving in and out over the 5-year period. The two options that I see a 1. Use Quicken or Money instead. They do a good job of calculating returns over your desired periods. 2. Develop a macro. It wouldn't be too hard to create a macro which would calculate the returns, given an ending date. -- Regards, Fred "tfrentz" wrote in message ... I'm sure many of you have seen the performance numbers published by most mutual fund companies for their different funds. They usually show MTD (month to date), YTD (year to date), 1 year, 3 year, and 5 year percentage returns. I would like to do the same and have Excel automatically update the return percentages every time I update my current portfolio balance with the current date. This is what I'm tracking in Excel over an extended period of time Column A - Date Column B - Amount So it shows up as: 10/25/2006 30,000 10/24/2006 29,800 10/23/2006 29,550 10/20/2006 28,900 10/19/2006 29,001 10/18/2006 29,450 10/17/2006 29,698 10/16/2006 27,300 etc...and this goes on down for 5 years of data. I'm having trouble putting together the formula's to calculate the returns. Some of the complexity relates to there being only 5 days in a week where the dates/amounts can change due to only 5 work days per week. Also, I presume for the MTD calculation, that a 30 day window would suffice even though it isn't exact due to a different amount of days in each month. I've searched for a template to accomplish this, but none seems available. Seems like a pretty common task. I can't believe it's not out there. I'd really appreciate it if someone could help me out. Thanks, Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hot key for time? | New Users to Excel | |||
Hot key for time? | Excel Worksheet Functions | |||
Hot key for time? | Excel Discussion (Misc queries) | |||
calculate time periods | Excel Worksheet Functions | |||
time sheet to calculate 2 different columns | Excel Worksheet Functions |