Saturday, June 30, 2018

AVERAGEIF in Excel


Using the Averageif function in Excel

Question:  Below are two-year return statistics for an investment grade fund and a government bond fund.   Use the Averagif command in Excel to calculate the average when government bond returns exceed investment grade bond returns and the average when government bond returns are lower than investment grade bond returns.


Two Year Returns for Two Bond Funds
Year
Investment Grade Bond Fund
Government Bond Fund
Diff
2006
6.8%
7.5%
0.7%
2007
7.0%
6.4%
-0.7%
2008
3.8%
12.9%
9.2%
2009
-3.7%
15.2%
18.9%
2010
8.9%
17.8%
8.9%
2011
25.8%
23.5%
-2.3%
2012
16.6%
14.0%
-2.5%
2013
13.5%
14.5%
1.0%
2014
4.5%
7.1%
2.6%
2015
6.5%
5.8%
-0.7%
2016
3.4%
5.1%
1.7%
2017
2.3%
1.3%
-1.0%



Discussion of AverageIf formula:

The AverageIf formula has three inputs – (1) the range of variables with information on the categories that must be averaged, (2) the criteria or category label of the item being averaged, and (3) the range of values being averaged.

Analysis:


Column A in the chart below contains values of the difference between two-year returns on the government bond fund and the investment bond fund.

Column A contains the range for the third input of the averageif function, the range of values being averaged.

Column B is a dummy variable set to 1 if the return on the government bond fund is greater than return on the investment bond fund and 0 otherwise.

Column B contains the values for the first input of the averageif function, information on all available categories.

The second input of the average if function is the value of the category being averaged.

In this case the input is 1 for the average when government bond returns are greater than investment bond returns and 0 if the desired average is for the group where investment bond returns exceed government bond returns.

The calculations are laid out in the table below.


A
B
Diff Government Bond fund and Investment Grade Bond Fund
Dummy 1 if Diff>0, and ) otherwise
0.7%
1
-0.7%
0
9.2%
1
18.9%
1
8.9%
1
-2.3%
0
-2.5%
0
1.0%
1
2.6%
1
-0.7%
0
1.7%
1
-1.0%
0
Average if Returns on  Government Bood>Investment Grade Bond
6.1%
Note for Above Average
AVERAGEIF(B3:B14,1,A3:A14)
Average if Returns on  Government Bood<Investment Grade Bond
-1.4%
Note for Above Average
AVERAGEIF(B3:B14,0,A3:A14)


Brief Financial Discussion:   The gap between government bond returns and investment grade bond returns is much large when government bonds outperform investment bonds.   This supports my preference of the use of government bonds over investment grade bonds in a portfolio that includes equity.

Go here for a discussion of the broader finance issues.






No comments:

Post a Comment