0

say I have $X saved already and I will from now on save a constant amount each year (Amount Y). I also want to have amount Z saved in the future. The question is how long do I need to save?

I want to do the whole thing in excel with excel formulas if possible.

Mathematically

FV(X) + FVannuity(Y) = Z in N years.

Excels PDuration can solve the lump sum part and Excels NPER can solve the annuity. How do I combine these two to get one answer for N ?

Killerpixler
  • 101
  • 1
  • Are the payments are made at the beginning of the compounding period or the end for the annuity? Also, not sure this is the right venue for Excel questions. – Patrick Sep 18 '13 at 14:15
  • payments are made at the end of the period. Sorry. Where should i go for excel questions? – Killerpixler Sep 18 '13 at 14:21

1 Answers1

0

You can just solve the equation algebraically in terms of $X, Y, Z$ and $i$ which we will denote the interest rate by. Then you can implement the formula in Excel as you desire.

HINT: I'm guessing maybe you do not know the formula for a geometric series : $$a+ax+ax^2+ \cdots +ax^{n-1}$$ for some constant $a$. The link provided will give you a proof of the formula. Can you then use this to get the formula for the annuity in question? Then you should be able to solve for $n$. Your basic set up is correct (i.e. $FV(X) + FVannuity(Y) = Z$). You just need to substitute the "words" with algebraic expressions involving $X,Y,Z,n$ and $i$ and solve for $n$.

Patrick
  • 2,106