Mr. DebtFree here, today I am going to show you how to simply calculate the number of years you have left before retirement or financial independence in google sheets. With just a few simple button clicks you will know exactly when you can expect to be done working forever. I will also include myself as a case study for a real world example.

First there are some assumptions that must be made:

- Your spending will not increase in retirement.
- You must assume an interest rate for your investment gains. 7% is a good number for an S & P 500 fund like VOO or a Total US market fund like VTI.
- You must assume a safe withdrawal rate. We will use 4% which is a widely accepted value (google 4% rule if you are not familiar with this)
- You must know how much you spend and how much you will invest each month. I use my personal finance spreadsheet with the last year or two of data for this. Mint is also a good source.
- You must know the present value of your investments.

The NPER command is used as follows:

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

rate = your interest rate / 12. For this case 0.07/12

pmt = Your monthly payment, for this example it is how much you invest each month.

pv = Present Value, this is your current invested amount.

fv = Future Value, this is how much you need to retire.

type = this is set to either 0 or 1, 0 means the payment is due at the end of a period, 1 means it is due at the beginning of the period. This can also be left out of the formula and google sheets will assume a 0 value.

**Example: Mr. DebtFree**

rate = 0.07/12

pmt = $4329 this is how much money I have left each month after spending/taxes on average.

**This is entered into the equation as a negative number.**

pv = $157,953 is the amount currently in my retirement accounts.

**This is entered into the equation as a negative number.**

fv= To calculate your amount needed to retire we are going to take your average monthly spending, divide it by your withdrawal rate, and multiply it by 12.

Average monthly spending = $2630

Withdrawal rate = 0.04 or 4%

fv = 2630/0.04*12 = $789,000

Lets plug this information into the formula!

**=NPER(0.07/12,-4329,-157953,789000,1)/12**

We divide by 12 to convert this from months to years. Now copy and paste that information into the equation block in google sheets.

Viola, we see that Mr. DebtFree will be financially independent in 7.58 years. Just in time for my 40th birthday!

Play around with the numbers to find out when you will be financially independent. Note this does not assume you will be paying taxes in retirement. When planning be sure to account for the tax bracket you will be paying into.

Here is a simple google sheets example for you. To edit this sheet, go to file -> make a copy -> save it to your drive. Open up your copy and have fun playing with the numbers. Only edit the light green cells.

So, armed with this new information... how much longer until you can call yourself financially independent?

## No comments:

## Post a Comment