looking for formula to calculate initial investment that will grow to $50K after 10 years

As a Christmas present, my father asked me to find an excel formula he 'used to know' many years ago.

I consider myself fairly fluent in excel, but apparently not at all in accounting, as I am totally stumped.

His question: How much do I need to invest to have $50K in 2022

He then gave me an array in excel, which starts with $3160, has an annual 6% increase in savings every year, a variable interest rate, and arrives at a cumulative investment.

He uses arrays like this and fiddles with the E9 cell until the O16 cell is $50K (see attachment)example.xlsx. He says years ago someone he worked with showed him a simple formula that does this... I have been playing with all sorts of net present value financial functions, but none really capture the complexity.

Any ideas out there? he is using excel 2010.

thanks in advance if possible, and i hope this is the correct thread to post this question to.

Hi farmedgirl!

There's a problem with your question. So let's clarify your intentions.

How much do you have in the account now? Is that $3160?

How much are you going to pay into the account at the beginning of each year? I assume that is x and that x will increase at 6% per year.

What rate of interest does the account pay?

I assume you are after $50000 at the end of 2022?

I also need clarification on when the first payment is to be made (now or after a year) and when the end of the investment period is.



My problem is that I have too many unknown variables and therefore I get multiple possible solutions.

Fix the interest rate and I can calculate the payment to be made now.

Fix the payment to be made now and I'll calculate the required rate of return.

It looks like it involves the initial balance accumulating plus the sum of a geometric progression and I get a tentative formula:

[3160 * (1 + i)^10] + [x * (1 - r^10)/(1 - r)] = 50000

r seems to be (1+.06)(1+i)

That leaves me with two unknowns i = rate of return and x = the amount of initial payment.


Give me one of those and I think there will be an explicit solution.

Hope that helps towards a solution.


Norman Harker

thank you everyone for your help! the goal seek did the trick and made my dad's christmas! thanks again and happy holidays!

thank you thank you! goal seek was what my dad was looking for! happy holidays!

Hi farmed girl!

Can you post a copy of your solution?

For an unknown i the solution is possibly not explicit but for the beginning deposit it probably is explicit.

Regards

Norman Harker