CHAPTER $3,$ CONCEPTS IN ACTION $3$: RETIREMENT CALCULATOR In this Excel tutorial, we try to replicate a retirement calculator that is often found on investing websites.

A popular feature in online business websites is a retirement calculator. With these tools, an investor can make a reasonable estimate of their retirement standing. Often, these calculators make assumptions and then ask for a few inputs from the investor. In this Concepts in Action, we will simulate the standard retiremen

Our calculator will simplify the world in that we will ignore taxes. Our goal is then to calculate what we will accumulate prior to retirement and then compare it to what we will need in retirement. This is also a useful exercise because we introduce the growing annuity concept in Excel.

For a growing annuity, we assume that our payments grow by a constant percentage each period. This is reasonable if I am modeling retirement as we assume that person's salary might grow by a fixed percentage over time. Another application would be capital budgeting if I let the sales of a product grow by a constant percentage each year. We will define, $g,$ as the fixed rate that our cash flows grow over time. The result, here, is that each payment $($PMT$)$ grows by g per

$PV={\displaystyle \underset{t=0}{\overset{t=T}{}}}\frac{F{V}_t}{(1+r{)}^t}=\frac{PMT}{(1+r{)}^1}+\frac{PMT}{(1+r{)}^2}+$cdots$+\frac{PMT}{(1+r{)}^T}$

$PV={\displaystyle \underset{t=0}{\overset{t=T}{}}}\frac{F{V}_t}{(1+r{)}^t}=\frac{PMT}{(1+r{)}^1}+\frac{(1+g)xPMT}{(1+r{)}^2}+$cdots$+\frac{(1+r{)}^{T-1}xPMT}{(1+r{)}^T}$

Again, we have a geometric series that can be reduced through substitution:

$PV=\frac{PMT}{r-g}x[1-(\frac{1+g}{1+r}{)}^N]$

For our formula to work in application, the interest rate must exceed the growth rate or $r>g.$ Without this condition met, the present value will be negative. This cash flow. This does not make economic sense. The future value of a growing

annuity can be found by subbing into the lump sum formula. The future value is then:

$FV=\frac{PMT}{r-g}x[(1+r{)}^N-(1+g{)}^N]$

The challenge for us is that there is no canned formula for growing annuity in Excel. We are doing this activity to work with this formula without having to calculate the year$-$by$-$year balance of an account. Let's set up our assumptions for our retirement problem:

$\backslash$table$[[$INPUTS:$,],[$Current Age,$35],[$Retirement Age,$67],[$Amount Saved So Far,$$200,000$