Google Sheets – Calculate Monthly Investment for Desired Amount Using Compound Interest

google sheets

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.

Best Answer

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

Related Solutions

Google Sheets – How to Calculate Compound Interest with Different Interest Rates

You can use PRODUCT to provide the product of a range of values, eg:

=PRODUCT(C2:C201)

And if you wanted the compound interest effective at each month, then in row 2:

=PRODUCT(C$2:C2)

and fill down as far as required.

Google Sheets – Calculate Compound Interest Rate

Yes, and it's called RATE. Example of usage:

=RATE(60,0,-100,150)

calculates the percentage required for growing $100 to $150 over 60 interest payment periods, while taking out $0 each period. The answer is 0.68%. Wolfram Alpha agrees.

The complete list of financial functions in Google Spreadsheets is here.

Best Answer

Related Solutions

Google Sheets – How to Calculate Compound Interest with Different Interest Rates

Google Sheets – Calculate Compound Interest Rate

Related Topic