How to caclulate porfolio returns over different time periods

[tfrentz]

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

 

[Fred Smith]

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 are:
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.

 

[tfrentz]

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]

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?

 

[tfrentz]

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]

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)

 

[tfrentz]

Thanks Fred. All problems solved.

Tim

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)