I'm trying to figure out how much to save each month to go from $100,000 today to $200,000 in 5 years. Is this possible? It seems the function requires a zero (as in a mortgage amortization) or a negative number (as if my $100,000 today was a DEBT). Can I do this if both PV and FV are positive whole numbers?
MacBook Pro 13″, macOS 12.0
Yes, time value of money problems can be confusing. Think of - as cash outflows and + as inflows.
The initial $100,000 is treated as an outflow (the initial investment comes out of your pocket) and is therefore entered as negative. The savings (investments) you contribute each period are also outflows and therefore negative. The interest earned each period and the future value of $200,000 are both inflows, and thus positive.
Assuming annual periods for a more compact illustration, the problem can be set up like this:
The formula in B7 that calculates the amount you need to save (invest) each month is:
=PMT(B2,B3,B4,B5,1)
(substitute ; for , in the formula if your region uses , as a decimal separator)
To satisfy yourself that this is the correct result you can map out the cumulative amounts in a simple table like Table 2, expressing all the numbers as positive.
You start with $100,000, add the amount invested (from the PMT function, with the sign reversed) and interest earned (sum of Starting + Invest multiplied by the interest rate) to derive the ending amount for that period. The Starting for the following period will be the Ending of the previous period. As you can see in Table 2 of the example, you end up with $200,000 at the end of five years.
To calculate the amount of monthly savings (investment) needed, simply use a monthly interest rate and 60 periods.
SG
Yes, time value of money problems can be confusing. Think of - as cash outflows and + as inflows.
The initial $100,000 is treated as an outflow (the initial investment comes out of your pocket) and is therefore entered as negative. The savings (investments) you contribute each period are also outflows and therefore negative. The interest earned each period and the future value of $200,000 are both inflows, and thus positive.
Assuming annual periods for a more compact illustration, the problem can be set up like this:
The formula in B7 that calculates the amount you need to save (invest) each month is:
=PMT(B2,B3,B4,B5,1)
(substitute ; for , in the formula if your region uses , as a decimal separator)
To satisfy yourself that this is the correct result you can map out the cumulative amounts in a simple table like Table 2, expressing all the numbers as positive.
You start with $100,000, add the amount invested (from the PMT function, with the sign reversed) and interest earned (sum of Starting + Invest multiplied by the interest rate) to derive the ending amount for that period. The Starting for the following period will be the Ending of the previous period. As you can see in Table 2 of the example, you end up with $200,000 at the end of five years.
To calculate the amount of monthly savings (investment) needed, simply use a monthly interest rate and 60 periods.
SG
Excellent explanation. Many thanks.
Financial functions in Numbers
