Question

I need to see this work done in excel please using formulas. Thank you!

4) You are saving for retirement in 20 years. Today, you place $100,000 in a bank account that pays 4% interest, compounded annually, leaving the funds on deposit for the entire 20 years. You also contribute $2,000 each year into your pension plan for 10 years, beginning this year. The pension plan grows at 7% a year. How much do you have available for retirement, 20 years from now? BE CAREFUL; this is a multi-step problem.

Answer 1

To solve this formula in excel, we can using fv formula. This formula will do all magic for us.

FV stands for future value,

Syntax for it is FV(rate,nper,pmt,[pv],[type])

rate is the interest rate per period. So if we compound quaterly then we have divide annual rate by 4

nper = No. of payments we are making in case of annuity when we use this formula as a compound interest formula then it denotes no. of periods. For e.g say loan is at 5 year compounded semiannually then nper = 10

pmt: In case of annuity,payments per period

pv: present value

Solution is:

Present Value( Money in Bank Account)	$100,000
Interest Rate	4%
Compounding periods per year	1
Time	20
Money after 20 years	$219,112.31
For Retirement Plan
Contribution	2000
Total years contribution	10
Rate	7%
Compounding periods per year	1
Money after 10 years	$27,632.90
Money after 20 years	$54,358.09
Total Money At time of Retirement(Bank saving + Retirement Plan Saving)	$273,470.40

Below screenshot shows the formulas

Use below table to copy paste formula in excel.

Present Value( Money in Bank Account)	100000
Interest Rate	0.04
Compounding periods per year	1
Time	20
Money after 20 years	=FV(B3/B4,B5*B4,0,-B2)
For Retirement Plan
Contribution	2000
Total years contribution	10
Rate	0.07
Compounding periods per year	1
Money after 10 years	=FV(B11,B10,-B9)
Money after 20 years	=FV(B11/B12,(20-B10)*B12,0,-B13)
Total Money At time of Retirement(Bank saving + Retirement Plan Saving)	=B6+B14