Author Topic: Excel Help - Need a formula  (Read 2656 times)

thelamb

  • 5 O'Clock Shadow
  • *
  • Posts: 95
  • Age: 44
  • Location: Columbus, OH
Excel Help - Need a formula
« on: January 04, 2014, 09:19:49 AM »
x = current amount saved
y = years till goal
z = goal amount
w = yearly interest

v = amount would need to save each year to meet goal. 

Anybody have the excel formula to calculate v? 

Thanks!

MilStachian

  • 5 O'Clock Shadow
  • *
  • Posts: 94
  • Location: New England
Re: Excel Help - Need a formula
« Reply #1 on: January 04, 2014, 09:27:18 AM »
This website is great for Excel financial formulas.

http://www.excel-easy.com/functions/financial-functions.html

thelamb

  • 5 O'Clock Shadow
  • *
  • Posts: 95
  • Age: 44
  • Location: Columbus, OH
Re: Excel Help - Need a formula
« Reply #2 on: January 04, 2014, 09:47:27 AM »
Thanks.  Figured it out, was just the pmt function.  I feel stupid now. 

marty998

  • Walrus Stache
  • *******
  • Posts: 7372
  • Location: Sydney, Oz
Re: Excel Help - Need a formula
« Reply #3 on: January 04, 2014, 03:12:47 PM »
For the really bored:

This problem is a geometric series/progression,

initial amount (P) is invested for n years, r being the interest rate (decimal - e.g. .05 for 5%)

end value of initial amount = P(1+r)^n

assume your yearly saved amount (v) is invested at the start of each year (problem is slightly different for end of year investment, just divide (Z) below by (1+r))

1st year savings = v(1+r)^n
2nd year savings = v(1+r)^(n-1)
3rd year savings = v(1+r)^(n-2)
.
(n-1)th year savings = v(1+r)^(n-(n-2)) = v(1+r)^2
nth year savings = v(1+r)^(n-(n-1)) = v(1+r)^1

If we reverse the order, you can see that each amounted is invested for (1+r) times more than the next year (basic compounding).

sum of all yearly savings is a geometric progression. For simplicity, take the 1st term (A) as v(1+r)^1 and the multiplyer as (1+r) for n years.

Sum (Z) = A(((1+r)^n)-1)/(r)
=

Total = P + Z