Excel Future Value - Compounding Revisited

In the Penny Puzzle exercise, we saw how a penny that doubles each day for a month can amount to some serious cashish.   Did you know that there's a formula that investors use to calculate the future value of an investment that grows at a continual rate?  It's called (not surprisingly), the Future Value formula, or FV.

The equation for FV is as follows:

             FV = PV x (1 + r) ^t               

             where PV is the original amount of money invested (what you start with),

             r is the growth rate over time (usually a yearly or annual growth rate),

             and t is the number of periods that the investment compounds at (usually # of years)

     

    1) Can you write an Excel formula that will calculate the End of Day $ value on the last day of 

        the month (i.e. May 31st) in the case of the penny puzzle?            

In the real world, however, investments never have such alarming growth rates.  If ever you should come across an investment opportunity that promised you those kind of returns, you should probably run fast because it's more likely to be a scam than legitimate. 

In practice, annual investment returns are more likely to be anywhere from 2% - 10%.  Home prices (i.e. real estate), has historically shown growth rates of around 4% (without a mortgage) to about 15% with a mortgage - in stable economic conditions.   

Meanwhile, traditional investment allocations for retirement accounts typically get anywhere from 5% - 11%  

    2)   Let's try to calculate how much your investment in a home would be over 20 years, assuming 

          a growth rate of 6%.  Here's what the spreadsheet might look like:

Can you graph the value of your home over the course of these 20 years?

And, can you come up with a formula cell that can come up with the same answer as in the long method? 

    3)   Finally, let's calculate the Future Value of an investment of $10,000 that earns 7% per year for 40 years.  Can you come up with a one-cell calculation for this using the FV formula?

And that's it.  You've just completed the Excel compound growth or Future Value assignment.

Remember to Save Your Spreadsheet  using File/Save As     ExcelFV_FirstNameLastName     and attach it to a GMail that you will send to me at rwiegs@gmail.com 

Remember, if you get stuck with creating the table in Excel or whatever, please ask for help.  You can also come by during lunch period for one-one-one or small group assistance.