Monday, January 11, 2016

Calculation of Rates of Return In Excel



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 %




Authors Note: 

The author has written a book solving 15 financial problems in Excel.   The book is available on Kindle.




This book solves fifteen practical finance problems in Excel spreadsheets. Topics covered include -- (1) selecting a type of mortgage, (2) the cost of bad credit, (3) calculating the accumulation of house equity, (4) calculation of repayment periods on credit cards, (5) the value of the mortgage tax deduction, (5) the impact of student debt and consumer loans on the mortgage qualification decision, (6) the impact of the sequence of market returns on your 401(k) balance, and (7) the relative cost (including fuel consumption) of the Toyota Prius and the Toyota Corolla.

No comments:

Post a Comment