Using Excel to Estimate Your Future Investments

There are two Microsoft Excel functions that I have found invaluable in planning our future financial future.  The first function , FV() or Future Value outputs the future value of an investment based on periodic, constant payments and constant interest rates.  Meaning I have $10,000 to invest and I want to direct deposit $400 every month for the next 5 years and assume 7% interest, how much will I have?  The second function I use is POWER().  I use this one to understand the annualized return of an investment.  Think about FV() as looking into the future, and POWER() as analyzing the past.

Future Value Example

Lets look at FV() first using the example above.  You have $10,000 to invest in an index fund.  You also want to add $400 per month for the next 5 years.  We will assume our normal 7% rate of return.

=FV(.07/12,60,-400,-10000,0)

This investment will yield $42,813.41.

The general syntax for FV() is FV(rate,nper,pmt,pv,type).

• rate - interest rate (divide by 12 if the interest rate is per month)
• nper - number of payment periods in years (or months if dividing interest rate by 12)
• pmt - payment made each period (needs to be negative)
• pv - present value (initial investment, needs to be negative)
• type - 0 for payments at end of period, 1 for the beginning of the period.  (I usually use 0)

Power Example

Lets use the POWER() function to reverse our Future Investment example.  This one requires a little bit of math, but the basic structure is:

=POWER(Final Value/Initial Value, 1/Period)-1

More specifically =POWER(42813.41/10000,1/5)-1

Wait a second, this is 0.3376829 or about 33.77%.  Why didn't this work out to be 7%?  Oh yeah, we added $400 per month.  Be careful, this is your money we are slicing and dicing. 

Maybe we should use a different example.  Perhaps your house.  You bought it for $200,000 fifteen years ago.  You just sold it for $280,000.

=POWER(280000/200000,1/15)-1 or 0.02268496 or about 2.27% return.

Recall our multipliers from Impact of Saving a Few Chronic Dollars with Compound Interest?

• to calculate a weekly expense compounded over ten years, multiply the price by 752

• for a monthly expense, multiply by 173

Do they work?  Lets use an example of saving $100 per month, maybe from dropping cable.

=FV(0.07/12,120,-100,0,0) which yields $17,308.48

and if you divide $17,308.48 by 173 you get $100.05.  Pretty darn close!

Photo credit: SG2012 / Foter / CC BY