This paper describes how to calculate the rate of return of a mutual fund or a portfolio of mutual funds with the aid of a spread sheet.
First we consider the short term situation. Here the period is a quarter or a year, and simple interest computations are appropriate. The "method of adjusted beginnings" resolves the problem of intermediate transactions.
Then we consider a longer term. There is no closed formula to compute the compound rate corresponding to a series of transactions over an extended period of time. Nevertheless a method of "successive approximations" serves to compute the rate as accurately as desired.
The examples discussed are computed in the Microsoft Excel workbooks referenced below. You may need to twiddle with the columns a little. The widths are perfect for my Mac; they may leave something to be desired on PCs.
Here is the data pertinent to calculating the quarterly returns for a portfolio of mutual funds. We assume that no external transactions have taken place during the quarter. (An external transaction occurs when money is invested in or withdrawn from the portfolio. Reinvested dividends and capital gains are strictly internal and have no direct bearing on return calculations.)
| Beginning Date | 01/01/96 |
| End Date | 03/31/96 |
| Fund | Beginning Amount | End Amount |
|---|---|---|
| Astor Growth | 20,000.00 | 22,121.12 |
| Bedford Fund | 30,000.00 | 28,372.56 |
| Canton Income | 20,000.00 | 21,172.82 |
| Delf Cap Apprec | 15,000.00 | 18,524.18 |
| Excel Gr & Inc | 35,000.00 | 38,714.22 |
| Portfolio | 120,000.00 | 128,904.90 |
The (simple, quarterly interest) returns for the above portfolio may be
calculated by means of the formula
rate of return |
= |
change in value / beginning amount |
The following rate table summarizes the computations. The "percent of portfolio" column lists the proportions of the funds making up the portfolio at the beginning of the quarter, rounded to the nearest percent.
| Fund | Beginning Amount | Pct of Portfolio |
Change in Value | Rate of Return |
|---|---|---|---|---|
| Astor Growth | 20,000.00 | 17% | +2,121.12 | +10.6% |
| Bedford Fund | 30,000.00 | 25% | -1,627.44 | -5.4% |
| Canton Income | 20,000.00 | 17% | +1,172.82 | +5.9% |
| Delf Cap Apprec | 15,000.00 | 13% | +3,524.18 | +23.5% |
| Excel Gr & Inc | 35,000.00 | 29% | +3,714.22 | +10.6% |
| Portfolio | 120,000.00 | 100% | +8,904.90 | +7.4% |
This Rate Of Return Table is exactly what we are looking for. With certain reinterpretations the same rate table can describe the general situation, where any number of external transactions occur during the quarter.
Now we consider the general case, when external transactions are involved. The data needed for complete return computations are
| Beginning Date | 01/01/96 |
| End Date | 03/31/96 |
| Fund | Beginning Amount | End Amount |
|---|---|---|
| Astor Growth | 20,000.00 | 22,121.12 |
| Bedford Fund | 30,000.00 | 0.00 |
| Canton Income | 20,000.00 | 27,360.56 |
| Delf Cap Apprec | 15,000.00 | 28,803.67 |
| Excel Gr & Inc | 35,000.00 | 38,714.22 |
| Fiducial Fund | 0.00 | 20,322.18 |
| Portfolio | 120,000.00 | 137,321.75 |
| Fund | Transaction Date | Transaction Amount |
Proportion of Time Left | Adjusted Trans Amount |
|---|---|---|---|---|
| Bedford Fund | 02/15/96 | -28,973.18 | 0.495 | -14,341.72 |
| Canton Income | 01/26/96 | +2,000.00 | 0.714 | +1,428.00 |
| Canton Income | 02/23/96 | +2,000.00 | 0.407 | +814.00 |
| Canton Income | 03/29/96 | +2,000.00 | 0.022 | +44.00 |
| Delf Cap Apprec | 02/22/96 | +10,000.00 | 0.418 | +4,180.00 |
| Fiducial Fund | 02/26/96 | +20,000.00 | 0.374 | +7,480.00 |
| Portfolio | +7,026.82 | -395.72 |
The first three columns of the Transaction Table record the transactions during the quarter. Positive amounts correspond to purchases, negative amounts to redemptions. The last two columns of the Transaction Table and the following Rate Of Return Table will be discussed in the next two sections.
Here is the Rate Of Return Table corresponding to the above data:
| Fund | Adjusted Beginning | Pct of Portfolio |
Net Change in Value | Rate of Return |
|---|---|---|---|---|
| Astor Growth | 20,000.00 | 17% | +2,121.12 | +10.6% |
| Bedford Fund | 15,658.28 | 13% | -1,026.82 | -6.6% |
| Canton Income | 22,286.00 | 19% | +1,360.56 | +6.1% |
| Delf Cap Apprec | 19,180.00 | 16% | +3,803.67 | +19.8% |
| Excel Gr & Inc | 35,000.00 | 29% | +3,714.22 | +10.6% |
| Fiducial Fund | 7,480.00 | 6% | +322.18 | +4.3% |
| Portfolio | 119,604.28 | 100% | +10,294.93 | +8.6% |
The Method of Adjusted Beginnings is a method of computing simple interest rates of return over a given period. This method can be described by the following formulas:
|
The Rate Of Return Table for the general example was generated by the Method of Adjusted Beginnings. The percent of the portfolio occupied by an individual fund is the percent taken up by its adjusted beginning.
Consider Delf Capital Appreciation. The change in value of Delf during
the quarter was 28,803.67 - 15,000.00 = $13,803.67 (Terminal
Amount Table). However, $10,000 worth of shares was purchased in the
middle of the period, on 2/22/96 (Transaction Table). Consequently, the
"net change in value," the change attributable to the earning of
"interest," was only 13,803.67 - 10,000 = $3,803.67. This net
change is precisely the amount given by the formula
net change in value |
= |
end amount - ( beginning amount + transaction amounts ) |
Now let us examine precisely how Delf Capital Appreciation earned interest during the quarter. The beginning amount, $15,000, clearly earned interest throughout the period. However, the $10,000 invested on February 22 only earned interest from February 23 until the end of the quarter, for only 38 of the 91 days in the quarter. The ratio 38/91 = 0.418 (rounded to three places) is the "proportion of time left," the proportion of the quarter over which the $10,000 actually earned interest.
From the simple interest standpoint the above situation is equivalent to
having
| 15,000 + 10,000 * 0.418 | = | 15,000 + 4,180 | = | $19,180.00 |
adjusted beginning amount |
= |
beginning amount + adjusted transaction amounts |
adjusted transaction amount |
= |
transaction amount * proportion of time left |
In view of the above equivalence, the quarterly rate of return on Delf
Capital Appreciation, namely 19.8%, must be given by
rate of return |
= |
net change in value / adjusted beginning amount |
There is nothing special about Delf Capital Appreciation, of course. The quarterly rates of return for each of the other funds and the portfolio as a whole are calculated the same way.
The Method of Adjusted Beginnings is a method for computing simple interest rates of return for a full period. When there are no intermediate, external transactions, such a rate of return measures the percentage growth during the period. This is not the case when there are intermediate transactions. For example, an amount invested mid-period that grows 10% by the end the period has a 20% rate of return.
In the above examples we have taken one quarter as a sample period. Quarterly rates can be annualized by multiplying by four. In most cases this just confuses the issue. The fact that a fund or a portfolio grows at a 10% quarterly rate during an individual quarter does not generally imply that it will grow at a 40% annual rate over a year.
The Method of Adjusted Beginnings is appropriate for computing annual rates over a period of one year. The method can be implemented on a spread sheet very easily. Date arithmetic should be used to calculate the proportions of time left.
Simple interest calculations are inappropriate for periods of longer than one year. To complete this paper we will give an example of how to compute the annual rate of interest, continuously compounded, for data spanning several years. These computations can be easily implemented on a spread sheet as well.
The table below is a combination Transaction and Rate of Return Table for a single fund over a four year period. The first three columns give all relevant data: the dates, the beginning and end values, and the external transactions in between. For convenience we think of the beginning and end values as transactions. The beginning amount is deposited the day before the period begins. The end amount is withdrawn after the close of the last day of the four year period. Deposits (purchases) are signed positively; withdrawals (sales) are signed negatively.
The fourth column shows the number of years (rounded to three decimal
places) from the date of a transaction until the end of the period under
consideration, 12/31/95. This column has been computed with the formula
years left |
= |
( end date - transaction date ) / 365.25 |
| [12/31/95] - [5/17/93] | = | 958 days left |
| 958 days / 365.25 days per year | = | 2.623 years left (rounded to three places) |
The last two columns show the annual rates of interest, compound and
simple, and the corresponding growth of each transaction during
the period. The compound growth of an amount is defined to be
compound growth |
= |
amount * EXP( rate * years left ) |
simple growth |
= |
amount * ( 1 + rate * years left ) |
Here is the table:
|
TRANSACTION AND RATE OF RETURN TABLE |
Compound Annual Rate |
Simple Annual Rate |
|||
|---|---|---|---|---|---|
| 16.7% | 23.6% | ||||
| Date | Transaction | Amount | Years Left |
Compound Growth | Simple Growth |
| 12/31/91 | Beginning Value | 21,327.52 | 4.000 | 41,595.76 | 41,460.70 |
| 05/17/93 | Purchase | 10,000.00 | 2.623 | 15,496.68 | 16,190.28 |
| 06/13/94 | Redemption | -15,000.00 | 1.550 | -19,431.59 | -20,487.00 |
| 02/13/95 | Purchase | 10,000.00 | 0.879 | 11,581.14 | 12,074.44 |
| 12/31/95 | End Value | -49,208.62 | 0.000 | -49,208.62 | -49,208.62 |
| Totals | -22,881.10 | +33.37 | +29.80 | ||
How do you read above table? Look at the compound growth column. Under an annual interest rate of 16.7% the transactions prior to the end date grow to an amount that is $33.37 more than the end amount, $49,208.62. This is the significance of the compound growth total, +33.37. A compound growth total of 0.00 would indicate an exact match--the prior transactions grow to exactly the end amount.
Since 16.7% produces growth that is $33.37 too much for the end amount, the exact growth rate must be less than 16.7%. However, when we plug in a compound rate of 16.6%, the growth total becomes -153.35. The prior transactions fall $153.35 short of explaining the end amount. It follows that the exact compound growth rate is between 16.6% and 16.7%, and closer to the latter.
The compound annual rate of return must be obtained by successive approximations. There is no closed formula that will give you this number. Fortunately it is not a difficult matter to get a good approximation (16.7% is accurate to this many places) once the growth formulas are in place on a spread sheet. You start with a guess like 10% and see that the growth total is around -11,000; $11,000 short. You increase the rate to 15%, and now you are only $3,000 short. 17% gives a growth total of almost +600; $600 too much. And so it goes. By experimenting in this way you find that 16.7% is the best three digit approximation of the exact rate. (Indeed, the exact rate, 16.682...%, has no more meaning than the approximation, 16.7%, for this kind of application.)
You can get the simple rate of return by the same method of successive
approximations. There is a closed formula for the simple rate, however.
It reads
rate of return |
= |
net change in value / sum of time-weighted amounts |
| 22,881.10 / 97,080.08 | = | 23.6% |
How to determine rates of return is a mystery to many investors. When a beginning amount grows to so much at the end of a period, the problem is somewhat tractable. However, when intermediate investments and redemptions confuse the issue, most investors just throw up their hands.
In this paper we have tried to remedy the situation. We show how rates of return can be calculated in the most general situations with the help of a spread sheet.
| Last modified: 17-Dec-96 | Vic Norton - |