Tuesday, July 30, 2019

Calculation of Rates of Return In Excel

This post discusses the concept of rate of return using adjusted close stock prices with both monthly and annual compounding.

Question:   The table below has the adjusted closing price of Vanguard’s emerging market fund (VEIEX) at two dates  -- the date of the fund’s inception and the opening date of 2016.   What is the annualized return on this fund assuming that returns are compounded monthly? 


Price of Vanguard Emerging Market Price
at Two Dates
Adjusted Close Price
of VEiEX

The data used here was also used in a post on whether the VEIEX fund is a suitable product for investors attempting to diversify and minimize risk associated with the U.S. market.    That post can be found here.


The monthly rate of return r that leads to VEIEX going from 7.0 to 19.3 months over this time span can be obtained from the equation

F=S x (1+rm)n    

Where F is 19.3, S is 7.0 and n is the number of elapsed months.

I need to find the number of elapsed months.

To do this I went back to the original database with all data on all months, created a column variable called month, and numbered the months from 0 to n.

The first and last row of this database looks like this

VEIEX Price Data with
Month Column
Adjusted Close Price of VEiEX

Here 260 is the number of elapsed months.

Note on Calculation of Elapsed Months:  The number of elapsed months could also be calculated by placing date information into the month and year functions.  Interested reader should go here.

 So solve for the rate of return

rm = (19.3/7.0) (1/260)  - 1


annualize by multiplying by 12 and get


So our answer is 4.69%.

A Technical Note:

The annual interest rate calculated here of 4.69% is the interest rate when interest is compounded monthly.   What if interest was not compounded monthly?  What if it was compounded annually?

260 months is the same as 21.67 years.

The annual interest rate compounded annually is

r = (19.3/7)(1/21.67) -1


4.792 %

No comments:

Post a Comment