Question

A young engineer's starting salary is $55,000. The engineer expects annual raises of 3%. The engineer will deposit 10% of the annual salary at the end of each year in a savings account. What must the savings interest rate be so that there will be $150,000 in the savings account after 15 years? Show what excel formula was used to solve.

Answer 1

Answer 2

Given:

• Starting salary (P): $55,000

• Annual raise: 3%

• Deposit rate: 10% of salary (end of each year)

• Time period (n): 15 years

• Desired future value (FV): $150,000

• 
  

Step 1: Calculate annual deposits (geometric growth due to raises)
Each year's deposit = Previous year's deposit × 1.03
First deposit (Year 1) = $55,000 × 10% = $5,500

Step 2: Future value of growing annuity formula
The future value of deposits with annual growth (g = 3%) and interest rate (r) is:

$$FV=\text{First Deposit}\times \frac{(1+r{)}^n-(1+g{)}^n}{r-g}$$

We solve for $r$ such that $FV=\mathrm{\$}150,000$.

Step 3: Use Excel's RATE function
Since deposits grow annually, we adjust the calculation using:

=RATE(n, -First Deposit, 0, FV, 0, g)

Inputs:

• nper = 15

• pmt = -5500 (negative for cash outflow)

• pv = 0

• fv = 150000

• type = 0 (end-of-period deposits)

• guess = 0.05 (initial guess for iterative solver)

• 
  

Excel Formula:

= RATE(15, -5500, 0, 150000, 0, 0.03)

Answer:

The savings interest rate must be $\boxed{{\displaystyle 5.56\mathrm{\%}}}$