I received an email from Stacey a few weeks ago asking:
“Which formula would I use to find the initial investment amount required to have $15,000 at the end of 5 years, if my bank pays 4.75% interest, compounded monthly?”
The answer is the PV Function.
=PV(4.75%/12,60,0,-15000,0)
=$11,834.50
Excel’s PV Function
PV Function Syntax
=PV(rate,nper,pmt,[fv],[type])
Returns the present value of an investment: the total amount that a series of future payments is worth now.
Rate – is the interest rate per period.
I’ve divided 4.75%/12 as the interest is compounded monthly.
Nper – is the total number of payment periods in an investment.
Again, I’ve entered 60 months as interest is compounded monthly.
Pmt – is the payment made each period and cannot change over the life of the investment.
Stacey isn’t making any further payments so this is zero.
Fv – is the future value, or a cash balance you want to attain after the last payment is made.
This is Stacey’s goal amount.
Type – use 1 where the payment is at the beginning of the period or 0 for the end of the period.
There are no payments being made in Stacey’s example so this is irrelevant, in fact I could have omitted this argument altogether and the formula would look like this:
=PV(4.75%/12,60,0,-15000)
Of course if Stacey wanted to save a little extra each month, say $100 (at the beginning of each month) it would lessen the initial investment required by over $5,000.
=PV(4.75%/12,60,100,-15000,1)
=$6,482.03
Notice how the Pmt is a positive value and the Fv is negative.
This is necessary to keep in line with cash flow conventions and yield the correct result. You can choose which figure is negative, just so long as one is and one isn’t.
For example if you wanted your initial investment amount represented as a negative figure your formula would be like this:
=PV(4.75%/12,60,-100,15000,1)
Want to know how much you need to invest now to have $1m in 10 years at 4.75% saving $1000 each month? I did.
It’s $526,714.50.
And since I don’t have a spare half mil sitting around I’d best get back to work 🙂