Let's say I want to generate $100,000 over 5 years at 9% interest compounding yearly.

Is there a function or formula that will let me calculate the amount to be invested per month to reach that goal in that period of time?

I looked into the financial functions here, this one looked similar but doesnt do what I want.

1 Answer 1


The command PMT can be used for this. Specifically,

=PMT(0.09, 5, 0, -100000)

returns $16,709.25. I checked that the amount is correct:

  • $16,709.25 after 1 year (investment only)
  • $34,922.32 after 2 years (investment + 9% interest on 16,709.25)
  • $54,774.58 after 3 years (investment + 9% interest on 34,922.32)
  • $76,413.54 after 4 years
  • $100,000.00 after 5 years

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.