How to convert daily returns into weekly returns?

[Mirko]

Hi everyone,
I have a nice case to prepare and need weekly riskfree interest returns on the UK treasury bond. Right now I have daily returns and want to convert them into weekly. The problem is, weeks do not always contain five trading days. How can I turn my data into weekly average riskfree interest returns?
Please, help me
kind regards
Mirko

Look at a part of my spreadsheet below.<SCRIPT language=JavaScript src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

UK interest daily.xls
	A	B	C	D
1	Date	Interest rate	weekday
2	4-Jan-88	9.87	1
3	5-Jan-88	9.99	2
4	6-Jan-88	10.05	3
5	7-Jan-88	10.09	4
6	8-Jan-88	10.13	5
7	11-Jan-88	10.05	1
8	12-Jan-88	10.07	2
9	13-Jan-88	10.28	3
10	14-Jan-88	10.11	4
11	15-Jan-88	9.98	5
12	18-Jan-88	9.87	1
13	19-Jan-88	9.96	2
14	20-Jan-88	9.91	3
15	21-Jan-88	9.86	4
16	22-Jan-88	9.71	5
17	25-Jan-88	9.7	1
18	26-Jan-88	9.76	2
19	27-Jan-88	9.69	3
20	28-Jan-88	9.56	4
21	29-Jan-88	9.55	5
22	1-Feb-88	9.74	1
23	2-Feb-88	9.62	2
24	3-Feb-88	9.64	3
25	4-Feb-88	9.68	4
26	5-Feb-88	9.75	5
27	8-Feb-88	9.93	1
28	9-Feb-88	9.85	2
UK interest daily

 

[dave3009]

Example

Top one is a pivot based on group by 7 days (a week), second is using an additional WEEKNUM Column. You see the massive difference. Using the group option means that the OP can select the start day of their week, so more versatile.

Excel Workbook
	A	B	C
1
2
3	Row Labels	Average of Daily Rate
4	04/01/1988 - 10/01/1988	10.029
5	11/01/1988 - 17/01/1988	10.101
6	18/01/1988 - 24/01/1988	9.865
7	25/01/1988 - 31/01/1988	9.6568
8	01/02/1988 - 07/02/1988	9.689
9	08/02/1988 - 10/02/1988	9.8915
10	Grand Total	9.869888889
11
12
13	Row Labels	Average of Daily Rate
14	2	10.029
15	3	10.101
16	4	9.865
17	5	9.6568
18	6	9.689
19	7	9.8915
20	Grand Total	9.869888889
21
22
Sheet4

 

[lalbatros]

I am not sure I understood the question correctly.
But assuming the interrest rates you have listed are valid for one day, then I would not use pivot tables to solve this question.

I would simply calculate for each day of the table the cumulated value of a given investment.
This is just one additional column to the table.

For example, assuming the investment value is 1000 end of 03-jan-88,
it is easy to calculate the value end of 04-jan-88, based on 9.87% interrest.
In this way you can proceed to get these values:

end of 04-jan-88 you get a value of 1000.2579172624
end of 05-jan-88 you get a value of 1000.518893289
end of 06-jan-88 you get a value of 1000.78143269735
end of 07-jan-88 you get a value of 1001.0450376673
... and so on ...

It is then easy to calculate the average interrest rate between any couple of dates, using the definition of an interrest rate.

Please note that averaging on a long period could produce errors in the evaluation of the average interrest rate over that period. Clearly, the definition of an interrest rate has to be applied. In particular, summing or averaging interrest rates can only produce approximate results.

Note also, that the method described above may lead to some errors too, because of roundings, when long periods are involved. It is not difficult to improve the precison by summing logarithms instead of multiplying factors. But the principle remains the same: stick to the definitions.

 

[lalbatros]

Oops,

I forgot to say one thing.
For non-trading days, you have to specify the rule, just as for weekends too.
I would assume that the last rate applies till the next trading day.
Therefore, it would be easy to fill in a full calendar of daily returns.
It is then straigthforward to calculate the average return between any two dates just from the value ratio between these two dates.