fbpx
Investments

How to Create your Own Retirement Calculator

How to Create your Own Retirement Calculator

Disclosure: This post contains affiliate links. If you click through and make a purchase, Iโ€™ll earn a commission, at no additional cost to you. Read my full disclosure here.

Disclosure: This post might contain affiliate links. If you click through and make a purchase, Iโ€™ll earn a commission, at no additional cost to you. Read my full disclosure here.

Spreadsheets are really powerful tools that can perform “magic” in the hands of a knowledgeable user. At the same time, they might seem complicated, hard-to-use and math-heavy for most people. In this post we’ll show how to create your own retirement calculator using 5 simple spreadsheet functions. You can use the embedded spreadsheet to do all the calculations or download the file and open it using Microsoft Excel (paid) or Google Sheets (which is free).

What questions will we focus on?

  1. How much money do I need to retire?
  2. How much money do I need to contribute in order to reach my financial target?
  3. If I invest $X/year, what should be my asset allocation, in order to hit my financial target?
  4. N years after the original plan, I have saved $Y. Can I retire early?
  5. How much money will I have X years after retirement?

Spreadsheet

This is the spreadsheet that answers all the above questions. Feel free to change the numbers in the yellow cells, based on your own financial situation. If you want also to dive deeper into the underlying assumptions and the corresponding calculations, you can also download the spreadsheet and import it to Excel or Google Sheets. I have also written detailed explanations for each caculation in the sections below.

Assumptions

  1. You are using a mix of a Total Stock Market Index Fund (e.g. VTSAX) and a Total Bond Market Index Fund (e.g. VBTLX)
  2. The inflation rate is 2% per year
  3. The stock market (VTSAX) will grow 6% per year and the bond market (VBTLX) will grow 2.5% per year (nominal values)
  4. The contributions to the retirement accounts are happening bi-monthly (twice a month).
    1. The bi-monthly growth rate is (1 + annual_rate)^(1/24) -1 and the values are shown in the following table (calculated using Excel or Google Sheets)
    2. The approximation annual_rate/24 is simpler and close enough, but not 100% accurate
Annual rateBi-monthly rate
Stocks6%0.243%
Bond2.5%0.103%

The 5 simple functions that we use

  1. FV: Calculates the Future Value of an investment based on a constant stream of additional payments and a constant interest rate (which increases the principal)
  2. PMT: Calculates the payment based on constant payments and a constant interest rate.
  3. NPER: Returns the number of payments required to reach a specific Future Value
  4. PV Calculates the present value of an investment based on a constant interest rate. 
  5. RATE: Returns the interest rate per period

The above functions use the following arguments

  1. rate: The interest rate per period
  2. nper: The total number of payment periods
  3. pmt: The payment made each period
  4. [optional] pv: The present value, or the lump-sum amount that a series of future payments is worth right now. If it’s omitted, then it’s assumed to be 0
  5. [optional] fv: The future value you want to reach after the last payment is made. If it’s omitted, then it’s assumed to be 0
  6. [optional] type: If it’s equal to 0, then the payments are due at the end of the period (default). If it’s equal to 1, then the payments are due at the beginning of the period

Financial questions

1. How much money do I need to retire?

This is the only question, where we won’t need to use one of the above functions. We can calculate the amount that you want to spend at your first year of retirement and multiply by a factor of 25x (if you’re feeling aggressive) to 33x (if you’re more conservative). Since the current stock market valuation is quite high, my recommendation is to use 33x.

For example, if you want to spend $100k at your first year at retirement, then you should plan on accumulating between $2.5M ($100k * 25) and $3.3M ($100 * 33). My recommendation is to plan for $3.3M in this scenario.

For the following scenarios, I’ll use $3M as your financial target, in order to simplify the calculations.

However, both the $3M (financial target) and the $100k (retirement spending) are expressed in today’s dollars. Due to inflation, these numbers will be higher in the future. Let’s assume that you want to retire 30 years from now. The corresponding amounts will be:

  • Financial Target: $3M*(1+2%)^30 = $5,434,084.75
    • The calculation FV(2%, 30, 0, -3000000) gives the same result
  • Retirement spending: $100k*(1+2%)^30 = $181,136.16
    • The calculation FV(2%, 30, 0, -100000) gives the same result

