Using the Averageif function in Excel
Question: Below are twoyear 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 twoyear 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