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