2. How much money do I need to invest in every paycheck in order to retire after 30 years? My financial target is $3M (today’s purchasing power), i.e. $5.4M (nominal amount, expressed in future $) and I currently have $200k in my accounts.

In order to answer this question we need to use the PMT function.

PMT(rate, nper, pv, [fv], [type])

2a. Let’s assume that you want to invest 100% in VTSAX (6% nominal annual growth, 0.243% nominal bi-monthly growth)

In order to calculate the amount in the investment account 30 years (30*24 paychecks) from now we will use FV and the nominal growth rate (6% annually, 0.243% bi-monthly).

=PMT(0.243%, 30*24, -200000, -5,434,084.75) = $2,196.06

This corresponds to $2,196.06 * 24 = $52,705.52 per year

So, if you add $2,196.06 to your account in every paycheck, then in 30 years you’ll be able to accumulate $5.4M , which will have the same purchasing power as $3M.

2b. Now let’s assume that I want to invest 80% in stocks (VTSAX) and 20% in bonds (VTBLT)

The new growth rates will be

80% * (stock growth rate) + 20% * (bond growth rate)

  1. Annual : 80%*6%+20%*2.5%=5.3%
  2. Bi-monthly: 0.22%

The bi-monthly contribution will be:

=PMT(0.22%, 30*24, 200000, -5,434,084.75) = $2,615.33

and the annual contribution will be $2,615.33 * 12 = $62,767.83

The following chart shows the total amount in the portfolio for the next 30 years.

How to Create your Own Retirement Calculator - Accumulation phase

3. If I invest $X/year, what should be my asset allocation, in order to hit my financial target?

As we saw in the above example, the asset allocation changes the rate. Let’s assume instead of of annual contributions of $62,767.83 (which was calculated in the previous section) we can contribute $70,000/year which means that each contribution will be $2,916.67.

We will use the FV function.

FV(rate,nper,pmt,[pv],[type])

If we make no other changes, then this means that we will accumulate:

=FV(0.243%, 30*24, -2,916.67, -200000) = $6,840,266.44

However, another aspect would be to calculate the required asset allocation, if we want to reach the same financial target as before ($5,434,084.75). In order to do this we’ll have to find the corresponding interest rate using the RATE function.

RATE(nper, pmt, pv, [fv], [type], [guess])

=(1-RATE(24*30,$2,916.67,-$5,434,084.75,-200000))^30 – 1 = 4.8%

Now we know that X% of the allocation will be in stock and (1-X)% in bonds.

By doing the math we get that 66% should be in stocks and 34% in bonds.

N years after the original plan, I have saved $Y. Am I falling behind the initial schedule or am I ahead?

Let’s assume that 10 years from the initial calculation have passed, i.e. there are 20 more years remaining. We can calculate how much money you should have saved, based on the initial plan using the PV function.

PV(rate, nper, pmt, [fv], [type])

=PV(0.243%, 20*24, 2,615.33, -5,434,084.75, -200000) = $1,072,640.79

If you’ve saved more than $$1,072,640.79, you’re ahead of schedule.

Let’s assume that you’ve saved $1.5M and you’re trying to figure out when you’ll be able to retire. We’ll use the NPER function.

NPER(rate,pmt,pv,[fv],[type])

=-NPER (0.243%, 2,615.33, -5,434,084.75, -200000 )/24 = 16.6 years later

5. How much money will I have X years after retirement?

At retirement your asset allocation will change. Also, the contributions will be lower (if any). Let’s assume that:

  1. You have accumulated your initial target ($5,434,084.75)
  2. You new asset allocation is 60% stocks and 40% bonds
    1. Growth rate will be 60%*0.243% + 40%*0.109% = 0.19%
  3. Every year you will withdraw the amount that was initially planned ($181,136.16).

Now you want to find out how much money you’ll have 10 years after retirement, assuming no additional contributions.

=FV(0.19%, 10*24, 181,136.16/24, -5,434,084.75) = $6,225,512.69

The following chart shows the total amount in the portfolio for the next 30 years.

How to Create your Own Retirement Calculator - Distribution phase

Recommended Articles

Leave a Reply

%d bloggers like this: