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? 

Data:

Price of Vanguard Emerging Market Price
at Two Dates
Date
Adjusted Close Price
of VEiEX
5/4/94
7.0
1/4/16
19.3


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.




Analysis:  

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
Date
Month
Adjusted Close Price of VEiEX
5/4/94
0
7.0
1/4/16
260
19.3



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


=0.003908

annualize by multiplying by 12 and get

0.0469


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


or


4.792 %





No comments:

Post a Comment