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.


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, privacy policy and cookie policy

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