Question

How would you approach this using the excel functions of pv and pmt?

Here's the question:
You want to accumulate $1 million by your retirement date, which is 25 years from now. you will make 25 deposits in your bank, with the first occurring today. The bank pays 8% interest, compounded annually. You expect to receive annual raises at 3%, which will offset inflation, and you will let the amount you deposit each year also grow by 3% (i.e, your second deposit will be 3% greater than your first, the third will be 3% greater than the second, etc.) How much must your first deposit be if you are to meet your goal.

Answer 1

To approach this using Excel functions, we can use the PV (present value) and PMT (payment) functions to calculate the amount of the first deposit. Here are the steps:

1. Calculate the future value of the $1 million using the FV (future value) function:

=FV(8%, 25, 0, -1000000)

This returns a value of $4,660,962.91, which is the amount that will accumulate in 25 years at 8% interest.

2. Calculate the present value of the future amount using the PV function:

=PV(8%-3%, 25, -1*FV(8%, 25, 0, -1000000), 0, 1)

This returns a value of $1,257,407.11, which is the amount that needs to be deposited today to accumulate to $4,660,962.91 in 25 years at 8% interest with 3% annual raises.

3. Calculate the payment amount using the PMT function:

=PMT(8%, 25, -1*PV(8%-3%, 25, 0, 1257407.11, 1), 0, 1)*(1+3%)

This returns a value of $68,527.78, which is the amount of the first deposit.

So, the first deposit should be $68,527.78 to accumulate $1 million in 25 years with 8% interest, 3% annual raises, and 3% increase in deposit each year.