Question

Clipboard Font Alignment A11 B 1 2 DELFI G H I On June 1, 2008 you invested $2,750 into a stock mutual fund. You then invested $1,750 into that same mutual fund on the first day of June for each of the years 2009 through 2019. You averaged a 7.5% annual rate of return on your investment in this mutual fund. What is the value of your investment in this mutual fund on June 1, 2019? You must use an Excel function to answer this question in order to earn full credit. Format your answer as currency, and show the cents. 4 14 15 16 17

I need this answered in Excel step by step please and using the corresponding Excel Functions.

Answer 1

To solve this problem, we will use FV function in Excel.

The formula for FV function is as follows :-

=FV (rate, nper, pmt)

Rate = Annual Rate of Return earned on the Investment

nper = Total number of periods

pmt = Payment made each period

In this problem, the investor makes the following series of Investments:-

Particulars 1 June, 2008 1 June, 2009 1 June, 2010 1 June, 2011 1 June, 2012 1 June, 2013 1 June, 2014 1 June, 2015 1 June, 2016 1 June, 2017 1 June, 2018 1 June, 2019 Amount $2,750.00 $1,750.00 $1,750.00 $1,750.00 $1,750.00 $1,750.00 $1,750.00 $1,750.00 $1,750.00 $1,750.00 $1,750.00 $1,750.00

Let us calculate the Future Value of the Annual Investment $1,750 made for 11 years from 2009 to 2019, which yields 7.5%, using FV function. Attaching 2 excel snap shots, one showing final value and the other showing formula used:-

PMT Rate nper $1,750.00 7.50% FV ($28,364.21)

PMT Rate nper 1750 0.075 FV =FV(C19,C20, C18)

For the initial investment of $2,750 made in June 2008, the total return will be :-

$ 2,750 * (1+0.075)^11 = $ 6,093

Adding both values :- ( $6,093 + $ 28,364) = $ 34,457

Hence, value of this Investment in 2019 = $ 34,457