Excel 2003 - help with formula please

[Guest]

I want to create a projection, ie
now I have $1,000 @ 5%pa. How much do I need to add per week or per month
so that in 10 years time I have $1million? I have created my own
spreadsheet, but feel sure there must be a simpler way.
Many thanks. Trish

 

[Jason]

Trish,
It sounds like you could use the FV function and the solver in Excel
to accomplish this. Here's how it would work:

In A1 put the rate of return: 5%
In A2 put the number of payments: 120 (or 520 if you want to use
weeks)
In A3 put the amount of each payment: $500 (this will change)
In A4 put the present value: $1000
In A5 put this formula: =-FV(A1/12,A2,A3,A4) or =-FV(A1/52,A2,A3,A4)
if you are using weekly payments
In A6 put your target value: $1,000,000
In A7 put this formula: =A5-A6

Now open the Solver by clicking Tools --> Solver. In the Solver
window, set the field labeled "Set Target Cell" to A7. In the field
labeled "Equal to:", select Equal to 0. Set the field "By Changing
Cells:" to A3. Click the Solve button. In the Solver Results window,
click OK. Cell A3 will now have the amount you need to add in each
time period to achieve your goal in 10 years.

Hopefully, this is what you were looking for.