Question

How would you do this problem using excels PV formula and the PMT function?

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

$18,497.43

Step-by-step explanation:

Enter the information given in the problem into a new Excel worksheet. The information you will need includes the present value (PV), future value (FV), number of periods (N), interest rate (I/Y), and periodic payment (PMT).

Calculate the future value (FV) of the savings you need at retirement. In this case, the FV is $1 million.

Calculate the number of periods (N) you have to save for retirement. In this case, N is 25 years.

Calculate the interest rate (I/Y) of the savings account. In this case, the interest rate is 8% compounded annually.

Calculate the periodic payment (PMT) that you need to make each year to reach your savings goal. To do this, use Excel's PMT function, with the following inputs:

Rate: 8% (the annual interest rate)

Nper: 25 (the number of years)

Pv: 0 (the present value, which is 0 because you have not yet made any deposits)

Fv: -$1,000,000 (the future value, entered as a negative value because it represents money you will owe)

Type: 0 (since the payments are made at the end of each period)

Use the PV formula to calculate the amount of the first deposit you need to make today. The PV formula is:

PV = PMT * [(1 - (1 + r)^-n) / r] + FV / (1 + r)^n

where:

PV is the present value of the deposits you need to make today

PMT is the periodic payment you need to make each year

r is the annual interest rate

n is the number of years

In this case, the PMT is calculated in step 5, r is 8%, n is 25, and FV is $1 million. Therefore, the PV formula becomes:

PV = PMT * [(1 - (1 + r)^-n) / r] + FV / (1 + r)^n

PV = $18,497.43