S10: Performance Evaluation – Spreadsheet Applications

1

Using EXCEL to Evaluate Portfolio Performance
Assume that
• today is 3 January,
• investors have an investment horizon of 60 days, and
• there are two funds to evaluate, an Aussie fund and the All Ords index portfolio.
Stage 1: Collecting Data and Generating Excess Returns for the SIM
Table A: Source Data
Returns

Date
02/01
03/01
04/01
07/01
08/01
09/01
…
27/02
28/02
01/03
04/03

Aust
Aussie
60 day
Fund
Bank Bill (Unit Price)
4.84%
2.4650
4.86%
2.4775
4.87%
2.4950
4.90%
2.5175
4.93%
2.5175
4.91%
2.5325
…
…
5.07%
2.5650
5.07%
2.5775
5.05%
2.5875
4.94%
2.5800

All
Ords
2464.8
2503.8
2551.6
2545.8
2571.0
2513.4
…
2601.4
2616.5
2609.1
2644.3

Aussie
Fund
0.0051
0.0070
0.0090
0.0000
0.0059
…
0.0059
0.0049
0.0039
-0.0029

All
Ords
0.0157
0.0189
-0.0023
0.0098
-0.0227
…
0.0097
0.0058
-0.0028
0.0134

Excess Returns
Aussie
Fund
0.0049
0.0069
0.0088
-0.0001
0.0058
…
0.0057
0.0047
0.0037
-0.0030

All
Ords
0.0156
0.0188
-0.0024
0.0097
-0.0228
…
0.0096
0.0056
-0.0030
0.0133

S10: Performance Evaluation – Spreadsheet Applications

2

Stage 2: Generating Inputs for the Performance Measures
Table B: Summary Information required by the Performance Measures
Aussie
All
Fund
Ords
Average Portfolio Return
0.0011 0.0016
Standard Deviation of Portfolio Return
0.0049 0.0106
Risk-Free
Return
0.0001
alpha
0.0008
t-stat
1.0909
beta
0.0670
Residual Standard Deviation
0.0048

Both the Sharpe and Treynor indices require:
• the arithmetic average return to summarise the fund’s overall performance during the evaluation period and
• the risk-free rate at the beginning of the period.
The standard deviation of portfolio return is required by the Sharpe’s ratio.
We also regress the excess returns of the Aussie fund on the excess returns of the market index. The outputs that are of interests include
• the intercept and its t-stat - to test if the fund had a significant and positive alpha,
• the coefficient of the X-variable – to obtain the portfolio beta for computing the Treynor index, and
• the residuals – to generate the